学步园

(1)AbstractDbFactory.cs 
using System; 
using System.Data; 

namespace DbService 
{ 
/// <summary> 
/// 数据库抽象工厂接口 
/// </summary> 
public interface AbstractDbFactory 
{ 
/// <summary> 
/// 建立默认连接 
/// </summary> 
/// <returns>数据库连接</returns> 
IDbConnection CreateConnection(); 

/// <summary> 
/// 根据连接字符串建立Connection对象 
/// </summary> 
/// <param name="strConn">连接字符串</param> 
/// <returns>Connection对象</returns> 
IDbConnection CreateConnection(string strConn); 

/// <summary> 
/// 建立Command对象 
/// </summary> 
/// <returns>Command对象</returns> 
IDbCommand CreateCommand(); 

/// <summary> 
/// 建立DataAdapter对象 
/// </summary> 
/// <returns>DataAdapter对象</returns> 
IDbDataAdapter CreateDataAdapter(); 

/// <summary> 
/// 根据Connection建立Transaction 
/// </summary> 
/// <param name="myDbConnection">Connection对象</param> 
/// <returns>Transaction对象</returns> 
IDbTransaction CreateTransaction(IDbConnection myDbConnection); 

/// <summary> 
/// 根据Command建立DataReader 
/// </summary> 
/// <param name="myDbCommand">Command对象</param> 
/// <returns>DataReader对象</returns> 
IDataReader CreateDataReader(IDbCommand myDbCommand); 

/// <summary> 
/// 获得连接字符串 
/// </summary> 
/// <returns>连接字符串</returns> 
string GetConnectionString(); 
} 
} 

(2)Factory.cs 

using System; 
using System.Configuration; 

namespace DbService 
{ 
/// <summary> 
/// Factory类 
/// </summary> 
public sealed class Factory 
{ 
private static volatile Factory singleFactory = null; 
private static object syncObj = new object(); 
/// <summary> 
/// Factory类构造函数 
/// </summary> 
private Factory() 
{ 
} 

/// <summary> 
/// 获得Factory类的实例 
/// </summary> 
/// <returns>Factory类实例</returns> 
public static Factory GetInstance() 
{ 
if(singleFactory == null) 
{ 
lock(syncObj) 
{ 
if(singleFactory == null) 
{ 
singleFactory = new Factory(); 
} 
} 
} 
return singleFactory; 
} 

/// <summary> 
/// 建立Factory类实例 
/// </summary> 
/// <returns>Factory类实例</returns> 
public AbstractDbFactory CreateInstance() 
{ 
AbstractDbFactory abstractDbFactory = null; 
switch(ConfigurationSettings.AppSettings["DatabaseType"].ToLower()) 
{ 
case "sqlserver": 
{ 
abstractDbFactory = new SqlFactory(); 
break; 
} 
case "oledb": 
{ 
abstractDbFactory = new OleDbFactory(); 
break; 
} 
case "odbc": 
{ 
abstractDbFactory = new OdbcFactory(); 
break; 
} 
} 
return abstractDbFactory; 
} 
} 
}
以下3个类分别是Factory针对SqlServer专用连接、OleDb连接和Odbc连接时的具体实现： 

(3)SqlFactory.cs 

using System; 
using System.Data; 
using System.Data.SqlClient; 
using System.Configuration; 

namespace DbService 
{ 
/// <summary> 
/// 针对SqlServer专用连接的工厂 
/// </summary> 
public class SqlFactory : AbstractDbFactory 
{ 
/// <summary> 
/// 构造函数 
/// </summary> 
public SqlFactory() 
{ 
} 

/// <summary> 
/// 建立默认Connection对象 
/// </summary> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection() 
{ 
return new SqlConnection(); 
} 

/// <summary> 
/// 根据连接字符串建立Connection对象 
/// </summary> 
/// <param name="strConn">连接字符串</param> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection(string strConn) 
{ 
return new SqlConnection(strConn); 
} 

/// <summary> 
/// 建立Command对象 
/// </summary> 
/// <returns>Command对象</returns> 
public IDbCommand CreateCommand() 
{ 
return new SqlCommand(); 
} 

/// <summary> 
/// 建立DataAdapter对象 
/// </summary> 
/// <returns>DataAdapter对象</returns> 
public IDbDataAdapter CreateDataAdapter() 
{ 
return new SqlDataAdapter(); 
} 

/// <summary> 
/// 根据Connection建立Transaction 
/// </summary> 
/// <param name="myDbConnection">Connection对象</param> 
/// <returns>Transaction对象</returns> 
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) 
{ 
return myDbConnection.BeginTransaction(); 
} 

/// <summary> 
/// 根据Command建立DataReader 
/// </summary> 
/// <param name="myDbCommand">Command对象</param> 
/// <returns>DataReader对象</returns> 
public IDataReader CreateDataReader(IDbCommand myDbCommand) 
{ 
return myDbCommand.ExecuteReader(); 
} 

/// <summary> 
/// 获得连接字符串 
/// </summary> 
/// <returns>连接字符串</returns> 
public string GetConnectionString() 
{ 
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; 
} 

} 
} 

