using Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Model;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;

namespace DataBase
{
    public class SqlServerHelper :IDbHelper, IDisposable
    {
        protected LogHelper Logger = new LogHelper("SQLServerLog");
        private DbCommand dbCommand = null;
        protected string connectionString = "";
        private static object locker = new object();
        private SqlDatabase db = null;

        public DbCommand DbCommand
        {
            get
            {
                return this.dbCommand;
            }
            set
            {
                this.dbCommand = value;
            }
        }

        public SqlServerHelper(string connString)
        {
            this.connectionString = connString;
        }

        public SqlDatabase GetDatabase()
        {
            SqlDatabase result;
            if (this.db == null)
            {
                if (ConfigHelper.GetAppSettings("ConStringEncrypt") == "true")
                {
                    this.db = new SqlDatabase(DESEncrypt.Decrypt(this.connectionString));
                }
                else
                {
                    this.db = new SqlDatabase(this.connectionString);
                }
                result = this.db;
            }
            else
            {
                lock (SqlServerHelper.locker)
                {
                    result = this.db;
                }
            }
            return result;
        }

        protected void AddInParameter(DbCommand cmd, SqlParam[] _params)
        {
            if (_params != null)
            {
                for (int i = 0; i < _params.Length; i++)
                {
                    SqlParam _param = _params[i];
                    DbType type = DbType.AnsiString;
                    if (_param.FiledValue is DateTime)
                    {
                        type = DbType.DateTime;
                    }
                    this.GetDatabase().AddInParameter(cmd, _param.FieldName.Replace(":", "@"), type, _param.FiledValue);
                }
            }
        }

        protected void AddInParameter(DbCommand cmd, Hashtable ht)
        {
            if (ht != null)
            {
                foreach (string key in ht.Keys)
                {
                    if (key == "Msg")
                    {
                        this.GetDatabase().AddOutParameter(cmd, "@" + key, DbType.AnsiString, 1000);
                    }
                    else
                    {
                        this.GetDatabase().AddInParameter(cmd, "@" + key, DbType.AnsiString, ht[key]);
                    }
                }
            }
        }

        protected void AddMoreParameter(DbCommand cmd, Hashtable ht)
        {
            if (ht != null)
            {
                foreach (string key in ht.Keys)
                {
                    if (key.StartsWith("OUT_"))
                    {
                        string tmp = key.Remove(0, 4);
                        this.GetDatabase().AddOutParameter(cmd, "@" + tmp, DbType.AnsiString, 1000);
                    }
                    else
                    {
                        this.GetDatabase().AddInParameter(cmd, "@" + key, DbType.AnsiString, ht[key]);
                    }
                }
            }
        }

        public SqlParam[] GetParameter(Hashtable ht)
        {
            SqlParam[] _params = new SqlParam[ht.Count];
            int i = 0;
            foreach (string key in ht.Keys)
            {
                _params[i] = new SqlParam("@" + key, ht[key]);
                i++;
            }
            return _params;
        }

        public object GetObjectValue(StringBuilder sql)
        {
            return this.GetObjectValue(sql, null);
        }

        public object GetObjectValue(StringBuilder sql, SqlParam[] param)
        {
            object result;
            try
            {
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(this.dbCommand, param);
                result = this.db.ExecuteScalar(this.dbCommand);
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------根据SQL返回影响行数-----------\r\n",
					sql.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public int ExecuteBySql(StringBuilder sql)
        {
            return this.ExecuteBySql(sql, null);
        }

        public int ExecuteBySql(StringBuilder sql, SqlParam[] param)
        {
            int num = 0;
            try
            {
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(this.dbCommand, param);
                using (DbConnection conn = this.db.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(this.dbCommand, trans);
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        num = -1;
                        this.Logger.WriteLog(string.Concat(new object[]
						{
							"-----------根据SQL执行,回滚事物-----------\r\n",
							sql.ToString(),
							"\r\n",
							e.Message,
							"\r\n返回值",
							num,
							"\r\n"
						}));
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new object[]
				{
					"-----------执行sql语句服务器连接失败-----------\r\n",
					e.Message,
					"\r\n返回值",
					num,
					"\r\n"
				}));
            }
            return num;
        }

