利用ADO.NET的体系架构打造通用的数据库访问通用类

在周公的博客上看到了他写的这篇博文，忍不住转载过来了。

说明
在之前周公曾写过针对不同数据库的数据库访问通用类，如针对SQLite的、针对Access的、针对Oracle的、针对SQL Server的。总结了这些通用类的通用方法，其实无非就是针对不同类型的数据库创建Connection、Command、DataAdapter及DataReader，然后对外提供范围ExecuteTable(),ExecuteDataReader、ExecuteScalar()及ExecuteNonQuery()方法，有了这四个方法我们就可以完成针对数据库的所有操作了。在之前周公就曾经想过将这些数据库访问通用类提炼出来，写成一个针对各种数据库通用的数据库通用类，按照一般的方法写的话就需要收集常用的数据库访问类库才能编译，如果用反射的办法虽然也可以解决问题，但是周公又不愿意代码里到处都是反射的代码，在针对.NET Framework进行分析的基础上，写成了今天的数据库访问通用类。

分析
请先看下图：

在System.Data.Common命名空间下定义了针对所有数据库的Connection、Command、DataAdapter及DataReader对象的抽象类，分别是DbConnection、DbCommand、DbDataAdapter及DbDataReader，在这些抽象类中定义了针对所有数据库的通用方法和属性，不光是在.NET Framework中微软提供的针对ODBC、OleDB、Oracle、SQL Server类中如此，在微软未提供、由数据库厂商提供的ADO.NET类也是如此（假如有一天你自己也开发了一个数据库，为了提供给.NET开发人员使用，也应该遵循这个规定）。除此之外，在System.Data.Common命名空间下还提供了两个类，一个是DbProviderFactories，另一个是DbProviderFactory，DbProviderFactories类提供的方法有两个（包括一个重载形式），GetFactoryClasses()方法返回在系统中注册的DbProviderFactory类（如果在系统中注册了，就会在machine.config中的<configuration><system.data><DbProviderFactories>下添加针对这个数据库的相关信息），GetFactory()的两个重载方法都是返回一个指定的DbProviderFactor抽象类，在DbProviderFactory抽象类中又定义了创建DbConnection、DbCommand、DbDataAdapter及DbDataReader的方法。而不同的数据库访问类程序集中又都提供了对DbProviderFactory这个抽象类的实现（包括所有由数据库厂商提供的ADO.NET类）。所以我们要解决的问题是如何创建针对不同数据库的DbProviderFactory这个抽象类的实现。

解决
我们知道machine.config是所有config文件的鼻祖，包括web.config和app.config，程序在获取配置信息时会首先从距离自己层次关系最近的config文件查询起，一直到machine.config文件为止。那么我们就首先从自己的config文件做章。
下面的一段代码是从machine.config文件中摘取出来的：

<system.data>
<DbProviderFactories>
<addname="OdbcDataProvider"invariant="System.Data.Odbc"description=".NetFrameworkDataProviderforOdbc"type="System.Data.Odbc.OdbcFactory,System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>
<addname="OleDbDataProvider"invariant="System.Data.OleDb"description=".NetFrameworkDataProviderforOleDb"type="System.Data.OleDb.OleDbFactory,System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>
<addname="OracleClientDataProvider"invariant="System.Data.OracleClient"description=".NetFrameworkDataProviderforOracle"type="System.Data.OracleClient.OracleClientFactory,System.Data.OracleClient,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>
<addname="SqlClientDataProvider"invariant="System.Data.SqlClient"description=".NetFrameworkDataProviderforSqlServer"type="System.Data.SqlClient.SqlClientFactory,System.Data,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>
<addname="MicrosoftSQLServerCompactDataProvider"invariant="System.Data.SqlServerCe.3.5"description=".NETFrameworkDataProviderforMicrosoftSQLServerCompact"type="System.Data.SqlServerCe.SqlCeProviderFactory,System.Data.SqlServerCe,Version=3.5.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>

