asp.net Oracle数据库访问操作类

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OracleClient;
using System.Configuration;
using System.Data.Common;
using System.Collections.Generic;

 /// <summary>
 /// 数据访问抽象基础类
 /// 
 /// </summary>
public class DBBase
{
 
 //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库. 
 public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString();
 public DBBase()
 {
 }
 #region 检查用户名是否存在
 /// <summary>
 /// 检查用户名是否存在，存在返回true，不存在返回false
 /// </summary>
 /// <param name="strSql"></param>
 /// <returns></returns>
 public static bool Exists(string strSql)
 {
 using (OracleConnection connection = new OracleConnection(connectionString))
 {
 connection.Open();
 OracleCommand myCmd = new OracleCommand(strSql, connection);
 try
 { 
 object obj = myCmd.ExecuteScalar(); //返回结果的第一行一列
 myCmd.Parameters.Clear();
 if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 {
 return false;
 }
 else
 {
 return true;
 } 
 }
 catch (Exception ex)
 {
 throw ex;
 }
 }
 }
 #endregion
 #region 执行简单SQL语句 返回影响的记录数
 /// <summary>
 /// 执行SQL语句，返回影响的记录数
 /// </summary>
 /// <param name="SQLString">SQL语句</param>
 /// <returns>影响的记录数</returns>
 public static int ExecuteSql(string SQLString)
 { OracleConnection connection = null;
 OracleCommand cmd = null;
 try
 {
 connection = new OracleConnection(connectionString);
 cmd = new OracleCommand(SQLString, connection);
 connection.Open();
 int rows = cmd.ExecuteNonQuery();
 return rows;
 }
 finally
 {
 if (cmd != null)
 {
 cmd.Dispose();
 }
 if (connection != null)
 {
 connection.Close();
 connection.Dispose();
 }
 }
 }
 #endregion

