DBManager类：
管理数据库连接字符串，数据库类型（供应商），创建线程内唯一的数据库连接。
using System; using System.Collections.Generic; using System.Configuration; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Common { /// <summary> /// 数据库管理类 /// </summary> public class DBManager { //配置文件中的数据库连接字符串 private static readonly string connectionStr = ConfigurationManager.AppSettings["conStr"]; /// <summary> /// 数据库类型 默认支持sqlserver数据库 /// </summary> public static readonly string dbProviderName = string.IsNullOrEmpty(ConfigurationManager.AppSettings["dbProviderName"]) ? "System.Data.SqlClient" : ConfigurationManager.AppSettings["dbProviderName"]; [ThreadStatic] public static DBHelper helper; /// <summary> /// 创建数据库访问类，且线程内唯一 /// </summary> /// <returns></returns> public static DBHelper Instace() { if (helper == null) { helper = new DBHelper(connectionStr, dbProviderName); return helper; } return helper; } } }
DBHelper类：
工厂类创建各类数据库，以及基本操作方法。
using System; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Reflection; using System.Text; using System.Threading.Tasks; namespace Common { /// <summary> /// 数据库工具类 /// </summary> public class DBHelper { //数据库连接字符串 private string connectionStr = string.Empty; //数据库工厂，用于创建不同数据库的连接 private DbProviderFactory factory; //数据库连接 private DbConnection _connection; public DbConnection Connection { get { return _connection; } set { _connection = value; } } /// <summary> /// 构造方法获得一个工厂。 /// </summary> /// <param name="connectionStr">数据库连接字符串</param> /// <param name="dbProviderName">数据库类型</param> public DBHelper(string connectionStr, string dbProviderName) { this.connectionStr = connectionStr; factory = DbProviderFactories.GetFactory(dbProviderName); } /// <summary> /// 打开数据库连接 /// </summary> public void OpenConnection() { if (Connection == null) { Connection = factory.CreateConnection(); Connection.ConnectionString = connectionStr; } if (Connection.State != ConnectionState.Open) { Connection.Open(); } } /// <summary> /// 得到Command对象 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="pars"></param> /// <returns></returns> private DbCommand CreateCmd(CommandType cmdType, string cmdText, params DbParameter[] pars) { DbCommand cmd = factory.CreateCommand(); cmd.Connection = Connection; cmd.CommandType = cmdType; cmd.CommandText = cmdText; if (pars != null) { cmd.Parameters.AddRange(pars); } return cmd; } /// <summary> /// 得到reader对象 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="pars"></param> /// <returns></returns> public DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] pars) { OpenConnection(); DbCommand cmd = CreateCmd(cmdType, cmdText, pars); return cmd.ExecuteReader(); } /// <summary> /// 执行非查询操作，返回影响行数 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="pars"></param> /// <returns></returns> public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] pars) { OpenConnection(); DbCommand cmd = CreateCmd(cmdType, cmdText, pars); int count = cmd.ExecuteNonQuery(); Connection.Close(); if (count > 0) { return count; } else { return -1; } } /// <summary> /// 返回查询的第一行第一列 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="pars"></param> /// <returns></returns> public object ExecuteScal(CommandType cmdType, string cmdText, params DbParameter[] pars) { OpenConnection(); DbCommand cmd = CreateCmd(cmdType, cmdText, pars); object obj = cmd.ExecuteScalar(); Connection.Close(); return obj; } /// <summary> /// 根据查询获取数据集 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="pars"></param> /// <returns></returns> public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] pars) { OpenConnection(); DbCommand cmd = CreateCmd(cmdType, cmdText, pars); DbDataAdapter da = factory.CreateDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds); Connection.Close(); return ds; } #region 创建类的对象，返回泛型集合 public static IList<T> ToList<T>(DbDataReader reader) { Type type = typeof(T); IList<T> list = null; if (type.IsValueType || type == typeof(string)) list = CreateValue<T>(reader, type); else list = CreateObject<T>(reader, type); reader.Dispose(); reader.Close(); return list; } private static IList<T> CreateObject<T>(DbDataReader reader, Type type) { IList<T> list = new List<T>(); PropertyInfo[] properties = type.GetProperties(); string name = string.Empty; while (reader.Read()) { T local = Activator.CreateInstance<T>(); for (int i = 0; i < reader.FieldCount; i++) { name = reader.GetName(i); foreach (PropertyInfo info in properties) { if (name.Equals(info.Name)) { info.SetValue(local, Convert.ChangeType(reader[info.Name], info.PropertyType), null); break; } } } list.Add(local); } return list; } private static IList<T> CreateValue<T>(DbDataReader reader, Type type) { IList<T> list = new List<T>(); while (reader.Read()) { T local = (T)Convert.ChangeType(reader[0], type, null); list.Add(local); } return list; } #endregion } }
