C#数据库访问之DbHelper类
来源:互联网 发布:电脑兼容性测试软件 编辑:程序博客网 时间:2024/06/05 14:55
===========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
- C#数据库访问之DbHelper类
- C#数据库编程 之 DbHelper数据操作类【DBHelper.CS】
- C#数据库编程 之 DbHelper数据操作类【DBHelper.CS】
- .net数据库访问类—DBHelper
- 数据库操作工具类--DBHelper--C#2.0
- DbHelper-SQL数据库访问助手
- C# DBHelper类 参考
- c# SqlServer DbHelper类
- C#自用DBHelper类
- C#通用DBHelper类
- 数据库通用类DBHelper
- dbhelper类,连接数据库
- Android中多线程访问数据库、DBHelper
- C#之数据库访问
- C# MySQL DBHelper帮助类
- DBHelper 数据库操作封装类
- DBHelper连接oracle数据库类
- DBHelper c#
- 回归自然
- 主机地址字符顺序 和网络字符顺序
- 在Linux系统环境下修改MySQL的root密码
- ASP.net--仿网易登录界面
- 那时,我们正年轻
- C#数据库访问之DbHelper类
- Thread(1)
- myeclipse10.0优化及SVN安装配置
- 我原来如此
- js 设计模式 chapter 1
- Hadoop源码分析 HDFS ClientProtocol——addBlock
- POJ 3636 Nested Dolls
- 编译原理中的正则表达式、NFA和DFA
- javase总结