我们可以看到每个节点的组成很固定，都有name、invariant、description、type四个属性，它们的作用分别如下：
name：数据提供程序的可识别名称。
invariant：可以以编程方式用于引用数据提供程序的名称。
description：数据提供程序的可识别描述。
type：工厂类的完全限定名，它包含用于实例化该对象的足够的信息。
注意在全局范围类不同存在相同的invariant值，比如说在machine.config中已经定义了，就不能在自己的config文件中重复定义。此外，在type中Version、Culture及PublicKeyToken也不是必须的。
刚刚已经说了程序在获取配置信息时会首先从距离自己层次关系最近的config文件查询起，一直到machine.config文件为止，那么我们可以在自己的config文件中定义非微软提供的数据库访问类库信息，在这里周公也提供了绝大部分非微软提供的数据库访问类库信息，如下：

<addname="SQLiteDataProvider"invariant="System.Data.SQLite"description=".NetFrameworkDataProviderforSQLite"type="System.Data.SQLite.SQLiteFactory,System.Data.SQLite"/>
<addname="InformixDataProvider"invariant="IBM.Data.Informix"description=".NetFrameworkDataProviderforInformix"type="IBM.Data.Informix.IfxFactory,IBM.Data.Informix"/>
<addname="DB2DataProvider"invariant="IBM.Data.DB2.iSeries"description=".NetFrameworkDataProviderforDB2iSeries"type="IBM.Data.DB2.iSeries.DB2Factory,IBM.Data.DB2.iSeries"/>
<addname="FirebirdDataProvider"invariant="FirebirdSql.Data.FirebirdClient"description="Firebird"type="FirebirdSql.Data.FirebirdClient.FirebirdClientFactory,FirebirdSql.Data.FirebirdClient"/>
<addname="OracleDataProvider"invariant="Oracle.DataAccess.Client"description=".NetFrameworkDataProviderforOracle"type="Oracle.DataAccess.Client.OracleClientFactory,Oracle.DataAccess"/>
<addname="PostgreSQLDataProviderDataProvider"invariant="Npgsql"description=".NetFrameworkDataProviderforPostgreSQL"type="Npgsql.NpgsqlFactory,System.Data"/>

当然，也并不是在每次开发的时候都需要添加这些信息，如果本机安装了对应的程序集，那么就无需配置，除此之外，对于根本不会访问的数据库类型也不必添加对应的程序集信息。
代码实现

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Text;
usingSystem.Data;
usingSystem.Data.Common;
usingSystem.Reflection;
usingSystem.Text.RegularExpressions;

