用C#实现的数据库抽象工厂(三)

用C#实现的数据库抽象工厂(三)

以下是在应用时真正要调用到的类：

(6)DbAccess.cs

using System;
using System.Data;

namespace DbService
{
///

1<summary>   
2/// DbAccess类,即进行数据库访问时需要调用的类   
3/// </summary>

public sealed class DbAccess
{
///

1<summary>   
2/// DbAccess构造函数   
3/// </summary>

private DbAccess()
{
}

///

1<summary>   
2/// 无条件查询操作,即查询表中所有记录   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strColumn"/>

列名组
///

1<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;
}

///

1<summary>   
2/// 条件查询操作   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strColumn"/>

列名组
///

1<param name="strCondition"/>

条件
///

1<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;
}

///

1<summary>   
2/// 单条记录的插入操作   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strColumn"/>

列名组
///

1<param name="strValue"/>

值组
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();
}
}

///

1<summary>   
2/// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录   
3/// </summary>

///

1<param name="ds"/>

批量记录组成的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();
}
}

///

1<summary>   
2/// 无条件删除操作,即删除表中所有记录   
3/// </summary>

///

1<param name="strTableName"/>

表名
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();
}
}

///

1<summary>   
2/// 条件删除操作   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strCondition"/>

条件
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();
}
}

///

1<summary>   
2/// 无条件更新操作,即更新表中所有记录   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strColumn"/>

列名组
///

1<param name="strValue"/>

值组
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();
}
}

///

1<summary>   
2/// 条件更新操作   
3/// </summary>

///

1<param name="strTableName"/>

表名
///

1<param name="strColumn"/>

列名组
///

1<param name="strValue"/>

值组
///

1<param name="strCondition"/>

条件
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下增加一些关于数据库连接字符串的变量：

 1<appsettings>
 2<add key="DatabaseType" value="SqlServer"></add>
 3<add key="SqlServerServer" value="Ricky"></add>
 4<add key="SqlServerDatabase" value="test"></add>
 5<add key="SqlServerUid" value="sa"></add>
 6<add key="SqlServerPwd" value="henhaoba"></add>
 7<add key="OleDbProvider" value="Microsoft.jet.oledb.4.0"></add>
 8<add key="OleDbDataSource" value="D:\test.mdb"></add>
 9<add key="OdbcDriver" value="Microsoft Access Driver (*.mdb)"></add>
10<add key="OdbcDBQ" value="d:\test.mdb"></add>
11</appsettings>

现在一切OK，大家可以通过改变Web.config中的变量来使用不同的数据库连接方式(SqlServer专用连接、OleDb连接和Odbc连接)连接不同的数据库，同时整个使用仍通过DbAccess，不受任何影响。欢迎大家批评指正：）

全文完

Published At
Categories with Web编程
Tagged with
comments powered by Disqus