在ASP.NET网站开发中可能会遇到要更换数据库,如将Aceess换为SQL Server ,由于Aceess引用System.Data.OleDb命名空间,SQL Server引用System.Data.SqlClient命名空间。在一般程序代码中我们需要更改连接字符串,ADO.NET数据访问命令对象,最麻烦的是不同数据库有各自不同Sql命令。我们可以使用抽象工厂模式实现ASP.NET访问不同数据库。
1、抽象工厂模式(Abstract Factory):提供一个创建一系列相互依赖对象的接口,而无需指定他们的具体类。
2、一般组成:抽象产品接口,具体产品类,抽象工厂接口,具体工厂类,客户端代码
3、作用:易于交换产品系列,在一个应用中只需在初始化的时候出现一次,这就使得改变一个应用的具体工厂的具体工厂变得非常容易,只需改变具体工厂即可使用不同的产品配置
让具体的创建实例过程与客户端分离,客户端是通过他们的抽象类接口操纵实例,产品的具体类名也被具体工厂是实现分离,不会出现在客户代码中。
网上提供的代码基本是为了讲解说明C#设计模式,由C#控制台程序代码实现,并没有真正实现访问数据库。我这里通过建立一个ASP.NET网站,实现访问Access与SQL Server数据库。主要参考《大话设计模式》
具体步骤代码如下:
1、 分别在Access和SQL Server建立数据库DaHua与数据库表User(ID,Name),Department(ID,DepartName)
2、 启动Visual Studio 2005/2008新建网站
3、 到web.config添加数据库连接
4、 在网站解决方案右键新建类库ClassLibrary1,新建类文件,包括:
AbstractUser抽象类,SqlserverUser类,AccessUser类
AbstractDepartment抽象类,SqlserverDepartment类,AccessDepartment 类
DataAccess类
生成dll
5、到项目添加引用,项目-右键-添加引用-项目-ClassLibrary1
6、前台代码:添加一个GridView控件,三个Button控件,2个TextBox控件
用于查询表User和Department表内容,插入User表数据
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;
namespace ClassLibrary1
{
/// <summary>
///AbstractUser抽象类
/// </summary>
public abstract class AbstractUser
{
public abstract DataSet GetUser();
public abstract void InsertUser(string userID, string userName);
}
/// <summary>
/// SqlserverUser类
/// </summary>
public class SqlserverUser : AbstractUser
{
string constr = ConfigurationManager.ConnectionStrings["constrSQLDahua"].ConnectionString;
public override DataSet GetUser()
{
SqlConnection conn = new SqlConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [User]", conn);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public override void InsertUser(string userID, string userName)
{
SqlConnection conn = new SqlConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into [User] values('" + Int32.Parse(userID) + "','" + userName + "') ";
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
/// AccessUser类
/// </summary>
public class AccessUser : AbstractUser
{
string constr = ConfigurationManager.ConnectionStrings["constrAccessDaHua"].ConnectionString;
public override DataSet GetUser()
{
OleDbConnection conn = new OleDbConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand("select * from [User]", conn);
OleDbDataAdapter sda = new OleDbDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public override void InsertUser(string userID, string userName)
{
OleDbConnection conn = new OleDbConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into [User] values('" + Int32.Parse(userID) + "','" + userName + "') ";
cmd.ExecuteNonQuery();
conn.Close();
}
}
/// <summary>
///AbstractDepartment抽象类
/// </summary>
public abstract class AbstractDepartment
{
public abstract DataSet GetDepartment();
// void InsertDepartment(string depID, string depName);
}
/// <summary>
/// SqlserverDepartment类
/// </summary>
public class SqlserverDepartment : AbstractDepartment
{
string constr = ConfigurationManager.ConnectionStrings["constrSQLDahua"].ConnectionString;
public override DataSet GetDepartment()
{
SqlConnection conn = new SqlConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
SqlCommand cmd = new SqlCommand("select * from [Department]", conn);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}
/// <summary>
/// AccessDepartment 类
/// </summary>
public class AccessDepartment : AbstractDepartment
{
string constr = ConfigurationManager.ConnectionStrings["constrAccessDaHua"].ConnectionString;
public override DataSet GetDepartment()
{
OleDbConnection conn = new OleDbConnection(constr);
if (conn.State == ConnectionState.Closed)
conn.Open();
OleDbCommand cmd = new OleDbCommand("select * from [Department]", conn);
OleDbDataAdapter sda = new OleDbDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
}
/// <summary>
///DataAccess类
///使用反射
/// </summary>
public class DataAccess
{
private static readonly string AssemblyName = "ClassLibrary1"; //程序集名称
private static readonly string db = "Sqlserver"; //数据库类型名,要更改访问的数据库,只需更改这里
public static AbstractUser CreateUser()
{
string className = AssemblyName + "." + db + "User"; //命名空间.要实例化的类名
return (AbstractUser)Assembly.Load(AssemblyName).CreateInstance(className);
}
public static AbstractDepartment CreateDepartment()
{
string className = AssemblyName + "." + db + "Department";
return (AbstractDepartment)Assembly.Load(AssemblyName).CreateInstance(className);
}
}
}
前台代码:
public partial class _Default : System.Web.UI.Page
{
ClassLibrary1.AbstractUser au = null;
ClassLibrary1.AbstractDepartment ad = null;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
au =ClassLibrary1.DataAccess.CreateUser();
DataSet ds = au.GetUser();
GridView1.DataSource = ds;
GridView1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e)
{
string userID = txtID.Text;
string userName = txtName.Text ;
au = ClassLibrary1.DataAccess.CreateUser();
au.InsertUser(userID,userName);
}
//部门查询
protected void Button3_Click(object sender, EventArgs e)
{
ad = ClassLibrary1.DataAccess.CreateDepartment();
DataSet ds = ad.GetDepartment();
GridView1.DataSource = ds;
GridView1.DataBind();
}
}