|
1. 首先我們先來定義數(shù)據(jù)庫的類型
using System;
namespace UserLib.Database
{
///
///數(shù)據(jù)庫類型
///
public enum DatabaseType
{
OleDb, //OleDb 數(shù)據(jù)庫
SqlServer, //SqlServer 數(shù)據(jù)庫
Odbc, //Odbc 數(shù)據(jù)庫
}
}
2. 接著我們定義一個數(shù)據(jù)庫訪問的接口,提供通用的訪問方法
using System;
using System.Data;
using System.Data.Common;
namespace UserLib.Database
{
///
///數(shù)據(jù)庫通用接口
///
public interface IDatabase
{
///
///打開數(shù)據(jù)庫
///
///連接字符串
void Open(string connString);
///
///關(guān)閉數(shù)據(jù)庫
///
void Close();
///
///數(shù)據(jù)集
///
DataSet DataSetDB { get;}
///
///數(shù)據(jù)連接
///
DbConnection ConnectionDB { get;}
///
///獲取數(shù)據(jù)庫類型
///
///數(shù)據(jù)庫類型
DatabaseType GetDatabaseType();
///
///執(zhí)行SQL語句
///
///SQL語句
///影響行數(shù)
int ExecuteSQL(string sql);
///
///執(zhí)行SQL語句
///
///數(shù)據(jù)命令
///影響行數(shù)
int ExecuteSQL(DbCommand cmd);
///
///執(zhí)行SQL語句
///
///SQL語句
///第一行第一列值
object ExecuteSQLReturn(string sql);
///
///執(zhí)行SQL語句
///
///數(shù)據(jù)命令
///第一行第一列值
object ExecuteSQLReturn(DbCommand cmd);
///
///返回DataReader對象
///
///SQL語句
///DataReader對象
DbDataReader ReturnDataReader(string sql);
///
///返回DataReader對象
///
///查詢命令
///DataReader對象
DbDataReader ReturnDataReader(DbCommand cmd);
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///SQL語句
///表名稱
///數(shù)據(jù)集
DataSet AddTable(string sql, string tbName);
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///查詢命令
///表名稱
///數(shù)據(jù)集
DataSet AddTable(DbCommand cmdSelect, string tbName);
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///SQL語句
///表名稱
///導(dǎo)出數(shù)據(jù)適配器
///數(shù)據(jù)集
DataSet AddTable(string sql, string tbName, out DbDataAdapter da);
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///查詢命令
///表名稱
///導(dǎo)出數(shù)據(jù)適配器
///數(shù)據(jù)集
DataSet AddTable(DbCommand cmdSelect, string tbName, out DbDataAdapter da);
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///數(shù)據(jù)適配器
///表名稱
///數(shù)據(jù)集
DataSet AddTable(DbDataAdapter da, string tbName);
///
///移除表
///
///表名稱
void RemoveTable(string tbName);
///
///移除表
///
///表的索引
void RemoveTable(int index);
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系名稱
///主表名稱
///主表中的列
///子表名稱
///子表中的列
///數(shù)據(jù)集
DataSet AddRelation(string name, string prnTable, string prnCol, string chdTable, string chdCol);
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系名稱
///主表名稱
///主表中的列
///子表名稱
///子表中的列
///導(dǎo)出關(guān)系
///數(shù)據(jù)集
DataSet AddRelation(string name, string prnTable, string prnCol, string chdTable, string chdCol, out DataRelation dr);
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系
///數(shù)據(jù)集
DataSet AddRelation(DataRelation dr);
///
///移除關(guān)系
///
///關(guān)系名稱
void RemoveRelation(string relationName);
///
///移除關(guān)系
///
///關(guān)系索引
void RemoveRelation(int index);
}
}
3. 接著我們實(shí)現(xiàn)IDatabase接口, 提供一個具體的數(shù)據(jù)庫訪問類
例: (Sql Server)
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
namespace UserLib.Database
{
///
/// SqlServer數(shù)據(jù)庫的連接與處理
///
public class SqlServer : IDatabase
{
private SqlConnection connDB; //Connection(連接) 對象
private DataSet dsDB = new DataSet(); //DataSet(數(shù)據(jù)集) 對象
///
///構(gòu)造函數(shù)
///
public SqlServer() { }
///
///構(gòu)造函數(shù)
///
///連接字符串
public SqlServer(string connString)
{
Open(connString);
}
///
///打開數(shù)據(jù)庫
///
///連接字符串
public void Open(string connString)
{
connDB = new SqlConnection(connString);
connDB.Open();
}
///
///關(guān)閉數(shù)據(jù)庫
///
public void Close()
{
connDB.Close();
}
///
///數(shù)據(jù)庫連接
///
public DbConnection ConnectionDB
{
get
{
return connDB;
}
}
///
///數(shù)據(jù)集
///
public DataSet DataSetDB
{
get
{
return dsDB;
}
}
///
///獲取數(shù)據(jù)庫類型
///
///數(shù)據(jù)庫類型
public DatabaseType GetDatabaseType()
{
return DatabaseType.SqlServer;
}
///
///執(zhí)行SQL語句
///
///SQL語句
///影響行數(shù)
public int ExecuteSQL(string sql)
{
SqlCommand cmdDB = connDB.CreateCommand();
cmdDB.CommandText = sql;
int n = cmdDB.ExecuteNonQuery();
return n;
}
///
///執(zhí)行SQL語句
///
///數(shù)據(jù)命令
///影響行數(shù)
public int ExecuteSQL(DbCommand cmd)
{
SqlCommand cmdDB = new SqlCommand();
cmdDB = (SqlCommand)cmd;
int n = cmdDB.ExecuteNonQuery();
return n;
}
///
///執(zhí)行SQL語句
///
///SQL語句
///第一行第一列值
public object ExecuteSQLReturn(string sql)
{
SqlCommand cmdDB = connDB.CreateCommand();
cmdDB.CommandText = sql;
object obj = cmdDB.ExecuteScalar();
return obj;
}
///
///執(zhí)行SQL語句
///
///數(shù)據(jù)命令
///第一行第一列值
public object ExecuteSQLReturn(DbCommand cmd)
{
SqlCommand cmdDB = new SqlCommand();
cmdDB = (SqlCommand)cmd;
object obj = cmdDB.ExecuteScalar();
return obj;
}
///
///返回DataReader對象
///
///SQL語句
///DataReader對象
public DbDataReader ReturnDataReader(string sql)
{
SqlCommand cmdDB = connDB.CreateCommand();
cmdDB.CommandText = sql;
return cmdDB.ExecuteReader();
}
///
///返回DataReader對象
///
///查詢命令
///DataReader對象
public DbDataReader ReturnDataReader(DbCommand cmd)
{
SqlCommand cmdDB = new SqlCommand();
cmdDB = (SqlCommand)cmd;
return cmdDB.ExecuteReader();
}
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///SQL語句
///表名稱
///數(shù)據(jù)集
public DataSet AddTable(string sql, string tbName)
{
SqlDataAdapter daTable = new SqlDataAdapter(sql, connDB);
//根據(jù)Select命令生成Insert Delete Update命令
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
daTable.InsertCommand = cmdBuilder.GetInsertCommand();
daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
//把數(shù)據(jù)庫中的鍵包含進(jìn)來
daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daTable.Fill(dsDB, tbName);
return dsDB;
}
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///查詢命令
///表名稱
///數(shù)據(jù)集
public DataSet AddTable(DbCommand cmdSelect, string tbName)
{
SqlDataAdapter daTable = new SqlDataAdapter((SqlCommand)cmdSelect);
//根據(jù)Select命令生成Insert Delete Update命令
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
daTable.InsertCommand = cmdBuilder.GetInsertCommand();
daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
//把數(shù)據(jù)庫中的鍵包含進(jìn)來
daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daTable.Fill(dsDB, tbName);
return dsDB;
}
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///SQL語句
///表名稱
///導(dǎo)出數(shù)據(jù)適配器
///數(shù)據(jù)集
public DataSet AddTable(string sql, string tbName, out DbDataAdapter da)
{
SqlDataAdapter daTable = new SqlDataAdapter(sql, connDB);
//根據(jù)Select命令生成Insert Delete Update命令
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
daTable.InsertCommand = cmdBuilder.GetInsertCommand();
daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
//把數(shù)據(jù)庫中的鍵包含進(jìn)來
daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da = daTable;
daTable.Fill(dsDB, tbName);
return dsDB;
}
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///查詢命令
///表名稱
///導(dǎo)出數(shù)據(jù)適配器
///數(shù)據(jù)集
public DataSet AddTable(DbCommand cmdSelect, string tbName, out DbDataAdapter da)
{
SqlDataAdapter daTable = new SqlDataAdapter((SqlCommand)cmdSelect);
//根據(jù)Select命令生成Insert Delete Update命令
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
daTable.InsertCommand = cmdBuilder.GetInsertCommand();
daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
//把數(shù)據(jù)庫中的鍵包含進(jìn)來
daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da = daTable;
daTable.Fill(dsDB, tbName);
return dsDB;
}
///
///在數(shù)據(jù)集中加入有SQL語句生成的表
///
///數(shù)據(jù)適配器
///表名稱
///數(shù)據(jù)集
public DataSet AddTable(DbDataAdapter da, string tbName)
{
SqlDataAdapter daTable = new SqlDataAdapter();
daTable = (SqlDataAdapter)da;
//根據(jù)Select命令生成Insert Delete Update命令
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(daTable);
daTable.InsertCommand = cmdBuilder.GetInsertCommand();
daTable.DeleteCommand = cmdBuilder.GetDeleteCommand();
daTable.UpdateCommand = cmdBuilder.GetUpdateCommand();
//把數(shù)據(jù)庫中的鍵包含進(jìn)來
daTable.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daTable.Fill(dsDB, tbName);
return dsDB;
}
///
///移除表
///
///表名稱
public void RemoveTable(string tbName)
{
dsDB.Tables.Remove(tbName);
}
///
///移除表
///
///表的索引
public void RemoveTable(int index)
{
dsDB.Tables.RemoveAt(index);
}
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系名稱
///主表名稱
///主表中的列
///子表名稱
///子表中的列
///數(shù)據(jù)集
public DataSet AddRelation(string name, string prnTable,string prnCol,string chdTable,string chdCol)
{
dsDB.Relations.Add(name, dsDB.Tables[prnTable].Columns[prnCol], dsDB.Tables[chdTable].Columns[chdCol]);
return dsDB;
}
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系名稱
///主表名稱
///主表中的列
///子表名稱
///子表中的列
///導(dǎo)出關(guān)系
///數(shù)據(jù)集
public DataSet AddRelation(string name, string prnTable,string prnCol,string chdTable,string chdCol,out DataRelation dr)
{
DataRelation drTables = new DataRelation(name, dsDB.Tables[prnTable].Columns[prnCol], dsDB.Tables[chdTable].Columns[chdCol]);
dr = drTables;
dsDB.Relations.Add(drTables);
return dsDB;
}
///
///在數(shù)據(jù)集中加入關(guān)系
///
///關(guān)系
///數(shù)據(jù)集
public DataSet AddRelation(DataRelation dr)
{
dsDB.Relations.Add(dr);
return dsDB;
}
///
///移除關(guān)系
///
///關(guān)系名稱
public void RemoveRelation(string relationName)
{
dsDB.Relations.Remove(relationName);
}
///
///移除關(guān)系
///
///關(guān)系索引
public void RemoveRelation(int index)
{
dsDB.Relations.RemoveAt(index);
}
}
}
4. 最后定義數(shù)據(jù)庫工廠, 實(shí)現(xiàn)通用的數(shù)據(jù)庫訪問
using System;
using System.Data;
namespace UserLib.Database
{
///
///數(shù)據(jù)庫工廠
///
public class DBFactory
{
///
///私有構(gòu)造函數(shù)
///
private DBFactory() { }
///
///將字符串轉(zhuǎn)成DatabaseType枚舉類型
///
///字符串
///DatabaseType枚舉類型
private static DatabaseType ToDatabaseType(string src)
{
switch (src.ToLower())
{
case "oledb":
return DatabaseType.OleDb;
case "sqlserver":
return DatabaseType.SqlServer;
case "odbc":
return DatabaseType.Odbc;
default:
throw new Exception("類型不存在");
}
}
///
///創(chuàng)建數(shù)據(jù)庫類實(shí)例
///
///數(shù)據(jù)庫類型
///數(shù)據(jù)庫類實(shí)例
public static IDatabase Create(DatabaseType dbType)
{
switch(dbType)
{
case DatabaseType.OleDb:
return new OleDb();
case DatabaseType.SqlServer:
return new SqlServer();
case DatabaseType.Odbc:
return new Odbc();
default:
return null;
}
}
///
///創(chuàng)建數(shù)據(jù)庫類實(shí)例
///
///數(shù)據(jù)庫類型
///數(shù)據(jù)庫類實(shí)例
public static IDatabase Create(string dbType)
{
return Create(ToDatabaseType(dbType));
}
///
///創(chuàng)建數(shù)據(jù)庫類實(shí)例
///
///>數(shù)據(jù)庫類型
///數(shù)據(jù)庫連接字符串
///數(shù)據(jù)庫類實(shí)例
public static IDatabase Create(DatabaseType dbType,string connString)
{
switch(dbType)
{
case DatabaseType.OleDb:
return new OleDb(connString);
case DatabaseType.SqlServer:
return new SqlServer(connString);
case DatabaseType.Odbc:
return new Odbc(connString);
default:
return null;
}
}
///
///創(chuàng)建數(shù)據(jù)庫類實(shí)例
///
///>數(shù)據(jù)庫類型
///數(shù)據(jù)庫連接字符串
///數(shù)據(jù)庫類實(shí)例
public static IDatabase Create(string dbType, string connString)
{
return Create(ToDatabaseType(dbType), connString);
}
}
}
5. 用法
using System;
using UserLib.Database;
namespace Test
{
...
string ConnStr="...";
IDatabase db = DBFactory.Create(DatabaseType.SqlServer, ConnStr);
...
}
6. 備注
以上程序在.Net Framework 1.1 環(huán)境下編寫
.Net 2.0 已經(jīng)提供了訪問數(shù)據(jù)庫的工廠模式
|