///<summary>
///通用数据库访问类，封装了对数据库的常见操作
///作者：周公
///日期：2011-07-18
///博客地址：http://blog.csdn.net/zhoufoxcn或http://zhoufoxcn.blog.51cto.com
///说明：(1)任何人都可以免费使用，请尽量保持此段说明。
///(2)这个版本还不是最终版本，有任何意见或建议请到http://weibo.com/zhoufoxcn处留言。
///</summary>
publicsealedclassDbUtility
{
publicstringConnectionString{get;privateset;}
privateDbProviderFactoryproviderFactory;
///<summary>
///构造函数
///</summary>
///<paramname="connectionString">数据库连接字符串</param>
///<paramname="providerType">数据库类型枚举，参见<paramrefname="providerType"/></param>
publicDbUtility(stringconnectionString,DbProviderTypeproviderType)
{
ConnectionString=connectionString;
providerFactory=ProviderFactory.GetDbProviderFactory(providerType);
if(providerFactory==null)
{
thrownewArgumentException("Can'tloadDbProviderFactoryforgivenvalueofproviderType");
}
}
///<summary>
///对数据库执行增删改操作，返回受影响的行数。
///</summary>
///<paramname="sql">要执行的增删改的SQL语句</param>
///<paramname="parameters">执行增删改语句所需要的参数</param>
///<returns></returns>
publicintExecuteNonQuery(stringsql,IList<DbParameter>parameters)
{
returnExecuteNonQuery(sql,parameters,CommandType.Text);
}
///<summary>
///对数据库执行增删改操作，返回受影响的行数。
///</summary>
///<paramname="sql">要执行的增删改的SQL语句</param>
///<paramname="parameters">执行增删改语句所需要的参数</param>
///<paramname="commandType">执行的SQL语句的类型</param>
///<returns></returns>
publicintExecuteNonQuery(stringsql,IList<DbParameter>parameters,CommandTypecommandType)
{
using(DbCommandcommand=CreateDbCommand(sql,parameters,commandType))
{
command.Connection.Open();
intaffectedRows=command.ExecuteNonQuery();
command.Connection.Close();
returnaffectedRows;
}
}
///<summary>
///执行一个查询语句，返回一个关联的DataReader实例
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<returns></returns>
publicDbDataReaderExecuteReader(stringsql,IList<DbParameter>parameters)
{
returnExecuteReader(sql,parameters,CommandType.Text);
}
///<summary>
///执行一个查询语句，返回一个关联的DataReader实例
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<paramname="commandType">执行的SQL语句的类型</param>
///<returns></returns>
publicDbDataReaderExecuteReader(stringsql,IList<DbParameter>parameters,CommandTypecommandType)
{
DbCommandcommand=CreateDbCommand(sql,parameters,commandType);
command.Connection.Open();
returncommand.ExecuteReader(CommandBehavior.CloseConnection);
}
///<summary>
///执行一个查询语句，返回一个包含查询结果的DataTable
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<returns></returns>
publicDataTableExecuteDataTable(stringsql,IList<DbParameter>parameters)
{
returnExecuteDataTable(sql,parameters,CommandType.Text);
}
///<summary>
///执行一个查询语句，返回一个包含查询结果的DataTable
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<paramname="commandType">执行的SQL语句的类型</param>
///<returns></returns>
publicDataTableExecuteDataTable(stringsql,IList<DbParameter>parameters,CommandTypecommandType)
{
using(DbCommandcommand=CreateDbCommand(sql,parameters,commandType))
{
using(DbDataAdapteradapter=providerFactory.CreateDataAdapter())
{
adapter.SelectCommand=command;
DataTabledata=newDataTable();
adapter.Fill(data);
returndata;
}
}
}
///<summary>
///执行一个查询语句，返回查询结果的第一行第一列
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<returns></returns>
publicObjectExecuteScalar(stringsql,IList<DbParameter>parameters)
{
returnExecuteScalar(sql,parameters,CommandType.Text);
}

///<summary>
///执行一个查询语句，返回查询结果的第一行第一列
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<paramname="commandType">执行的SQL语句的类型</param>
///<returns></returns>
publicObjectExecuteScalar(stringsql,IList<DbParameter>parameters,CommandTypecommandType)
{
using(DbCommandcommand=CreateDbCommand(sql,parameters,commandType))
{
command.Connection.Open();
objectresult=command.ExecuteScalar();
command.Connection.Close();
returnresult;
}
}
///<summary>
///创建一个DbCommand对象
///</summary>
///<paramname="sql">要执行的查询语句</param>
///<paramname="parameters">执行SQL查询语句所需要的参数</param>
///<paramname="commandType">执行的SQL语句的类型</param>
///<returns></returns>
privateDbCommandCreateDbCommand(stringsql,IList<DbParameter>parameters,CommandTypecommandType)
{
DbConnectionconnection=providerFactory.CreateConnection();
DbCommandcommand=providerFactory.CreateCommand();
connection.ConnectionString=ConnectionString;
command.CommandText=sql;
command.CommandType=commandType;
command.Connection=connection;
if(!(parameters==null||parameters.Count==0))
{
foreach(DbParameterparameterinparameters)
{
command.Parameters.Add(parameter);
}
}
returncommand;
}
}
///<summary>
///数据库类型枚举
///</summary>
publicenumDbProviderType:byte
{
SqlServer,
MySql,
SQLite,
Oracle,
ODBC,
OleDb,
Firebird,
PostgreSql,
DB2,
Informix,
SqlServerCe
}
///<summary>
///DbProviderFactory工厂类
///</summary>
publicclassProviderFactory
{
privatestaticDictionary<DbProviderType,string>providerInvariantNames=newDictionary<DbProviderType,string>();
privatestaticDictionary<DbProviderType,DbProviderFactory>providerFactoies=newDictionary<DbProviderType,DbProviderFactory>(20);
staticProviderFactory()
{
//加载已知的数据库访问类的程序集
providerInvariantNames.Add(DbProviderType.SqlServer,"System.Data.SqlClient");
providerInvariantNames.Add(DbProviderType.OleDb,"System.Data.OleDb");
providerInvariantNames.Add(DbProviderType.ODBC,"System.Data.ODBC");
providerInvariantNames.Add(DbProviderType.Oracle,"Oracle.DataAccess.Client");
providerInvariantNames.Add(DbProviderType.MySql,"MySql.Data.MySqlClient");
providerInvariantNames.Add(DbProviderType.SQLite,"System.Data.SQLite");
providerInvariantNames.Add(DbProviderType.Firebird,"FirebirdSql.Data.Firebird");
providerInvariantNames.Add(DbProviderType.PostgreSql,"Npgsql");
providerInvariantNames.Add(DbProviderType.DB2,"IBM.Data.DB2.iSeries");
providerInvariantNames.Add(DbProviderType.Informix,"IBM.Data.Informix");
providerInvariantNames.Add(DbProviderType.SqlServerCe,"System.Data.SqlServerCe");
}
///<summary>
///获取指定数据库类型对应的程序集名称
///</summary>
///<paramname="providerType">数据库类型枚举</param>
///<returns></returns>
publicstaticstringGetProviderInvariantName(DbProviderTypeproviderType)
{
returnproviderInvariantNames[providerType];
}
///<summary>
///获取指定类型的数据库对应的DbProviderFactory
///</summary>
///<paramname="providerType">数据库类型枚举</param>
///<returns></returns>
publicstaticDbProviderFactoryGetDbProviderFactory(DbProviderTypeproviderType)
{
//如果还没有加载，则加载该DbProviderFactory
if(!providerFactoies.ContainsKey(providerType))
{
providerFactoies.Add(providerType,ImportDbProviderFactory(providerType));
}
returnproviderFactoies[providerType];
}
///<summary>
///加载指定数据库类型的DbProviderFactory
///</summary>
///<paramname="providerType">数据库类型枚举</param>
///<returns></returns>
privatestaticDbProviderFactoryImportDbProviderFactory(DbProviderTypeproviderType)
{
stringproviderName=providerInvariantNames[providerType];
DbProviderFactoryfactory=null;
try
{
//从全局程序集中查找
factory=DbProviderFactories.GetFactory(providerName);
}
catch(ArgumentExceptione)
{
factory=null;
}
returnfactory;
}
}

