数据库层抽象工厂解决方案归档[不断升级版]
2024-07-21 02:23:33
供稿：网友
简单工厂模式就是根据提供给它的数据,返回几个可能类中的一个类的实例.通常它返回的类都有一个共同的父类和共同的方法，但是每个方法执行的任务不同，而且根据不同的数据进行了优化。比如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
{
/// <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，不受任何影响。
attention:（原始资料出自何处已忘v_v）
