SqlHelper基于微软官方的Helper

基于微软官方的Helper, 把常用的方法封装出来, 简化操作

    public class SqlHelper
    {
        private static string _connString = "";

        // 数据库连接字符串
        public static string ConnString
        {
            get
            {
                if (Util.IsNull(_connString))
                {
                    _connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["Default"].ToString();
                }

                return _connString;
            }
        }

        ///----------------数据库脚本工具------------------------------------
        public static int ExecuteNonQuery(string sql)
        {
            return MsSqlHelper.ExecuteNonQuery(ConnString, CommandType.Text, sql);
        }

        public static string ExecuteQueryString(string sql)
        {
            return (string)MsSqlHelper.ExecuteScalar(ConnString, CommandType.Text, sql);
        }

        public static DataTable ExecuteQuery(string sql)
        {
            var ds = ExecuteQueryDataSet(sql);
            if (null == ds)
            {
                return null;
            }
            else
            {
                return ds.Tables[0];
            }
        }

        public static DataTable ExecuteQuery(string sql, IDbDataParameter[] dbParameters)
        {
            DataSet ds = MsSqlHelper.ExecuteDataset(ConnString, sql, dbParameters);
            if (null != ds && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public static DataSet ExecuteQueryDataSet(string sql)
        {
            return MsSqlHelper.ExecuteDataset(ConnString, CommandType.Text, sql);
        }

        public static DataTable RunProcTable(string procName, SqlParameter[] dbParameters)
        {
            DataSet ds = RunProc(procName, dbParameters);
            if (null != ds && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }
        }

        public static DataSet RunProc(string procName, SqlParameter[] dbParameters)
        {
            return MsSqlHelper.ExecuteDataset(ConnString, CommandType.StoredProcedure, procName, dbParameters);
        }

        public static int RunProc(string procName)
        {
            return MsSqlHelper.ExecuteNonQuery(ConnString, CommandType.StoredProcedure, procName);
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>        
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(ConnString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction 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 (SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        /// <summary>
        ///自定义连接  执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>       
        public static void ExecuteSqlTran(string connString, ArrayList SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction 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 (SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        //从DataTable批量插入数据到表中
        public static void SqlBulkCopyData(DataTable dt, string tableName, Dictionary<string, string> colMap)
        {
            using (SqlBulkCopy copy = new SqlBulkCopy(ConnString))
            {
                //告诉copy对象按照指定的5000条数据量来进行插入 
                //如果这个数据太庞大的话，一定要加上BatchSize 来分配处理
                copy.BatchSize = 5000;

                //告诉copy将dt中的数据插入到数据库CrmChat14中的SqlBulkCopyDemo表
                copy.DestinationTableName = tableName;

                //告诉copy dt内存表中的dd列对应的是SqlBulkCopyDemo表中的BName,他们之间一定要有一个映射
                foreach (var d in colMap)
                {
                    copy.ColumnMappings.Add(d.Key, d.Value);
                }

                //将数据批量插入表中
                copy.WriteToServer(dt);
            }
        }

        public static bool IsNull(DataTable dt)
        {
            if (null == dt || dt.Rows.Count <= 0)
            {
                return true;
            }
            return false;
        }

        public static bool IsNull(DataSet ds)
        {
            if (null == ds || ds.Tables.Count <= 0 || IsNull(ds.Tables[0]))
            {
                return true;
            }
            return false;
        }
    }