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

用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="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，不受任何影响。欢迎大家批评指正：）

 

全文完