using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace CSharpStudy { class SqlHelper { private static SqlConnection _con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyStrConn"].ConnectionString); private SqlCommand _cmd = null; /// 获取服务器名称 /// </summary> public string Server { get { return _con.DataSource; } } /// <summary> /// 获取数据库名称 /// </summary> public string Database { get { return _con.Database; } } /// <summary> /// 获取数据库连接 /// </summary> public SqlConnection Connection { get { return _con; } } /// <summary> /// 将查询的结果返回一个实体的集合 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="queryCommandText">sql查询语句</param> /// <returns>返回结果</returns> public static List<T> QueryEntityList<T>(string queryCommandText) { return QueryEntityList<T>(queryCommandText, false); } /// <summary> /// 将查询的结果返回一个实体的集合
/// 调用QueryEntityList<T>("select * from Table where Id=@Id", false, new SqlParamter("@Id", Id)) /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="queryCommandText">sql查询语句</param> /// <param name="isProcedure">是否调用存储过程</param> /// <param name="parameters">查询时所须多个参数集</param> /// <returns>返回结果</returns> public static List<T> QueryEntityList<T>(string queryCommandText, bool isProcedure, params SqlParameter[] parameters) { try { Type ty = typeof(T); PropertyInfo[] pr = ty.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.SetProperty); SqlCommand cmd = new SqlCommand(queryCommandText, _con); cmd.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text; if (parameters != null && parameters.Count() > 0) { ConvertNullToDBNull(parameters); cmd.Parameters.AddRange(parameters); } if (_con.State == ConnectionState.Closed) _con.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); List<T> lst = new List<T>(); while (dr.Read()) { T obj = (T)System.Activator.CreateInstance(ty); foreach (var item in pr) { try { if (dr[item.Name] != null && dr[item.Name] != DBNull.Value) { item.SetValue(obj, dr[item.Name], null); } } catch (System.Exception ex) { continue; } } lst.Add(obj); } dr.Close(); return lst.Count == 0 ? null : lst; } finally { if (_con.State == ConnectionState.Open) _con.Close(); } } /// <summary> /// 对参数中null或空字符串 参数转换为dbnull /// </summary> /// <param name="parameters"></param> private static void ConvertNullToDBNull(SqlParameter[] parameters) { if (parameters != null && parameters.Count() > 0) { foreach (SqlParameter item in parameters) { if (item.Value == null || string.Empty.Equals(item.Value)) item.Value = DBNull.Value; } } } /// <summary> /// 数据新增/修改/删除,不使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText) { return Execute(commandText, false, false); } /// <summary> /// 数据新增/修改/删除,可自已选择是否使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <param name="usingTranscation">是否使用事务方式</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText, bool usingTranscation) { return Execute(commandText, false, usingTranscation); } /// <summary> /// 数据新增/修改/删除,不使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <param name="parameters">执行语句所须多个参数集</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText, params SqlParameter[] parameters) { return Execute(commandText, false, false, parameters); } /// <summary> /// 数据新增/修改/删除,可自已选择是否使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <param name="usingTranscation">是否使用事务方式</param> /// <param name="parameters">执行语句所须多个参数集</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText, bool usingTranscation, params SqlParameter[] parameters) { return Execute(commandText, false, usingTranscation, parameters); } /// <summary> /// 数据新增/修改/删除,可调用存储过程和指定是否使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <param name="isProcedure">是否调用存储过程</param> /// <param name="usingTranscation">是否使用事务方式</param> /// <param name="parameters">执行语句所须多个参数集</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText, bool isProcedure, bool usingTranscation, params SqlParameter[] parameters) { SqlTransaction tr = null; try { _cmd = new SqlCommand(commandText, _con); if (_con.State == ConnectionState.Closed) _con.Open(); if (usingTranscation) { tr = _con.BeginTransaction(); _cmd.Transaction = tr; } _cmd.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text; if (parameters != null && parameters.Count() > 0) { ConvertNullToDBNull(parameters); _cmd.Parameters.AddRange(parameters); } int result = _cmd.ExecuteNonQuery(); if (tr != null) tr.Commit(); return result; } catch (System.Exception ex) { if (tr != null) tr.Rollback(); throw ex; } finally { if (_con.State == ConnectionState.Open) _con.Close(); } } /// <summary> /// 数据新增/修改/删除,可调用存储过程和指定是否使用事务 /// </summary> /// <param name="commandText">传入的执行语句,只能是单个插入、修改、删除语句</param> /// <param name="isProcedure">是否调用存储过程</param> /// <param name="usingTranscation">是否使用事务方式</param> /// <param name="commandTimeout">SQL执行操作的超时时间,默认为30秒</param> /// <param name="parameters">执行语句所须多个参数集</param> /// <returns>返回受影响的行数</returns> public int Execute(string commandText, bool isProcedure, bool usingTranscation, uint commandTimeout, params SqlParameter[] parameters) { SqlTransaction tr = null; try { _cmd = new SqlCommand(commandText, _con); if (_con.State == ConnectionState.Closed) _con.Open(); _cmd.CommandTimeout = Convert.ToInt32(commandTimeout); if (usingTranscation) { tr = _con.BeginTransaction(); _cmd.Transaction = tr; } _cmd.CommandType = isProcedure ? CommandType.StoredProcedure : CommandType.Text; if (parameters != null && parameters.Count() > 0) { ConvertNullToDBNull(parameters); _cmd.Parameters.AddRange(parameters); } int result = _cmd.ExecuteNonQuery(); if (tr != null) tr.Commit(); return result; } catch (System.Exception ex) { if (tr != null) tr.Rollback(); throw ex; } finally { if (_con.State == ConnectionState.Open) _con.Close(); } } } }
<?xml version="1.0" encoding="utf-8" ?> <configuration> <startup> <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" /> </startup> <connectionStrings> <add name="MyStrConn" connectionString="Data Source=127.0.0.1;Initial Catalog=Test;User ID=sa;Password=123456" /> </connectionStrings> </configuration>