【前言】
作为一款成熟的面向对象高级编程语言，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环境问题，先不进行测试。