【前言】
作为一款成熟的面向对象高级编程语言，C#在ADO.Net的支持上已然是做的很成熟，我们可以方便地调用ADO.Net操作各类关系型数据库，在使用了多年的Sql_Helper_DG后，由于项目需要，于是乎，就准备写一个Mysql_Helper在实现过程中，发现ADO.Net封装之完善，以及面向对象的封装、继承、多态，有了这些特性，何不把数据库操作封装成为一个通用的类呢，此文由此铺展而来…
【实现功能】
这篇文章将要介绍的主要内容如下：
1、ADO.NET之SqlServer
2、ADO.NET之Oracle
3、ADO.NET之MySql
4、充分利用面向对象的特征，实现通用的操作类
【环境准备】
1、MySql连接器的DLL引用
使用Nuget搜索 MySql.Data 引用即可：
2、Oracle连接器的DLL引用
使用Nuget搜索 Oracle.ManagedDataAccess 进行引用：
【实现思路】
在ADO.NET对SqlServer，Oracle，Mysql的操作熟练的基础上，我们逐渐发现所有的操作都是使用的同一套的东西，不同的是:
SqlServer的操作使用的是SqlConnection、SqlCommand，SqlDataAdapter；
MySql使用的是MySqlConnection、MySqlCommand、MySqlDataAdapter；
Oracle使用的是OracleSqlConnection、OracleCommand、OracleDataAdapter；
该连接类，操作类都分别继承自基础类：DbConnection、DbCommand、DbDataAdapter；
其类间关系如图所示：
1.DbConnection家族
2.DbCommand家族
3.DBDataAdapter家族
了解如上的几个特点后，我们里面能联系到了“多态”这个概念，我们可以使用同一套相同的代码，用“多态”的特性实例化出不同的实例，进而可以进一步封装我们的操作，达到代码精炼可重用的目的。
【实现过程】
1.定义枚举类 Opt_DataBaseType 用于参数选择具体要实例的数据库
public enum Opt_DataBaseType { SqlServer, MySql, Oracle }
2.自定义内部类SqlConnection_WR_Safe（多态提供DbConnection的对象、读写分离的支持）
1.在该内部类中，我们定义类属性DbConnection用于承接根据不同的数据库参数多态实例化后的对应Connection
2.实现IDisposable接口，提供释放DbConnection的方法
3.在读数据库连接失败时，及时切换到读写主数据库，提升系统的可用性
internal class SqlConnection_WR_Safe : IDisposable { /// <summary> /// SqlConnection /// </summary> public DbConnection DbConnection { get; set; } public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_RW) { this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW); } /** * if read db disabled,switchover to read write db immediately * */ public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_R, string ConnString_RW) { try { this.DbConnection = GetDbConnection(dataBaseType, ConnString_R); } catch (Exception) { this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW); } } /// <summary> /// GetDataBase ConnectionString by database type and connection string -- private use /// </summary> /// <param name="dataBaseType"></param> /// <param name="ConnString"></param> /// <returns></returns> private DbConnection GetDbConnection(Opt_DataBaseType dataBaseType, string ConnString) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlConnection(ConnString); case Opt_DataBaseType.MySql: return new MySqlConnection(ConnString); case Opt_DataBaseType.Oracle: return new OracleConnection(ConnString); default: return new SqlConnection(ConnString); } } /// <summary> /// Must Close Connection after use /// </summary> public void Dispose() { if (this.DbConnection != null) { this.DbConnection.Dispose(); } } }
3.自定义内部类 DbCommandCommon 用于提供DbCommand对象
internal class DbCommandCommon : IDisposable { /// <summary> /// common dbcommand /// </summary> public DbCommand DbCommand { get; set; } public DbCommandCommon(Opt_DataBaseType dataBaseType) { this.DbCommand = GetDbCommand(dataBaseType); } /// <summary> /// Get DbCommand select database type /// </summary> /// <param name="dataBaseType"></param> /// <returns></returns> private DbCommand GetDbCommand(Opt_DataBaseType dataBaseType) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlCommand(); case Opt_DataBaseType.MySql: return new MySqlCommand(); case Opt_DataBaseType.Oracle: return new OracleCommand(); default: return new SqlCommand(); } } /// <summary> /// must dispose after use /// </summary> public void Dispose() { if (this.DbCommand != null) { this.DbCommand.Dispose(); } } }
4.自定义内部类 DbDataAdapterCommon 用于提供DbDataAdapter
该类继承自DbDataAdapter，以实现DataAdapter的Fill方法，可以将结果集填充到DataSet中去。
/// <summary> /// DbDataAdapterCommon /// </summary> internal class DbDataAdapterCommon : DbDataAdapter, IDisposable { public DbDataAdapter DbDataAdapter { get; set; } public DbDataAdapterCommon(Opt_DataBaseType dataBaseType, DbCommand dbCommand) { //get dbAdapter this.DbDataAdapter = GetDbAdapter(dataBaseType, dbCommand); //provid select command this.SelectCommand = dbCommand; } private DbDataAdapter GetDbAdapter(Opt_DataBaseType dataBaseType, DbCommand dbCommand) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlDataAdapter(); case Opt_DataBaseType.MySql: return new MySqlDataAdapter(); case Opt_DataBaseType.Oracle: return new OracleDataAdapter(); default: return new SqlDataAdapter(); } } /// <summary> /// must dispose after use /// </summary> public new void Dispose() { if (this.DbDataAdapter != null) { this.DbDataAdapter.Dispose(); } } }
5.在执行Sql查询的时候，我们便使用我们自定义的内部类进行操作
>1 这里以ExecuteNonQuery为例：
public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); return cmd.DbCommand.ExecuteNonQuery(); } } }
该代码通过参数DataBaseType确定要实例化的数据库类型，ConnString_RW传入写数据库的连接字符串进行实例化，DbCommand也是使用dataBaseType实例我们需要实际操作的数据库对象。
>2 查询ExecuteDataSet方法：
该方法通过参数dataBaseType确定要实例化的具体DbConnection，通过读写分离的连接字符串进行选择读库和写库。
public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } }
全部代码见此：
1、数据库选择器枚举类：Opt_DataBaseType->
/********************************************************* * CopyRight: QIXIAO CODE BUILDER. * Version:4.2.0 * Author:qixiao(柒小) * Create:2017-09-26 17:54:28 * Update:2017-09-26 17:54:28 * E-mail: dong@qixiao.me | wd8622088@foxmail.com * GitHub: https://github.com/dong666 * Personal web site: http://qixiao.me * Technical WebSit: http://www.cnblogs.com/qixiaoyizhan/ * Description: * Thx , Best Regards ~ *********************************************************/ namespace QX_Frame.Bantina.Options { public enum Opt_DataBaseType { SqlServer, MySql, Oracle } }
2、主类代码Db_Helper_DG->
/********************************************************* * CopyRight: QIXIAO CODE BUILDER. * Version:4.2.0 * Author:qixiao(柒小) * Create:2017-9-26 17:41:42 * Update:2017-9-26 17:41:42 * E-mail: dong@qixiao.me | wd8622088@foxmail.com * GitHub: https://github.com/dong666 * Personal web site: http://qixiao.me * Technical WebSit: http://www.cnblogs.com/qixiaoyizhan/ * Description: * Thx , Best Regards ~ *********************************************************/ using MySql.Data.MySqlClient; using Oracle.ManagedDataAccess.Client; using QX_Frame.Bantina.Options; using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Linq; using System.Reflection; namespace QX_Frame.Bantina { public abstract class Db_Helper_DG { #region ConnString 链接字符串声明 /// <summary> /// 连接字符串 ConnString_Default 默认，且赋值时会直接覆盖掉读写 /// </summary> private static string _connString = Configs.QX_Frame_Helper_DG_Config.ConnectionString_DB_QX_Frame_Default; public static string ConnString_Default { get { return _connString; } set { _connString = value; ConnString_RW = _connString; ConnString_R = _connString; } } /// <summary> /// 连接字符串 ConnString_RW 读写数据库使用 /// </summary> public static string ConnString_RW = _connString; /// <summary> /// 连接字符串 ConnString_R 读数据库使用 /// </summary> public static string ConnString_R = _connString; /// <summary> /// DataBaseType Select default:sqlserver /// </summary> public static Opt_DataBaseType dataBaseType = Configs.QX_Frame_Helper_DG_Config.DataBaseType; #endregion static Db_Helper_DG() { //if (string.IsNullOrEmpty(ConnString_RW) || string.IsNullOrEmpty(ConnString_R)) //{ // throw new ArgumentNullException("ConnString Can Not Be Null !"); //} } #region ExcuteNonQuery 执行sql语句或者存储过程,返回影响的行数---ExcuteNonQuery /// <summary> /// 执行sql语句或存储过程，返回受影响的行数,不带参数。 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 有默认值CommandType.Text</param> /// <returns>返回受影响的行数</returns> public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); return cmd.DbCommand.ExecuteNonQuery(); } } } /// <summary> /// 执行sql语句或存储过程，返回受影响的行数。 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 t</param> /// <param name="parms">SqlParameter[]参数数组，允许空</param> /// <returns>返回受影响的行数</returns> public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, parms);//参数增加了commandType 可以自己编辑执行方式 return cmd.DbCommand.ExecuteNonQuery(); } } } /// <summary> /// 执行sql命令，返回受影响的行数。 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="obj">object[]参数数组，允许空</param> /// <returns>返回受影响的行数</returns> public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType, params object[] obj) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, obj);//参数增加了commandType 可以自己编辑执行方式 return cmd.DbCommand.ExecuteNonQuery(); } } } #endregion #region ExecuteScalar 执行sql语句或者存储过程,执行单条语句，返回单个结果---ScalarExecuteScalar /// <summary> /// 执行sql语句或存储过程 返回ExecuteScalar （返回自增的ID）不带参数 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 有默认值CommandType.Text</param> /// <returns></returns> public static object ExecuteScalar(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); return cmd.DbCommand.ExecuteScalar(); } } } /// <summary> /// 执行sql语句或存储过程 返回ExecuteScalar （返回自增的ID） /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="parms">SqlParameter[]参数数组，允许空</param> /// <returns></returns> public static object ExecuteScalar(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, parms); return cmd.DbCommand.ExecuteScalar(); } } } /// <summary> /// 执行sql语句或存储过程 返回ExecuteScalar （返回自增的ID） /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="obj">object[]参数数组，允许空</param> /// <returns></returns> public static object ExecuteScalar(string commandTextOrSpName, CommandType commandType, params object[] obj) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, obj); return cmd.DbCommand.ExecuteScalar(); } } } #endregion #region ExecuteReader 执行sql语句或者存储过程,返回DataReader---DaataReader /// <summary> /// 执行sql语句或存储过程 返回DataReader 不带参数 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 有默认值CommandType.Text</param> /// <returns></returns> public static DbDataReader ExecuteReader(string commandTextOrSpName, CommandType commandType = CommandType.Text) { //sqlDataReader不能用using 会关闭conn 导致不能获取到返回值。注意：DataReader获取值时必须保持连接状态 SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW); DbCommandCommon cmd = new DbCommandCommon(dataBaseType); PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); return cmd.DbCommand.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 执行sql语句或存储过程 返回DataReader /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="parms">SqlParameter[]参数数组，允许空</param> /// <returns></returns> public static DbDataReader ExecuteReader(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) { //sqlDataReader不能用using 会关闭conn 导致不能获取到返回值。注意：DataReader获取值时必须保持连接状态 SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW); DbCommandCommon cmd = new DbCommandCommon(dataBaseType); PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, parms); return cmd.DbCommand.ExecuteReader(CommandBehavior.CloseConnection); } /// <summary> /// 执行sql语句或存储过程 返回DataReader /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="obj">object[]参数数组，允许空</param> /// <returns></returns> public static DbDataReader ExecuteReader(string commandTextOrSpName, CommandType commandType, params object[] obj) { //sqlDataReader不能用using 会关闭conn 导致不能获取到返回值。注意：DataReader获取值时必须保持连接状态 SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW); DbCommandCommon cmd = new DbCommandCommon(dataBaseType); PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, obj); return cmd.DbCommand.ExecuteReader(CommandBehavior.CloseConnection); } #endregion #region ExecuteDataTable 执行sql语句或者存储过程,返回一个DataTable---DataTable /** * Update At 2017-3-2 14:58:45 * Add the ExecuteDataTable Method into Sql_Helper_DG **/ /// <summary> /// 执行sql语句或存储过程，返回DataTable不带参数 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 有默认值CommandType.Text</param> /// <returns></returns> public static DataTable ExecuteDataTable(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); using (DbDataAdapter da = new SqlDataAdapter(cmd.DbCommand as SqlCommand)) { DataSet ds = new DataSet(); da.Fill(ds); ) { ]; } return default(DataTable); } } } } /// <summary> /// 执行sql语句或存储过程，返回DataTable /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="parms">SqlParameter[]参数数组，允许空</param> /// <returns></returns> public static DataTable ExecuteDataTable(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, parms); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); ) { ]; } return default(DataTable); } } } } /// <summary> /// 执行sql语句或存储过程，返回DataTable /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 </param> /// <param name="obj">object[]参数数组，允许空</param> /// <returns></returns> public static DataTable ExecuteDataTable(string commandTextOrSpName, CommandType commandType, params object[] obj) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, obj); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); ) { ]; } return default(DataTable); } } } } #endregion #region ExecuteDataSet 执行sql语句或者存储过程,返回一个DataSet---DataSet /// <summary> /// 执行sql语句或存储过程，返回DataSet 不带参数 /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 有默认值CommandType.Text</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType = CommandType.Text) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } } /// <summary> /// 执行sql语句或存储过程，返回DataSet /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型</param> /// <param name="parms">SqlParameter[]参数数组，允许空</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, parms); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } } /// <summary> /// 执行sql语句或存储过程，返回DataSet /// </summary> /// <param name="ConnString">连接字符串，可以自定义，可以以使用SqlHelper_DG.ConnString</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">命令类型 </param> /// <param name="obj">object[]参数数组，允许空</param> /// <returns></returns> public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType, params object[] obj) { using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) { using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) { PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType, obj); using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) { DataSet ds = new DataSet(); da.Fill(ds); return ds; } } } } #endregion #region ExecuteList 执行sql语句或者存储过程，返回一个List<T>---List<T> public static List<Entity> ExecuteList<Entity>(string commandTextOrSpName, CommandType commandType = CommandType.Text) where Entity : class { return GetListFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType)); } public static List<Entity> ExecuteList<Entity>(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) where Entity : class { return GetListFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType, parms)); } public static List<Entity> ExecuteList<Entity>(string commandTextOrSpName, CommandType commandType, params object[] obj) where Entity : class { return GetListFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType, obj)); } #endregion #region ExecuteEntity 执行sql语句或者存储过程，返回一个Entity---Entity public static Entity ExecuteEntity<Entity>(string commandTextOrSpName, CommandType commandType = CommandType.Text) where Entity : class { return GetEntityFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType)); } public static Entity ExecuteEntity<Entity>(string commandTextOrSpName, CommandType commandType, params DbParameter[] parms) where Entity : class { return GetEntityFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType, parms)); } public static Entity ExecuteEntity<Entity>(string commandTextOrSpName, CommandType commandType, params object[] obj) where Entity : class { return GetEntityFromDataSet<Entity>(ExecuteDataSet(commandTextOrSpName, commandType, obj)); } #endregion #region ---PreparCommand 构建一个通用的command对象供内部方法进行调用--- /// <summary> /// 不带参数的设置sqlcommand对象 /// </summary> /// <param name="conn">sqlconnection对象</param> /// <param name="cmd">sqlcommmand对象</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">语句的类型</param> private static void PreparCommand(DbConnection conn, DbCommand cmd, string commandTextOrSpName, CommandType commandType) { //打开连接 if (conn.State != ConnectionState.Open) { conn.Open(); } //设置SqlCommand对象的属性值 cmd.Connection = conn; cmd.CommandType = commandType; cmd.CommandText = commandTextOrSpName; cmd.CommandTimeout = ; } /// <summary> /// 设置一个等待执行的SqlCommand对象 /// </summary> /// <param name="conn">sqlconnection对象</param> /// <param name="cmd">sqlcommmand对象</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">语句的类型</param> /// <param name="parms">参数，sqlparameter类型，需要指出所有的参数名称</param> private static void PreparCommand(DbConnection conn, DbCommand cmd, string commandTextOrSpName, CommandType commandType, params SqlParameter[] parms) { //打开连接 if (conn.State != ConnectionState.Open) { conn.Open(); } //设置SqlCommand对象的属性值 cmd.Connection = conn; cmd.CommandType = commandType; cmd.CommandText = commandTextOrSpName; cmd.CommandTimeout = ; if (parms != null) { cmd.Parameters.Clear(); cmd.Parameters.AddRange(parms); } } /// <summary> /// PreparCommand方法，可变参数为object需要严格按照参数顺序传参 /// 之所以会用object参数方法是为了我们能更方便的调用存储过程，不必去关系存储过程参数名是什么，知道它的参数顺序就可以了 sqlparameter必须指定每一个参数名称 /// </summary> /// <param name="conn">sqlconnection对象</param> /// <param name="cmd">sqlcommmand对象</param> /// <param name="commandTextOrSpName">sql语句或存储过程名称</param> /// <param name="commandType">语句的类型</param> /// <param name="parms">参数，object类型，需要按顺序赋值</param> private static void PreparCommand(DbConnection conn, DbCommand cmd, string commandTextOrSpName, CommandType commandType, params object[] parms) { //打开连接 if (conn.State != ConnectionState.Open) { conn.Open(); } //设置SqlCommand对象的属性值 cmd.Connection = conn; cmd.CommandType = commandType; cmd.CommandText = commandTextOrSpName; cmd.CommandTimeout = ; cmd.Parameters.Clear(); if (parms != null) { cmd.Parameters.AddRange(parms); } } #endregion #region 通过Model反射返回结果集 Model为 Entity 泛型变量的真实类型---反射返回结果集 /// <summary> /// 反射返回一个List T 类型的结果集 /// </summary> /// <typeparam name="T">Model中对象类型</typeparam> /// <param name="ds">DataSet结果集</param> /// <returns></returns> public static List<Entity> GetListFromDataSet<Entity>(DataSet ds) where Entity : class { try { List<Entity> list = new List<Entity>();//实例化一个list对象 PropertyInfo[] propertyInfos = typeof(Entity).GetProperties(); //获取T对象的所有公共属性 DataTable dt = ds.Tables[]; // 获取到ds的dt ) { //判断读取的行是否>0 即数据库数据已被读取 foreach (DataRow row in dt.Rows) { Entity model1 = System.Activator.CreateInstance<Entity>();//实例化一个对象，便于往list里填充数据 foreach (PropertyInfo propertyInfo in propertyInfos) { try { //遍历模型里所有的字段 if (row[propertyInfo.Name] != System.DBNull.Value) { //判断值是否为空，如果空赋值为null见else if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType); //将convertsionType转换为nullable对的基础基元类型 propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], nullableConverter.UnderlyingType), null); } else { propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], propertyInfo.PropertyType), null); } } else { propertyInfo.SetValue(model1, null, null);//如果数据库的值为空，则赋值为null } } catch (Exception) { propertyInfo.SetValue(model1, null, null);//如果数据库的值为空，则赋值为null } } list.Add(model1);//将对象填充到list中 } } return list; } catch (Exception ex) { throw ex; } } /// <summary> /// 反射返回一个T类型的结果 /// </summary> /// <typeparam name="T">Model中对象类型</typeparam> /// <param name="reader">SqlDataReader结果集</param> /// <returns></returns> public static Entity GetEntityFromDataReader<Entity>(DbDataReader reader) where Entity : class { try { Entity model = System.Activator.CreateInstance<Entity>(); //实例化一个T类型对象 PropertyInfo[] propertyInfos = model.GetType().GetProperties(); //获取T对象的所有公共属性 using (reader) { if (reader.Read()) { foreach (PropertyInfo propertyInfo in propertyInfos) { //遍历模型里所有的字段 if (reader[propertyInfo.Name] != System.DBNull.Value) { //判断值是否为空，如果空赋值为null见else if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType); //将convertsionType转换为nullable对的基础基元类型 propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], nullableConverter.UnderlyingType), null); } else { propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], propertyInfo.PropertyType), null); } } else { propertyInfo.SetValue(model, null, null);//如果数据库的值为空，则赋值为null } } return model;//返回T类型的赋值后的对象 model } } return default(Entity);//返回引用类型和值类型的默认值0或null } catch (Exception ex) { throw ex; } } /// <summary> /// 反射返回一个T类型的结果 /// </summary> /// <typeparam name="T">Model中对象类型</typeparam> /// <param name="ds">DataSet结果集</param> /// <returns></returns> public static Entity GetEntityFromDataSet<Entity>(DataSet ds) where Entity : class { return GetListFromDataSet<Entity>(ds).FirstOrDefault(); } #endregion } /** * author:qixiao * time:2017-9-18 18:02:23 * description:safe create sqlconnection support * */ internal class SqlConnection_WR_Safe : IDisposable { /// <summary> /// SqlConnection /// </summary> public DbConnection DbConnection { get; set; } public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_RW) { this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW); } /** * if read db disabled,switchover to read write db immediately * */ public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_R, string ConnString_RW) { try { this.DbConnection = GetDbConnection(dataBaseType, ConnString_R); } catch (Exception) { this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW); } } /// <summary> /// GetDataBase ConnectionString by database type and connection string -- private use /// </summary> /// <param name="dataBaseType"></param> /// <param name="ConnString"></param> /// <returns></returns> private DbConnection GetDbConnection(Opt_DataBaseType dataBaseType, string ConnString) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlConnection(ConnString); case Opt_DataBaseType.MySql: return new MySqlConnection(ConnString); case Opt_DataBaseType.Oracle: return new OracleConnection(ConnString); default: return new SqlConnection(ConnString); } } /// <summary> /// Must Close Connection after use /// </summary> public void Dispose() { if (this.DbConnection != null) { this.DbConnection.Dispose(); } } } /// <summary> /// Common sqlcommand /// </summary> internal class DbCommandCommon : IDisposable { /// <summary> /// common dbcommand /// </summary> public DbCommand DbCommand { get; set; } public DbCommandCommon(Opt_DataBaseType dataBaseType) { this.DbCommand = GetDbCommand(dataBaseType); } /// <summary> /// Get DbCommand select database type /// </summary> /// <param name="dataBaseType"></param> /// <returns></returns> private DbCommand GetDbCommand(Opt_DataBaseType dataBaseType) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlCommand(); case Opt_DataBaseType.MySql: return new MySqlCommand(); case Opt_DataBaseType.Oracle: return new OracleCommand(); default: return new SqlCommand(); } } /// <summary> /// must dispose after use /// </summary> public void Dispose() { if (this.DbCommand != null) { this.DbCommand.Dispose(); } } } /// <summary> /// DbDataAdapterCommon /// </summary> internal class DbDataAdapterCommon : DbDataAdapter, IDisposable { public DbDataAdapter DbDataAdapter { get; set; } public DbDataAdapterCommon(Opt_DataBaseType dataBaseType, DbCommand dbCommand) { //get dbAdapter this.DbDataAdapter = GetDbAdapter(dataBaseType, dbCommand); //provid select command this.SelectCommand = dbCommand; } private DbDataAdapter GetDbAdapter(Opt_DataBaseType dataBaseType, DbCommand dbCommand) { switch (dataBaseType) { case Opt_DataBaseType.SqlServer: return new SqlDataAdapter(); case Opt_DataBaseType.MySql: return new MySqlDataAdapter(); case Opt_DataBaseType.Oracle: return new OracleDataAdapter(); default: return new SqlDataAdapter(); } } /// <summary> /// must dispose after use /// </summary> public new void Dispose() { if (this.DbDataAdapter != null) { this.DbDataAdapter.Dispose(); } } } }
Db_Helper_DG
Db_Helper_DG简介：
本类分为 ExecuteNonQuery、ExecuteScalar、ExecuteScalar、ExecuteDataTable、ExecuteDataSet、ExecuteList Entity、ExecuteEntity七大部分，每一部分分为 无条件参数执行Sql语句或存储过程、SqlParameter[]参数执行Sql语句，Object[]参数执行存储过程三个重载方法。
方法的详细代码见上一条主代码Db_Helper_DG中折叠部分，这里对ExecuteListEntity和ExecuteEntity方法进行介绍。
此二方法是为了将查询结果和Model即Entity实体进行映射所用，使用C#反射Reflect技术，进行将查询结果直接赋值成为了Entity或者List<Entity>对象（此亦是ORM框架的核心）
ExecuteList方法通过二次封装，显式调用GetListFromDataSet方法，从DataSet结果集中遍历结果以进行赋值，代码如下：
public static List<Entity> GetListFromDataSet<Entity>(DataSet ds) where Entity : class { List<Entity> list = new List<Entity>();//实例化一个list对象 PropertyInfo[] propertyInfos = typeof(Entity).GetProperties(); //获取T对象的所有公共属性 DataTable dt = ds.Tables[]; // 获取到ds的dt ) { //判断读取的行是否>0 即数据库数据已被读取 foreach (DataRow row in dt.Rows) { Entity model1 = System.Activator.CreateInstance<Entity>();//实例化一个对象，便于往list里填充数据 foreach (PropertyInfo propertyInfo in propertyInfos) { try { //遍历模型里所有的字段 if (row[propertyInfo.Name] != System.DBNull.Value) { //判断值是否为空，如果空赋值为null见else if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType); //将convertsionType转换为nullable对的基础基元类型 propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], nullableConverter.UnderlyingType), null); } else { propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], propertyInfo.PropertyType), null); } } else { propertyInfo.SetValue(model1, null, null);//如果数据库的值为空，则赋值为null } } catch (Exception) { propertyInfo.SetValue(model1, null, null);//如果数据库的值为空，则赋值为null } } list.Add(model1);//将对象填充到list中 } } return list; }
ExecuteEntity部分又分为从DataReader中获取和Linq从List<Entity>获取第一条进行获取两种方式，由于DataReader有占用连接不释放的特点，在高并发的环境下使用并不友好，因此在实际生产环境中使用推荐使用第二种Linq获取List<Entity>的方式：
public static Entity GetEntityFromDataReader<Entity>(DbDataReader reader) where Entity : class { Entity model = System.Activator.CreateInstance<Entity>(); //实例化一个T类型对象 PropertyInfo[] propertyInfos = model.GetType().GetProperties(); //获取T对象的所有公共属性 using (reader) { if (reader.Read()) { foreach (PropertyInfo propertyInfo in propertyInfos) { //遍历模型里所有的字段 if (reader[propertyInfo.Name] != System.DBNull.Value) { //判断值是否为空，如果空赋值为null见else if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换 NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType); //将convertsionType转换为nullable对的基础基元类型 propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], nullableConverter.UnderlyingType), null); } else { propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], propertyInfo.PropertyType), null); } } else { propertyInfo.SetValue(model, null, null);//如果数据库的值为空，则赋值为null } } return model;//返回T类型的赋值后的对象 model } } return default(Entity);//返回引用类型和值类型的默认值0或null }
public static Entity GetEntityFromDataSet<Entity>(DataSet ds) where Entity : class { return GetListFromDataSet<Entity>(ds).FirstOrDefault(); }
【系统测试】
在全部功能实现之余，下面我们进行代码测试环节。
1、MySql数据库操作
各种方式给Db_Helper_DG的链接字符串属性进行赋值，这里不再赘述。
根据测试表的设计进行新建对应的实体类：
public class TB_People { public Guid Uid { get; set; } public string Name { get; set; } public int Age { get; set; } public int ClassId { get; set; } }
填写好连接字符串，并给Db_Helper_DG类的ConnString_Default属性赋值后，我们直接调用方法进行查询操作。
调用静态方法ExecuteList以便直接映射到实体类：
List<TB_People> peopleList = Db_Helper_DG.ExecuteList<TB_People>()); foreach (var item in peopleList) { Console.WriteLine(item.Name); }
这里的MySql语句 select * from student where ClassId=?ClassId 然后参数化赋值 ?ClassId=1 进行查询。
结果如下：
可见，查询结果并无任何差池，自动映射到了实体类的属性。
至于，SqlServer就不再进行测试了吧，Oracle由于本人当前Oracle环境问题，先不进行测试。
