数据库访问一般有不同中数据库,比如Oracle,Sqlserver,Mysql等。 怎样使得程序有一定的通用性。我们可以使用工厂模式来实现。具体代码如下:
1 抽象类
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for absDB
/// </summary>
public abstract class AbsDB
...{
public AbsDB()
...{
//
// TODO: Add constructor logic here
//
}
//得到数据库连接
public abstract IDbConnection Connection ...{ get;}
//打开数据库连接
public abstract void Open();
//关闭数据库连接
public abstract void Close();
//开始一个事务
public abstract void BeginTrans();
//提交一个事务
public abstract void CommitTrans();
//回滚一个事务
public abstract void RollbackTrans();
//执行Sql语句,没有返回值
public abstract void ExeSql(string strSql, string[] strParams, object[] objValues);
//执行Sql,返回DataSet
public abstract DataSet ExeSqlForDataSet(string QueryString);
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for absDB
/// </summary>
public abstract class AbsDB
...{
public AbsDB()
...{
//
// TODO: Add constructor logic here
//
}
//得到数据库连接
public abstract IDbConnection Connection ...{ get;}
//打开数据库连接
public abstract void Open();
//关闭数据库连接
public abstract void Close();
//开始一个事务
public abstract void BeginTrans();
//提交一个事务
public abstract void CommitTrans();
//回滚一个事务
public abstract void RollbackTrans();
//执行Sql语句,没有返回值
public abstract void ExeSql(string strSql, string[] strParams, object[] objValues);
//执行Sql,返回DataSet
public abstract DataSet ExeSqlForDataSet(string QueryString);
}
}
2 Oracle连接的实例化
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for OracleDB
/// </summary>
internal class OracleDB : AbsDB
...{
/**//// <summary>
/// 数据库连接状态表示
/// </summary>
private string OpenFlag = "OPEN";
public OracleDB()
...{
}
//数据库连接
private OracleConnection conn;
//事务处理类
private OracleTransaction trans;
//指示当前是否正处于事务中
private bool inTransactionFlag = false;
public override IDbConnection Connection
...{
get ...{ return this.conn; }
}
public OracleDB(string strConnection)
...{
this.conn = new OracleConnection(strConnection);
}
public override void Open()
...{
if (conn.State.ToString().ToUpper() != OpenFlag)
this.conn.Open();
}
public override void Close()
...{
if (conn.State.ToString().ToUpper() == OpenFlag)
this.conn.Close();
}
public override void BeginTrans()
...{
trans = conn.BeginTransaction();
inTransactionFlag = true;
}
public override void CommitTrans()
...{
trans.Commit();
inTransactionFlag = false;
}
public override void RollbackTrans()
...{
trans.Rollback();
inTransactionFlag = false;
}
public override void ExeSql(string strSql, string[] strParams, object[] strValues)
...{
//创建命令
OracleCommand cmd = new OracleCommand();
//设置连接
cmd.Connection=this.conn ;
//比较参数个数和参数值数组的长度是否匹配
if ((strParams != null) && (strParams.Length != strValues.Length))
...{
throw new Exception("查询参数和值不对应!");
}
cmd.CommandText = strSql;
if (strParams != null)
...{
for (int i = 0; i < strParams.Length; i++)
...{
cmd.Parameters.Add(strParams[i], strValues[i]);
}
}
//执行SQL语句
cmd.ExecuteNonQuery();
}
/**//// <summary>
/// 执行数据库查询并将结果用数据集(DataSet)的形式返回
/// </summary>
/// <param name="QueryString">SQL语句</param>
/// <returns></returns>
public override DataSet ExeSqlForDataSet(string QueryString)
...{
//创建命令
OracleCommand cmd = new OracleCommand();
//设置连接
cmd.Connection = this.conn;
//传入查询语句
cmd.CommandText = QueryString;
//创建数据集
DataSet ds = new DataSet();
//创建适配器
OracleDataAdapter ad = new OracleDataAdapter();
//适配器命令
ad.SelectCommand = cmd;
//填充到数据集(DataSet)
ad.Fill(ds);
//返回结果数据集
return ds;
}
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for OracleDB
/// </summary>
internal class OracleDB : AbsDB
...{
/**//// <summary>
/// 数据库连接状态表示
/// </summary>
private string OpenFlag = "OPEN";
public OracleDB()
...{
}
//数据库连接
private OracleConnection conn;
//事务处理类
private OracleTransaction trans;
//指示当前是否正处于事务中
private bool inTransactionFlag = false;
public override IDbConnection Connection
...{
get ...{ return this.conn; }
}
public OracleDB(string strConnection)
...{
this.conn = new OracleConnection(strConnection);
}
public override void Open()
...{
if (conn.State.ToString().ToUpper() != OpenFlag)
this.conn.Open();
}
public override void Close()
...{
if (conn.State.ToString().ToUpper() == OpenFlag)
this.conn.Close();
}
public override void BeginTrans()
...{
trans = conn.BeginTransaction();
inTransactionFlag = true;
}
public override void CommitTrans()
...{
trans.Commit();
inTransactionFlag = false;
}
public override void RollbackTrans()
...{
trans.Rollback();
inTransactionFlag = false;
}
public override void ExeSql(string strSql, string[] strParams, object[] strValues)
...{
//创建命令
OracleCommand cmd = new OracleCommand();
//设置连接
cmd.Connection=this.conn ;
//比较参数个数和参数值数组的长度是否匹配
if ((strParams != null) && (strParams.Length != strValues.Length))
...{
throw new Exception("查询参数和值不对应!");
}
cmd.CommandText = strSql;
if (strParams != null)
...{
for (int i = 0; i < strParams.Length; i++)
...{
cmd.Parameters.Add(strParams[i], strValues[i]);
}
}
//执行SQL语句
cmd.ExecuteNonQuery();
}
/**//// <summary>
/// 执行数据库查询并将结果用数据集(DataSet)的形式返回
/// </summary>
/// <param name="QueryString">SQL语句</param>
/// <returns></returns>
public override DataSet ExeSqlForDataSet(string QueryString)
...{
//创建命令
OracleCommand cmd = new OracleCommand();
//设置连接
cmd.Connection = this.conn;
//传入查询语句
cmd.CommandText = QueryString;
//创建数据集
DataSet ds = new DataSet();
//创建适配器
OracleDataAdapter ad = new OracleDataAdapter();
//适配器命令
ad.SelectCommand = cmd;
//填充到数据集(DataSet)
ad.Fill(ds);
//返回结果数据集
return ds;
}
}
}
3 SqlServer的实例化(自己对照上面写个,我也没写。^_^)
4 根据不同的string连接来创建不同的实例。
web.config中设置共用连接:
<appSettings>
<add key="DBConnStr" value="Data Source=INFOPLAT;User ID=InfoManager;Password=admin;" />
</appSettings>
using
System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for DBConn
/// </summary>
public class DBConn
...{
public DBConn()
...{
}
/**//// <summary>
/// 根据不同的字符串连接来使用不同的处理程序。
/// 工厂方法应用(可以根据不同的strConnection创建不同的连接)。
/// </summary>
/// <param name="strConnection">数据库连接字符串</param>
/// <returns></returns>
public static AbsDB GetDBConn()
...{
// 只有一个Oracle连接时使用,如果有多个,在此添加。在Web.Config里配置。
string strConnection = System.Configuration.ConfigurationManager.AppSettings["DBConnStr"];
// 创建OracleDB连接对象
return new OracleDB(strConnection);
/**////比如有sqlserver添加如下
///if(strConnection=?)
///{
/// return(SqlServerDB(strConnection));
///}
}
}
}
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
namespace HHSCInfor.App_Code.Database
... {
/**//// <summary>
/// Summary description for DBConn
/// </summary>
public class DBConn
...{
public DBConn()
...{
}
/**//// <summary>
/// 根据不同的字符串连接来使用不同的处理程序。
/// 工厂方法应用(可以根据不同的strConnection创建不同的连接)。
/// </summary>
/// <param name="strConnection">数据库连接字符串</param>
/// <returns></returns>
public static AbsDB GetDBConn()
...{
// 只有一个Oracle连接时使用,如果有多个,在此添加。在Web.Config里配置。
string strConnection = System.Configuration.ConfigurationManager.AppSettings["DBConnStr"];
// 创建OracleDB连接对象
return new OracleDB(strConnection);
/**////比如有sqlserver添加如下
///if(strConnection=?)
///{
/// return(SqlServerDB(strConnection));
///}
}
}
}
5 测试程序:
AbsDB conn
=
DBConn.GetDBConn();
DataSet ds = conn.ExeSqlForDataSet( " select * from sysFunction " );
this .Label1.Text = ds.Tables[ 0 ].Rows[ 2 ][ " 功能名称 " ].ToString();
conn.Close();
DataSet ds = conn.ExeSqlForDataSet( " select * from sysFunction " );
this .Label1.Text = ds.Tables[ 0 ].Rows[ 2 ][ " 功能名称 " ].ToString();
conn.Close();
我的测试通过。
如果是连接池用单例来实现。