OracleHelper类

/// <summary>
  /// ClassName: OracleHelper
  /// Describe:数据库操作帮助类
  /// Author:wzb
  /// </summary>
  public class OracleHelper
  {


  #region 1、执行命令，返回受影响的行数
  /// <summary>
  /// Execute a OracleCommand (that returns no resultset) against an existing database connection 
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:  
  /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
  /// </remarks>
  /// <param name="conn">an existing database connection</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-Oracle command</param>
  /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
  /// <returns>an int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(OracleConnection connection, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
  {
  int val = 0;
  try
  {
  OracleCommand cmd = connection.CreateCommand();
  PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
  val = cmd.ExecuteNonQuery();
  cmd.Parameters.Clear();
  }
  catch (OracleException oe)
  {
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(connection);
  }
  return val;
  }
  #endregion

  #region 2、执行命令，返回受影响的行数 重载，带事务
  /// <summary>
  /// Execute a OracleCommand (that returns no resultset) using an existing Oracle Transaction 
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:  
  /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
  /// </remarks>
  /// <param name="trans">an existing Oracle transaction</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-Oracle command</param>
  /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
  /// <returns>an int representing the number of rows affected by the command</returns>
  public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
  {
  int val = 0;
  try
  {
  OracleCommand cmd = trans.Connection.CreateCommand();
  PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
  val = cmd.ExecuteNonQuery();
  cmd.Parameters.Clear();
  trans.Commit();
  }
  catch (OracleException oe)
  {
  trans.Rollback();
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(trans.Connection);
  }

  trans = null;
  return val;
  }
  #endregion

  #region 3、执行命令，返回 OracleDataReader
  /// <summary>
  /// Execute a OracleCommand that returns a resultset against the database specified in the connection string 
  /// using the provided parameters. 
  /// </summary>
  /// <param name="connectionString">a valid connection string for a OracleConnection</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-Oracle command</param>
  /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
  /// <returns>A OracleDataReader containing the results</returns>
  public static OracleDataReader ExecuteReader(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
  {
  OracleCommand cmd = conn.CreateCommand();
  //we use a try/catch here because if the method throws an exception we want to
  //close the connection throw code, because no datareader will exist, hence the
  //commandBehaviour.CloseConnection will not work
  OracleDataReader reader = null;
  try
  {
  PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
  reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
  cmd.Parameters.Clear();
  }
  catch
  {

  throw;
  }

  return reader;

  }
  #endregion

  #region 4、执行命令，返回单个值
  /// <summary>
  /// Execute a OracleCommand that returns the first column of the first record against the database specified in the connection string 
  /// using the provided parameters.
  /// </summary>
  /// <remarks>
  /// e.g.:  
  /// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OracleParameter("@prodid", 24));
  /// </remarks>
  /// <param name="connectionString">a valid connection string for a OracleConnection</param>
  /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
  /// <param name="commandText">the stored procedure name or T-Oracle command</param>
  /// <param name="commandParameters">an array of OracleParamters used to execute the command</param>
  /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
  public static object ExecuteScalar(OracleConnection conn, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
  {
  OracleCommand cmd = conn.CreateCommand();
  object val = null;
  try
  {
  PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
  val = cmd.ExecuteScalar();
  cmd.Parameters.Clear();
  }
  catch (OracleException oe)
  {  
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(conn);
  }
  return val;

  }
  #endregion

  #region 5、封装命令参数
  /// <summary>
  /// Prepare a command for execution
  /// </summary>
  /// <param name="cmd">OracleCommand object</param>
  /// <param name="conn">OracleConnection object</param>
  /// <param name="trans">OracleTransaction object</param>
  /// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
  /// <param name="cmdText">Command text, e.g. Select * from Products</param>
  /// <param name="cmdParms">OracleParameters to use in the command</param>
  private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
  {
  try
  {
  if (conn.State != ConnectionState.Open)
  {
  conn.Open();
  }
  }
  catch 
  {
  throw;
  }

  cmd.Connection = conn;
  cmd.CommandText = cmdText;

  if (trans != null)
  {
  cmd.Transaction = trans;
  }
  cmd.CommandType = cmdType;

  if (cmdParms != null)
  {
  foreach (OracleParameter parm in cmdParms)
  {

  cmd.Parameters.Add(parm);
  }
  }
  }
  #endregion

  #region 6、根据传入参数，获取存储过程的返回值
  /// <summary>
  /// 根据传入参数，获取存储过程的返回值
  /// </summary>
  /// <param name="con">数据库连接</param>
  /// <param name="cmdType">命令类型（T-OracleCommand或存储过程）</param>
  /// <param name="CommandText">命令字符串</param>
  /// <param name="Parameters">参数数组</param>
  /// <returns>返回值</returns>
  public static string ExecuteScalarOutputValue(OracleConnection con, CommandType cmdType, string CommandText, OracleParameter[] Parameters)
  {
  object obj = null;
  try
  {

  OracleCommand command = con.CreateCommand();
  PrepareCommand(command, con, null, cmdType, CommandText, Parameters);
  command.ExecuteNonQuery();
  obj = command.Parameters[Parameters.Length - 1].Value; //最后一个是返回参数

  }
  catch (OracleException oe)
  {
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(con);
  }
  return obj.ToString();
  }
  #endregion

  #region 7 执行命令 执行存储过程，填充DataTable 对象
  /// <summary>
  /// 填充DataTable 对象
  /// </summary>
  /// <param name="conn">数据库连接对象</param>
  /// <param name="cmdType">命令类型，指存储过程或T-Oracle语句</param>
  /// <param name="commandText">存储过程名称或语句</param>
  /// <param name="Parameters">参数数组</param>
  /// <param name="table">用户传入的DataTable对象</param>
  public static void FillDataTable(OracleConnection conn, CommandType cmdType, string commandText, OracleParameter[] Parameters, DataTable table)
  {

  OracleCommand command = conn.CreateCommand();
  try
  {
  PrepareCommand(command, conn, null, cmdType, commandText, Parameters);
  OracleDataAdapter adapter = new OracleDataAdapter(command);
  if (table == null)
  {
  table = new DataTable();
  }
  adapter.Fill(table);

  }
  catch (OracleException oe)
  {
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(conn);
  }

  }
  #endregion
  /// <summary>
  /// 根据传入参数，获取存储过程的返回值(带事务)
  /// </summary>
  /// <param name="con">数据库连接OracleConnection实例</param>
  /// <param name="tran">OracleTransation事例</param>
  /// <param name="cmdType">CommandType枚举</param>
  /// <param name="CommandText">sql语句，或存储过程名</param>
  /// <param name="Parameters">sql语句参数或者存储过程参数</param>
  /// <returns>返回值</returns>
  //public static string ExecuteScalarOutputValue(OracleConnection con, CommandType cmdType, string CommandText, params OracleParameter[] Parameters)
  //{
  // object obj = null;
  // try
  // {

  // OracleCommand command = new OracleCommand();
  // PrepareCommand(command, con, null, cmdType, CommandText, Parameters);
  // command.ExecuteNonQuery();
  // obj = command.Parameters[Parameters.Length - 1].Value; //最后一个是返回参数
  // command.Parameters.Clear();

  // }
  // catch (OracleException oe)
  // {
  // throw oe;
  // }
  // finally
  // {
  // ConnectionManager.Close(con);
  // }
  // return obj.ToString();
  //}
  /// <summary>
  /// 根据传入参数，获取存储过程的返回值(带事务)
  /// </summary>
  /// <param name="con">数据库连接OracleConnection实例</param>
  /// <param name="tran">OracleTransation事例</param>
  /// <param name="cmdType">CommandType枚举</param>
  /// <param name="CommandText">sql语句，或存储过程名</param>
  /// <param name="Parameters">sql语句参数或者存储过程参数</param>
  /// <returns>返回值集合</returns>
  public static ArrayList ExecuteOutputValues(OracleConnection con, CommandType cmdType, string CommandText, params OracleParameter[] Parameters)
  {
  ArrayList list = new ArrayList();
  try
  {
  OracleCommand command = con.CreateCommand();
  PrepareCommand(command, con, null, cmdType, CommandText, Parameters);
  command.ExecuteNonQuery();
   
  object obj = null;
  foreach (OracleParameter par in Parameters)
  {
  if (par.Direction == ParameterDirection.Output)
  {
  obj = par.Value;
  list.Add(obj);
  }
  }
  command.Parameters.Clear();
  }
  catch (OracleException oe)
  {  
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(con);
  }
  return list;
  }

  /// <summary>
  /// 插入一条日志记录
  /// </summary>
  /// <param name="HandleIP">操作机器的IP</param>
  /// <param name="HandleHumanAccounts">操作人帐号</param>
  /// <param name="HandleContent">操作内容 如："修改黑名单商品 条形码为" + tbx_BarCode.Text </param>
  /// <param name="OparetorType">操作类型（传数值） 0:用户登录 1:重要操作 2:无权限操作 3:普通日志</param>
  /// <param name="GongSSIDS">登陆用户工商ID</param>
  /// <param name="GongSSName">工商名</param>
  /// <param name="XJName">县局名</param>
  /// <param name="SJName">市局名</param>
  public static void InsertLog(string HandleIP, string HandleHumanAccounts, string HandleContent, string OparetorType, string GongSSIDS, string GongSSName, string XJName, string SJName)
  {

  string commandText = "P_CERTIFICATEMANAGE_SYSTEMLOGM.InsertLog";
  OracleParameter[] param = new OracleParameter[8];
  param[0] = new OracleParameter("inHandleIP", HandleIP);
  param[1] = new OracleParameter("inHANDLEHUMANACCOUNTS", HandleHumanAccounts);
  param[2] = new OracleParameter("inHANDLECONTENT", HandleContent);
  param[3] = new OracleParameter("inOPARETORTYPE", OparetorType);
  param[4] = new OracleParameter("inGongSSIDS", GongSSIDS);
  param[5] = new OracleParameter("inGongSSName", GongSSName);
  param[6] = new OracleParameter("inXJName", SJName);
  param[7] = new OracleParameter("inSJName", SJName);
  OracleConnection conn = null;
  try
  {
  conn = ConnectionManager.GetCon();
  OracleHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, commandText, param);
  }
  catch (Exception ex)
  {
  throw ex;
  }
  finally
  {
  ConnectionManager.Close(conn);
  }
  }
  /// <summary>
  /// 根据传入参数，执行存储过程(带事务)
  /// </summary>
  /// <param name="con">数据库连接OracleConnection实例</param>
  /// <param name="tran">OracleTransation事例</param>
  /// <param name="cmdType">CommandType枚举</param>
  /// <param name="CommandText">sql语句，或存储过程名</param>
  /// <param name="Parameters">sql语句参数或者存储过程参数</param>
  public static void ExecuteNonOutputValue(OracleConnection con, CommandType cmdType, string CommandText, params OracleParameter[] Parameters)
  {
  try
  {

  OracleCommand command = con.CreateCommand();
  PrepareCommand(command, con, null, cmdType, CommandText, Parameters);
  command.ExecuteNonQuery();
  command.Parameters.Clear();

  }
  catch (OracleException oe)
  {
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(con);
  }

  }
  /// <summary>
  /// 执行命令返回存储过程里单个值。
  /// </summary>
  /// <param name="trans"></param>
  /// <param name="cmdType"></param>
  /// <param name="CommandText"></param>
  /// <param name="Parameters"></param>
  /// <returns></returns>
  public static int ExecuteScalarOutputValue2(OracleConnection con, CommandType cmdType, string CommandText, OracleParameter[] Parameters)
  {
   
  object reNun = 0;
  try
  {
  OracleCommand command = con.CreateCommand();
   
  PrepareCommand(command, con, null, cmdType, CommandText, Parameters);
  command.ExecuteNonQuery();
  reNun = command.Parameters[Parameters.Length - 1].Value; //最后一个是返回参数
   
  }
  catch (OracleException oe)
  {
   
  throw oe;
  }
  finally
  {
  ConnectionManager.Close(con);
  }
  return int.Parse(reNun.ToString());
  }



  }