本文章为原创内容,如需转载,请注明作者及出处,谢谢!
一、在System.Data.Common命名空间下,存在这样的一个类:
//
//摘要://表示一组方法,这些方法用于创建提供程序对数据源类的实现的实例。
public abstract classDbProviderFactory
{//
//摘要://初始化 System.Data.Common.DbProviderFactory 类的新实例。
protectedDbProviderFactory();//
//摘要://指定特定的 System.Data.Common.DbProviderFactory 是否支持 System.Data.Common.DbDataSourceEnumerator//类。//
//返回结果://如果 System.Data.Common.DbProviderFactory 的实例支持 System.Data.Common.DbDataSourceEnumerator//类,则为 true;否则为 false。
public virtual bool CanCreateDataSourceEnumerator { get; }//
//摘要://返回实现 System.Data.Common.DbCommand 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbCommand 的新实例。
public virtualDbCommand CreateCommand();//
//摘要://返回实现 System.Data.Common.DbCommandBuilder 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbCommandBuilder 的新实例。
public virtualDbCommandBuilder CreateCommandBuilder();//
//摘要://返回实现 System.Data.Common.DbConnection 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbConnection 的新实例。
public virtualDbConnection CreateConnection();//
//摘要://返回实现 System.Data.Common.DbConnectionStringBuilder 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbConnectionStringBuilder 的新实例。
public virtualDbConnectionStringBuilder CreateConnectionStringBuilder();//
//摘要://返回实现 System.Data.Common.DbDataAdapter 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbDataAdapter 的新实例。
public virtualDbDataAdapter CreateDataAdapter();//
//摘要://返回实现 System.Data.Common.DbDataSourceEnumerator 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbDataSourceEnumerator 的新实例。
public virtualDbDataSourceEnumerator CreateDataSourceEnumerator();//
//摘要://返回实现 System.Data.Common.DbParameter 类的提供程序的类的一个新实例。//
//返回结果://System.Data.Common.DbParameter 的新实例。
public virtualDbParameter CreateParameter();//
//摘要://返回提供程序的类的新实例,该实例可实现提供程序的 System.Security.CodeAccessPermission 类的版本。//
//参数://state://System.Security.Permissions.PermissionState 值之一。//
//返回结果://指定 System.Security.Permissions.PermissionState 的 System.Security.CodeAccessPermission//对象。
public virtualCodeAccessPermission CreatePermission(PermissionState state);
}
我们可以看到,在此类中,有很多用于创建数据库相关对象的类型,如DbConnection,DbCommand,DbDataAdapter等。
而且,实现诸如SqlConnection、SqlCommand、SqlDataAdapter(这里用的是SQL Server)的类型,都分别继承自DbConnection,DbCommand,DbDataAdapter,
因此,我们可以使用DbProviderFactory来创建我们想要的、可实现任何数据库的DbHelper。
二、实现基本的DbHelper帮助类
1、我们将DbHelper定义为抽象类,并在类中提供一个抽象可读属性,名称叫DbProviderFactory,返回类型为DbProviderFactory(注:名称与返回类型可以为一样,也可以不一样),
2、我们利用在该抽象类实现的子类中重写DbProviderFactory方法,并在子类的构造函数中为该属性赋值,该值就是已经实现了具体数据库类型的DbProviderFactory。
定义的代码参考如下:
public abstract classDbHelper
{public abstract DbProviderFactory DbProviderFactory { get; }
}
3、我们为该抽象类编写一个构造函数,传进去的参数为连接字符串,并将其存储在可读的ConnectionString字段里,代码如下:
public abstract classDbHelper
{public DbHelper(stringconnectionString)
{
ConnectionString=connectionString;
}public string ConnectionString { get; }public abstract DbProviderFactory DbProviderFactory { get; }
}
4、在DbHelper编写一些用于实现数据库相关操作的方法,这里就用到了DbProviderFactory类中的方法,以下方法仅供参考,具体请参照其他完整的DbHelp帮助类,
DbHelper完整代码如下:
//帮助类的基类(抽象类)
public abstract classDbHelper
{public DbHelper(stringconnectionString)
{
ConnectionString=connectionString;
}public abstract DbProviderFactory DbProviderFactory { get; }public string ConnectionString { get; }//以下实现的帮助类方法,仅供该例子使用,具体请参照其他完整的DbHelp帮助类
private void ThrowExceptionIfLengthNotEqual(string[] sqls, paramsDbParameter[][] parameters)
{if (parameters.GetLength(0) != 0 && sqls.Length != parameters.GetLength(0)) throw new ArgumentException($"一维数组{nameof(sqls)}的长度与二维数组{nameof(parameters)}长度的第一维长度不一致");
}private T[] Execute(string[] sqls, CommandType commandType = CommandType.Text, ExecuteMode executeMode = ExecuteMode.NonQuery, paramsDbParameter[][] parameters)
{
ThrowExceptionIfLengthNotEqual(sqls, parameters);if(executeMode == ExecuteMode.NonQuery && typeof(T) != typeof(int)) throw new InvalidCastException("使用NonQuery模式时,必须将类型T指定为int");using (DbConnection connection =DbProviderFactory.CreateConnection())using (DbCommand command =DbProviderFactory.CreateCommand())
{
connection.ConnectionString=ConnectionString;
connection.Open();
command.Connection=connection;
command.CommandType=commandType;
DbTransaction transaction=connection.BeginTransaction();
command.Transaction=transaction;try{
List resultList = new List();for (int i = 0; i < sqls.Length; i++)
{
command.CommandText=sqls[i];if (parameters.GetLength(0) != 0)
{
command.Parameters.Clear();
command.Parameters.AddRange(parameters[i]);
}object result = null;switch(executeMode)
{caseExecuteMode.NonQuery:
result= command.ExecuteNonQuery(); break;caseExecuteMode.Scalar:
result= command.ExecuteScalar(); break;default: throw newNotImplementedException();
}
resultList.Add((T)Convert.ChangeType(result,typeof(T)));
}
transaction.Commit();returnresultList.ToArray();
}catch{
transaction.Rollback();throw;
}
}
}public int ExecuteNonQuery(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public int[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters)=> Execute(sqls, CommandType.Text, ExecuteMode.NonQuery,parameters);public int ExecuteNonQueryWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQueryWithProc(new string[] { sql }, new DbParameter[][] { parameter })[0];public int[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.StoredProcedure, ExecuteMode.NonQuery, parameters);public T ExecuteScalar(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public T[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.Text,ExecuteMode.Scalar, parameters);public T ExecuteScalarWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public T[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.StoredProcedure, ExecuteMode.Scalar, parameters);enumExecuteMode
{
NonQuery,Scalar
}private DataTable[] Fill(string[] selectSqls, CommandType commandType = CommandType.Text, paramsDbParameter[][] parameters)
{
ThrowExceptionIfLengthNotEqual(selectSqls, parameters);using (DbConnection connection =DbProviderFactory.CreateConnection())using (DbDataAdapter adapter =DbProviderFactory.CreateDataAdapter())using (DbCommand command =DbProviderFactory.CreateCommand())
{
connection.ConnectionString=ConnectionString;
connection.Open();
command.Connection=connection;
command.CommandType=commandType;
adapter.SelectCommand=command;
List resultList = new List();for (int i = 0; i < selectSqls.Length; i++)
{
command.CommandText=selectSqls[i];if (parameters.GetLength(0) != 0)
{
command.Parameters.Clear();
command.Parameters.AddRange(parameters[i]);
}
DataTable table= newDataTable();
adapter.Fill(table);
resultList.Add(table);
}returnresultList.ToArray();
}
}public DataTable Fill(string selectSql, params DbParameter[] parameter) => Fill(new string[] { selectSql }, new DbParameter[][] { parameter })[0];public DataTable[] Fill(string[] selectSqls, params DbParameter[][] parameters) =>Fill(selectSqls, CommandType.Text, parameters);public DataTable FillWithProc(string selectSql, params DbParameter[] parameter) => FillWithProc(new string[] { selectSql }, new DbParameter[][] { parameter })[0];public DataTable[] FillWithProc(string[] selectSqls, params DbParameter[][] parameters) =>Fill(selectSqls, CommandType.StoredProcedure, parameters);
}
三、实现具体的数据库帮助类
1、实现Sql Server的帮助类,具体方法:只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可,其他的数据库帮助类亦是如此,
代码如下:
//用于Sql Server的帮助类
public classSqlClientHelper : DbHelper
{public SqlClientHelper(string connectionString) : base(connectionString)
{this.DbProviderFactory =SqlClientFactory.Instance;
}public override DbProviderFactory DbProviderFactory { get; }
}
2、参照以上,实现SQLite帮助类如下:
//用于SQLite的帮助类
public classSQLiteHelper : DbHelper
{public SQLiteHelper(string connectionString) : base(connectionString)
{
DbProviderFactory=SQLiteFactory.Instance;
}public override DbProviderFactory DbProviderFactory { get; }
}
3、其他数据库的帮助类,正如以上所说的,只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可。
四、示例演示
使用前,必须引用了System.Data.SQLite,具体请参考一下文章:
编写的客户端代码,如下:
classProgram
{//客户端调用
static void Main(string[] args)
{string fileName = "Test.db";if(File.Exists(fileName)) File.Delete(fileName);
SQLiteConnection.CreateFile(fileName);
SQLiteHelper helper= new SQLiteHelper($"Data Source = {fileName}");
helper.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Info(ID integer PRIMARY KEY AUTOINCREMENT, Guid text)");
List sqlList = new List();for (int i = 0; i < 1000; i++)
{
sqlList.Add($"INSERT INTO Info VALUES(null,'{Guid.NewGuid()}')");
}
helper.ExecuteNonQuery(sqlList.ToArray());
DataTable table= helper.Fill("SELECT * FROM Info");
table.Rows.Cast().ToList().ForEach(x => Console.WriteLine($"{x[0]}\t{x[1]}"));
Console.ReadKey();
}
}
输出的结果如下:
五、完整代码如下:
usingSystem;usingSystem.Linq;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.Common;usingSystem.Data.SqlClient;usingSystem.Data.SQLite;usingSystem.IO;namespaceConsoleApp
{classProgram
{//客户端调用
static void Main(string[] args)
{string fileName = "Test.db";if(File.Exists(fileName)) File.Delete(fileName);
SQLiteConnection.CreateFile(fileName);
SQLiteHelper helper= new SQLiteHelper($"Data Source = {fileName}");
helper.ExecuteNonQuery("CREATE TABLE IF NOT EXISTS Info(ID integer PRIMARY KEY AUTOINCREMENT, Guid text)");
List sqlList = new List();for (int i = 0; i < 1000; i++)
{
sqlList.Add($"INSERT INTO Info VALUES(null,'{Guid.NewGuid()}')");
}
helper.ExecuteNonQuery(sqlList.ToArray());
DataTable table= helper.Fill("SELECT * FROM Info");
table.Rows.Cast().ToList().ForEach(x => Console.WriteLine($"{x[0]}\t{x[1]}"));
Console.ReadKey();
}
}//用于Sql Server的帮助类
public classSqlClientHelper : DbHelper
{public SqlClientHelper(string connectionString) : base(connectionString)
{this.DbProviderFactory =SqlClientFactory.Instance;
}public override DbProviderFactory DbProviderFactory { get; }
}//用于SQLite的帮助类
public classSQLiteHelper : DbHelper
{public SQLiteHelper(string connectionString) : base(connectionString)
{
DbProviderFactory=SQLiteFactory.Instance;
}public override DbProviderFactory DbProviderFactory { get; }
}//--------------------------------------------------------------------------------//其他数据库的帮助类,只要重写DbHelper类的DbProviderFactory属性并在构造函数为其赋值即可//--------------------------------------------------------------------------------//帮助类的基类(抽象类)
public abstract classDbHelper
{public DbHelper(stringconnectionString)
{
ConnectionString=connectionString;
}public abstract DbProviderFactory DbProviderFactory { get; }public string ConnectionString { get; }//以下实现的帮助类方法,仅供该例子使用,具体请参照其他完整的DbHelp帮助类
private void ThrowExceptionIfLengthNotEqual(string[] sqls, paramsDbParameter[][] parameters)
{if (parameters.GetLength(0) != 0 && sqls.Length != parameters.GetLength(0)) throw new ArgumentException($"一维数组{nameof(sqls)}的长度与二维数组{nameof(parameters)}长度的第一维长度不一致");
}private T[] Execute(string[] sqls, CommandType commandType = CommandType.Text, ExecuteMode executeMode = ExecuteMode.NonQuery, paramsDbParameter[][] parameters)
{
ThrowExceptionIfLengthNotEqual(sqls, parameters);if(executeMode == ExecuteMode.NonQuery && typeof(T) != typeof(int)) throw new InvalidCastException("使用NonQuery模式时,必须将类型T指定为int");using (DbConnection connection =DbProviderFactory.CreateConnection())using (DbCommand command =DbProviderFactory.CreateCommand())
{
connection.ConnectionString=ConnectionString;
connection.Open();
command.Connection=connection;
command.CommandType=commandType;
DbTransaction transaction=connection.BeginTransaction();
command.Transaction=transaction;try{
List resultList = new List();for (int i = 0; i < sqls.Length; i++)
{
command.CommandText=sqls[i];if (parameters.GetLength(0) != 0)
{
command.Parameters.Clear();
command.Parameters.AddRange(parameters[i]);
}object result = null;switch(executeMode)
{caseExecuteMode.NonQuery:
result= command.ExecuteNonQuery(); break;caseExecuteMode.Scalar:
result= command.ExecuteScalar(); break;default: throw newNotImplementedException();
}
resultList.Add((T)Convert.ChangeType(result,typeof(T)));
}
transaction.Commit();returnresultList.ToArray();
}catch{
transaction.Rollback();throw;
}
}
}public int ExecuteNonQuery(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public int[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters)=> Execute(sqls, CommandType.Text, ExecuteMode.NonQuery,parameters);public int ExecuteNonQueryWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQueryWithProc(new string[] { sql }, new DbParameter[][] { parameter })[0];public int[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.StoredProcedure, ExecuteMode.NonQuery, parameters);public T ExecuteScalar(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public T[] ExecuteNonQuery(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.Text,ExecuteMode.Scalar, parameters);public T ExecuteScalarWithProc(string sql, params DbParameter[] parameter) => ExecuteNonQuery(new string[] { sql }, new DbParameter[][] { parameter })[0];public T[] ExecuteNonQueryWithProc(string[] sqls, params DbParameter[][] parameters) => Execute(sqls, CommandType.StoredProcedure, ExecuteMode.Scalar, parameters);enumExecuteMode
{
NonQuery,Scalar
}private DataTable[] Fill(string[] selectSqls, CommandType commandType = CommandType.Text, paramsDbParameter[][] parameters)
{
ThrowExceptionIfLengthNotEqual(selectSqls, parameters);using (DbConnection connection =DbProviderFactory.CreateConnection())using (DbDataAdapter adapter =DbProviderFactory.CreateDataAdapter())using (DbCommand command =DbProviderFactory.CreateCommand())
{
connection.ConnectionString=ConnectionString;
connection.Open();
command.Connection=connection;
command.CommandType=commandType;
adapter.SelectCommand=command;
List resultList = new List();for (int i = 0; i < selectSqls.Length; i++)
{
command.CommandText=selectSqls[i];if (parameters.GetLength(0) != 0)
{
command.Parameters.Clear();
command.Parameters.AddRange(parameters[i]);
}
DataTable table= newDataTable();
adapter.Fill(table);
resultList.Add(table);
}returnresultList.ToArray();
}
}public DataTable Fill(string selectSql, params DbParameter[] parameter) => Fill(new string[] { selectSql }, new DbParameter[][] { parameter })[0];public DataTable[] Fill(string[] selectSqls, params DbParameter[][] parameters) =>Fill(selectSqls, CommandType.Text, parameters);public DataTable FillWithProc(string selectSql, params DbParameter[] parameter) => FillWithProc(new string[] { selectSql }, new DbParameter[][] { parameter })[0];public DataTable[] FillWithProc(string[] selectSqls, params DbParameter[][] parameters) =>Fill(selectSqls, CommandType.StoredProcedure, parameters);
}
}
View Code