最近学习了一下设计模式,便尝试用C#对数据库的访问操作采用抽象工厂的模式实现一下,其中与标准的模式略有不同,加入了一些自己的想法,希望大家批评指正,代码共分为6个类:
(1)AbstractDbFactory.cs
using
System;
using
System.Data;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 数据库抽象工厂接口
/// </summary>
public interface AbstractDbFactory
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立默认连接
/// </summary>
/// <returns>数据库连接</returns>
IDbConnection CreateConnection();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据连接字符串建立Connection对象
/// </summary>
/// <param name="strConn">连接字符串</param>
/// <returns>Connection对象</returns>
IDbConnection CreateConnection(string strConn);
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立Command对象
/// </summary>
/// <returns>Command对象</returns>
IDbCommand CreateCommand();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立DataAdapter对象
/// </summary>
/// <returns>DataAdapter对象</returns>
IDbDataAdapter CreateDataAdapter();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Connection建立Transaction
/// </summary>
/// <param name="myDbConnection">Connection对象</param>
/// <returns>Transaction对象</returns>
IDbTransaction CreateTransaction(IDbConnection myDbConnection);
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Command建立DataReader
/// </summary>
/// <param name="myDbCommand">Command对象</param>
/// <returns>DataReader对象</returns>
IDataReader CreateDataReader(IDbCommand myDbCommand);
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得连接字符串
/// </summary>
/// <returns>连接字符串</returns>
string GetConnectionString();
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
(2)Factory.cs
using
System;
using
System.Configuration;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// Factory类
/// </summary>
public sealed class Factory
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private static volatile Factory singleFactory = null;
private static object syncObj = new object();
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// Factory类构造函数
/// </summary>
private Factory()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得Factory类的实例
/// </summary>
/// <returns>Factory类实例</returns>
public static Factory GetInstance()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if(singleFactory == null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
lock(syncObj)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if(singleFactory == null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
singleFactory = new Factory();
}
}
}
return singleFactory;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立Factory类实例
/// </summary>
/// <returns>Factory类实例</returns>
public AbstractDbFactory CreateInstance()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
AbstractDbFactory abstractDbFactory = null;
switch(ConfigurationSettings.AppSettings["DatabaseType"].ToLower())
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
case "sqlserver":
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
abstractDbFactory = new SqlFactory();
break;
}
case "oledb":
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
abstractDbFactory = new OleDbFactory();
break;
}
case "odbc":
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
abstractDbFactory = new OdbcFactory();
break;
}
}
return abstractDbFactory;
}
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
以下3个类分别是Factory针对SqlServer专用连接、OleDb连接和Odbc连接时的具体实现:
(3)SqlFactory.cs
using
System;
using
System.Data;
using
System.Data.SqlClient;
using
System.Configuration;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 针对SqlServer专用连接的工厂
/// </summary>
public class SqlFactory : AbstractDbFactory
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 构造函数
/// </summary>
public SqlFactory()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立默认Connection对象
/// </summary>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new SqlConnection();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据连接字符串建立Connection对象
/// </summary>
/// <param name="strConn">连接字符串</param>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new SqlConnection(strConn);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立Command对象
/// </summary>
/// <returns>Command对象</returns>
public IDbCommand CreateCommand()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new SqlCommand();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立DataAdapter对象
/// </summary>
/// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new SqlDataAdapter();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Connection建立Transaction
/// </summary>
/// <param name="myDbConnection">Connection对象</param>
/// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbConnection.BeginTransaction();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Command建立DataReader
/// </summary>
/// <param name="myDbCommand">Command对象</param>
/// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbCommand.ExecuteReader();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得连接字符串
/// </summary>
/// <returns>连接字符串</returns>
public string GetConnectionString()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strServer = ConfigurationSettings.AppSettings["SqlServerServer"];
string strDatabase = ConfigurationSettings.AppSettings["SqlServerDatabase"];
string strUid = ConfigurationSettings.AppSettings["SqlServerUid"];
string strPwd = ConfigurationSettings.AppSettings["SqlServerPwd"];
string strConnectionString = "Server = " + strServer + "; Database = " + strDatabase + "; Uid = " + strUid + "; Pwd = " + strPwd + ";";
return strConnectionString;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
(4)OleDbFactory.cs
using
System;
using
System.Data;
using
System.Data.OleDb;
using
System.Configuration;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 针对OleDb连接的工厂
/// </summary>
public class OleDbFactory : AbstractDbFactory
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 构造函数
/// </summary>
public OleDbFactory()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立默认Connection对象
/// </summary>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OleDbConnection();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据连接字符串建立Connection对象
/// </summary>
/// <param name="strConn">连接字符串</param>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OleDbConnection(strConn);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立Command对象
/// </summary>
/// <returns>Command对象</returns>
public IDbCommand CreateCommand()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OleDbCommand();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立DataAdapter对象
/// </summary>
/// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OleDbDataAdapter();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Connection建立Transaction
/// </summary>
/// <param name="myDbConnection">Connection对象</param>
/// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbConnection.BeginTransaction();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Command建立DataReader
/// </summary>
/// <param name="myDbCommand">Command对象</param>
/// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbCommand.ExecuteReader();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得连接字符串
/// </summary>
/// <returns>连接字符串</returns>
public string GetConnectionString()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strProvider = ConfigurationSettings.AppSettings["OleDbProvider"];
string strDataSource = ConfigurationSettings.AppSettings["OleDbDataSource"];
string strConnectionString = "Provider = " + strProvider + ";Data Source = " + strDataSource + ";";
return strConnectionString;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
(5)OdbcFactory.cs
using
System;
using
System.Data;
using
System.Data.Odbc;
using
System.Configuration;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 针对Odbc连接的工厂
/// </summary>
public class OdbcFactory : AbstractDbFactory
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 构造函数
/// </summary>
public OdbcFactory()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立默认Connection对象
/// </summary>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OdbcConnection();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据连接字符串建立Connection对象
/// </summary>
/// <param name="strConn">连接字符串</param>
/// <returns>Connection对象</returns>
public IDbConnection CreateConnection(string strConn)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OdbcConnection(strConn);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立Command对象
/// </summary>
/// <returns>Command对象</returns>
public IDbCommand CreateCommand()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OdbcCommand();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 建立DataAdapter对象
/// </summary>
/// <returns>DataAdapter对象</returns>
public IDbDataAdapter CreateDataAdapter()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return new OdbcDataAdapter();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Connection建立Transaction
/// </summary>
/// <param name="myDbConnection">Connection对象</param>
/// <returns>Transaction对象</returns>
public IDbTransaction CreateTransaction(IDbConnection myDbConnection)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbConnection.BeginTransaction();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据Command建立DataReader
/// </summary>
/// <param name="myDbCommand">Command对象</param>
/// <returns>DataReader对象</returns>
public IDataReader CreateDataReader(IDbCommand myDbCommand)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return myDbCommand.ExecuteReader();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 获得连接字符串
/// </summary>
/// <returns></returns>
public string GetConnectionString()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strDriver = ConfigurationSettings.AppSettings["OdbcDriver"];
string strDBQ = ConfigurationSettings.AppSettings["OdbcDBQ"];
string strConnectionString = "Driver={" + strDriver + "}; DBQ=" + strDBQ + ";";
return strConnectionString;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
以下是在应用时真正要调用到的类:
(6)DbAccess.cs
using
System;
using
System.Data;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DbService
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// DbAccess类,即进行数据库访问时需要调用的类
/// </summary>
public sealed class DbAccess
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// DbAccess构造函数
/// </summary>
private DbAccess()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 无条件查询操作,即查询表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <returns>无条件查询结果</returns>
public static DataSet SelectAll(string strTableName, string[] strColumn)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "SELECT ";
for(int i = 0; i < strColumn.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName);
concreteDbCommand.CommandText = strSql;
concreteDbAdapter.SelectCommand = concreteDbCommand;
concreteDbAdapter.Fill(ds);
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
ds.Clear();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
return ds;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "SELECT ";
for(int i = 0; i < strColumn.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName + " WHERE " + strCondition);
concreteDbCommand.CommandText = strSql;
concreteDbAdapter.SelectCommand = concreteDbCommand;
concreteDbAdapter.Fill(ds);
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
ds.Clear();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
return ds;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 单条记录的插入操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strValue">值组</param>
public static void Insert(string strTableName, string[] strColumn, object[] strValue)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "INSERT INTO " + strTableName + " (";
for(int i = 0; i < strColumn.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('");
for(int i = 0; i < strValue.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strValue[i] + "', '");
}
strSql += (strValue[strValue.Length - 1] + "')");
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
/// </summary>
/// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
public static void InsertSet(ref DataSet ds)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
foreach(DataTable dt in ds.Tables)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
foreach(DataRow dr in dt.Rows)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "INSERT INTO " + dt.TableName + " (";
for(int i = 0; i < dt.Columns.Count - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (dt.Columns[i].Caption + ", ");
}
strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('");
for(int i = 0; i < dt.Columns.Count - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (dr[i] + "', '");
}
strSql += (dr[dt.Columns.Count - 1] + "')");
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
}
}
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 无条件删除操作,即删除表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
public static void DeleteAll(string strTableName)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "DELETE FROM " + strTableName;
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 条件删除操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strCondition">条件</param>
public static void Delete(string strTableName, string strCondition)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 无条件更新操作,即更新表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strValue">值组</param>
public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "UPDATE " + strTableName + " SET ";
for(int i = 0; i < strColumn.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
}
strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' ");
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
string strSql = "UPDATE " + strTableName + " SET ";
for(int i = 0; i < strColumn.Length - 1; i++)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
}
strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " WHERE " + strCondition);
concreteDbCommand.CommandText = strSql;
concreteDbCommand.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbTrans.Rollback();
throw;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
concreteDbConn.Close();
}
}
}
}
![None.gif](/Images/OutliningIndicators/None.gif)
最后一步,在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,不受任何影响。欢迎大家批评指正:)