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