闲来无事，写一个通用的直持多数据库的DBHelper，支持单连接批量执行SQL
因为用了TransactionScope所以请引用System.TransactionScope.dll
代码尚未测试，请不要在生产环境中使用，后续会用测试好的代码替换，并会持续支持，欢迎大家一起学习
欢迎加入 QQ群一起学习254082423
废话不多说直接上代码
using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Text;using System.Threading.Tasks;namespace Utility{internal class DbObject{/// <summary>/// 脚本/// </summary>internal string CommandText{get;set;}/// <summary>/// 脚本参数集/// </summary>internal IList<IDbDataParameter> DbParameters{get;}}}
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Transactions;namespace Utility{internal class DBHelper<T> where T : class, IDbConnection, new(){/// <summary>/// 装载参数/// </summary>/// <param name="command"></param>/// <param name="parameters"></param>private void PrepareDbParameters(IDbCommand command, IDbDataParameter[] parameters){if (Equals(command, null))throw new Exception("Command对象不能为空");foreach (var item in parameters)command.Parameters.Add(item);}private void PrepareDbParameters(IDbCommand command, IList<IDbDataParameter> parameters){if (Equals(command, null))throw new Exception("Command对象不能为空");foreach (var item in parameters)command.Parameters.Add(item);}/// <summary>/// 执行查询/// </summary>/// <typeparam name="U"></typeparam>/// <param name="connectionString">连接字符串</param>/// <param name="commandText">查询语句</param>/// <param name="parameters">查询参数</param>/// <returns>数据集</returns>internal DataSet ExecuteQuery<U>(string connectionString, CommandType commandType, string commandText,IDbDataParameter[] parameters)where U : class, IDbDataAdapter, new(){using (IDbConnection conn = new T()){conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = commandText;this.PrepareDbParameters(command, parameters);using (var ds = new DataSet()){IDbDataAdapter adapter = new U();adapter.SelectCommand = command;adapter.Fill(ds);return ds;}}}/// <summary>/// 执行查询/// </summary>/// <param name="connectionString">连接字符串</param>/// <param name="commandType">脚本类型</param>/// <param name="commandText">执行的脚本</param>/// <param name="parameters">执行参数</param>/// <param name="behavior">查询结果行为</param>/// <returns></returns>internal IDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText,IDbDataParameter[] parameters, CommandBehavior behavior){using (IDbConnection conn = new T()){conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = commandText;this.PrepareDbParameters(command, parameters);if (conn.State == ConnectionState.Broken)conn.Close();if (conn.State != ConnectionState.Open)conn.Open();using (var reader = command.ExecuteReader(behavior)){return reader;}}}/// <summary>/// 执行SQL/// </summary>/// <param name="connectionString"></param>/// <param name="commandType"></param>/// <param name="commandText"></param>/// <param name="parameters"></param>internal U ExecuteScalar<U>(string connectionString, CommandType commandType, string commandText,IDbDataParameter[] parameters)where U : struct{using (IDbConnection conn = new T()){conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = commandText;this.PrepareDbParameters(command, parameters);if (conn.State == ConnectionState.Broken)conn.Close();if (conn.State != ConnectionState.Open)conn.Open();var result = (U)command.ExecuteScalar();conn.Close();return result;}}/// <summary>/// 批量执行SQL/// </summary>/// <param name="connectionString"></param>/// <param name="commandType"></param>/// <param name="commandText"></param>/// <param name="parameters"></param>/// <param name="behavior"></param>/// <returns></returns>internal IList<U> ExecuteScalarBatch<U>(string connectionString, CommandType commandType, IList<DbObject> dbObjects)where U : struct{using (IDbConnection conn = new T()){using (TransactionScope trans = new TransactionScope()){IList<U> list = new List<U>();try{for (var i = 0; i < dbObjects.Count; i++){var curDbObj = dbObjects[i];conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = mandText;this.PrepareDbParameters(command, curDbObj.DbParameters.ToArray());if (conn.State == ConnectionState.Broken)conn.Close();if (conn.State != ConnectionState.Open)conn.Open();var result = (U)command.ExecuteScalar();list.Add(result);plete();}}catch (Exception ex){ throw ex;}return list;}}}/// <summary>/// 执行SQL/// </summary>/// <param name="connectionString"></param>/// <param name="commandType"></param>/// <param name="commandText"></param>/// <param name="parameters"></param>/// <param name="behavior"></param>/// <returns></returns>internal int ExecuteNoneQuery(string connectionString, CommandType commandType, string commandText,IDbDataParameter[] parameters, CommandBehavior behavior){using (IDbConnection conn = new T()){conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = commandText;this.PrepareDbParameters(command, parameters);if (conn.State == ConnectionState.Broken)conn.Close();if (conn.State != ConnectionState.Open)conn.Open();int affectCount = command.ExecuteNonQuery();return affectCount;}}/// <summary>/// 批量执行SQL/// </summary>/// <param name="connectionString"></param>/// <param name="commandType"></param>/// <param name="commandText"></param>/// <param name="parameters"></param>/// <param name="behavior"></param>/// <returns></returns>internal IList<int> ExecuteNoneQueryBatch<U>(string connectionString, CommandType commandType, IList<DbObject> dbObjects){using (IDbConnection conn = new T()){using (TransactionScope trans = new TransactionScope()){IList<int> list = new List<int>();try{for (var i = 0; i < dbObjects.Count; i++){var curDbObj = dbObjects[i];conn.ConnectionString = connectionString;IDbCommand command = conn.CreateCommand();mandType = commandType;mandText = mandText;this.PrepareDbParameters(command, curDbObj.DbParameters.ToArray());if (conn.State == ConnectionState.Broken)conn.Close();if (conn.State != ConnectionState.Open)conn.Open();var result = command.ExecuteNonQuery();list.Add(result);plete();}}catch (Exception ex){throw ex;}return list;}}}}}
后续会放出基于该类的SqlHelper
