C#工具类（一）：使用ADO.Net封装SqlHelper

说明：

1.完整的增删查改方法封装；

2.支持包含事务处理的批量Sql执行；

3.支持将DataReader和DataTable转化为实体集合；

    转化为实体类集合时，用到的Null类的代码详见C#工具类（二）：对各数据类型Null值的封装

4.经过实际项目使用的检验，稳定好用。

    以下是完整代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Xml.Linq;
using System.Reflection;

namespace Kinglong.App.Tools
{
    /// 

    /// SQLHelper类
    /// 
    public class SqlHelper
    {
        /// 

        /// 数据库连接字符串
        /// 
        public static string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

        /// 

        /// 执行非查询的sql操作
        /// 
        ///sql语句
        ///CommandType类型
        ///sql语句参数数组
        /// 受影响行数
        public static int ExecuteNonQuery(string cmdtext, CommandType cmdtype, params SqlParameter[] paras)
        {
            int count;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(cmdtext, conn))
                {
                    cmd.CommandType = cmdtype;
                    if (paras != null && paras.Length > 0)
                    {
                        cmd.Parameters.AddRange(paras);
                    }
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    count = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return count;
                }
            }
        }

        public static int ExecuteNonQuery(string cmdtext, params SqlParameter[] paras)
        {
            return ExecuteNonQuery(cmdtext, CommandType.Text, paras);
        }

        /// 

        /// 获取单行单列数据的查询
        /// 
        ///sql语句
        ///sql命令类型
        ///参数数组
        /// object对象的查询结果
        public static object ExecuteScalar(string cmdText, CommandType ctype, params SqlParameter[] paras)
        {
            object o = null;
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    cmd.CommandType = ctype;
                    if (paras != null && paras.Length > 0)
                    {
                        cmd.Parameters.AddRange(paras);
                    }

                    if (conn.State == System.Data.ConnectionState.Closed)
                    {
                        try
                        {
                            conn.Open();
                        }
                        catch
                        {
                            throw new Exception("数据库连接失败！");
                        }
                    }
                    o = cmd.ExecuteScalar();
                }
            }
            return o;
        }

        public static object ExecuteScalar(string cmdText, params SqlParameter[] paras)
        {
            return ExecuteScalar(cmdText, CommandType.Text, paras);
        }

        /// 

        /// 查询多行的数据,返回Datareader对象
        /// 
        ///sql语句
        ///sql命令类型
        ///传入的参数数组
        /// 返回DataReader对象
        public static SqlDataReader ExecuteReader(string cmdText, CommandType ctype, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(constr);

            using (SqlCommand cmd = new SqlCommand(cmdText, conn))
            {
                cmd.CommandType = ctype;
                if (paras != null && paras.Length > 0)
                {
                    cmd.Parameters.AddRange(paras);
                }

                if (conn.State == System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] paras)
        {
            return ExecuteReader(cmdText, CommandType.Text, paras);
        }

        /// 

        /// 查询数据库获取DataTable对象
        /// 
        ///sql语句
        ///sql执行类型
        ///传入的参数
        /// 返回查询结果的DataTable对象
        public static DataTable ExecuteTable(string cmdText, CommandType ctype, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    cmd.CommandType = ctype;
                    if (paras != null && paras.Length > 0)
                    {
                        cmd.Parameters.AddRange(paras);
                    }
                    if (conn.State == System.Data.ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                }
            }
        }

        public static DataTable ExecuteTable(string cmdText, params SqlParameter[] paras)
        {
            return ExecuteTable(cmdText, CommandType.Text, paras);
        }

        /// 

        /// 使用数据库事务执行多条SQL语句。无参数，顺序执行，可用于先删除后插入场景的批量操作。
        /// 
        ///多条SQL语句
        /// 返回受影响行数
        public static int ExecuteSqlTran(List<string> sqlList)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                //打开连接，开启一个事务
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                //先声明cmd 后面再分步赋值和执行
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.Transaction = tran;
                    int count = 0;
                    try
                    {
                        for (int i = 0; i < sqlList.Count; i++)
                        {
                            cmd.CommandText = sqlList[i];
                            count += cmd.ExecuteNonQuery();
                        }
                        tran.Commit();
                        return count;
                    }
                    catch (Exception)
                    {
                        tran.Rollback();
                        return -1;
                    }
                }
            }
        }

        /// 

        /// 使用数据库事务执行多条SQL语句。支持参数化，无序执行，适用于与顺序无关的批量操作
        /// 
        ///SQL语句的哈希表（key为sql语句，value是该语句的SqlParameter[]）
        public static int ExecuteSqlTran(Hashtable sqlList)
        {
            using (SqlConnection conn = new SqlConnection(constr))
            {
                conn.Open();
                int count = 0;
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry entry in sqlList)
                        {
                            string cmdText = entry.Key.ToString();
                            SqlParameter[] cmdParms = (SqlParameter[])entry.Value;
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                            count += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();//循环使用了同一个cmd，参数赋值时使用了Add，查询后应清除掉，避免循环的下一次查询参数列表受之前赋值的影响。
                        }
                        trans.Commit();
                        return val;
                    }
                    catch
                    {
                        trans.Rollback();
                        throw;
                    }
                }
            }
        }

        /// 

        /// 装配参数
        /// 
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    //ParameterDirection.InputOutput既能输入也能输出
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static List<t> FillCollection<t>(SqlDataReader reader) where T : class, new()
        {
            List<t> tlist = new List<t>();
            while (reader.Read())
            {
                T t = new T();
                foreach (PropertyInfo p in t.GetType().GetProperties())
                {
                    try
                    {
                        object colValue = reader[p.Name];
                        if (coloumnValue == null || coloumnValue == DBNull.Value)
                        {
                            p.SetValue(t, Null.SetNull(p), null);
                        }
                        else
                        {
                            p.SetValue(t, colValue, null);
                        }
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
                tlist.Add(t);
            }
            return tlist;
        }

        /// 

        /// 将DataTable转化为List
        /// 
        public static List<t> FillCollection<t>(DataTable dt) where T : class, new()
        {
            List<t> tlist = new List<t>();

            if (dt == null || dt.Rows.Count == 0)
            {
                return tlist;
            }

            DataColumnCollection cols = dt.Columns;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                T t = new T();
                foreach (PropertyInfo p in t.GetType().GetProperties())
                {
                    try
                    {
                        DataColumn coloumn = cols[p.Name];
                        if (coloumn == null)
                        {
                            continue;
                        }
                        object value = dt.Rows[i][coloumn];
                        if (value == null || value == DBNull.Value)
                        {
                            p.SetValue(t, Null.SetNull(p), null);
                        }
                        else
                        {
                            p.SetValue(t, value, null);
                        }
                    }
                    catch (Exception)
                    {
                    }
                }
                tlist.Add(t);
            }
            return tlist;
        }
    }
}