C# .Net 连接Oracle数据库DataHelPer 类型

最新推荐文章于 2026-05-28 09:26:04 发布

原创最新推荐文章于 2026-05-28 09:26:04 发布 · 714 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

ASP.NET开发

SQL SERVER

这是一个C# .Net中用于连接Oracle数据库的DataHelPer类，包含建立连接、执行SQL语句、存储过程、数据适配器、数据集等操作的方法。类使用了Oracle.manageddataAccess包，并提供了连接字符串获取、数据库连接对象、数据读取器、数据适配器创建等实用功能。

要先下载对应的Oracle.manageddataAccess 的包引用，如果找不到也可以到这里下载。我的一个案例分析（https://download.csdn.net/download/wulixing/11227168）

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;
using System.Configuration;
using System.Security.Cryptography;
using System.Text;

namespace WKZF.Common
{
/// <summary>
/// Oracle数据库底层访问类
/// </summary>
public class OracleDataHelPer
{
public string ConnStr = null;
//数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.

/// <summary>
/// 数据库连接
/// </summary>
/// <param name="connName">web.config中连接名称</param>
public string GetOracleConn(string connName)
{
ConnStr = System.Configuration.ConfigurationManager.AppSettings[connName];
return ConnStr;
}

public OracleDataHelPer(string Str)
{
try
{
this.ConnStr = Str;
}
catch (Exception ex)
{
throw ex;
}
}

/// <summary>
/// 返回connection对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <returns></returns>
public OracleConnection ReturnConn(string connName)
{
OracleConnection Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
return Conn;
}

public void Dispose(OracleConnection Conn)
{
if (Conn != null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}

/// <summary>
/// 运行SQL语句
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
public void RunProc(string connName,string SQL)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
try
{
Cmd.ExecuteNonQuery();
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return;
}

/// <summary>
/// 运行SQL语句返回DataReader
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public OracleDataReader RunProcGetReader(string connName, string SQL)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleCommand Cmd;
Cmd = CreateCmd(SQL, Conn);
OracleDataReader Dr;
try
{
Dr = Cmd.ExecuteReader(CommandBehavior.Default);
}
catch
{
throw new Exception(SQL);
}
//Dispose(Conn);
return Dr;
}

/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="SQL"></param>
/// <param name="Conn"></param>
/// <returns></returns>
public OracleCommand CreateCmd(string SQL, OracleConnection Conn)
{
OracleCommand Cmd;
Cmd = new OracleCommand(SQL, Conn);
return Cmd;
}

/// <summary>
/// 生成Command对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public OracleCommand CreateCmd(string connName,string SQL )
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleCommand Cmd;
Cmd = new OracleCommand(SQL, Conn);
return Cmd;

}

/// <summary>
/// 返回adapter对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public OracleDataAdapter CreateDa(string connName, string SQL)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleDataAdapter Da;
Da = new OracleDataAdapter(SQL, Conn);
return Da;
}

/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <param name="Ds"></param>
/// <returns></returns>
public DataSet RunProc(string connName, string SQL, DataSet Ds)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
//if (Conn.State == System.Data.ConnectionState.Closed)

Conn.Open();

OracleDataAdapter Da;
//Da = CreateDa(SQL, Conn);
Da = new OracleDataAdapter(SQL, Conn);
try
{
Da.Fill(Ds);
}
catch (Exception Err)
{
throw Err;
}
Dispose(Conn);
return Ds;

}

