C#工具类(一):使用ADO.Net封装SqlHelper
作者:陆金龙
发表时间:2015-05-11 23:57
说明:
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; } } }