工厂模式连接数据库(二)
如果是连接池用单例来实现。
=========================================================================
email: glchengang@hotmail.com
blog: glchengang.yeah.net
- 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;中的值设置到一个文本文件中。
=====================================================================================
简单工厂模式就是根据提供给它的数据,返回几个可能类中的一个类的实例.通常它返回的类都有一个共同的父类和共同的方法,但是每个方法执行的任务不同,而且根据不同的数据进行了优化。比如X是一个基类,xy和xz是从它派生出来的,XFactory类根据给出的参数决定返回那一个子类,返回那一个类的实例与程序员无关,因为这些类有同样的方法,只是实现不同,返回那个类的实例取决于工厂,工厂功能可能很复杂,但通常都是相当简间的.
接下来我们用一个例子来说明这个简单的工厂模式.假如我们要输入一个人的姓名,
有两个方式,一种是“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
{
///
/// 数据库抽象工厂接口
///
public interface AbstractDbFactory
{
///
/// 建立默认连接
///
/// 数据库连接
IDbConnection CreateConnection();
///
/// 根据连接字符串建立Connection对象
///
/// 连接字符串
/// Connection对象
IDbConnection CreateConnection(string strConn);
///
/// 建立Command对象
///
/// Command对象
IDbCommand CreateCommand();
///
/// 建立DataAdapter对象
///
/// DataAdapter对象
IDbDataAdapter CreateDataAdapter();
///
/// 根据Connection建立Transaction
///
/// Connection对象
/// Transaction对象
IDbTransaction CreateTransaction(IDbConnection myDbConnection);
///
/// 根据Command建立DataReader
///
/// Command对象
/// DataReader对象
IDataReader CreateDataReader(IDbCommand myDbCommand);
///
/// 获得连接字符串
///
/// 连接字符串
string GetConnectionString();
}
}
(2)Factory.cs
using System;
using System.Configuration;
namespace DbService
{
///
/// Factory类
///
public sealed class Factory
{
private static volatile Factory singleFactory = null;
private static object syncObj = new object();
///
/// Factory类构造函数
///
private Factory()
{
}
///
/// 获得Factory类的实例
///
/// Factory类实例
public static Factory GetInstance()
{
if(singleFactory == null)
{
lock(syncObj)
{
if(singleFactory == null)
{
singleFactory = new Factory();
}
}
}
return singleFactory;
}
///
/// 建立Factory类实例
///
/// Factory类实例
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
{
///
/// 针对SqlServer专用连接的工厂
///
public class SqlFactory : AbstractDbFactory
{
///
/// 构造函数
///
public SqlFactory()
{
}
///
/// 建立默认Connection对象
///
/// Connection对象
public IDbConnection CreateConnection()
{
return new SqlConnection();
}
///
/// 根据连接字符串建立Connection对象
///
/// 连接字符串
/// Connection对象
public IDbConnection CreateConnection(string strConn)
{
return new SqlConnection(strConn);
}
///
/// 建立Command对象
///
/// Command对象
public IDbCommand CreateCommand()
{
return new SqlCommand();
}
///
/// 建立DataAdapter对象
///
/// DataAdapter对象
public IDbDataAdapter CreateDataAdapter()
{
return new SqlDataAdapter();
}
///
/// 根据Connection建立Transaction
///
/// Connection对象
/// Transaction对象
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
{
return myDbConnection.BeginTransaction();
}
///
/// 根据Command建立DataReader
///
/// Command对象
/// DataReader对象
public IDataReader CreateDataReader(IDbCommand myDbCommand)
{
return myDbCommand.ExecuteReader();
}
///
/// 获得连接字符串
///
/// 连接字符串
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
{
///
/// 针对OleDb连接的工厂
///
public class OleDbFactory : AbstractDbFactory
{
///
/// 构造函数
///
public OleDbFactory()
{
}
///
/// 建立默认Connection对象
///
/// Connection对象
public IDbConnection CreateConnection()
{
return new OleDbConnection();
}
///
/// 根据连接字符串建立Connection对象
///
/// 连接字符串
/// Connection对象
public IDbConnection CreateConnection(string strConn)
{
return new OleDbConnection(strConn);
}
///
/// 建立Command对象
///
/// Command对象
public IDbCommand CreateCommand()
{
return new OleDbCommand();
}
///
/// 建立DataAdapter对象
///
/// DataAdapter对象
public IDbDataAdapter CreateDataAdapter()
{
return new OleDbDataAdapter();
}
///
/// 根据Connection建立Transaction
///
/// Connection对象
/// Transaction对象
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
{
return myDbConnection.BeginTransaction();
}
///
/// 根据Command建立DataReader
///
/// Command对象
/// DataReader对象
public IDataReader CreateDataReader(IDbCommand myDbCommand)
{
return myDbCommand.ExecuteReader();
}
///
/// 获得连接字符串
///
/// 连接字符串
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
{
///
/// 针对Odbc连接的工厂
///
public class OdbcFactory : AbstractDbFactory
{
///
/// 构造函数
///
public OdbcFactory()
{
}
///
/// 建立默认Connection对象
///
/// Connection对象
public IDbConnection CreateConnection()
{
return new OdbcConnection();
}
///
/// 根据连接字符串建立Connection对象
///
/// 连接字符串
/// Connection对象
public IDbConnection CreateConnection(string strConn)
{
return new OdbcConnection(strConn);
}
///
/// 建立Command对象
///
/// Command对象
public IDbCommand CreateCommand()
{
return new OdbcCommand();
}
///
/// 建立DataAdapter对象
///
/// DataAdapter对象
public IDbDataAdapter CreateDataAdapter()
{
return new OdbcDataAdapter();
}
///
/// 根据Connection建立Transaction
///
/// Connection对象
/// Transaction对象
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
{
return myDbConnection.BeginTransaction();
}
///
/// 根据Command建立DataReader
///
/// Command对象
/// DataReader对象
public IDataReader CreateDataReader(IDbCommand myDbCommand)
{
return myDbCommand.ExecuteReader();
}
///
/// 获得连接字符串
///
///
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
{
///
/// DbAccess类,即进行数据库访问时需要调用的类
///
public sealed class DbAccess
{
///
/// DbAccess构造函数
///
private DbAccess()
{
}
///
/// 无条件查询操作,即查询表中所有记录
///
/// 表名
/// 列名组
/// 无条件查询结果
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;
}
///
/// 条件查询操作
///
/// 表名
/// 列名组
/// 条件
/// 条件查询结果
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;
}
///
/// 单条记录的插入操作
///
/// 表名
/// 列名组
/// 值组
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();
}
}
///
/// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
///
/// 批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名
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();
}
}
///
/// 无条件删除操作,即删除表中所有记录
///
/// 表名
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();
}
}
///
/// 条件删除操作
///
/// 表名
/// 条件
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();
}
}
///
/// 无条件更新操作,即更新表中所有记录
///
/// 表名
/// 列名组
/// 值组
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();
}
}
///
/// 条件更新操作
///
/// 表名
/// 列名组
/// 值组
/// 条件
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下增加一些关于数据库连接字符串的变量:
现在一切OK,大家可以通过改变Web.config中的变量来使用不同的数据库连接方式(SqlServer专用连接、OleDb连接和Odbc连接)连接不同的数据库,同时整个使用仍通过DbAccess,不受任何影响。
============================================================
今天看了看设计模式中的工场模式,感觉还不错,一时兴起,便将我原来利用简单工场模式写的一个操作数据库的类大至改成了工场模式,算是加深我对工场模式的理解吧。下面来看看实现过程:
一。采用工场模式实现对Connection对象的操作
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace DBFactory
{
/// 抽象Connection工場類
public abstract class ConnectionFactory
{
protected string connectionString;
public ConnectionFactory(string connString)
{
this.connectionString = connString;
}
public abstract IDbConnection GetConnection();
}
/// 實現SqlConnection對象的具體工作類
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();
}
}
}
/// 實現OleDbConnection對象的具體工場類
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();
}
}
}
}
二。采用工场模式实现对Command对象的操作
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace DBFactory
{
/// 抽象CommandFactory類
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();
}
/// 實現SqlCommand對象的具體類
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);
}
}
/// 實現OleDbCommand對象的具體類
public class AccessCommand : CommandFactory
{
public AccessCommand(IDbConnection conn,string commText) : base(conn,commText){}
public override IDbCommand GetCommand()
{
return new OleDbCommand(commandText,(OleDbConnection)iConn);
}
}
}
三。采用工场模式实现对DataAdapter对象的操作
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace DBFactory
{
/// 抽象DataAdapterFactory工場類
public abstract class DataAdapterFactory
{
protected IDbCommand iComm;
public DataAdapterFactory(IDbCommand comm)
{
this.iComm = comm;
}
public abstract DbDataAdapter GetDataAdapter();
}
/// 實現SqlDataAdapter對象的具體類
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);
}
}
/// 實現OleDbDataAdapter對象的具體類
public class AccessDataAdapter : DataAdapterFactory
{
public AccessDataAdapter(IDbCommand comm) : base(comm){}
public override DbDataAdapter GetDataAdapter()
{
return new OleDbDataAdapter((OleDbCommand)iComm);
}
}
}
四。这里利用简单工场模式来返回以上的抽象工场对象
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace DBFactory
{
public class SimpleFactory
{
public SimpleFactory(){}
/// 返回抽象的ConnectionFactory工場對象
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;
}
/// 返回抽象的CommandFactory工場對象
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;
}
/// 返回抽象的DataAdapterFactory工場對象
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.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(){}
/// 數據庫連接字符串
public static string ConnectionString
{
get
{
if(connectionString==null) connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
return connectionString;
}
set{connectionString = value;}
}
/// 數據庫類型
public static string DBType
{
get
{
if(dbType==null) dbType = ConfigurationSettings.AppSettings["DataBaseType"];
return dbType;
}
set{dbType=value;}
}
/// 執行SQL語句返回DataSet
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 commString = "select * from tbl_Vip";
ExecuteDB.ConnectionString = connString;
ExecuteDB.DBType = "sql";
DataGrid1.DataSource=ExecuteDB.ExcuteSql(commString);
DataGrid1.DataBind();