/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL">SQL语句</param>
/// <param name="Ds">DataSet对象</param>
/// <param name="tablename">表名</param>
/// <returns></returns>
public DataSet RunProc(string connName,string SQL, DataSet Ds, string tablename)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleDataAdapter Da;
Da = CreateDa(SQL, connName);
try
{
Da.Fill(Ds, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}

/// <summary>
/// 运行SQL语句,返回DataSet对象
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <param name="Ds"></param>
/// <param name="StartIndex"></param>
/// <param name="PageSize"></param>
/// <param name="tablename"></param>
/// <returns></returns>
public DataSet RunProc(string connName,string SQL, DataSet Ds, int StartIndex, int PageSize, string tablename)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleDataAdapter Da;
Da = CreateDa(SQL, connName);
try
{
Da.Fill(Ds, StartIndex, PageSize, tablename);
}
catch (Exception Ex)
{
throw Ex;
}
Dispose(Conn);
return Ds;
}
/// <summary>
/// 执行多条SQL语句，实现数据库事务。
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQLStringList">多条SQL语句</param>
public void ExecuteSqlTran(string connName, List<string> SQLStringList)
{
using (OracleConnection conn = new OracleConnection(GetOracleConn(connName)))
{
conn.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
OracleTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
}

/// <summary>
/// 检验是否存在数据
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public bool ExistDate(string connName, string SQL)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleDataReader Dr;
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
Dispose(Conn);
return true;
}
else
{
Dispose(Conn);
return false;
}
}

/// <summary>
/// 返回SQL语句执行结果的第一行第一列
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public string ReturnValue(string connName, string SQL)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
string result;
OracleDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = "";
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Dispose(Conn);
return result;
}

/// <summary>
/// 返回SQL语句第一列,第ColumnI列,
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <param name="ColumnI"></param>
/// <returns></returns>
public string ReturnValue(string connName, string SQL, int ColumnI)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
string result;
OracleDataReader Dr;
try
{
Dr = CreateCmd(SQL, Conn).ExecuteReader();
}
catch
{
throw new Exception(SQL);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = "";
}
Dr.Close();
Dispose(Conn);
return result;
}

