本方案可实现仅修改app.config即可连接不同数据库,但是设计数据库时需要注意各种数据库的数据类型是不一样的。
各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承自IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter,用一个工厂来实现接口的实例即可实现连接不同数据库。
首先,需要新建一个类库,命名为DbManager,此类库需要5个文件,
1、创建一个枚举类型:DataProvider.cs
- namespace DbManager
- {
- public enum DataProvider
- {
- Oracle,
- SqlServer,
- OleDb,
- Odbc,
- MySql
- }
- }
2、创建一个工厂类,用来产生以上不同数据库的实例:DBManagerFactory.cs
- using System.Data;
- using System.Data.Odbc;
- using System.Data.SqlClient;
- using System.Data.OleDb;
- using System.Data.OracleClient; //需要添加引用
- using MySql.Data.MySqlClient; //请自行安装MySQLConnector/Net后添加引用
- namespace DbManager
- {
- public sealed class DBManagerFactory
- {
- private DBManagerFactory()
- {
- }
- public static IDbConnection GetConnection(DataProvider providerType)
- {
- IDbConnection iDbConnection;
- switch (providerType)
- {
- case DataProvider.SqlServer:
- iDbConnection = new SqlConnection();
- break;
- case DataProvider.OleDb:
- iDbConnection = new OleDbConnection();
- break;
- case DataProvider.Odbc:
- iDbConnection = new OdbcConnection();
- break;
- case DataProvider.Oracle:
- iDbConnection = new OracleConnection();
- break;
- case DataProvider.MySql:
- iDbConnection = new MySqlConnection();
- break;
- default:
- return null;
- }
- return iDbConnection;
- }
- public static IDbCommand GetCommand(DataProvider providerType)
- {
- switch (providerType)
- {
- case DataProvider.SqlServer:
- return new SqlCommand();
- case DataProvider.OleDb:
- return new OleDbCommand();
- case DataProvider.Odbc:
- return new OdbcCommand();
- case DataProvider.Oracle:
- return new OracleCommand();
- case DataProvider.MySql:
- return new MySqlCommand();
- default:
- return null;
- }
- }
- public static IDbDataAdapter GetDataAdapter(DataProvider providerType)
- {
- switch (providerType)
- {
- case DataProvider.SqlServer:
- return new SqlDataAdapter();
- case DataProvider.OleDb:
- return new OleDbDataAdapter();
- case DataProvider.Odbc:
- return new OdbcDataAdapter();
- case DataProvider.Oracle:
- return new OracleDataAdapter();
- case DataProvider.MySql:
- return new MySqlDataAdapter();
- default:
- return null;
- }
- }
- public static IDbTransaction GetTransaction(DataProvider providerType)
- {
- IDbConnection iDbConnection = GetConnection(providerType);
- IDbTransaction iDbTransaction = iDbConnection.BeginTransaction();
- return iDbTransaction;
- }
- 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.OleDb:
- for (int i = 0; i < paramsCount; i++)
- {
- idbParams[i] = new OleDbParameter();
- }
- break;
- case DataProvider.Odbc:
- for (int i = 0; i < paramsCount; i++)
- {
- idbParams[i] = new OdbcParameter();
- }
- break;
- case DataProvider.Oracle:
- for (int i = 0; i < paramsCount; i++)
- {
- idbParams[i] = new OracleParameter();
- }
- break;
- case DataProvider.MySql:
- for (int i = 0; i < paramsCount; i++)
- {
- idbParams[i] = new MySqlParameter();
- }
- break;
- default:
- idbParams = null;
- break;
- }
- return idbParams;
- }
- }
- }
3、创建一个接口:IDBManager.cs
- using System.Data;
- namespace DbManager
- {
- 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);
- }
- }
4、创建一个类来实现IDBManager接口:DBManager.cs
- using System;
- using System.Data;
- namespace 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 DBManager()
- {
- }
- public DBManager(DataProvider providerType)
- {
- ProviderType = providerType;
- }
- public DBManager(DataProvider providerType, string connectionString)
- {
- ProviderType = providerType;
- ConnectionString = 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 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;
- }
- #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.cs,来调用DBManager类,外部来直接调用DBHelper类即可。
- using System;
- using System.Data;
- using System.Configuration;
- namespace DbManager
- {
- public class DBHelper
- {
- private static readonly IDBManager dbManager = new DBManager(GetDataProvider(), GetConnectionString());
- /// <summary>
- /// 从配置文件中选择数据库类型
- /// </summary>
- /// <returns>DataProvider枚举值</returns>
- 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 "OleDb":
- dataProvider = DataProvider.OleDb;
- break;
- case "Odbc":
- dataProvider = DataProvider.Odbc;
- break;
- case "MySql":
- dataProvider = DataProvider.MySql;
- break;
- default:
- return DataProvider.Odbc;
- }
- return dataProvider;
- }
- /// <summary>
- /// 从配置文件获取连接字符串
- /// </summary>
- /// <returns>连接字符串</returns>
- 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, paramName, objValue);
- }
- /// <summary>
- /// 执行增删改
- /// </summary>
- /// <param name="sqlString">安全的sql语句string.Format()</param>
- /// <returns>操作成功返回true</returns>
- public static bool ExecuteNonQuery(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">安全的sql语句string.Format()</param>
- /// <returns>返回IDataReader</returns>
- public static IDataReader ExecuteReader(string sqlString)
- {
- try
- {
- dbManager.Open();
- return dbManager.ExecuteReader(CommandType.Text, sqlString);
- }
- catch (Exception e)
- {
- throw new Exception(e.Message);
- }
- }
- }
- }
现在,将上述项目生成一个DbManager.dll类库,在具体的DAL层里面就可以直接调用了。
DBHelper类没有全部写完,只写了ExecuteNonQuery()和ExecuteReader()两个方法,对于有参和无参的增删改查操作暂时够用,返回DataSet的方法未写,Transaction相关的也未写。
6、app.config
- <?xml version="1.0" encoding="utf-8" ?>
- <configuration>
- <connectionStrings>
- <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
- <!-- 通过改变ConnectionString的值来更换数据库连接字符串
- <add name="ConnString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBDemo.mdb;Jet OLEDB:Database Password=1234"/>
- <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;Integrated Security=SSPI"/>
- <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=sa;pwd=1234"/>
- <add name="ConnString" connectionString="server=localhost;database=yourDbName;Persist Security Info=False;uid=root;pwd=mysqladmin"/>
- -->
- </connectionStrings>
- <appSettings>
- <add key="DataProvider" value="MySql"/>
- <!-- 通过改变value值来更换数据库
- <add key="DataProvider" value="Oracle"/>
- <add key="DataProvider" value="SqlServer"/>
- <add key="DataProvider" value="OleDb"/>
- <add key="DataProvider" value="Odbc"/>
- <add key="DataProvider" value="MySql"/>
- -->
- </appSettings>
- </configuration>
7、程序中的调用
举个简单的例子,我们就创建一个控制台应用程序,然后添加DbManager.dll的引用
Program.cs文件的样子:
- using System;
- using System.Data;
- using DbManager; //记得引入命名空间
- namespace DBDemo
- {
- class Program
- {
- static void Main(string[] args)
- {
- SelectWithoutParams();
- Console.WriteLine("------安全sql语句string.Format()的查询结果------");
- SelectWithSafeSql(4);
- Console.WriteLine("------参数化语句的查询结果-------");
- SelectWithParams("总统套间");
- }
- private static void SelectWithoutParams()
- {
- const string sql = "select * from RoomType";
- IDataReader reader = DBHelper.ExecuteReader(sql);
- while (reader.Read())
- {
- Console.WriteLine(reader["TypeName"].ToString());
- }
- DBHelper.Close(); //记得关闭reader
- }
- private static void SelectWithSafeSql(int TypeId)
- {
- string sql = string.Format("select * from RoomType where TypeId={0}", TypeId);
- IDataReader reader = DBHelper.ExecuteReader(sql);
- while (reader.Read())
- {
- Console.WriteLine(reader["TypeName"].ToString());
- }
- DBHelper.Close();
- }
- private static void SelectWithParams(string typeName)
- {
- string sql = "select * from RoomType where TypeName=@TypeName";
- //先创建参数,然后才能添加参数
- DBHelper.CreateParameters(1); //参数个数,1个
- DBHelper.AddParameters(0, "@TypeName", typeName);
- IDataReader reader = DBHelper.ExecuteReader(sql);
- while (reader.Read())
- {
- Console.WriteLine(reader["TypeName"].ToString());
- }
- DBHelper.Close();
- }
- }
- }
OK!全部完成!在具体的DAL层中,调用DBHelper的相关方法即可,如果是查询方法,记得最后要写关闭代码。只要表结构一样,可以在app.config中随意切换数据库。
最后注意的是:
各个数据库的插入语句不一样,假设我们有4个字段,第一个字段fieldName1为自增字段。
对于SQLServer,不需要写自增字段,
语句是:INSERT INTO table VALUES(value2, value3, value4);
对于MySQL,自增字段位置需要写null代替,
语句是:INSERT INTO table VALUES(NULL, value2, value3, value4);
而对于ACCESS数据库,则必须写完整,
语句是:INSERT INTO table(fieldName2, fieldName3,fieldName4) VALUES(value2, value3, value4);
为了实现兼容,大家还是都按完整的来写,就不会有错了。