/***********************************************************************
* 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, Comma
