打造 通用的 支持多数据库 操作的 DBHelper
闲来无事，写一个通用的直持多数据库的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(); command.CommandType = commandType; command.CommandText = 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(); command.CommandType = commandType; command.CommandText = 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(); command.CommandType = commandType; command.CommandText = 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(); command.CommandType = commandType; command.CommandText = curDbObj.CommandText; 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); trans.Complete(); } } 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(); command.CommandType = commandType; command.CommandText = 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(); command.CommandType = commandType; command.CommandText = curDbObj.CommandText; 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); trans.Complete(); } } catch (Exception ex) { throw ex; } return list; } } } } }
后续会放出基于该类的SqlHelper
