C# SqlHelper帮助类

数据库连接配置

configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
    </startup>
	<connectionStrings>
		<add name="ConnStr" connectionString="server=127.0.0.1;database=PSaleStoreDB;uid=sa;pwd=1" 
			 providerName="System.Data.SqlClient" />
	</connectionStrings>
</configuration>

SqlHelper帮助类

public class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

        /// <summary>
        /// 增，删，改
        /// 执行sql语句或者存储过程，返回受影响的行数
        /// </summary>
        /// <param name="SQLString">SQL语句或者存储过程名</param>
        /// <param name="cmdType">执行脚本类型 1：SQL语句 2:存储过程</param>
        /// <param name="cmdParms">参数列表</param>
        /// <returns></returns>
        public static int ExecuteNonSql(string sql, int cmdType, params SqlParameter[] cmdParms)
        {
            int result = 0;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = BUilderCommand(conn, sql, cmdType, null, cmdParms);
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
            }
            return result;
        }

        /// <summary>
        /// 执行sql查询，返回第一行，第一列的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, int cmdType, params SqlParameter[] cmdParms)
        {
            object result = null;
            using (SqlConnection conn = new SqlConnection(connStr)) 
            {
                SqlCommand cmd = BUilderCommand(conn, sql, cmdType, null, cmdParms);
                // 返回第一行，第一列的值
                result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                if (result == null || result == DBNull.Value)
                {
                    return null;
                }
                else {
                    return result;
                }
            }
        }

        /// <summary>
        /// 执行查询操作，返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static SqlDataReader ExecuteReader(string sql, int cmdType, params SqlParameter[] cmdParms) 
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = BUilderCommand(conn, sql, cmdType, null, cmdParms);
            SqlDataReader reader;
            try
            {
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return reader;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception("创建reader对象发生异常", ex);
            }
        }

        /// <summary>
        /// 执行查询，查询结果填充到DataTable 只针对查询一个表的情况
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, int cmdType, params SqlParameter[] cmdParms)
        {
            DataTable dt = null;
            using (SqlConnection conn = new SqlConnection(connStr)) 
            {
                SqlCommand cmd = BUilderCommand(conn, sql, cmdType, null, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                dt = new DataTable();
                da.Fill(dt);
            }

            return dt;
        }

        /// <summary>
        /// 执行查询，数据填充到DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        public static DataSet GetDataSet(string sql, int cmdType, params SqlParameter[] cmdParms)
        {
            DataSet ds = null;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = BUilderCommand(conn, sql, cmdType, null, cmdParms);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                ds = new DataSet();
                da.Fill(ds);
            }

            return ds;
        }

        /// <summary>
        /// 事务，执行批量sql
        /// </summary>
        /// <param name="listSql"></param>
        /// <returns></returns>
        public static bool ExecuteTrans(List<string> listSql) 
        {
            using (SqlConnection conn = new SqlConnection(connStr)) 
            {
                conn.Open();
                SqlTransaction trans = conn.BeginTransaction();
                SqlCommand cmd = BUilderCommand(conn, "", 1, trans);

                try
                {
                    int count = 0;
                    for (int i = 0; i < listSql.Count; i++)
                    {
                        if (listSql[i].Length > 0)
                        {
                            cmd.CommandText = listSql[i];
                            cmd.CommandType = CommandType.Text;
                            count += cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("执行事务出现异常", ex);
                }
            }
        }

        /// <summary>
        /// 事务，批量执行包括sql，脚本类型，参数列表
        /// </summary>
        /// <param name="comList"></param>
        /// <returns></returns>
        public static bool ExecuteTrans(List<CommandInfo> comList) 
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();    
                SqlTransaction trans = conn.BeginTransaction();
                SqlCommand cmd = BUilderCommand(conn, "", 1, trans);

                try
                {
                    int count = 0;
                    for (int i = 0; i < comList.Count; i++)
                    {
                        cmd.CommandText = comList[i].CommandText;

                        if (comList[i].IsProc)
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                        }
                        else {
                            cmd.CommandType = CommandType.Text;
                        }

                        if (comList[i].Paras != null && comList[i].Paras.Length > 0)
                        {
                            cmd.Parameters.Clear();
                            foreach (var p in comList[i].Paras)
                            {
                                cmd.Parameters.Add(p);
                            }
                        }

                        count += cmd.ExecuteNonQuery();
                    }

                    trans.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw new Exception("执行事务出现异常", ex);
                }
            }
        }

        /// <summary>
        /// 事务，通过委托执行sql
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="action"></param>
        /// <returns></returns>
        public static T ExecuteTrans<T>(Func<IDbCommand, T> action) 
        {
            using (IDbConnection conn = new SqlConnection(connStr)) {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                IDbCommand cmd = conn.CreateCommand();
                cmd.Transaction = trans;
                return action(cmd);
            }
        }

        /// <summary>
        /// 构建SqlCommand
        /// </summary>
        /// <param name="conn">数据库连接对象</param>
        /// <param name="sql">sql语句或者存储过程</param>
        /// <param name="cmdType">命令字符串的类型</param>
        /// <param name="trans">事务</param>
        /// <param name="cmdParms">参数列表</param>
        /// <returns></returns>
        private static SqlCommand BUilderCommand(SqlConnection conn, string sql, int cmdType, 
            SqlTransaction trans, params SqlParameter[] cmdParms)
        {
            if (conn == null)
            {
                throw new ArgumentNullException("数据库连接对象不能为空！");
            }

            SqlCommand command = new SqlCommand(sql, conn);
            if (cmdType == 2)
            {
                command.CommandType = CommandType.StoredProcedure;
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            if (trans != null)
            {
                command.Transaction = trans;
            }
            if (cmdParms != null && cmdParms.Length > 0)
            {
                command.Parameters.Clear();
                command.Parameters.AddRange(cmdParms);
            }

            return command;
        }
    }

public class CommandInfo
    {
        // sql或者存储过程名
        public string CommandText;
        // 参数列表
        public SqlParameter[] Paras;
        // 是否是存储过程
        public bool IsProc;

        public CommandInfo() { 
        
        }

        public CommandInfo(string comText, bool isProc)
        {
            this.CommandText = comText;
            this.IsProc = isProc;
        }

        public CommandInfo(string comText, bool isProc, SqlParameter[] paras)
        {
            this.CommandText = comText;
            this.Paras = paras;
            this.IsProc = isProc;
        }
    }