oracle/***********************************************************************
 * Module:  OraHelper.cs
 * Author:  hellopj
 * Purpose: Definition of the Class OraHelper
 ***********************************************************************/

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Collections;
using System.Configuration;
namespace OraHelper
{
 public abstract class OraHelper
 {
   public static readonly string CONN_STRING_NON_DTC = ConfigurationSettings.AppSettings["ConnStr"].Trim();
  public static OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC);

  public static void OpenConnection()
  {
   if (conn.State != ConnectionState.Open)
    conn.Open();
  }
  public static void CloseConnection()
  {
   if (conn.State == ConnectionState.Open)
    conn.Close();
  }
  public static DataSet getDataSet(string cmdText)
  {
   
   OracleDataAdapter Adapter = new OracleDataAdapter(cmdText,conn);
   DataSet Data = new DataSet();
   Adapter.Fill(Data);
   return Data;
  }
  public static OracleDataReader ExecuteReader(string cmdText)
  {
   
   OracleCommand cmd = new OracleCommand();
   cmd.Connection=conn;
   cmd.CommandText=cmdText;
   OracleDataReader rdr = cmd.ExecuteReader();
   cmd.Parameters.Clear();
   return rdr;
  }
  public static int ExecuteNonQuery(string cmdText)
  {
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    comm.ExecuteNonQuery();
    return 1;
   }
   catch
   {
    return 0;
   }
  }
  public static void ExecuteNonQuery(string cmdText,OracleConnection conn)
  {
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    comm.ExecuteNonQuery();
   }
   catch
   {
    
   }
  }
  public static string ExecuteScalar(string cmdText)
  {
   string status="";
   try
   {
    OracleCommand comm=new OracleCommand(cmdText,conn);
    status=comm.ExecuteScalar().ToString();
    return status;
   }
   catch(Exception err)
   {
    //return "-1";
    return err.Message.ToString();
    
   }
  }
  private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
  public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   OracleCommand cmd = new OracleCommand();
   cmd.Connection = conn;
   cmd.Parameters.Clear();
   cmd.CommandText=cmdText;
   foreach (OracleParameter parm in cmdParms)
    cmd.Parameters.Add(parm);
   int val=cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

  public static int ExecuteNonQuery(OracleTransaction trans, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, cmdParms);
   int val = cmd.ExecuteNonQuery();
   cmd.Parameters.Clear();
   return val;
  }

  
  public static OracleDataReader ExecuteReader(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   
   OracleCommand cmd = new OracleCommand();
   try
   {
    PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
    OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    cmd.Parameters.Clear();
    return rdr;
   
   }
   catch (Exception e)
   {
    conn.Close();
    throw e;
   } 
  }
  
 
  public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }

  
  public static object ExecuteScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   return val;
  }
  
  public static int getScalar(CommandType cmdType, string cmdText, params OracleParameter[] cmdParms)
  {
   
   OracleCommand cmd = new OracleCommand();
   PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
   object val = cmd.ExecuteScalar();
   cmd.Parameters.Clear();
   int nVal  = Convert.ToInt32(val);
   return nVal;
   
  }

  
  
  public static void CacheParameters(string cacheKey, params OracleParameter[] cmdParms)
  {
   parmCache[cacheKey] = cmdParms;
  }

  
  public static OracleParameter[] GetCachedParameters(string cacheKey)
  {
   OracleParameter[] cachedParms = (OracleParameter[])parmCache[cacheKey];
   
   if (cachedParms == null)
    return null;
   
   
   OracleParameter[] clonedParms = new OracleParameter[cachedParms.Length];

   
   for (int i = 0, j = cachedParms.Length; i < j; i++)
    clonedParms[i] = (OracleParameter)((ICloneable)cachedParms[i]).Clone();

   return clonedParms;
  }

  
  private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, CommandType cmdType, string cmdText, OracleParameter[] cmdParms)
  {
   
   if (conn.State != ConnectionState.Open)
    conn.Open();
   cmd.Connection = conn;
   cmd.CommandText = cmdText;
   cmd.CommandType = cmdType;
   //if (trans != null)
   //cmd.Transaction = trans;
   if (cmdParms != null)
   {
    foreach (OracleParameter parm in cmdParms)
     cmd.Parameters.Add(parm);
   }
  }

  public static int GetID(string SeqName) //得到序列的nextval
  {
   OracleCommand cmd = new OracleCommand();
   using (OracleConnection conn = new OracleConnection(CONN_STRING_NON_DTC))
   {
    string SqlText="select "+SeqName+".nextval from dual";
    
    PrepareCommand(cmd, conn, null, CommandType.Text,SqlText,null);
    int val = Convert.ToInt32(cmd.ExecuteScalar());
    cmd.Parameters.Clear();
    return val;
   }
  }
   }
}