SqlHandler

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.EntityClient;
using System.Data.Objects;
using System.Collections;
using System.Data.SqlClient;

namespace GY.DA.Common
{
    //为了处理所传哈希表key大小写不一致的情况，在建立哈希表时，采用如下方式建立：
    // private Hashtable data = new Hashtable(StringComparer.OrdinalIgnoreCase);

    /// <summary>
    /// 实现对数据的SQL操作（增删改）
    /// </summary>
    public class SqlHandler
    {
        #region 参数定义
        private DbConnection conn;
        /// <summary>
        /// 链接字符串
        /// </summary>
        private string _tableName = "";
        private List<string> _tableKeys = new List<string>();
        private List<DbParameter> plist = new List<DbParameter>();
        private List<DbCommand> operList = new List<DbCommand>();
        private Hashtable _FieldsTypes = new Hashtable(StringComparer.OrdinalIgnoreCase);
        public Hashtable FieldsTypes
        {
            get
            {
                return this._FieldsTypes;
            }
        }
        #endregion


        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="ASysName">连接字符串名称</param>
        /// <param name="tableName"></param>
        public SqlHandler(DbConnection connection, string tableName)
        {
            useTran = true;
            conn = connection;
            _tableName = tableName;
            Prepair();
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="AConnName">连接字符串名称</param>
        public SqlHandler(DbConnection connection)
        {
            conn = connection;
        }

        /// <summary>
        /// 支持多个表更新
        /// </summary>
        public string tableName
        {
            get { return _tableName; }
            set { SetTableName(value); }
        }

        /// <summary>
        /// 是否使用事务
        /// </summary>
        public bool useTran { get; set; }

        /// <summary>
        /// 设置表的主键字段，数据处理时按照主键进行更新、删除，
        /// 插入数据时，如果主键有值，则插入主键值，否则不插入主键值
        /// </summary>
        /// <param name="keys"></param>
        public void SetTableKeys(string[] keys)
        {
            _tableKeys.Clear();
            _tableKeys.AddRange(keys);
        }

        private void SetTableName(string value)
        {
            if (value.Trim().ToLower() == _tableName.ToLower())
            {
                return;
            }
            _tableName = value;
            Prepair();
        }

        private DbType TypeToDbType(Type t)
        {
            DbType dbt;
            try
            {
                dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
            }
            catch
            {
                dbt = DbType.Object;
            }
            return dbt;
        }

        //获取数据表结构及字段类型
        public void Prepair()
        {
            if (conn.State != ConnectionState.Open) { conn.Open(); }
            _FieldsTypes.Clear();
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from " + _tableName + " where 0!=0";
            using (DbDataReader reader = cmd.ExecuteReader())
            {
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    //if (reader.GetName(i) == "PicStream" && _tableName.ToLower() == "XianCCY_PicS_Stream".ToLower())
                    //{
                    //}
                    _FieldsTypes[reader.GetName(i)] = TypeToDbType(reader.GetFieldType(i));
                }
            }
        }

        //如果不是字符类型的数据库类型，paramValue为带客格的字符串时去掉空格
        private object getParamValue(bool valueEmpytToDBNull, object paramValue, DbType paramType)
        {
            object result = paramValue;
            if (paramValue == null) { return result; }
            if (valueEmpytToDBNull && (paramValue.GetType().Name == "String"))
            {
                if ((paramType != DbType.AnsiString) || (paramType != DbType.String) || (paramType != DbType.StringFixedLength))
                {
                    result = paramValue.ToString().Trim();
                }
            }
            return result;
        }

        private bool CreateSqlParams(string paramName, object paramValue, DbType paramType, DbCommand cmd, bool valueEmpytToDBNull = true)
        {
            bool result = false;
            if ((cmd == null) || (string.IsNullOrEmpty(paramName)))
            {
                return result;
            }
            paramValue = getParamValue(valueEmpytToDBNull, paramValue, paramType);
            DbParameter param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.DbType = paramType;
            if ((paramValue == null) || (valueEmpytToDBNull) && (string.IsNullOrEmpty(paramValue.ToString())))
            {
                param.Value = DBNull.Value;
            }
            else
            {
                if (paramType == DbType.Boolean)
                {
                    string v = paramValue.GetType().ToString().ToLower();
                    bool value = true;  //数字类型，0代表false,字符串：true代表true，bool型直接用，其它只要不空，就为真
                    if (v.Contains("int"))
                    {
                        value = (int)paramValue != 0;
                    }
                    else if (v.Contains("string"))
                    {
                        string pv= paramValue.ToString().ToLower();
                        value = pv == "true" || pv == "on" || pv == "1" || pv == "checked" || pv=="是";
                    }
                    else if (v.Contains("bool"))
                    {
                        value = (bool)paramValue;
                    }

                    param.Value = value;
                }
                else
                {
                    if (paramType.ToString().ToLower().Contains("date") && (paramValue == null || paramValue.ToString() == ""))
                    {
                        param.Value = DBNull.Value;
                    }
                    else
                    {
                        param.Value = paramValue;
                    }
                }
            }
            cmd.Parameters.Add(param);
            return result;
        }