        public int BatchExecuteBySql(StringBuilder[] sqls, object[] param)
        {
            int num = 0;
            StringBuilder sql_Log = new StringBuilder();
            int result;
            try
            {
                using (DbConnection connection = this.GetDatabase().CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        for (int i = 0; i < sqls.Length; i++)
                        {
                            StringBuilder builder = sqls[i];
                            sql_Log.Append(builder + "\r\n");
                            if (builder != null)
                            {
                                SqlParam[] paramArray = (SqlParam[])param[i];
                                DbCommand sqlStringCommand = this.db.GetSqlStringCommand(builder.ToString());
                                this.AddInParameter(sqlStringCommand, paramArray);
                                num = this.db.ExecuteNonQuery(sqlStringCommand, transaction);
                            }
                        }
                        transaction.Commit();
                        connection.Close();
                        result = num;
                        return result;
                    }
                    catch (Exception exception)
                    {
                        num = -1;
                        transaction.Rollback();
                        this.Logger.WriteLog(string.Concat(new object[]
						{
							"-----------批量执行sql语句-----------\r\n",
							sql_Log.ToString(),
							"\r\n",
							exception.Message,
							"\r\n返回值",
							num,
							"\r\n"
						}));
                    }
                }
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new object[]
				{
					"-----------批量执行sql语句服务器连接失败-----------\r\n",
					e.Message,
					"\r\n返回值",
					num,
					"\r\n"
				}));
            }
            result = num;
            return result;
        }

        public DataTable GetDataTable(string TargetTable)
        {
            StringBuilder sql = new StringBuilder();
            DataTable result;
            try
            {
                sql.Append("SELECT * FROM " + TargetTable);
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                result = ReaderToIListHelper.DataTableToIDataReader(this.db.ExecuteReader(this.dbCommand),"");
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------获取数据集DataTable-----------\r\n",
					sql.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public DataTable GetDataTable(string TargetTable, string orderField, string orderType)
        {
            StringBuilder sql = new StringBuilder();
            DataTable result;
            try
            {
                sql.Append(string.Concat(new string[]
				{
					"SELECT * FROM ",
					TargetTable,
					" ORDER BY ",
					orderField,
					" ",
					orderType
				}));
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                result = ReaderToIListHelper.DataTableToIDataReader(this.db.ExecuteReader(this.dbCommand),"");
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------获取数据集DataTable-----------\r\n",
					sql.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public DataTable GetDataTableBySQL(StringBuilder sql)
        {
            return this.GetDataTableBySQL(sql, null,"");
        }

        public DataRow GetDataRowBySQL(StringBuilder sql) 
        {
            DataTable dt = this.GetDataTableBySQL(sql, null, "");
            if (dt != null && dt.Rows.Count >0) 
            {
                return dt.Rows[0];
            }
            return null;
        }

        public DataTable GetDataTableBySQL(StringBuilder sql,string tableName)
        {
            return this.GetDataTableBySQL(sql, null, tableName);
        }

        public DataTable GetDataTableBySQL(StringBuilder sql, SqlParam[] param, string tableName)
        {
            DataTable result;
            try
            {
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(this.dbCommand, param);
                result = ReaderToIListHelper.DataTableToIDataReader(this.db.ExecuteReader(this.dbCommand), tableName);
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------获取数据集DataTable-----------\r\n",
					sql.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public DataTable GetDataTableProc(string procName, Hashtable ht)
        {
            DataTable result;
            try
            {
                this.dbCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(this.dbCommand, ht);
                using (DbConnection conn = this.db.CreateConnection())
                {
                    conn.Open();
                    DbTransaction trans = conn.BeginTransaction();
                    result = ReaderToIListHelper.DataTableToIDataReader(this.db.ExecuteReader(this.dbCommand),"");
                }
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------执行一存储过程DataTable-----------\r\n",
					procName.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public DataTable GetDataTableProcReturn(string procName, Hashtable ht, ref Hashtable rs)
        {
            DataTable result;
            try
            {
                this.dbCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddMoreParameter(this.dbCommand, ht);
                DataSet ds = this.db.ExecuteDataSet(this.dbCommand);
                rs = new Hashtable();
                foreach (string key in ht.Keys)
                {
                    if (key.StartsWith("OUT_"))
                    {
                        string tmp = key.Remove(0, 4);
                        object val = this.db.GetParameterValue(this.dbCommand, "@" + tmp);
                        rs[key] = val;
                    }
                }
                result = ds.Tables[0];
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new object[]
				{
					"-----------执行一存储过程DataTable返回多个值-----------\r\n",
					procName.ToString(),
					rs,
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }
        
        public DataSet GetDataSetBySQL(StringBuilder sql)
        {
            return this.GetDataSetBySQL(sql, null);
        }

        public DataSet GetDataSetBySQL(StringBuilder sql, SqlParam[] param)
        {
            DataSet result;
            try
            {
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(this.dbCommand, param);
                result = this.db.ExecuteDataSet(this.dbCommand);
            }
            catch (Exception e)
            {
                this.Logger.WriteLog("-----------获取数据集DataSet-----------\n" + sql.ToString() + "\n" + e.Message);
                result = null;
            }
            return result;
        }

        public IDataReader GetDataReaderBySQL(StringBuilder sql)
        {
            return this.GetDataReaderBySQL(sql, null);
        }

        public IDataReader GetDataReaderBySQL(StringBuilder sql, SqlParam[] param)
        {
            IDataReader result;
            try
            {
                this.dbCommand = this.GetDatabase().GetSqlStringCommand(sql.ToString());
                this.AddInParameter(this.dbCommand, param);

                result = this.db.ExecuteReader(this.dbCommand);
            }
            catch (Exception e)
            {
                this.Logger.WriteLog("-----------获取数据集流DataReader-----------\n" + sql.ToString() + "\n" + e.Message);
                result = null;
            }
            return result;
        }

        public int ExecuteByProc(string procName, Hashtable ht)
        {
            int num = 0;
            int result;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch (Exception exception)
                    {
                        transaction.Rollback();
                        this.Logger.WriteLog(string.Concat(new string[]
						{
							"-----------执行存储过程-----------\r\n",
							procName,
							"\r\n",
							exception.Message,
							"\r\n"
						}));
                    }
                    connection.Close();
                    result = num;
                    return result;
                }
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------执行存储过程服务器连接失败-----------\r\n",
					procName,
					"\r\n",
					e.Message,
					"\r\n"
				}));
            }
            result = num;
            return result;
        }

        public int ExecuteByProcNotTran(string procName, Hashtable ht)
        {
            int num = 0;
            int result;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand);
                    }
                    catch (Exception exception)
                    {
                        this.Logger.WriteLog(string.Concat(new string[]
						{
							"-----------执行存储过程-----------\r\n",
							procName,
							"\r\n",
							exception.Message,
							"\r\n"
						}));
                    }
                    connection.Close();
                    result = num;
                    return result;
                }
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------执行存储过程服务器连接失败-----------\r\n",
					procName,
					"\r\n",
					e.Message,
					"\r\n"
				}));
            }
            result = num;
            return result;
        }

        public int ExecuteByProcReturn(string procName, Hashtable ht, ref Hashtable rs)
        {
            int num = 0;
            int result;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddMoreParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                    }
                    connection.Close();
                }
                rs = new Hashtable();
                foreach (string str in ht.Keys)
                {
                    if (str.StartsWith("OUT_"))
                    {
                        object parameterValue = this.db.GetParameterValue(storedProcCommand, "@" + str.Remove(0, 4));
                        rs[str] = parameterValue;
                    }
                }
                result = num;
                return result;
            }
            catch (Exception exception)
            {
                this.Logger.WriteLog("-----------执行存储过程返回指定消息-----------\n" + procName + "\n" + exception.Message);
            }
            result = num;
            return result;
        }

        public int ExecuteByProcReturnMsg(string procName, Hashtable ht, ref object msg)
        {
            int num = 0;
            try
            {
                DbCommand storedProcCommand = this.GetDatabase().GetStoredProcCommand(procName);
                this.AddInParameter(storedProcCommand, ht);
                using (DbConnection connection = this.db.CreateConnection())
                {
                    connection.Open();
                    DbTransaction transaction = connection.BeginTransaction();
                    try
                    {
                        num = this.db.ExecuteNonQuery(storedProcCommand, transaction);
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                    }
                    connection.Close();
                }
                msg = this.db.GetParameterValue(storedProcCommand, "@Msg");
            }
            catch (Exception exception)
            {
                this.Logger.WriteLog("-----------执行存储过程返回指定消息-----------\n" + procName + "\n" + exception.Message);
            }
            return num;
        }

        public bool Submit_AddOrEdit(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            bool result;
            if (string.IsNullOrEmpty(pkVal))
            {
                result = (this.InsertByHashtable(tableName, ht) > 0);
            }
            else
            {
                result = (this.UpdateByHashtable(tableName, pkName, pkVal, ht) > 0);
            }
            return result;
        }

        public Hashtable GetHashtableById(string tableName, string pkName, string pkVal)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select * From ").Append(tableName).Append(" Where ").Append(pkName).Append("=@ID");
            DataTable dt = this.GetDataTableBySQL(sb, new SqlParam[]
			{
				new SqlParam("@ID", pkVal)
			},"");
            return DataTableHelper.DataTableToHashtable(dt);
        }

        public int IsExist(string tableName, string pkName, string[] pkValues)
        {
            SqlParam[] param = new SqlParam[pkValues.Length];
            int index = 0;
            string str = "@ID" + index;
            StringBuilder sql = new StringBuilder(string.Concat(new string[]
            {
                "SELECT count(1) FROM ",
                tableName,
                " WHERE ",
                pkName,
                " IN ("
            }));
            for (int i = 0; i < param.Length - 1; i++)
            {
                object obj2 = pkValues[i];
                str = "@ID" + index;
                sql.Append(str).Append(",");
                param[index] = new SqlParam(str, obj2);
                index++;
            }
            str = "@ID" + index;
            sql.Append(str);
            param[index] = new SqlParam(str, pkValues[index]);
            sql.Append(")");
            return CommonHelper.GetInt(this.GetObjectValue(sql, param));

        }
        public int IsExist(string tableName, Hashtable ht)
        {
           
            int index = 0;
            string str = "@ID" + index;
            StringBuilder sql = new StringBuilder();

            sql.Append("SELECT count(1) FROM ");
            sql.Append(tableName);
           

            foreach (string key in ht.Keys)
            {
                if (index == 0) { index = 1;sql.Append(" Where "); }
                else sql.Append(" and ");
                sql.Append(" " + key);
                sql.Append(" = @" + key);
            }

            //StringBuilder sb = new StringBuilder();
            //sb.Append(sql.ToString().Substring(0, sql.ToString().Length - 1));

            return CommonHelper.GetInt(this.GetObjectValue(sql, GetParameter(ht)));

        }


        public int IsExist(string tableName, string pkName, string[] pkValues , Hashtable ht)
        {
            List<SqlParam> param = new List<SqlParam>();
            //SqlParam[] param = new SqlParam[pkValues.Length];
            int index = 0;
            string str = "@ID" + index;
            StringBuilder sql = new StringBuilder(string.Concat(new string[]
            {
                "SELECT count(1) FROM ",
                tableName,
                " WHERE ",
                pkName,
                " IN ("
            }));
            for (int i = 0; i < pkValues.Length - 1; i++)
            {
                object obj2 = pkValues[i];
                str = "@ID" + index;
                sql.Append(str).Append(",");
                param.Add (new SqlParam(str, obj2));
                index++;
            }

            str = "@ID" + index;
            sql.Append(str);
            param.Add(new SqlParam(str, pkValues[index]));
            sql.Append(")");

            foreach (string key in ht.Keys)
            {
                if (index>= 0 )  sql.Append(" and ");
                sql.Append(" " + key);
                sql.Append(" = @" + key);
                param.Add(new SqlParam("@"+key, ht[key]));

            }

            SqlParam[] para = null;
            if (param != null)
                para = param.ToArray();

            return CommonHelper.GetInt(this.GetObjectValue(sql, para));

        }

        public virtual int InsertByHashtable(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                sb_prame.Append("," + key);
                sp.Append(",@" + key);
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
            return this.ExecuteBySql(sb, this.GetParameter(ht));
        }

        public virtual int InsertByHashtableNullParam(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                sb_prame.Append("," + key);
                sp.Append("," + ht[key]);
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
            return this.ExecuteBySql(sb);
        }

        public int InsertByHashtableReturnPkVal(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Declare @ReturnValue int Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                sb_prame.Append("," + key);
                sp.Append(",@" + key);
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ") Set @ReturnValue=SCOPE_IDENTITY() Select @ReturnValue");
            object _object = this.GetObjectValue(sb, this.GetParameter(ht));
            return (_object == DBNull.Value) ? 0 : Convert.ToInt32(_object);
        }

        public int UpdateByHashtable(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");
           
            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(ht[key]);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append(ht[key]);
                }
            }
            sb.Append(" Where ").Append(pkName).Append("=").Append(pkVal);
          
            return this.ExecuteBySql(sb);
        }

        public int UpdateByHashtables(string tableName, string pkName, string pkVal, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");
            sb.Append(pkName);
            sb.Append(" = ");
            sb.Append("'" + pkVal + "'");


            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;

                    sb.Append(" Where ");
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(ht[key]);
                }
                else
                {
                    sb.Append(" and " + key);
                    sb.Append("=");
                    sb.Append(ht[key]);
                }
            }
            //sb.Append(" Where ").Append(pkName).Append("=").Append(pkVal);

            return this.ExecuteBySql(sb);
        }


        public int UpdateByHashtable(string tableName, string pkName, string pkVal,string up, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");
            if (!string.IsNullOrEmpty(up)) sb.Append(up);
            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append("=");
                    sb.Append("@" + key);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append("@" + key);
                }
            }
            sb.Append(" Where ").Append(pkName).Append("=").Append("@" + pkName);
            ht[pkName] = pkVal;
            SqlParam[] _params = this.GetParameter(ht);
            return this.ExecuteBySql(sb, _params);
        }


        public int UpdateByHashtable(string tableName, Hashtable hts, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");

            bool isFirstValue = true;
            foreach (string key in hts.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(hts[key]);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append(hts[key]);
                }
            }

            isFirstValue = true;

            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;

                    sb.Append(" Where ");
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(ht[key]);
                }
                else
                {
                    sb.Append(" and " + key);
                    sb.Append("=");
                    sb.Append(ht[key]);
                }
            }

            return this.ExecuteBySql(sb);
        }


        public int DeleteData(string tableName, string pkName, string pkVal)
        {
            StringBuilder sb = new StringBuilder(string.Concat(new string[]
			{
				"Delete From ",
				tableName,
				" Where ",
				pkName,
				" = @ID"
			}));
            return this.ExecuteBySql(sb, new SqlParam[]
			{
				new SqlParam("@ID", pkVal)
			});
        }

        public int DeleteData(string tableName,Hashtable ht)
        {
            int index = 0;
            string str = "@ID" + index;
            StringBuilder sql = new StringBuilder();

            sql.Append("Delete FROM ");
            sql.Append(tableName);


            foreach (string key in ht.Keys)
            {
                if (index == 0) { index = 1; sql.Append(" Where "); }
                else sql.Append(" and ");
                sql.Append(" " + key);
                sql.Append(" = @" + key);
            }

            return this.ExecuteBySql(sql, GetParameter(ht));
        }

        public int UpdateByHashtableA(string tableName, string pkName, object[] pkValues, Hashtable ht)
        {

            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");

            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(ht[key]);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append(ht[key]);
                }
            }
            sb.Append(" Where AuditFlag <> 'AD02' and ").Append(pkName).Append(" in (");

            SqlParam[] param = new SqlParam[pkValues.Length];
            int index = 0;
            string str = "@ID" + index;
            //StringBuilder sql = new StringBuilder(string.Concat(new string[]
            //{
            //    "DELETE FROM ",
            //    tableName,
            //    " WHERE ",
            //    pkName,
            //    " IN ("
            //}));
            for (int i = 0; i < param.Length - 1; i++)
            {
                object obj2 = pkValues[i];
                str = "@ID" + index;
                sb.Append(str).Append(",");
                param[index] = new SqlParam(str, obj2);
                index++;
            }
            str = "@ID" + index;
            sb.Append(str);
            param[index] = new SqlParam(str, pkValues[index]);
            sb.Append(")");
            return this.ExecuteBySql(sb, param);
        }

        public int UpdateByHashtable(string tableName, string pkName, object[] pkValues,Hashtable ht)
        {
          
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");

            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (isFirstValue)
                {
                    isFirstValue = false;
                    sb.Append(key);
                    sb.Append(" = ");
                    sb.Append(ht[key]);
                }
                else
                {
                    sb.Append("," + key);
                    sb.Append("=");
                    sb.Append(ht[key]);
                }
            }
            sb.Append(" Where ").Append(pkName).Append(" in (");

            SqlParam[] param = new SqlParam[pkValues.Length];
            int index = 0;
            string str = "@ID" + index;
            //StringBuilder sql = new StringBuilder(string.Concat(new string[]
            //{
            //    "DELETE FROM ",
            //    tableName,
            //    " WHERE ",
            //    pkName,
            //    " IN ("
            //}));
            for (int i = 0; i < param.Length - 1; i++)
            {
                object obj2 = pkValues[i];
                str = "@ID" + index;
                sb.Append(str).Append(",");
                param[index] = new SqlParam(str, obj2);
                index++;
            }
            str = "@ID" + index;
            sb.Append(str);
            param[index] = new SqlParam(str, pkValues[index]);
            sb.Append(")");
            return this.ExecuteBySql(sb, param);
        }


        public int BatchDeleteData(string tableName, string pkName, object[] pkValues)
        {
            SqlParam[] param = new SqlParam[pkValues.Length];
            int index = 0;
            string str = "@ID" + index;
            StringBuilder sql = new StringBuilder(string.Concat(new string[]
			{
				"DELETE FROM ",
				tableName,
				" WHERE ",
				pkName,
				" IN ("
			}));

            for (int i = 0; i < param.Length - 1; i++)
            {
                object obj2 = pkValues[i];
                str = "@ID" + index;
                sql.Append(str).Append(",");
                param[index] = new SqlParam(str, obj2);
                index++;
            }

            str = "@ID" + index;
            sql.Append(str);
            param[index] = new SqlParam(str, pkValues[index]);
            sql.Append(")");
            return this.ExecuteBySql(sql, param);
        }

        public DataTable GetPageList(string sql, SqlParam[] param, string orderField, string orderType, ref PageInfo pageInfo)
        {
            StringBuilder sb = new StringBuilder();
            DataTable result;
            try
            {
                int num = (pageInfo.PageIndex - 1) * pageInfo.PageSize;
                int num2 = pageInfo.PageIndex * pageInfo.PageSize;
                sb.Append("Select * From (Select ROW_NUMBER() Over (Order By " + orderField + " " + orderType);
                sb.Append(string.Concat(new object[]
				{
					") As rowNum, * From (",
					sql,
					") As T ) As N Where rowNum > ",
					num,
					" And rowNum <= ",
					num2
				}));
                pageInfo.RowCount = Convert.ToInt32(this.GetObjectValue(new StringBuilder("Select Count(1) From (" + sql + ") As t"), param));
                result = this.GetDataTableBySQL(sb, param,"");
            }
            catch (Exception e)
            {
                this.Logger.WriteLog(string.Concat(new string[]
				{
					"-----------数据分页（Oracle）-----------\r\n",
					sb.ToString(),
					"\r\n",
					e.Message,
					"\r\n"
				}));
                result = null;
            }
            return result;
        }

        public void Dispose()
        {
            if (this.dbCommand != null)
            {
                this.dbCommand.Dispose();
            }
        }

        #region add liudl 公共方法
        /// <summary>
        /// 多条件验证是否存在
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="sqlwher">查询条件</param>
        /// <returns>true 没有重复项  false存在重复项</returns>
        public bool IsExist(string tableName,string sqlwhere) 
        {
            bool bl = false;
            try
            {
                StringBuilder stringBuilder = new StringBuilder();
                stringBuilder.AppendFormat("SELECT COUNT(*) FROM {0} WHERE {1};", tableName,sqlwhere);
 
                DataRow row = this.GetDataRowBySQL(stringBuilder);
                if (row != null) 
                {
                    if (row[0].ToString() == "0") 
                    {
                        bl = true;
                    }
                }

                return bl;
            }
            catch
            {
                return bl;
            }
        }
        #endregion
    }
}