IDC资讯中心
用c#实现的数据库抽象工厂(三)
以下是在应用时真正要调用到的类：
(6)dbaccess.cs
using system;
using system.data;
namespace dbservice
{
/// <summary>
/// dbaccess类,即进行数据库访问时需要调用的类
/// </summary>
public class dbaccess
{
/// <summary>
/// dbaccess构造函数
/// </summary>
public 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="ricky" />
<add key="sqlserverdatabase" value="test" />
<add key="sqlserveruid" value="sa" />
<add key="sqlserverpwd" value="henhaoba" />
<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，不受任何影响。欢迎大家批评指正：）
全文完
