數據庫層抽象工廠解決方案歸檔[不斷升級版]
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）
