===========IDBHelper.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
namespace DAL
{
public interface IDBHelper
{
void getConn();
void openConn();
void closeConn();
void beginTrans();
void commitTrans();
void rollbackTrans();
object execScalar(string sql);
void execSql(string sql);
int execSql(string sql, OracleParameter[] para);
int execSqlREF(string sql);
int execSqlREF(string sql, OracleParameter[] para);
DataSet getDataSet(string sql);
DataSet getDataSet(string sql, OracleParameter[] para);
}
}
===========继承类DBHelperORACLE
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Data.Common;
namespace DAL
{
public class DBHelperORACLE : IDBHelper
{
public OracleConnection conn = null;
private OracleCommand cmd = null;
private OracleDataAdapter adapter = null;
private bool isTrans = false;
private OracleTransaction trans = null;
public DBHelperORACLE() { }
/**
* 获取数据库连接对象
* */
public void getConn()
{
String connStr = ConfigurationManager.AppSettings["DB"].ToString();
this.conn = new OracleConnection(connStr);
}
/**
* 打开数据库连接
* */
public void openConn()
{
this.getConn();
if ((this.conn != null) && (this.conn.State == ConnectionState.Closed))
{
this.conn.Open();
}
}
/**
* 关闭数据库连接
* */
public void closeConn()
{
if ((this.conn != null) && (this.conn.State != ConnectionState.Closed) && !this.isTrans)
{
this.conn.Close();
}
}
/**
* 开始事务
* */
public void beginTrans()
{
this.openConn();
this.isTrans = true;
this.trans = this.conn.BeginTransaction();
}
/**
* 提交事务
* */
public void commitTrans()
{
this.trans.Commit();
this.isTrans = false;
this.closeConn();
}
/**
* 回滚事务
* */
public void rollbackTrans()
{
this.trans.Rollback();
this.isTrans = false;
this.closeConn();
}
/**
* 执行DML语句
* */
public void execSql(string sql)
{
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
this.cmd = conn.CreateCommand();
}
this.cmd.CommandText = sql;
this.cmd.ExecuteNonQuery();
this.closeConn();
}
/**
* 执行DML语句(可带参数)
* */
public int execSql(string sql, OracleParameter[] para)
{
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
this.cmd = conn.CreateCommand();
}
this.cmd.CommandText = sql;
for (int i = 0; i < para.Length; i++)
{
this.cmd.Parameters.Add(para[i]);
}
int result = this.cmd.ExecuteNonQuery();
this.closeConn();
return result;
}
/**
* 执行DML语句,并返回单独一列的值
* */
public object execScalar(string sql)
{
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
}
this.cmd = conn.CreateCommand();
this.cmd.CommandText = sql;
object returnval=cmd.ExecuteScalar();
this.closeConn();
return returnval;
}
/**
* 执行DML语句,并返回影响行数
* */
public int execSqlREF(string sql)
{
int J;
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
this.cmd = conn.CreateCommand();
}
this.cmd.CommandText = sql;
J = this.cmd.ExecuteNonQuery();
this.closeConn();
return J;
}
/**
* 执行DML语句(可带参数)并返回影响行数
* */
public int execSqlREF(string sql, OracleParameter[] para)
{
int J = 0;
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
this.cmd = conn.CreateCommand();
}
this.cmd.CommandText = sql;
if (para != null)
{
for (int i = 0; i < para.Length; i++)
{
this.cmd.Parameters.Add(para[i]);
}
}
J = this.cmd.ExecuteNonQuery();
this.closeConn();
return J;
}
/**
* 通过SQL语句返回结果集
* */
public System.Data.DataSet getDataSet(string sql)
{
DataSet dataSet = new DataSet();
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
this.cmd = conn.CreateCommand();
}
cmd.CommandText = sql;
adapter = new OracleDataAdapter(cmd);
adapter.Fill(dataSet);
this.closeConn();
return dataSet;
}
/**
* 通过SQL语句返回结果集
* */
public System.Data.DataSet getDataSet(string sql, OracleParameter[] para)
{
DataSet dataSet = new DataSet();
try
{
if (this.isTrans)
{
this.cmd = conn.CreateCommand();
this.cmd.Transaction = this.trans;
}
else
{
this.openConn();
}
this.cmd = getSqlCmd(sql, para);
adapter = new OracleDataAdapter(cmd);
adapter.Fill(dataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.conn.Close();
}
return dataSet;
}
/**
* 过滤SQL语句
* */
private string GetSqlStr(string strSql)
{
int startIndex = 0;
int num2 = 0;
while (startIndex < strSql.Length)
{
char ch = strSql[startIndex];
if (ch.ToString() == "?")
{
strSql = strSql.Remove(startIndex, 1).Insert(startIndex, ":p" + num2.ToString());
num2++;
}
startIndex++;
}
return strSql;
}
/**
* 将参数传入Command对象后返回该Command对象
**/
private OracleCommand getSqlCmd(string sql, OracleParameter[] para)
{
OracleCommand command = conn.CreateCommand();
command.CommandText = sql;
for (int i = 0; i < para.Length; i++)
{
command.Parameters.Add(para[i]);
}
return command;
}
}
}
===========DBFactory 工厂类
using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
namespace DAL
{
public class DBFactory
{
private IDBHelper dbhelper;
public IDBHelper getDBFactory()
{
try
{
//获取当前的数据库类型
string dbType = ConfigurationManager.AppSettings["DBTYPE"].ToString();
if (dbType == "ORACLE")
{
dbhelper = new DBHelperORACLE();
return dbhelper;
}
return null;
}
catch
{
return null;
}
}
}
}
参照链接:
http://www.cnblogs.com/blsong/archive/2010/10/19/1855614.html
http://www.cnblogs.com/springyangwc/archive/2011/03/23/1993061.html
http://blog.csdn.net/nick4/article/details/2421823