在项目中通常可能会使用不同的数据源,可能是SQL Server也可能是ACCESS或者是Oracle,那么如何保证在使用不同数据源的时候,使项目代码更改的代价最小呢?
对,使用工厂模式.在Net1.1的时候,这需要项目实施者自己来完成.在Net2.0中,MS已经新增了几个用于实施工厂模式的类库. 首先我现在应用程序当前目录下新建Databases目录,再新建一个Access数据库与Sqlserver数据库 其中这2个数据库的结构都是一样的,都包含一个SampleData表,有ID,与IntegerValue字段然后回到VS中新建一个WinForm工厂模式连接数据库 项目,然后编辑App.Config文件如下:
| xml version="1.0" encoding="utf-8" ?> <configuration> <connectionStrings> <add name="SQL Server" providerName="System.Data.SqlClient" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Databases\MyData.mdf;Integrated Security=True;User Instance=True" /> <add name="MS Access" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Databases\MyData.mdb;Persist Security Info=True" /> connectionStrings> configuration> |
然后在主窗体中的Button事件中编写如下代码
| private void getDataButton_Click(object sender, EventArgs e) { try { //创建一个新的StopWatch来监视连接性能[Net2.0新增] Stopwatch myWatch = new Stopwatch(); // 开始计算connection and retrieval of data所花费的时间 myWatch.Start(); // 选择要使用的数据源 string strDataSource="MS Access"; // 根据选者的数据源获得连接字符串的配置对象 ConnectionStringSettings objConnectionSettings = ConfigurationManager.ConnectionStrings[strDataSource]; // 通过配置文件创建数据库驱动工厂的实例 DbProviderFactory objProviderFactory = DbProviderFactories.GetFactory(objConnectionSettings.ProviderName); // 通过数据库驱动工厂创建DBConnection实例 using (DbConnection objConnection = objProviderFactory.CreateConnection()) { // 从 objConnectionSettings 中获取连接字符串 objConnection.ConnectionString = objConnectionSettings.ConnectionString; // 打开 connection objConnection.Open(); // 通过数据驱动工厂创建 数据适配器和 Command DbDataAdapter myAdapter = objProviderFactory.CreateDataAdapter(); DbCommand myCommand = objProviderFactory.CreateCommand(); string myQuery = "SELECT * FROM SampleData"; DataSet myDataSet = new DataSet(); myCommand.Connection = objConnection; myCommand.CommandText = myQuery; myAdapter.SelectCommand = myCommand; myAdapter.Fill(myDataSet); displayDataGridView.DataSource = myDataSet.Tables[0]; // 停止StopWatch来查看连接和返回数据所花费的时间 myWatch.Stop(); elapsedTimeTextLabel.Text = "消耗时间: " + myWatch.ElapsedMilliseconds.ToString() + " ms"; providerLabel.Text = "数据驱动: " + objConnectionSettings.ProviderName.ToString(); connectionStringLabel.Text = objConnectionSettings.ConnectionString.ToString(); } } catch { MessageBox.Show("出现错误.", "Alert"); } } } |
这样,只需要更改strDataSource就可以使用不同的数据源,而且整个项目都不需要为不同的数据库而烦恼
=====================================================================================================使用设计模式构建通用数据库访问类
在应用程序的设计中，数据库的访问是非常重要的，我们通常需要将对数据库的访问集中起来，以保证良好的封装性和可维护性。在.Net中，数据库的访问，对于微软自家的SqlServer和其他数据库（支持OleDb），采用不同的访问方法，这些类分别分布于System.Data.SqlClient和System.Data.OleDb名称空间中。微软后来又推出了专门用于访问Oracle数据库的类库。我们希望在编写应用系统的时候，不因这么多类的不同而受到影响，能够尽量做到数据库无关，当后台数据库发生变更的时候，不需要更改客户端的代码。
这就需要我们在实际开发过程中将这些数据库访问类再作一次封装。经过这样的封装，不仅可以达到上述的目标，还可以减少操作数据库的步骤，减少代码编写量。在这个方面，微软为我们提供了Application Block，但是，可惜的是目前只支持Sql Server。这里，介绍一种在实际应用中得到了非常好的效果的实作策略——笔者编写的Websharp框架中的数据访问结构。Factory设计模式是使用的主要方法。
我们先来看看Factory的含义：定义一个用于创建对象的接口，让子类决定实例化哪一个类。Factory Method使一个类的实例化延迟到其子类。我们这里可能会处理对多种数据库的操作，因此，需要首先定义一个操纵数据库的接口，然后，根据数据库的不同，由类工厂决定实例化哪个类。
下面，我们首先来定义这个访问接口。为了方便说明问题，我们在这里只列出了比较少的方法，其他的方法是很容易参照添加的。
| public interface DataAccess { DatabaseType DatabaseType{get;} //数据库类型 IDbConnection DbConnection{get;} //得到数据库连接 void Open(); //打开数据库连接 void Close(); //关闭数据库连接 IDbTransaction BeginTransaction(); //开始一个事务 int ExecuteNonQuery(string commandText); //执行Sql语句 DataSet ExecuteDataset(string commandText);//执行Sql，返回DataSet } |
因为，DataAccess的具体实现类有一些共同的方法，所以，先从DataAccess实现一个抽象的AbstractDataAccess类，包含一些公用方法。然后，我们分别为Sql Server、Oracle和OleDb数据库编写三个数据访问的具体实现类：
| public sealed class MSSqlDataAccess : AbstractDataAccess { ……//具体实现代码。 }
public class OleDbDataAccess : AbstractDataAccess { ……//具体实现代码。 }
public class OracleDataAccess : AbstractDataAccess { ……//具体实现代码。 } |
现在我们已经完成了所要的功能，下面，我们需要创建一个Factory类，来实现自动数据库切换的管理。这个类很简单，主要的功能就是根据数据库类型，返回适当的数据库操纵类。
| public sealed class DataAccessFactory { private DataAccessFactory(){} private static PersistenceProperty defaultPersistenceProperty; public static PersistenceProperty DefaultPersistenceProperty { get{return defaultPersistenceProperty;} set{defaultPersistenceProperty=value;} } public static DataAccess CreateDataAccess(PersistenceProperty pp) { DataAccess dataAccess; switch(pp.DatabaseType) { case(DatabaseType.MSSQLServer): dataAccess = new MSSqlDataAccess(pp.ConnectionString); break; case(DatabaseType.Oracle): dataAccess = new OracleDataAccess(pp.ConnectionString); break; case(DatabaseType.OleDBSupported): dataAccess = new OleDbDataAccess(pp.ConnectionString); break; default: dataAccess=new MSSqlDataAccess(pp.ConnectionString); break; } return dataAccess; } public static DataAccess CreateDataAccess() { return CreateDataAccess(defaultPersistenceProperty); } } |
好了，现在，一切都完成了，客户端在代码调用的时候，可能就是采用如下形式：
| PersistenceProperty pp = new PersistenceProperty(); pp.ConnectionString = "server=127.0.0.1;uid=sa;pwd=;database=Northwind;"; pp.DatabaseType = DatabaseType. MSSQLServer; pp.UserID = “sa”; pp.Password = “”; DataAccess db= DataAccessFactory.CreateDataAccess(pp) db.Open(); ……//db.需要的操作 db.Close();
或者，如果事先设定了DataAccessFactory的DefaultPersistenceProperty属性，可以直接使用 DataAccess db= DataAccessFactory.CreateDataAccess() 方法创建DataAccess实例。 |
当数据库发生变化的时候，只需要修改PersistenceProperty的值，客户端不会感觉到变化，也不用去关心。这样，实现了良好的封装性。当然，前提是，你在编写程序的时候，没有用到特定数据库的特性，例如，Sql Server的专用函数。
=================================================================================数据库访问一般有不同中数据库，比如Oracle，Sqlserver，Mysql等。 怎样使得程序有一定的通用性。我们可以使用工厂模式来实现。具体代码如下：
1 抽象类
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; namespace HHSCInfor.App_Code.Database 
...{ 
/** <summary> 
/// Summary description for absDB 
/// </summary> public abstract class AbsDB ...{ public AbsDB() ...{ // // TODO: Add constructor logic here // } //得到数据库连接 public abstract IDbConnection Connection ...{ get;} //打开数据库连接 public abstract void Open(); //关闭数据库连接 public abstract void Close(); //开始一个事务 public abstract void BeginTrans(); //提交一个事务 public abstract void CommitTrans(); //回滚一个事务 public abstract void RollbackTrans(); //执行Sql语句，没有返回值 public abstract void ExeSql(string strSql, string[] strParams, object[] objValues); //执行Sql，返回DataSet public abstract DataSet ExeSqlForDataSet(string QueryString); } 
} 2 Oracle连接的实例化
using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OracleClient; namespace HHSCInfor.App_Code.Database ...{ /** <summary> /// Summary description for OracleDB /// </summary> internal class OracleDB : AbsDB ...{ /** <summary> /// 数据库连接状态表示 /// </summary> private string OpenFlag = "OPEN"; public OracleDB() ...{ } //数据库连接 private OracleConnection conn; //事务处理类 private OracleTransaction trans; //指示当前是否正处于事务中 private bool inTransactionFlag = false; public override IDbConnection Connection ...{ get ...{ return this.conn; } } public OracleDB(string strConnection) ...{ this.conn = new OracleConnection(strConnection); } public override void Open() ...{ if (conn.State.ToString().ToUpper() != OpenFlag) this.conn.Open(); } public override void Close() ...{ if (conn.State.ToString().ToUpper() == OpenFlag) this.conn.Close(); } public override void BeginTrans() ...{ trans = conn.BeginTransaction(); inTransactionFlag = true; } public override void CommitTrans() ...{ trans.Commit(); inTransactionFlag = false; } public override void RollbackTrans() ...{ trans.Rollback(); inTransactionFlag = false; } public override void ExeSql(string strSql, string[] strParams, object[] strValues) ...{ //创建命令 OracleCommand cmd = new OracleCommand(); //设置连接 cmd.Connection=this.conn ; //比较参数个数和参数值数组的长度是否匹配 if ((strParams != null) && (strParams.Length != strValues.Length)) ...{ throw new Exception("查询参数和值不对应!"); } cmd.CommandText = strSql; if (strParams != null) ...{ for (int i = 0; i < strParams.Length; i++) ...{ cmd.Parameters.Add(strParams[i], strValues[i]); } } //执行SQL语句 cmd.ExecuteNonQuery(); } /** <summary> /// 执行数据库查询并将结果用数据集(DataSet)的形式返回 /// </summary> /// <param name="QueryString">SQL语句</param> /// <returns></returns> public override DataSet ExeSqlForDataSet(string QueryString) ...{ //创建命令 OracleCommand cmd = new OracleCommand(); //设置连接 cmd.Connection = this.conn; //传入查询语句 cmd.CommandText = QueryString; //创建数据集 DataSet ds = new DataSet(); //创建适配器 OracleDataAdapter ad = new OracleDataAdapter(); //适配器命令 ad.SelectCommand = cmd; //填充到数据集(DataSet) ad.Fill(ds); //返回结果数据集 return ds; } } }
3 SqlServer的实例化（自己对照上面写个，我也没写。^_^）
4 根据不同的string连接来创建不同的实例。
web.config中设置共用连接:
<appSettings>
<add key="DBConnStr" value="Data Source=INFOPLAT;User ID=InfoManager;Password=admin;" /> </appSettings>using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.OracleClient; namespace HHSCInfor.App_Code.Database ...{ /** <summary> /// Summary description for DBConn /// </summary> public class DBConn ...{ public DBConn() ...{ } /** <summary> /// 根据不同的字符串连接来使用不同的处理程序。 /// 工厂方法应用(可以根据不同的strConnection创建不同的连接)。 /// </summary> /// <param name="strConnection">数据库连接字符串</param> /// <returns></returns> public static AbsDB GetDBConn() ...{ // 只有一个Oracle连接时使用，如果有多个，在此添加。在Web.Config里配置。 string strConnection = System.Configuration.ConfigurationManager.AppSettings["DBConnStr"]; // 创建OracleDB连接对象 return new OracleDB(strConnection); /**比如有sqlserver添加如下 ///if(strConnection=?) ///{ /// return(SqlServerDB(strConnection)); ///} } } } 5 测试程序：
AbsDB conn = DBConn.GetDBConn(); DataSet ds = conn.ExeSqlForDataSet("select * from sysFunction"); this.Label1.Text = ds.Tables[0].Rows[2]["功能名称"].ToString(); conn.Close(); 我的测试通过。
如果是连接池用单例来实现。
=========================================================================
- OracleDataOperate、SqlserverDataOperate、MysqlDataOperate，分别代表Oracle、Sqlserver、Mysql这三种数据库的操作类。继承自AbstractDataOperate
- AbstractDataOperate是一个抽象类，包含了那些不同种类数据库都是一样代码的操作方法。继承自DataOperate
- DataOperate是上面说的数据操作类的统一接口，只有两个方法：取得一条记录、插入一条记录。
- DataOperateFactory是一个工厂方法，统一用它的方法来得到数据库操作类的实例。
- SampleClass是我们系统的某个功能模块的类。
- People是一个实体类，代表一条记录。三个字段 oid唯一标识符、name姓名、date生日。
- 从例子中我们可以看到什么是面向接口的编程方式。SampleClass使用数据操作类可以不必关心具体是那个类，只要是符合接口的都行
- 要实例？只须调用DataOperateFactory.getInstance()即可，其它的交于DataOperateFactory这个工厂来做吧，使用端什么都不用关心。
- 我们要支持新的数据库类型，只须要象OracelDataOperate那样，再写一个继承AbstractDataOperate的类即可，比如SysbaseDataOperate。然后到DataOperateFactory中加入相应代码即可。
- 如果我们想要可配置性更高，可以用private static int dataType = MYSQL;中的值设置到一个文本文件中。
接下来我们用一个例子来说明这个简单的工厂模式.假如我们要输入一个人的姓名，
有两个方式，一种是“firstname lastname” 和“fristname,lastname”形式.我们的任务就是要确定lastname和firstname是否是逗号来确定姓名的先后.
第一步，设计一个父类:
public class CName { protected string frName ,lName; public string getFristName() { return frName;} public string getLastName() { return lName;} }
第二步,设计两个子类,它是由类CName的派生的。它们现实了接口，并在构造function中将名字分成了两部分.在CFirstFirst类中，做了一个简单的假设：最后一个空格前面的所有部分都属与firstname.
public class CFirstFirst : CName { public CFirstFirst (string sName) { int i = sName.Trim().Indexof(” “); if ( i >0 ) { frName = name.Substring(0,i).Trim(); lName = name.Substring(i+1).Trim(); } else { lName = sName; frName = “”; } } }
在LastFist类中，用逗号给lastname划分界限.当空格或逗号不存在时，两个类都提供了错误校正处理.
public class LastFrist :CName { public LastFrist(string sName) { int i = sName.Indexof(”,”); if ( i>0 ) { if ( i >0 ) { lName = name.Substring(0,i).Trim(); frName = name.Substring(i+1).Trim(); } else { lName = sName; frName = “”; } } } }
两种情况下，都把分开的名字保存在基类的CName中保护变量中.所以在派生类中根本不需要任何getFirstName和getLastName方法，因为基类中已给出.
第三步: 构造简单工厂.
现在很容易给出简单工厂类.只检查逗号来决定返回那个类的实例
public class NameFactory () { public CName getName(string sname) { int i = sname.Indexof(”,”); if ( i >0 ) { return new LastFrist (sname); } else { return new CFirstFirst (sname); } } }
第四步。使用工厂类:
string sName = “cedy hao”; NameFactory nf = new NameFactory (); CName name = nf.getName(sName); string sFristName = name.getFristName(); string sLastName = name.getLastName();
这种方法把和数据相关的问题与类的其它的方法分隔开来。采用抽象工厂模式设计出的系统类图如下： 数据库层抽象工厂解决方案归档： (1)AbstractDbFactory.cs
using System;
using System.Data;namespace DbService
{ /// <summary> /// 数据库抽象工厂接口 /// </summary> public interface AbstractDbFactory { /// <summary> /// 建立默认连接 /// </summary> /// <returns>数据库连接</returns> IDbConnection CreateConnection();/// <summary> /// 根据连接字符串建立Connection对象 /// </summary> /// <param name="strConn">连接字符串</param> /// <returns>Connection对象</returns>
IDbConnection CreateConnection(string strConn);/// <summary> /// 建立Command对象 /// </summary> /// <returns>Command对象</returns>
IDbCommand CreateCommand();/// <summary> /// 建立DataAdapter对象 /// </summary> /// <returns>DataAdapter对象</returns>
IDbDataAdapter CreateDataAdapter();/// <summary> /// 根据Connection建立Transaction /// </summary> /// <param name="myDbConnection">Connection对象</param> /// <returns>Transaction对象</returns>
IDbTransaction CreateTransaction(IDbConnection myDbConnection);/// <summary> /// 根据Command建立DataReader /// </summary> /// <param name="myDbCommand">Command对象</param> /// <returns>DataReader对象</returns>
IDataReader CreateDataReader(IDbCommand myDbCommand);/// <summary> /// 获得连接字符串 /// </summary> /// <returns>连接字符串</returns>
string GetConnectionString(); } }(2)Factory.cs
using System;
using System.Configuration;namespace DbService
{ /// <summary> /// Factory类 /// </summary> public sealed class Factory { private static volatile Factory singleFactory = null; private static object syncObj = new object(); /// <summary> /// Factory类构造函数 /// </summary> private Factory() { }/// <summary> /// 获得Factory类的实例 /// </summary> /// <returns>Factory类实例</returns>
public static Factory GetInstance() { if(singleFactory == null) { lock(syncObj) { if(singleFactory == null) { singleFactory = new Factory(); } } } return singleFactory; }/// <summary> /// 建立Factory类实例 /// </summary> /// <returns>Factory类实例</returns>
public AbstractDbFactory CreateInstance() { AbstractDbFactory abstractDbFactory = null; switch(ConfigurationSettings.AppSettings["DatabaseType"].ToLower()) { case "sqlserver": { abstractDbFactory = new SqlFactory(); break; } case "oledb": { abstractDbFactory = new OleDbFactory(); break; } case "odbc": { abstractDbFactory = new OdbcFactory(); break; } } return abstractDbFactory; } } }以下3个类分别是Factory针对SqlServer专用连接、OleDb连接和Odbc连接时的具体实现：
(3)SqlFactory.cs
using System;
using System.Data; using System.Data.SqlClient; using System.Configuration;namespace DbService
{ /// <summary> /// 针对SqlServer专用连接的工厂 /// </summary> public class SqlFactory : AbstractDbFactory { /// <summary> /// 构造函数 /// </summary> public SqlFactory() { }/// <summary> /// 建立默认Connection对象 /// </summary> /// <returns>Connection对象</returns> public IDbConnection CreateConnection()
{ return new SqlConnection(); }/// <summary> /// 根据连接字符串建立Connection对象 /// </summary> /// <param name="strConn">连接字符串</param> /// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn) { return new SqlConnection(strConn); }/// <summary> /// 建立Command对象 /// </summary> /// <returns>Command对象</returns>
public IDbCommand CreateCommand() { return new SqlCommand(); }/// <summary> /// 建立DataAdapter对象 /// </summary> /// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter() { return new SqlDataAdapter(); }/// <summary> /// 根据Connection建立Transaction /// </summary> /// <param name="myDbConnection">Connection对象</param> /// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) { return myDbConnection.BeginTransaction(); }/// <summary> /// 根据Command建立DataReader /// </summary> /// <param name="myDbCommand">Command对象</param> /// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand) { return myDbCommand.ExecuteReader(); }/// <summary> /// 获得连接字符串 /// </summary> /// <returns>连接字符串</returns>
public string GetConnectionString() { string strServer = ConfigurationSettings.AppSettings["SqlServerServer"]; string strDatabase = ConfigurationSettings.AppSettings["SqlServerDatabase"]; string strUid = ConfigurationSettings.AppSettings["SqlServerUid"]; string strPwd = ConfigurationSettings.AppSettings["SqlServerPwd"]; string strConnectionString = "Server = " + strServer + "; Database = " + strDatabase + "; Uid = " + strUid + "; Pwd = " + strPwd + ";"; return strConnectionString; } } }(4)OleDbFactory.cs
using System;
using System.Data; using System.Data.OleDb; using System.Configuration;namespace DbService
{ /// <summary> /// 针对OleDb连接的工厂 /// </summary> public class OleDbFactory : AbstractDbFactory { /// <summary> /// 构造函数 /// </summary> public OleDbFactory() { }/// <summary> /// 建立默认Connection对象 /// </summary> /// <returns>Connection对象</returns>
public IDbConnection CreateConnection() { return new OleDbConnection(); }/// <summary> /// 根据连接字符串建立Connection对象 /// </summary> /// <param name="strConn">连接字符串</param> /// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn) { return new OleDbConnection(strConn); }/// <summary> /// 建立Command对象 /// </summary> /// <returns>Command对象</returns>
public IDbCommand CreateCommand() { return new OleDbCommand(); }/// <summary> /// 建立DataAdapter对象 /// </summary> /// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter() { return new OleDbDataAdapter(); }/// <summary> /// 根据Connection建立Transaction /// </summary> /// <param name="myDbConnection">Connection对象</param> /// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) { return myDbConnection.BeginTransaction(); }/// <summary> /// 根据Command建立DataReader /// </summary> /// <param name="myDbCommand">Command对象</param> /// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand) { return myDbCommand.ExecuteReader(); }/// <summary> /// 获得连接字符串 /// </summary> /// <returns>连接字符串</returns>
public string GetConnectionString() { string strProvider = ConfigurationSettings.AppSettings["OleDbProvider"]; string strDataSource = ConfigurationSettings.AppSettings["OleDbDataSource"]; string strConnectionString = "Provider = " + strProvider + ";Data Source = " + strDataSource + ";"; return strConnectionString; } } }(5)OdbcFactory.cs
using System;
using System.Data; using System.Data.Odbc; using System.Configuration;namespace DbService
{ /// <summary> /// 针对Odbc连接的工厂 /// </summary> public class OdbcFactory : AbstractDbFactory { /// <summary> /// 构造函数 /// </summary> public OdbcFactory() { }/// <summary> /// 建立默认Connection对象 /// </summary> /// <returns>Connection对象</returns>
public IDbConnection CreateConnection() { return new OdbcConnection(); }/// <summary> /// 根据连接字符串建立Connection对象 /// </summary> /// <param name="strConn">连接字符串</param> /// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn) { return new OdbcConnection(strConn); }/// <summary> /// 建立Command对象 /// </summary> /// <returns>Command对象</returns>
public IDbCommand CreateCommand() { return new OdbcCommand(); }/// <summary> /// 建立DataAdapter对象 /// </summary> /// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter() { return new OdbcDataAdapter(); }/// <summary> /// 根据Connection建立Transaction /// </summary> /// <param name="myDbConnection">Connection对象</param> /// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) { return myDbConnection.BeginTransaction(); }/// <summary> /// 根据Command建立DataReader /// </summary> /// <param name="myDbCommand">Command对象</param> /// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand) { return myDbCommand.ExecuteReader(); }/// <summary> /// 获得连接字符串 /// </summary> /// <returns></returns>
public string GetConnectionString() { string strDriver = ConfigurationSettings.AppSettings["OdbcDriver"]; string strDBQ = ConfigurationSettings.AppSettings["OdbcDBQ"]; string strConnectionString = "Driver={" + strDriver + "}; DBQ=" + strDBQ + ";"; return strConnectionString; } } }
用C#实现的数据库抽象工厂(三)
以下是在应用时真正要调用到的类：
(6)DbAccess.cs
using System;
using System.Data;namespace DbService
{ /// <summary> /// DbAccess类,即进行数据库访问时需要调用的类 /// </summary> public sealed class DbAccess { /// <summary> /// DbAccess构造函数 /// </summary> private DbAccess() { }/// <summary> /// 无条件查询操作,即查询表中所有记录 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strColumn">列名组</param> /// <returns>无条件查询结果</returns>
public static DataSet SelectAll(string strTableName, string[] strColumn) { DataSet ds = new DataSet(); Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter(); try { string strSql = "Select "; for(int i = 0; i < strColumn.Length - 1; i++) { strSql += (strColumn[i] + ", "); } strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName); concreteDbCommand.CommandText = strSql; concreteDbAdapter.SelectCommand = concreteDbCommand; concreteDbAdapter.Fill(ds); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); ds.Clear(); throw; } finally { concreteDbConn.Close(); } return ds; }/// <summary> /// 条件查询操作 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strColumn">列名组</param> /// <param name="strCondition">条件</param> /// <returns>条件查询结果</returns>
public static DataSet Select(string strTableName, string[] strColumn, string strCondition) { DataSet ds = new DataSet(); Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; IDbDataAdapter concreteDbAdapter = abstractDbFactory.CreateDataAdapter(); try { string strSql = "Select "; for(int i = 0; i < strColumn.Length - 1; i++) { strSql += (strColumn[i] + ", "); } strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName + " Where " + strCondition); concreteDbCommand.CommandText = strSql; concreteDbAdapter.SelectCommand = concreteDbCommand; concreteDbAdapter.Fill(ds); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); ds.Clear(); throw; } finally { concreteDbConn.Close(); } return ds; }/// <summary> /// 单条记录的插入操作 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strColumn">列名组</param> /// <param name="strValue">值组</param>
public static void Insert(string strTableName, string[] strColumn, object[] strValue) { Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { string strSql = "Insert INTO " + strTableName + " ("; for(int i = 0; i < strColumn.Length - 1; i++) { strSql += (strColumn[i] + ", "); } strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('"); for(int i = 0; i < strValue.Length - 1; i++) { strSql += (strValue[i] + "', '"); } strSql += (strValue[strValue.Length - 1] + "')"); concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; } finally { concreteDbConn.Close(); } }/// <summary> /// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录 /// </summary> /// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
public static void InsertSet(ref DataSet ds) { Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { foreach(DataTable dt in ds.Tables) { foreach(DataRow dr in dt.Rows) { string strSql = "Insert INTO " + dt.TableName + " ("; for(int i = 0; i < dt.Columns.Count - 1; i++) { strSql += (dt.Columns[i].Caption + ", "); } strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('"); for(int i = 0; i < dt.Columns.Count - 1; i++) { strSql += (dr[i] + "', '"); } strSql += (dr[dt.Columns.Count - 1] + "')"); concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); } } concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; }finally
{ concreteDbConn.Close(); } }/// <summary> /// 无条件删除操作,即删除表中所有记录 /// </summary> /// <param name="strTableName">表名</param>
public static void DeleteAll(string strTableName) { Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { string strSql = "Delete FROM " + strTableName; concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; } finally { concreteDbConn.Close(); } }/// <summary> /// 条件删除操作 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strCondition">条件</param>
public static void Delete(string strTableName, string strCondition) { Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { string strSql = "Delete FROM " + strTableName + " Where " + strCondition; concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; } finally { concreteDbConn.Close(); } }/// <summary> /// 无条件更新操作,即更新表中所有记录 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strColumn">列名组</param> /// <param name="strValue">值组</param>
public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue) { Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { string strSql = "Update " + strTableName + " SET "; for(int i = 0; i < strColumn.Length - 1; i++) { strSql += (strColumn[i] + " = '" + strValue[i] + "', "); } strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' "); concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; } finally { concreteDbConn.Close(); } }/// <summary> /// 条件更新操作 /// </summary> /// <param name="strTableName">表名</param> /// <param name="strColumn">列名组</param> /// <param name="strValue">值组</param> /// <param name="strCondition">条件</param> public static void Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
{ Factory factory = Factory.GetInstance(); AbstractDbFactory abstractDbFactory = factory.CreateInstance(); IDbConnection concreteDbConn = abstractDbFactory.CreateConnection(); concreteDbConn.ConnectionString = abstractDbFactory.GetConnectionString(); concreteDbConn.Open(); IDbCommand concreteDbCommand = abstractDbFactory.CreateCommand(); IDbTransaction concreteDbTrans = abstractDbFactory.CreateTransaction(concreteDbConn); concreteDbCommand.Connection = concreteDbConn; concreteDbCommand.Transaction = concreteDbTrans; try { string strSql = "Update " + strTableName + " SET "; for(int i = 0; i < strColumn.Length - 1; i++) { strSql += (strColumn[i] + " = '" + strValue[i] + "', "); } strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " Where " + strCondition); concreteDbCommand.CommandText = strSql; concreteDbCommand.ExecuteNonQuery(); concreteDbTrans.Commit(); } catch { concreteDbTrans.Rollback(); throw; } finally { concreteDbConn.Close(); } } } }最后一步，在Web.config中的根结点configuration下增加一些关于数据库连接字符串的变量：
<appSettings>
<add key="DatabaseType" value="SqlServer" /> <add key="SqlServerServer" value="flyingyoko" /> <add key="SqlServerDatabase" value="test" /> <add key="SqlServerUid" value="sa" /> <add key="SqlServerPwd" value="****" /> <add key="OleDbProvider" value="Microsoft.jet.oledb.4.0" /> <add key="OleDbDataSource" value="D:\test.mdb" /> <add key="OdbcDriver" value="Microsoft Access Driver (*.mdb)" /> <add key="OdbcDBQ" value="d:\test.mdb" /> </appSettings>现在一切OK，大家可以通过改变Web.config中的变量来使用不同的数据库连接方式(SqlServer专用连接、OleDb连接和Odbc连接)连接不同的数据库，同时整个使用仍通过DbAccess，不受任何影响。
============================================================今天看了看设计模式中的工场模式，感觉还不错，一时兴起，便将我原来利用简单工场模式写的一个操作数据库的类大至改成了工场模式，算是加深我对工场模式的理解吧。下面来看看实现过程： 一。采用工场模式实现对Connection对象的操作
using System; using System.Data; using System.Data.Odbc; using System.Data.OleDb; using System.Data.SqlClient; namespace DBFactory 
{ 
/**//// <summary>抽象Connection工場類</summary> 
public abstract class ConnectionFactory { protected string connectionString; public ConnectionFactory(string connString) { this.connectionString = connString; 
} public abstract IDbConnection GetConnection(); } /**//// <summary>實現SqlConnection對象的具體工作類</summary> public class SqlConnection : ConnectionFactory { public SqlConnection() : this(null){} public SqlConnection(string connString) : base(connString){} public override IDbConnection GetConnection() { if(connectionString!=null) { return new System.Data.SqlClient.SqlConnection(connectionString); } else { return new System.Data.SqlClient.SqlConnection(); } } } /**//// <summary>實現OleDbConnection對象的具體工場類</summary> public class AccessConnection :ConnectionFactory { public AccessConnection() : this(null){} public AccessConnection(string connString) :base(connString){} public override IDbConnection GetConnection() { if(connectionString!=null) { return new OleDbConnection(connectionString); } else { return new OleDbConnection(); } } } 
} 
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; namespace DBFactory { /**//// <summary>抽象CommandFactory類</summary> public abstract class CommandFactory { protected string commandText; protected IDbConnection iConn; public CommandFactory(IDbConnection conn,string commText) { this.iConn = conn; this.commandText = commText; } public abstract IDbCommand GetCommand(); } /**//// <summary>實現SqlCommand對象的具體類</summary> public class SqlCommand : CommandFactory { public SqlCommand(IDbConnection conn,string commText) : base(conn,commText){} public override IDbCommand GetCommand() { return new System.Data.SqlClient.SqlCommand(commandText,(System.Data.SqlClient.SqlConnection)iConn); } } /**//// <summary>實現OleDbCommand對象的具體類</summary> public class AccessCommand : CommandFactory { public AccessCommand(IDbConnection conn,string commText) : base(conn,commText){} public override IDbCommand GetCommand() { return new OleDbCommand(commandText,(OleDbConnection)iConn); } } }
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; namespace DBFactory { /**//// <summary>抽象DataAdapterFactory工場類</summary> public abstract class DataAdapterFactory { protected IDbCommand iComm; public DataAdapterFactory(IDbCommand comm) { this.iComm = comm; } public abstract DbDataAdapter GetDataAdapter(); } /**//// <summary>實現SqlDataAdapter對象的具體類</summary> public class SqlDataAdapter : DataAdapterFactory { public SqlDataAdapter(IDbCommand comm) : base(comm){} public override DbDataAdapter GetDataAdapter() { return new System.Data.SqlClient.SqlDataAdapter((System.Data.SqlClient.SqlCommand)iComm); } } /**//// <summary>實現OleDbDataAdapter對象的具體類</summary> public class AccessDataAdapter : DataAdapterFactory { public AccessDataAdapter(IDbCommand comm) : base(comm){} public override DbDataAdapter GetDataAdapter() { return new OleDbDataAdapter((OleDbCommand)iComm); } } } 
四。这里利用简单工场模式来返回以上的抽象工场对象
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; namespace DBFactory { public class SimpleFactory { public SimpleFactory(){} /**//// <summary>返回抽象的ConnectionFactory工場對象</summary> public static ConnectionFactory GetConnFactory(string connString,string dbType) { ConnectionFactory factory; switch(dbType.ToUpper()) { case "SQL": factory = new DBFactory.SqlConnection(connString); break; case "ACCESS": factory = new DBFactory.AccessConnection(connString); break; default: factory = null; break; } return factory; } /**//// <summary>返回抽象的CommandFactory工場對象</summary> public static CommandFactory GetCommFactory(IDbConnection conn,string commText,string dbType) { CommandFactory factory; switch(dbType.ToUpper()) { case "SQL": factory = new DBFactory.SqlCommand(conn,commText); break; case "ACCESS": factory = new DBFactory.AccessCommand(conn,commText); break; default: factory = null; break; } return factory; } /**//// <summary>返回抽象的DataAdapterFactory工場對象</summary> public static DataAdapterFactory GetDataAdapterFactory(IDbCommand comm,string dbType) { DataAdapterFactory factory; switch(dbType.ToUpper()) { case "SQL": factory = new DBFactory.SqlDataAdapter(comm); break; case "ACCESS": factory = new DBFactory.AccessDataAdapter(comm); break; default: factory = null; break; } return factory; } } } 
using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; namespace DBFactory { public class ExecuteDB { private static string connectionString; private static string dbType; public ExecuteDB(){} /**//// <summary>數據庫連接字符串</summary> public static string ConnectionString { get { if(connectionString==null) connectionString = ConfigurationSettings.AppSettings["ConnectionString"]; return connectionString; } set{connectionString = value;} } /**//// <summary>數據庫類型</summary> public static string DBType { get { if(dbType==null) dbType = ConfigurationSettings.AppSettings["DataBaseType"]; return dbType; } set{dbType=value;} } /**//// <summary>執行SQL語句返回DataSet</summary> public static DataSet ExcuteSql(string sqlString) { DataSet ds = new DataSet(); ConnectionFactory objConn = SimpleFactory.GetConnFactory(ConnectionString,DBType); IDbConnection iConn = objConn.GetConnection(); iConn.Open(); CommandFactory objComm = SimpleFactory.GetCommFactory(iConn,sqlString,DBType); IDbCommand iComm = objComm.GetCommand(); DataAdapterFactory objAdapter = SimpleFactory.GetDataAdapterFactory(iComm,DBType); DbDataAdapter dataAdaper = objAdapter.GetDataAdapter(); dataAdaper.Fill(ds); iComm.Dispose(); iConn.Close(); iConn.Dispose(); return ds; } } } string connString = " data source=192.168.1.9;initial catalog=sqldll;persist security info=False;user id=sa;password=123456;workstation id=Server;packet size=4096 " ; string commString = " select * from tbl_Vip " ; ExecuteDB.ConnectionString = connString; ExecuteDB.DBType = " sql " ; DataGrid1.DataSource = ExecuteDB.ExcuteSql(commString); DataGrid1.DataBind();