 #region 执行查询语句，返回SqlDataReader
 /// <summary>
 /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 /// </summary>
 /// <param name="strSQL">查询语句</param>
 /// <returns>SqlDataReader</returns>
 public static OracleDataReader ExecuteReader(string strSQL)
 {
 OracleConnection connection = new OracleConnection(connectionString);
 OracleCommand cmd = new OracleCommand(strSQL, connection);
 try
 {
 connection.Open();
 OracleDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 return myReader;
 }
 catch (System.Data.OracleClient.OracleException e)
 {
 throw e;
 }
 finally
 {
 connection.Close(); }
 }
 #endregion
 #region 执行SQL查询语句，返回DataTable数据表
 /// <summary>
 /// 执行SQL查询语句
 /// </summary>
 /// <param name="sqlStr"></param>
 /// <returns>返回DataTable数据表</returns>
 public static DataTable GetDataTable(string sqlStr)
 {
 OracleConnection mycon = new OracleConnection(connectionString);
 OracleCommand mycmd = new OracleCommand(sqlStr, mycon);
 DataTable dt = new DataTable();
 OracleDataAdapter da = null;
 try
 {
 mycon.Open();
 da = new OracleDataAdapter(sqlStr, mycon);
 da.Fill(dt);

 }
 catch (Exception ex)
 {
 throw new Exception(ex.ToString());
 }
 finally
 {
 mycon.Close();
 }
 return dt;
 }
 #endregion
 #region 存储过程操作
 /// <summary>
 /// 运行存储过程,返回datatable;
 /// </summary>
 /// <param name="storedProcName">存储过程名称</param>
 /// <param name="parameters">参数</param>
 /// <returns></returns>
 public static DataTable RunProcedureDatatable(string storedProcName, IDataParameter[] parameters)
 {
 using (OracleConnection connection = new OracleConnection(connectionString))
 {
 DataSet ds = new DataSet();
 connection.Open();
 OracleDataAdapter sqlDA = new OracleDataAdapter();
 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
 sqlDA.Fill(ds);
 connection.Close();
 return ds.Tables[0];
 }
 }
 /// <summary>
 /// 执行存储过程
 /// </summary>
 /// <param name="storedProcName">存储过程名称</param>
 /// <param name="parameters">参数</param>
 /// <returns></returns>
 public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
 {
 using (OracleConnection connection = new OracleConnection(connectionString))
 {
 try
 {
 connection.Open();
 OracleCommand command = new OracleCommand(storedProcName, connection);
 command.CommandType = CommandType.StoredProcedure;
 foreach (OracleParameter parameter in parameters)
 {
 if (parameter != null)
 {
 // 检查未分配值的输出参数,将其分配以DBNull.Value.
 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
 (parameter.Value == null))
 {
 parameter.Value = DBNull.Value;
 }
 command.Parameters.Add(parameter);
 }
 }
 int rows = command.ExecuteNonQuery();
 return rows;
 } finally
 {
 connection.Close();
 }
 }
 }
 /// <summary>
 /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
 /// </summary>
 /// <param name="connection">数据库连接</param>
 /// <param name="storedProcName">存储过程名</param>
 /// <param name="parameters">存储过程参数</param>
 /// <returns>OracleCommand</returns>
 private static OracleCommand BuildQueryCommand(OracleConnection connection, string storedProcName, IDataParameter[] parameters)
 {
 OracleCommand command = new OracleCommand(storedProcName, connection);
 command.CommandType = CommandType.StoredProcedure;
 foreach (OracleParameter parameter in parameters)
 {
 if (parameter != null)
 {
 // 检查未分配值的输出参数,将其分配以DBNull.Value.
 if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
 (parameter.Value == null))
 {
 parameter.Value = DBNull.Value;
 }
 command.Parameters.Add(parameter);
 }
 }
 return command;
 }

 #endregion 
 #region 事务处理
 /// <summary>
 /// 执行多条SQL语句(list的形式)，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param> 
 /// 调用 Transaction 对象的 Commit 方法来完成事务，或调用 Rollback 方法来取消事务。
 public static int ExecuteSqlTran(List<String> SQLStringList)
 {
 using (OracleConnection connection = new OracleConnection(connectionString))
 {
 connection.Open();
 // 为事务创建一个命令
 OracleCommand cmd = new OracleCommand();
 cmd.Connection = connection;
 OracleTransaction tx = connection.BeginTransaction();// 启动一个事务
 cmd.Transaction = tx;
 try
 {
 int count = 0;
 for (int n = 0; n < SQLStringList.Count; n++)
 {
 string strsql = SQLStringList[n];
 if (strsql.Trim().Length > 1)
 {
 cmd.CommandText = strsql;
 count += cmd.ExecuteNonQuery();
 }
 }
 tx.Commit();//用Commit方法来完成事务
 return count;//
 }
 catch
 {
 tx.Rollback();//出现错误，事务回滚！
 return 0;
 }
 finally
 {
 cmd.Dispose();
 connection.Close();//关闭连接
 }
 }
 }
 #endregion
 #region 事务处理
 /// <summary>
 /// 执行多条SQL语句(字符串数组形式)，实现数据库事务。
 /// </summary>
 /// <param name="SQLStringList">多条SQL语句</param> 
 /// 调用 Transaction 对象的 Commit 方法来完成事务，或调用 Rollback 方法来取消事务。
 public static int ExecuteTransaction(string[] SQLStringList,int p)
 {
 using (OracleConnection connection = new OracleConnection(connectionString))
 {
 connection.Open();
 // 为事务创建一个命令
 OracleCommand cmd = new OracleCommand();
 cmd.Connection = connection;
 OracleTransaction tx = connection.BeginTransaction();// 启动一个事务
 cmd.Transaction = tx;
 try
 {
 int count = 0;
 for (int n = 0; n < p; n++)
 {
 string strsql = SQLStringList[n];
 if (strsql.Trim().Length > 1)
 {
 cmd.CommandText = strsql;
 count += cmd.ExecuteNonQuery();
 }
 }
 tx.Commit();//用Commit方法来完成事务
 return count;//
 }
 catch
 {
 tx.Rollback();//出现错误，事务回滚！
 return 0;
 }
 finally
 {
 cmd.Dispose();
 connection.Close();//关闭连接
 }
 }
 }
 #endregion
 /// <summary>
 /// 执行存储过程获取所需编号（各表主键）
 /// </summary>
 /// <param name="FlowName">存储过程参数</param>
 /// <param name="StepLen">存储过程参数（默认为1）</param>
 /// <returns>编号（各表主键）</returns>
 public static string Get_FlowNum(string FlowName, int StepLen = 1)
 {
 OracleConnection mycon = new OracleConnection(connectionString);
 try
 {
 mycon.Open();
 OracleCommand MyCommand = new OracleCommand("ALARM_GET_FLOWNUMBER", mycon);
 MyCommand.CommandType = CommandType.StoredProcedure;
 MyCommand.Parameters.Add(new OracleParameter("I_FlowName", OracleType.VarChar, 50));
 MyCommand.Parameters["I_FlowName"].Value = FlowName;
 MyCommand.Parameters.Add(new OracleParameter("I_SeriesNum", OracleType.Number));
 MyCommand.Parameters["I_SeriesNum"].Value = StepLen;
 MyCommand.Parameters.Add(new OracleParameter("O_FlowValue", OracleType.Number));
 MyCommand.Parameters["O_FlowValue"].Direction = ParameterDirection.Output;
 MyCommand.ExecuteNonQuery();
 return MyCommand.Parameters["O_FlowValue"].Value.ToString();
 }
 catch
 {
 return "";
 }
 finally
 {
 mycon.Close();
 }
 }
}

到此这篇关于asp.net Oracle数据库访问操作类就介绍到这了。鹰击天风壮，鹏飞海浪春。更多相关asp.net Oracle数据库访问操作类内容请查看相关栏目，小编编辑不易，再次感谢大家的支持！