OracleHelper.cs

This OracleHelper class is intended to encapsulate high performance, scalable best practices for common uses of OracleClient

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace Trans829115.DAL
{
public class OracleHelper
{
private string strConnectionString;
private OracleConnection oConnection;
private OracleCommand oCommand;
private int iTimeOut = 30;

public enum ExpectedType
{

StringType = 0,
NumberType = 1,
DateType = 2,
BooleanType = 3,
ImageType = 4
}

public OracleHelper()
{
try
{

strConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

oConnection = new OracleConnection(strConnectionString);
oCommand = new OracleCommand();
oCommand.CommandTimeout = iTimeOut;
oCommand.Connection = oConnection;

}
catch (Exception ex)
{
throw new Exception("Error initializing OracleHelper class." + Environment.NewLine + ex.Message);
}
}


public OracleHelper(string MyConnectionString)
{
try
{

strConnectionString = MyConnectionString;

oConnection = new OracleConnection(strConnectionString);
oCommand = new OracleCommand();
oCommand.CommandTimeout = iTimeOut;
oCommand.Connection = oConnection;

}
catch (Exception ex)
{
throw new Exception("Error initializing OracleHelper class." + Environment.NewLine + ex.Message);
}
}

public void Dispose()
{
try
{
//Dispose off connection object
if (oConnection != null)
{
if (oConnection.State != ConnectionState.Closed)
{
oConnection.Close();
}
oConnection.Dispose();
}

//Clean Up Command Object
if (oCommand != null)
{
oCommand.Dispose();
}

}

catch (Exception ex)
{
throw new Exception("Error disposing OracleHelper class." + Environment.NewLine + ex.Message);
}

}

public void CloseConnection()
{
if (oConnection.State != ConnectionState.Closed) oConnection.Close();
}

public int GetExecuteScalarByCommand(string Command)
{

object identity = 0;
try
{
oCommand.CommandText = Command;
oCommand.CommandTimeout = iTimeOut;
oCommand.CommandType = CommandType.StoredProcedure;

oConnection.Open();

oCommand.Connection = oConnection;
identity = oCommand.ExecuteScalar();
CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}
return Convert.ToInt32(identity);
}

public void GetExecuteNonQueryByCommand(string Command)
{
try
{
oCommand.CommandText = Command;
oCommand.CommandTimeout = iTimeOut;
oCommand.CommandType = CommandType.StoredProcedure;

oConnection.Open();

oCommand.Connection = oConnection;
oCommand.ExecuteNonQuery();

CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}
}

public void GetExecuteNonQueryBySQL(string strSQL)
{
try
{
oCommand.CommandText = strSQL;
oCommand.CommandTimeout = iTimeOut;
oCommand.CommandType = CommandType.Text;

oConnection.Open();

oCommand.Connection = oConnection;
oCommand.ExecuteNonQuery();

CloseConnection();
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}
}

public DataSet GetDatasetByCommand(string Command)
{
try
{
oCommand.CommandText = Command;
oCommand.CommandTimeout = iTimeOut;
oCommand.CommandType = CommandType.StoredProcedure;

oConnection.Open();

OracleDataAdapter adpt = new OracleDataAdapter(oCommand);
DataSet ds = new DataSet();
adpt.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}

public DataSet GetDatasetBySQL(string strSQL)
{
try
{
oCommand.CommandText = strSQL;
oCommand.CommandTimeout = iTimeOut;
oCommand.CommandType = CommandType.Text;

oConnection.Open();

OracleDataAdapter Adapter = new OracleDataAdapter(strSQL, oConnection);
DataSet ds = new DataSet();
Adapter.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConnection();
}
}


public OracleDataReader GetReaderBySQL(string strSQL)
{
oConnection.Open();
try
{
OracleCommand myCommand = new OracleCommand(strSQL, oConnection);
return myCommand.ExecuteReader();
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}
}

public OracleDataReader GetReaderByCmd(string Command)
{
OracleDataReader objOracleDataReader = null;
try
{
oCommand.CommandText = Command;
oCommand.CommandType = CommandType.StoredProcedure;
oCommand.CommandTimeout = iTimeOut;

oConnection.Open();
oCommand.Connection = oConnection;

objOracleDataReader = oCommand.ExecuteReader() ;
return objOracleDataReader;
}
catch (Exception ex)
{
CloseConnection();
throw ex;
}

}

public void AddParameterToSQLCommand(string ParameterName, OracleType ParameterType)
{
try
{
oCommand.Parameters.Add(new OracleParameter(ParameterName, ParameterType));
}

catch (Exception ex)
{
throw ex;
}
}
public void AddParameterToSQLCommand(string ParameterName, OracleType ParameterType,int ParameterSize)
{
try
{
oCommand.Parameters.Add(new OracleParameter(ParameterName, ParameterType, ParameterSize));
}

catch (Exception ex)
{
throw ex;
}
}
public void SetSQLCommandParameterValue(string ParameterName, object Value)
{
try
{
oCommand.Parameters[ParameterName].Value = Value;
}

catch (Exception ex)
{
throw ex;
}
}
}

}