/// <summary>
/// 生成一个存储过程使用的sqlcommand.
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <returns></returns>
public OracleCommand CreateCmd(string connName, string procName, OracleParameter[] prams)
{
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleCommand Cmd = new OracleCommand(procName, Conn);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (OracleParameter parameter in prams)
{
if (parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
return Cmd;
}

/// <summary>
/// 执行sql语句返回DataTable
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="sql">安全的sql语句</param>
/// <returns>根据sql语句得到所有记录</returns>
public DataTable GetDataTable(string connName, string sql)
{
using (OracleConnection conn = new OracleConnection(GetOracleConn(connName)))
{
OracleDataAdapter sda = new OracleDataAdapter(sql, conn);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}

}

/// <summary>
/// 运行存储过程,返回string
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <returns></returns>
public string RunProc(string connName, string procName, OracleParameter[] prams)
{
OracleDataReader Dr;
OracleCommand Cmd = CreateCmd(connName,procName, prams);
Dr = Cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
if (Dr.Read())
{
return Dr.GetValue(0).ToString();
}
else
{
return "";
}
}

/// <summary>
/// 运行存储过程,返回double
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <param name="re"></param>
/// <returns></returns>
public double RunProc1(string connName, string procName, OracleParameter[] prams, ref double re)
{
OracleCommand cmd = CreateCmd( connName,procName, prams);
cmd.ExecuteNonQuery();
re = Convert.ToDouble(cmd.Parameters[prams[0].ToString()].Value.ToString());
return Convert.ToDouble(cmd.Parameters[prams[1].ToString()].Value.ToString());//此为获取返回的输出参数值
}

/// <summary>
/// 运行存储过程,返回dataset
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="procName"></param>
/// <param name="prams"></param>
/// <param name="Ds"></param>
/// <returns></returns>
public DataSet RunProc(string connName, string procName, OracleParameter[] prams, DataSet Ds)
{
OracleCommand Cmd = CreateCmd(connName,procName, prams);
OracleDataAdapter Da = new OracleDataAdapter(Cmd);
try
{
Da.Fill(Ds);
}
catch (Exception Ex)
{
throw Ex;
}
return Ds;
}

/// <summary>
/// 获得记录总数
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="SQL"></param>
/// <returns></returns>
public int CalculateRecord(string connName, string SQL)
{
int intCount;
OracleConnection Conn;
Conn = new OracleConnection(GetOracleConn(connName));
Conn.Open();
OracleCommand MyComm = new OracleCommand(SQL, Conn);
OracleDataReader dr = MyComm.ExecuteReader();
if (dr.Read())
{
intCount = Int32.Parse(dr[0].ToString());
}
else
{
intCount = 0;
}
dr.Close();
Dispose(Conn);
return intCount;
}

/// <summary>
/// 身份号码操作:由18位的身份证号得到15位的
/// </summary>
/// <param name="sfzh"></param>
/// <returns></returns>
public string Creat15CidBy18(string sfzh)
{
string strPart1 = "";
string strPart2 = "";
string strRes = "";
if (sfzh.Length == 18)
{
strPart1 = sfzh.Substring(0, 6);
strPart2 = sfzh.Substring(8, 9);
strRes = strPart1 + strPart2;
}
else
{
strRes = sfzh;
}
return strRes;
}

/// <summary>
/// 由15位产生18位身份证号
/// </summary>
/// <param name="sfzh"></param>
/// <returns></returns>
public string Creat18CidBy15(string sfzh)
{
char[] strJiaoYan = { '1', '0', 'X', '9', '8', '7', '6', '5', '4', '3', '2' };
int[] intQuan = { 7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2, 1 };
string strTemp;
int intTemp = 0;

strTemp = sfzh.Substring(0, 6) + 19 + sfzh.Substring(6);
for (int i = 0; i <= strTemp.Length - 1; i++)
{
intTemp += int.Parse(strTemp.Substring(i, 1)) * intQuan[i];
}
intTemp = intTemp % 11;
return strTemp + strJiaoYan[intTemp];
}

/// <summary>
/// 用户权限判断函数
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="userid"></param>
/// <param name="rightcode"></param>
/// <returns></returns>
public bool CheckAuth(string connName,string userid, string rightcode)
{
bool result = false;
string sql;
string usertype;
if (rightcode.Length != 0)
{
sql = "select t.usertype,t.* from users t where t.userid='" + userid + "'";
if (ExistDate(connName,sql))
{
usertype = ReturnValue(connName,sql).ToString();
sql = "select * from users_right t where t.usertypeid='" + usertype + "' and rightcode='" + rightcode + "'";
if (ExistDate(connName,sql))
{
result = true;

}
else
{
result = false;
}

}
}
return result;
}

/// <summary>
/// 插入日志, 需要在库里添加一比重日志表QUERYLOG、USERS
/// </summary>
/// <param name="connName">数据库连接名称</param>
/// <param name="userid"></param>
/// <param name="username"></param>
/// <param name="remark"></param>
/// <param name="sql"></param>
/// <param name="ip"></param>
public void Log_Insert(string connName, string userid, string username, string remark, string sql, string ip)
{
int max_id;
if (!ExistDate(connName, "select id from QUERYLOG"))
max_id = 1;
else
max_id = Convert.ToInt32(ReturnValue(connName, "select max(id) as id from QUERYLOG").ToString()) + 1;

sql = sql.Replace("'", "").Replace("--", "");
remark = remark.Replace("'", "").Replace("--", "");
string loginid = ReturnValue(connName, "select LOGINID from USERS where USERID='" + userid + "'", 0);
string sql1 = "insert into QUERYLOG(USERID,REMARK,USERNAME,ID,SQL,INPUT_IP) values('" + loginid + "','" + remark + "','" + username + "','" + max_id + "','" + sql + "','" + ip + "')";
RunProc(connName, sql1);
}

/// <summary>
/// MD5加密
/// </summary>
/// <param name="password"></param>
/// <returns></returns>
public string MD5(string password)
{
byte[] bytes = Encoding.UTF8.GetBytes(password);
MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
byte[] bys = md5.ComputeHash(bytes);
string s = "";
for (int i = bys.Length - 1; i >= 0; i -= 2)
{
s += bys[i].ToString("X");
}
return s;
}
}
}

标签

#NET #QRACLE