(4)OleDbFactory.cs 

using System; 
using System.Data; 
using System.Data.OleDb; 
using System.Configuration; 

namespace DbService 
{ 
/// <summary> 
/// 针对OleDb连接的工厂 
/// </summary> 
public class OleDbFactory : AbstractDbFactory 
{ 
/// <summary> 
/// 构造函数 
/// </summary> 
public OleDbFactory() 
{ 
} 

/// <summary> 
/// 建立默认Connection对象 
/// </summary> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection() 
{ 
return new OleDbConnection(); 
} 

/// <summary> 
/// 根据连接字符串建立Connection对象 
/// </summary> 
/// <param name="strConn">连接字符串</param> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection(string strConn) 
{ 
return new OleDbConnection(strConn); 
} 

/// <summary> 
/// 建立Command对象 
/// </summary> 
/// <returns>Command对象</returns> 
public IDbCommand CreateCommand() 
{ 
return new OleDbCommand(); 
} 

/// <summary> 
/// 建立DataAdapter对象 
/// </summary> 
/// <returns>DataAdapter对象</returns> 
public IDbDataAdapter CreateDataAdapter() 
{ 
return new OleDbDataAdapter(); 
} 

/// <summary> 
/// 根据Connection建立Transaction 
/// </summary> 
/// <param name="myDbConnection">Connection对象</param> 
/// <returns>Transaction对象</returns> 
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) 
{ 
return myDbConnection.BeginTransaction(); 
} 

/// <summary> 
/// 根据Command建立DataReader 
/// </summary> 
/// <param name="myDbCommand">Command对象</param> 
/// <returns>DataReader对象</returns> 
public IDataReader CreateDataReader(IDbCommand myDbCommand) 
{ 
return myDbCommand.ExecuteReader(); 
} 

/// <summary> 
/// 获得连接字符串 
/// </summary> 
/// <returns>连接字符串</returns> 
public string GetConnectionString() 
{ 
string strProvider = ConfigurationSettings.AppSettings["OleDbProvider"]; 
string strDataSource = ConfigurationSettings.AppSettings["OleDbDataSource"]; 
string strConnectionString = "Provider = " + strProvider + ";Data Source = " + strDataSource + ";"; 
return strConnectionString; 
} 

} 
} 

(5)OdbcFactory.cs 

using System; 
using System.Data; 
using System.Data.Odbc; 
using System.Configuration; 

namespace DbService 
{ 
/// <summary> 
/// 针对Odbc连接的工厂 
/// </summary> 
public class OdbcFactory : AbstractDbFactory 
{ 
/// <summary> 
/// 构造函数 
/// </summary> 
public OdbcFactory() 
{ 
} 

/// <summary> 
/// 建立默认Connection对象 
/// </summary> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection() 
{ 
return new OdbcConnection(); 
} 

/// <summary> 
/// 根据连接字符串建立Connection对象 
/// </summary> 
/// <param name="strConn">连接字符串</param> 
/// <returns>Connection对象</returns> 
public IDbConnection CreateConnection(string strConn) 
{ 
return new OdbcConnection(strConn); 
} 

/// <summary> 
/// 建立Command对象 
/// </summary> 
/// <returns>Command对象</returns> 
public IDbCommand CreateCommand() 
{ 
return new OdbcCommand(); 
} 

/// <summary> 
/// 建立DataAdapter对象 
/// </summary> 
/// <returns>DataAdapter对象</returns> 
public IDbDataAdapter CreateDataAdapter() 
{ 
return new OdbcDataAdapter(); 
} 

/// <summary> 
/// 根据Connection建立Transaction 
/// </summary> 
/// <param name="myDbConnection">Connection对象</param> 
/// <returns>Transaction对象</returns> 
public IDbTransaction CreateTransaction(IDbConnection myDbConnection) 
{ 
return myDbConnection.BeginTransaction(); 
} 

/// <summary> 
/// 根据Command建立DataReader 
/// </summary> 
/// <param name="myDbCommand">Command对象</param> 
/// <returns>DataReader对象</returns> 
public IDataReader CreateDataReader(IDbCommand myDbCommand) 
{ 
return myDbCommand.ExecuteReader(); 
} 

/// <summary> 
/// 获得连接字符串 
/// </summary> 
/// <returns></returns> 
public string GetConnectionString() 
{ 
string strDriver = ConfigurationSettings.AppSettings["OdbcDriver"]; 
string strDBQ = ConfigurationSettings.AppSettings["OdbcDBQ"]; 
string strConnectionString = "Driver={" + strDriver + "}; DBQ=" + strDBQ + ";"; 
return strConnectionString; 
} 

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

(6)DbAccess.cs 

using System; 
using System.Data; 

namespace DbService 
{ 
/// <summary> 
/// DbAccess类,即进行数据库访问时需要调用的类 
/// </summary> 
public class DbAccess 
{ 
/// <summary> 
/// DbAccess构造函数 
/// </summary> 
public 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>
可以通过改变Web.config中的变量来使用不同的数据库连接方式(SqlServer专用连接、OleDb连接和Odbc连接)连接不同的数据库，同时整个使用仍通过DbAccess