.net平台上实现数据库访问工厂，连接不同的数据库
前言：最近由于项目需求变化，数据库将由sql server更换为mysql。涉及到大量的迁移工作，尤其dal层。于是产生了一个想法，实现一个数据库访问工厂，在更换数据库的时候，只需要在配置文件里更改一下配置即可。dal层代码不需要再做更改。
珍重申明：整个思路参照了该网址下（http://www.xuebuyuan.com/2204740.html）的一片文章，对该文章的作者提出感谢！只是结合我们项目的特点，做了更改。
实现步骤：整个数据库访问工厂DBFactory项目由5个文件组成。
1：创建一个枚举，在DataProvider.cs文件里实现。
public enum DataProvider
{
Oracle,
SqlServer,
MySql
}
主要应对主流的三大数据库。
2：建立一个接口：IDBManager.cs。接口文件是实现工厂的必备，当然也可以用abstrct类代替。提供了一个数据库访问和操作的模板。
public interface IDBManager
{
DataProvider ProviderType { get; set; }
IDbConnection Connection { get; set; }
IDataReader DataReader { get; set; }
IDbCommand Command { get; set; }
IDbTransaction Transaction { get; set; }
IDbDataParameter[] Parameters { get; set; }
string ConnectionString { get; set;}
void Open();
void Close();
void Dispose();
void CreateParameters(int paramsCount);
void AddParameters(int index,string paramName,object objValue);
void BeginTransaction();
void CommitTransaction();
void CloseReader();
IDataReader ExecuteReader(CommandType commandType,string commandText);
int ExecuteNonQuery(CommandType commandType,string commandText);
object ExecuteScalar(CommandType commandType,string commandText);
DataSet ExecuteDataSet(CommandType commandType,string commandText);
DataTable ExecuteDataTable(CommandType commandType, string commandText);
}
3：建立一个工厂类，根据配置，返回相应的数据库操作过程中的对象（IDbConnection等）。DBManagerFactory.cs。
public class DBManagerFactory
{
private DBManagerFactory() { }
/// <summary>
/// 根据配置中的数据库类型，返回相应的数据库连接
/// </summary>
/// <param name="providerType"></param>
/// <returns></returns>
public static IDbConnection GetConnection(DataProvider providerType)
{
IDbConnection iDbConnection;
switch(providerType)
{
case DataProvider.SqlServer:
iDbConnection = new SqlConnection();
break;
case DataProvider.MySql:
iDbConnection = new MySqlConnection();
break;
case DataProvider.Oracle:
iDbConnection = new OracleConnection();
break;
default:
return null;
}
return iDbConnection;
}
public static IDbCommand GetCommand(DataProvider providerType)
{
IDbCommand iDbCommand;
switch (providerType)
{
case DataProvider.SqlServer:
iDbCommand = new SqlCommand();
break;
case DataProvider.MySql:
iDbCommand = new MySqlCommand();
break;
case DataProvider.Oracle:
iDbCommand = new OracleCommand();
break;
default:
return null;
}
return iDbCommand;
}
/// <summary>
/// 根据配置中的数据库类型，返回相应的数据适配器
/// </summary>
/// <param name="providerType"></param>
/// <returns></returns>
public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
{
switch (providerType)
{
case DataProvider.SqlServer:
return new SqlDataAdapter();
case DataProvider.MySql:
return new MySqlDataAdapter();
case DataProvider.Oracle:
return new OracleDataAdapter();
default:
return null;
}
}
public static IDbTransaction GetTransaction(DataProvider providerType)
{
IDbConnection iDbConnection = GetConnection(providerType);
IDbTransaction iDbTransaction =iDbConnection.BeginTransaction();
return iDbTransaction;
}
/// <summary>
/// 根据数据库类型和参数数量，创建相应的参数数组
/// </summary>
/// <param name="providerType"></param>
/// <param name="paramsCount"></param>
/// <returns></returns>
public static IDbDataParameter[] GetParameters(DataProvider providerType, int paramsCount)
{
IDbDataParameter[] idbParams = new IDbDataParameter[paramsCount];
switch (providerType)
{
case DataProvider.SqlServer:
for (int i = 0; i < paramsCount; i++)
{
idbParams[i] = new SqlParameter();
}
break;
case DataProvider.MySql:
for (int i = 0; i < paramsCount; i++)
{
idbParams[i] = new MySqlParameter();
}
break;
case DataProvider.Oracle:
for(int i=0;i<paramsCount;i++)
{
idbParams[i] = new OracleParameter();
}
break;
default:
idbParams = null;
break;
}
return idbParams;
}
4：创建DBManager.cs，实现了IDBManager接口。那么工厂DBManagerFactory就可以通过配置文件，实现DBManager的多态性，实例化以后可以操作不同的数据库了。
public sealed class DBManager: IDBManager, IDisposable
{
#region 字段
private DataProvider _providerType;
private IDbConnection _idbConnection;
private IDataReader _iDataReader;
private IDbCommand _idbCommand;
private IDbTransaction _idbTransaction;
private IDbDataParameter[] _idbParameters;
private string _connectionString;
#endregion
#region 属性
public DataProvider ProviderType
{
get
{
return _providerType;
}
set
{
_providerType =value;
}
}
public IDbConnection Connection
{
get
{
return _idbConnection;
}
set
{
_idbConnection = value;
}
}
public IDataReader DataReader
{
get
{
return _iDataReader;
}
set
{
_iDataReader = value;
}
}
public IDbCommand Command
{
get
{
return _idbCommand;
}
set
{
_idbCommand= value;
}
}
public IDbTransaction Transaction
{
get
{
return _idbTransaction;
}
set
{
_idbTransaction = value;
}
}
public IDbDataParameter[] Parameters
{
get
{
return _idbParameters;
}
set
{
_idbParameters = value;
}
}
public string ConnectionString
{
get
{
return _connectionString;
}
set
{
_connectionString = value;
}
}
#endregion
#region 构造方法
public DBManager()
{
}
public DBManager(DataProvider providerType)
{
ProviderType=providerType;
}
public DBManager(DataProvider providerType,string connectionString)
{
ProviderType=providerType;
ConnectionString=connectionString;
}
#endregion
#region 公有方法
public void Open()
{
Connection=DBManagerFactory.GetConnection(ProviderType);
Connection.ConnectionString=ConnectionString;
if(Connection.State!=ConnectionState.Open)
{
Connection.Open();
}
Command=DBManagerFactory.GetCommand(ProviderType);
}
public void Close()
{
if(Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
}
public void Dispose()
{
GC.SuppressFinalize(this);
Close();
Command=null;
Transaction=null;
Connection=null;
}
public void CreateParameters(int paramsCount)
{
Parameters=new IDbDataParameter[paramsCount];
Parameters=DBManagerFactory.GetParameters(ProviderType, paramsCount);
}
public void AddParameters(int index,string paramName,object objValue)
{
if(index<Parameters.Length)
{
Parameters[index].ParameterName=paramName;
Parameters[index].Value=objValue;
}
}
public void BeginTransaction()
{
if(Transaction ==null)
{
Transaction=DBManagerFactory.GetTransaction(ProviderType);
}
Command.Transaction=Transaction;
}
public void CommitTransaction()
{
if(Transaction!= null)
{
Transaction.Commit();
}
Transaction=null;
}
public void CloseReader()
{
if(DataReader != null)
{
DataReader.Close();
}
}
public IDataReader ExecuteReader(CommandType commandType,string commandText)
{
Command=DBManagerFactory.GetCommand(ProviderType);
Command.Connection=Connection;
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
DataReader=Command.ExecuteReader();
Command.Parameters.Clear();
return DataReader;
}
public int ExecuteNonQuery(CommandType commandType,string commandText)
{
Command=DBManagerFactory.GetCommand(ProviderType);
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
int returnValue = Command.ExecuteNonQuery();
Command.Parameters.Clear();
return returnValue;
}
public object ExecuteScalar(CommandType commandType, string commandText)
{
Command=DBManagerFactory.GetCommand(ProviderType);
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
object returnValue = Command.ExecuteScalar();
Command.Parameters.Clear();
return returnValue;
}
public DataSet ExecuteDataSet(CommandType commandType, string commandText)
{
Command=DBManagerFactory.GetCommand(ProviderType);
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
IDbDataAdapter dataAdapter=DBManagerFactory.GetDataAdapter(ProviderType);
dataAdapter.SelectCommand=Command;
DataSet dataSet=new DataSet();
dataAdapter.Fill(dataSet);
Command.Parameters.Clear();
return dataSet;
}
public DataTable ExecuteDataTable(CommandType commandType, string commandText)
{
Command = DBManagerFactory.GetCommand(ProviderType);
PrepareCommand(Command, Connection, Transaction, commandType, commandText, Parameters);
IDbDataAdapter dataAdapter = DBManagerFactory.GetDataAdapter(ProviderType);
dataAdapter.SelectCommand = Command;
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
DataTable table = dataSet.Tables[0];
Command.Parameters.Clear();
return table;
}
#endregion
#region 私有方法
private void AttachParameters(IDbCommand command, IDbDataParameter[] commandParameters)
{
foreach(IDbDataParameter idbParameter in commandParameters)
{
if(idbParameter.Direction== ParameterDirection.InputOutput && idbParameter.Value== null)
{
idbParameter.Value=DBNull.Value;
}
command.Parameters.Add(idbParameter);
}
}
private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters)
{
command.Connection=connection;
command.CommandText=commandText;
command.CommandType=commandType;
if(transaction != null)
{
command.Transaction=transaction;
}
if(commandParameters!= null)
{
AttachParameters(command, commandParameters);
}
}
#endregion
5：建立DBHelper类，封装了DBManager类，该类下面的建立了很多的static方法，供DAL层直接调用就可以了。
/// <summary>
/// 调用DBManager类，外部来直接调用DBHelper类即可
/// </summary>
public class DBHelper
{
private static readonly IDBManager dbManager=new DBManager(GetDataProvider(), GetConnectionString());
//从配置文件中选择数据库类型
private static DataProvider GetDataProvider()
{
string providerType = ConfigurationManager.AppSettings["DataProvider"];
DataProvider dataProvider;
switch(providerType)
{
case "Oracle":
dataProvider=DataProvider.Oracle;
break;
case "SqlServer":
dataProvider=DataProvider.SqlServer;
break;
case "MySql":
dataProvider=DataProvider.MySql;
break;
default:
return DataProvider.SqlServer;
}
return dataProvider;
}
//从配置文件获取连接字符串
private static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["ConnString"].ConnectionString;
}
/// <summary>
/// 关闭数据库连接的方法
/// </summary>
public static void Close()
{
dbManager.Dispose();
}
/// <summary>
/// 创建参数
/// </summary>
/// <param name="paramsCount"></param>
public static void CreateParameters(int paramsCount)
{
dbManager.CreateParameters(paramsCount);
}
/// <summary>
/// 添加参数
/// </summary>
/// <param name="index"></param>
/// <param name="paramName"></param>
/// <param name="objValue"></param>
public static void AddParameters(int index,string paramName,object objValue)
{
dbManager.AddParameters(index, MatchParameters(paramName), objValue);
}
/// <summary>
/// 拼接sql语句执行非查询操作
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static bool ExecuteNonQueryByText(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteNonQuery(CommandType.Text, sqlString) > 0 ? true :false;
}
catch(Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 调用存储过程执行非查询操作
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static bool ExecuteNonQueryByProcedure(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteNonQuery(CommandType.StoredProcedure, sqlString) > 0 ? true : false;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 拼接sql语句执行查询操作，返回datareader
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static IDataReader ExecuteReaderByText(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteReader(CommandType.Text, sqlString);
}
catch(Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 调用存储过程执行查询操作，返回datareader
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static IDataReader ExecuteReaderByProcedure(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteReader(CommandType.StoredProcedure, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 拼接sql语句执行查询操作，返回dataset
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataSet ExecuteDataSetByText(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteDataSet(CommandType.Text, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 调用存储过程执行查询操作，返回dataset
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataSet ExecuteDataSetByProcedure(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteDataSet(CommandType.StoredProcedure, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 拼接sql语句执行查询操作，返回datatable
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataTable ExecuteDataTableByText(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteDataTable(CommandType.Text, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 调用存储过程执行查询操作，返回datatable
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataTable ExecuteDataTableByProcedure(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteDataTable(CommandType.StoredProcedure, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 拼接sql语句执行查询操作，返回首条
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static object ExecuteScalarByText(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteScalar(CommandType.Text,sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
/// <summary>
/// 调用存储过程执行查询操作，返回首条
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static object ExecuteScalarByProcedure(string sqlString)
{
try
{
dbManager.Open();
return dbManager.ExecuteScalar(CommandType.StoredProcedure, sqlString);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
dbManager.Dispose();
}
}
#region 私有方法
/// <summary>
/// 由于各个数据库的参数格式不同，此处为了保证dal层不因换数据库而修改
/// </summary>
/// <param name="paramName">参数类型</param>
/// <returns></returns>
private static string MatchParameters(string paramName)
{
string providerType = ConfigurationManager.AppSettings["DataProvider"];
string result=String.Empty;
switch (providerType)
{
case "Oracle":
result = paramName.StartsWith("@") ? paramName.Substring(1) : paramName;
break;
case "SqlServer":
result = paramName.StartsWith("@") ? paramName : paramName.Insert(0,"@");
break;
case "MySql":
result = paramName.StartsWith("@") ? paramName.Substring(1) : paramName;
break;
default:
return null;
}
return result;
}
#endregion
注意事项：1）在DAL层提供参数和存储过程名字来调用存储过程，由于sql server存储过程参数钱必须有@，而其他没有。为了在更换数据库的时候，完全不对dal更改代码。所以在DBHelper类中加了一个方法：
#region 私有方法
/// <summary>
/// 由于各个数据库的参数格式不同，此处为了保证dal层不因换数据库而修改
/// </summary>
/// <param name="paramName">参数类型</param>
/// <returns></returns>
private static string MatchParameters(string paramName)
{
string providerType = ConfigurationManager.AppSettings["DataProvider"];
string result=String.Empty;
switch (providerType)
{
case "Oracle":
result = paramName.StartsWith("@") ? paramName.Substring(1) : paramName;
break;
case "SqlServer":
result = paramName.StartsWith("@") ? paramName : paramName.Insert(0,"@");
break;
case "MySql":
result = paramName.StartsWith("@") ? paramName.Substring(1) : paramName;
break;
default:
return null;
}
return result;
}
#endregion
有了这个方法，我们在写dal层的时候，可以统一将存储过程参数加@，或是不加。
2）在工厂类中，需要引用
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
using Devart.Data.Oracle;
当然前提是该项目下，引用几个dll。
几个类库。其中MySql.Data.MySqlClient这个需要去mysql官网上下载驱动。而对于oracle我选择了免费版的devart提供的驱动。
