本方案可实现仅修改app.config即可连接不同数据库,但是设计数据库时需要注意各种数据库的数据类型是不一样的。 各种不同数据库的Connection、Command、DataAdapter、Transaction和Parameter都继承自IDbConnection、IDbCommand、IDbDataAdapter、IDbTransaction和IDbDataParameter,用一个工厂来实现接口的实例即可实现连接不同数据库。 首先,需要新建一个类库,命名为DbManager,此类库需要5个文件, 1、创建一个枚举类型:DataProvider.cs { { 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.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); 为了实现兼容,大家还是都按完整的来写,就不会有错了。 |
|
来自: weiledream > 《asp.net》