        public void InsertRow(Hashtable rowData)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "insert into " + _tableName + "(";
            string sqlv = " values(";
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    sql += "[" + key + "],";
                    sqlv += "@" + key + ",";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sqlv = sqlv.Substring(0, sqlv.Length - 1);
            cmd.CommandText = sql + ")" + sqlv + ")";
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
                }
            }
            operList.Add(cmd);
        }

        //利用key=value进行数据更新
        public void UpdateRow(Hashtable rowData, string key, string Value)
        {
            Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
            wData[key] = Value;
            UpdateRow(rowData, wData);
        }
        /// <summary>
        /// 利用表的键值进行更新，键取自Tablekeys,值从rowData中读取
        /// </summary>
        /// <param name="rowData"></param>
        public void UpdateRow(Hashtable rowData)
        {
            Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
            foreach (string key in _tableKeys)
            {
                if (!rowData.ContainsKey(key))
                {
                    throw new Exception("数据中缺少更新的主键！");
                }
                else
                {
                    wData[key] = rowData[key];
                }
            }
            UpdateRow(rowData, wData);
        }
        /// <summary>
        /// 根据whereData中的值对进行更新，数据采用=判断
        /// </summary>
        /// <param name="rowData"></param>
        /// <param name="whereData"></param>
        public void UpdateRow(Hashtable rowData, Hashtable whereData)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "update " + _tableName + " set ";
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key)) //不更新主键字段
                {
                    sql += "[" + key + "] = @" + key + ",";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);

            string wSQL = "";
            foreach (string key in whereData.Keys)
            {
                wSQL += "[" + key + "]" + "= @where__" + key + " and ";
            }
            if (wSQL != "")
            {
                sql += " where " + wSQL.Substring(0, wSQL.Length - 4);
            }

            //生成要更新的数据参数表
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
                }
            }
            //生成Where条件参数表
            foreach (string key in whereData.Keys)
            {
                CreateSqlParams("where__" + key, whereData[key], (DbType)_FieldsTypes[key], cmd, false);
            }
            cmd.CommandText = sql;
            operList.Add(cmd);
        }
        /// <summary>
        /// 根据whereSQL和whereParams中的值对进行数据更新，可以更新主键值
        /// </summary>
        /// <param name="rowData"></param>
        /// <param name="whereSQL"></param>
        /// <param name="whereParams"></param>
        public void UpdateRow(Hashtable rowData, string whereSQL, Hashtable whereParams)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "update " + _tableName + " set ";
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    sql += "[" + key + "] = @" + key + ",";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);

            //生成要更新的数据参数表
            foreach (string key in whereParams.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key], cmd);
                }
            }
            //生成Where条件参数表
            foreach (string key in whereParams.Keys)
            {
                CreateSqlParams(key, whereParams[key], (DbType)_FieldsTypes[key], cmd, false);
            }
            cmd.CommandText = sql + " where " + whereSQL;
            operList.Add(cmd);
        }

        public void DeleteRow(Hashtable whereData)
        {
            if (whereData.Count == 0) { return; }
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "delete from " + _tableName + " where";
            foreach (string key in whereData.Keys)
            {
                sql += " [" + key + "]" + "=@where__" + key + " and ";
                CreateSqlParams("where__" + key, whereData[key], (DbType)_FieldsTypes[key], cmd, false);
            }
            cmd.CommandText = sql.Substring(0, sql.Length - 4);
            operList.Add(cmd);
        }
        public void DeleteRow(string key, string value)
        {
            Hashtable wData = new Hashtable(StringComparer.OrdinalIgnoreCase);
            wData[key] = value;
            DeleteRow(wData);
        }

        public void DeleteRow(string delSql, Hashtable whereParams)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = delSql;
            //生成Where条件参数表
            foreach (string key in whereParams.Keys)
            {
                CreateSqlParams(key, whereParams[key], (DbType)_FieldsTypes[key], cmd, false);
            }
            operList.Add(cmd);
        }
        public void DeleteAll()
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "delete from " + _tableName;
            cmd.CommandText = sql;
            operList.Add(cmd);
        }

        /// <summary>
        /// 执行SQL语句添加
        /// </summary>
        /// <param name="ASqlCommond"></param>
        public void ExecuteSql(string ASqlCommond)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = ASqlCommond;
            operList.Add(cmd);
        }

        public bool CopyCommand(DbCommand fromCom, ref DbCommand toCom)
        {
            bool result = false;
            try
            {
                toCom.Parameters.Clear();
                toCom.CommandText = fromCom.CommandText;
                toCom.CommandType = fromCom.CommandType;
                foreach (DbParameter param in fromCom.Parameters)
                {
                    DbParameter tmpParam = toCom.CreateParameter();
                    tmpParam.DbType = param.DbType;
                    tmpParam.ParameterName = param.ParameterName;
                    tmpParam.Value = param.Value;
                    toCom.Parameters.Add(tmpParam);
                }
            }
            catch
            {
                return result;
            }
            result = true;
            return result;
        }

        public int ExecuteScalar()
        {
            int m_Ret = 0;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                foreach (DbCommand cmd in operList)
                {
                    m_Ret = Int32.Parse( cmd.ExecuteScalar().ToString());
                }

            }
            catch (Exception e)
            {
                m_Ret = 0;
                throw e;
            }
            finally
            {
                operList.Clear();
                conn.Close();
            }


            return m_Ret;
        }
        public string ErrorMessage {private set; get; }
        public int SqlSave()
        {
            int m_Ret = 0;
            ErrorMessage = "";
            DbTransaction dbTran = null;
            try
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                if (useTran)
                {
                    dbTran = conn.BeginTransaction();
                }
                for (int i = 0; i < operList.Count; i++)
                {
                    if (dbTran != null)
                    {
                        operList[i].Transaction = dbTran;
                    }
                    operList[i].ExecuteNonQuery();
                }
                if (dbTran != null) dbTran.Commit();
                m_Ret = 1;
            }
            catch (Exception ex)
            {
                if (dbTran != null) dbTran.Rollback();
                ErrorMessage = ex.Message;
                m_Ret = 0;
            }
            finally{
                operList.Clear();
                conn.Close();
            }

            return m_Ret;
        }

        // 扩展提交数据脚本方法，使用带参数的 insert update 或 delete sql 语句，不使用事务。
        /// <summary>
        /// 执行 insert update 或 delete 脚本并返回受影响的行数，使用 <paramref name="parameters"/> 指定的参数。
        /// </summary>
        /// <param name="sql">insert update 或 delete 脚本</param>
        /// <param name="parameters">数据参数，允许为空或空数组</param>
        /// <returns></returns>
        public int ExecuteSql(string sql, params System.Data.SqlClient.SqlParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            DbCommand cmd = conn.CreateCommand();

            if (parameters != null && parameters.Length > 0)
            {
                foreach (var p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }
            cmd.CommandText = sql;
            int result =  cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }

        public int ExecuteSqlProc(string sql, params System.Data.SqlClient.SqlParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open) conn.Open();
            DbCommand cmd = conn.CreateCommand();

            if (parameters != null && parameters.Length > 0)
            {
                foreach (var p in parameters)
                {
                    cmd.Parameters.Add(p);
                }
            }
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.StoredProcedure;
            int result = cmd.ExecuteNonQuery();
            conn.Close();
            return result;
        }
        /// <summary>
        /// 自增长id 插入 加获取插入id的条件
        /// </summary>
        /// <param name="rowData"></param>
        /// <param name="id_ZiZ">1</param>
        public void InsertRow(Hashtable rowData ,string id_ZiZ)
        {
            DbCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            string sql = "insert into " + _tableName + "(";
            string sqlv = " values(";
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    sql += "[" + key + "],";
                    sqlv += "@" + key + ",";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sqlv = sqlv.Substring(0, sqlv.Length - 1);
            cmd.CommandText = sql + ")" + sqlv + ")";
            if (!string.IsNullOrEmpty(id_ZiZ)&&id_ZiZ=="1")
            {
                cmd.CommandText += " SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] ";
            }
            foreach (string key in _FieldsTypes.Keys)
            {
                if (rowData.ContainsKey(key))
                {
                    CreateSqlParams(key, rowData[key], (DbType)_FieldsTypes[key.ToLower()], cmd);
                }
            }
            operList.Add(cmd);
        }
    }
}