Project_PlatForm/000_通用版本/004_收银管理系统/EShangPOS-浙江版本-5.1/libEShangPB/SyBaseHelper.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using iAnywhere.Data.SQLAnywhere;

namespace libEShangPB
{
    [ComVisible(false)]
    public class SyBaseHelper
    {
        private static readonly string connstring = "host=127.0.0.1;server=" + System.Net.Dns.GetHostName() + ";userid=dba;password=sql;Connect Timeout=3;";
        public static bool TestConnect()
        {
            using (SAConnection conn = new SAConnection(connstring))
            {
                try
                {
                    conn.Open();
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 查询本地数据库
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public static DataSet QueryOdbc(string SqlString)
        {
            using (SAConnection conn = new SAConnection(connstring))
            {
                SACommand cmd = new SACommand(SqlString, conn);
                try
                {
                    conn.Open();
                    SADataAdapter adp = new SADataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds);
                    conn.Close();
                    return ds;
                }
                catch (SAException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 查询本地数据库
        /// </summary>
        /// <param name="SqlString"></param>
        /// <returns></returns>
        public static DataSet QueryOdbc(string SqlString, string TableName = "Template")
        {
            using (SAConnection conn = new SAConnection(connstring))
            {
                SACommand cmd = new SACommand(SqlString, conn);
                try
                {
                    conn.Open();
                    SADataAdapter adp = new SADataAdapter(cmd);
                    DataSet ds = new DataSet();
                    adp.Fill(ds, TableName);
                    conn.Close();
                    return ds;
                }
                catch (SAException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="SQLString"></param>
        /// <param name="SQLParameter"></param>
        public static void ExecuteSqlTran(string SQLString, SAParameter SQLParameter)
        {
            using (SAConnection conn = new SAConnection(connstring))
            {
                conn.Open();
                SACommand cmd = new SACommand
                {
                    Connection = conn
                };
                SATransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    if (SQLString.Trim().Length > 1)
                    {
                        cmd.CommandText = SQLString;
                        cmd.Parameters.Add(SQLParameter);
                        cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
                catch (Exception E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="SQLStringList"></param>
        public static void ExecuteSqlTran(List<string> SQLStringList)
        {
            using (SAConnection conn = new SAConnection(connstring))
            {
                conn.Open();
                SACommand cmd = new SACommand
                {
                    Connection = conn
                };
                SATransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (SAException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="SQLStringList"></param>
        public static int ExecuteSqlTran(string SQLString)
        {
            int int_ExecuteNumber = 0;
            using (SAConnection conn = new SAConnection(connstring))
            {
                conn.Open();
                SACommand cmd = new SACommand
                {
                    Connection = conn
                };
                SATransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    if (SQLString.Trim().Length > 1)
                    {
                        cmd.CommandText = SQLString;
                        int_ExecuteNumber = cmd.ExecuteNonQuery();
                    }
                    tx.Commit();
                }
                catch (SAException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    conn.Close();
                }
                return int_ExecuteNumber;
            }
        }

        #region 方法 -> 数据值校验
        /// <summary>
        /// 数据值校验
        /// </summary>
        /// <param name="dataValue">待校验值</param>
        /// <param name="dataType">待校验值类型</param>
        /// <returns></returns>
        public static string DataValueCheck(string dataValue, CheckType dataType)
        {
            string _strResult = "NULL";
            switch (dataType)
            {
                case CheckType.String:
                    if (!string.IsNullOrWhiteSpace(dataValue))
                    {
                        _strResult = "'" + dataValue + "'";
                    }
                    break;
                case CheckType.DateTime:
                    try
                    {
                        _strResult = "DATETIME('" + DateTime.Parse(dataValue).ToString() + "')";
                    }
                    catch
                    {
                        _strResult = "NULL";
                    }
                    break;
                case CheckType.Decimal:
                    try
                    {
                        _strResult = decimal.Parse(dataValue).ToString();
                    }
                    catch
                    {
                        _strResult = "NULL";
                    }
                    break;
            }
            return _strResult;
        }

        /// <summary>
        /// 数据类型
        /// </summary>
        public enum CheckType
        {
            String = 0,
            Decimal = 1,
            DateTime = 2
        }
        #endregion
        #region 方法 -> 获取下一序号
        /// <summary>
        /// 获取下一序号
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="seqColumn">ID列字段名</param>
        /// <param name="whereSql">查询条件</param>
        /// <returns></returns>
        public static int CreateNextSequence(string tableName,
            string seqColumn, string whereSql = "1=1")
        {
            try
            {
                return int.Parse(QueryOdbc(
                    $"SELECT MAX({seqColumn}) FROM {tableName} WHERE {whereSql}"
                    ).Tables[0].Rows[0][0].ToString()) + 1;
            }
            catch
            {
                return 1;
            }
        }
        #endregion
    }
}