用法举例，访问SQLite数据库：

stringconnectionString=@"DataSource=D:\VS2008\NetworkTime\CrawlApplication\CrawlApplication.db3";
stringsql="SELECT*FROMWeibo_MediaorderbyIddesclimit0,20000";
DbUtilitydb=newDbUtility(connectionString,DbProviderType.SQLite);
DataTabledata=db.ExecuteDataTable(sql,null);
DbDataReaderreader=db.ExecuteReader(sql,null);
reader.Close();

用法举例，访问MySQL:

stringconnectionString=@"Server=localhost;Database=crawldb;Uid=root;Pwd=root;Port=3306;";
stringsql="SELECT*FROMWeibo_MediaorderbyIddesclimit0,20000";
DbUtilitydb=newDbUtility(connectionString,DbProviderType.MySql);
DataTabledata=db.ExecuteDataTable(sql,null);
DbDataReaderreader=db.ExecuteReader(sql,null);
reader.Close();

从上面的代码可以看出，使用这个通用的数据库通用类和以前针对特定的数据库通用类没有什么区别，这一切都是DbProviderFactory这个类的功劳。
总结
设计模式在编程中对于应对变化确实非常有用，因为在ADO.NET中存在着这么一个工厂模式，轻而易举地就解决了针对不同数据库访问的问题。

周公
2011-07-20