.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

OracleHelper.cs Class

Posted By:shahin       Posted Date: May 29, 2009    Points: 40    Category: ADO.NET    URL: http://www.dotnetspark.com  

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

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;
}
}
}

}


     

Further Readings:

Responses
Author: kalit         Company URL: http://www.dotnetspark.com
Posted Date: November 21, 2009

Copied From: http://kalitsikka.blogspot.com/2009/05/oraclehelpercs.html

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend