.NET开发

本类阅读TOP10

·NHibernate快速指南(翻译)
·vs.net 2005中文版下载地址收藏
·【小技巧】一个判断session是否过期的小技巧
·VB/ASP 调用 SQL Server 的存储过程
·?dos下编译.net程序找不到csc.exe文件
·通过Web Services上传和下载文件
·学习笔记(补)《.NET框架程序设计(修订版)》--目录
·VB.NET实现DirectDraw9 (2) 动画
·VB.NET实现DirectDraw9 (1) 托管的DDraw
·建站框架规范书之——文件命名

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
用C#实现的数据库抽象工厂(三)

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

用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,不受任何影响。欢迎大家批评指正:)

 

全文完




相关文章

相关软件