<connectionStrings>
<add name="ConStrng" connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.50.10)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=excise)));User Id=excise;Password=vat123$;"/>
</connectionStrings>
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration.Assemblies;
using CommercialTaxMissionMode.ObjectModel;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
namespace CommercialTaxMissionMode.DAL
{
public class OracleHelper
{
string constrg = System.Configuration.ConfigurationManager.ConnectionStrings["ConStrng"].ToString();
#region Reader
/// <summary>
/// Returns a datareader for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <param name="prms"></param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string cmdText, OracleParameter[] prms, CommandType Type)
{
try
{
OracleConnection conn = new OracleConnection(constrg);
using (OracleCommand cmd = new OracleCommand(cmdText, conn))
{
cmd.CommandType = Type;
if (prms != null)
{
foreach (OracleParameter p in prms)
{
cmd.Parameters.Add(p);
}
}
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a datareader for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public OracleDataReader ExecuteReader(string cmdText, CommandType Type)
{
return ExecuteReader(cmdText, null, Type);
}
#endregion
#region DataSet
/// <summary>
/// Returns a dataset for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <param name="prms"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string cmdText, OracleParameter[] prms,CommandType Type)
{
try
{
using (OracleConnection conn = new OracleConnection(constrg))
{
DataSet ds = new DataSet();
using (OracleCommand cmd = new OracleCommand(cmdText, conn))
{
cmd.CommandType = Type;
if (prms != null)
{
foreach (OracleParameter p in prms)
{
cmd.Parameters.Add(p);
}
}
OracleDataAdapter adpt = new OracleDataAdapter(cmd);
adpt.Fill(ds);
return ds;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a dataset for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public DataSet ExecuteDataSet(string cmdText, CommandType Type)
{
return ExecuteDataSet(cmdText, null, Type);
}
#endregion
#region DataTable
/// <summary>
/// Returns a datatable for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <param name="prms"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string cmdText, OracleParameter[] prms, CommandType Type)
{
try
{
using (OracleConnection conn = new OracleConnection(constrg))
{
DataTable dt = new DataTable();
using (OracleCommand cmd = new OracleCommand(cmdText, conn))
{
cmd.CommandType = Type;
if (prms != null)
{
foreach (OracleParameter p in prms)
{
cmd.Parameters.Add(p);
}
}
OracleDataAdapter adpt = new OracleDataAdapter(cmd);
adpt.Fill(dt);
return dt;
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns a datatable for the oracle command
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public DataTable ExecuteDataTable(string cmdText, CommandType Type)
{
return ExecuteDataTable(cmdText, null, Type);
}
#endregion
#region NonQuery
/// <summary>
/// Executes a non query
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <param name="prms"></param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, OracleParameter[] prms, CommandType Type)
{
try
{
using (OracleConnection conn = new OracleConnection(constrg))
{
using (OracleCommand cmd = new OracleCommand(cmdText, conn))
{
cmd.CommandType = Type;
if (prms != null)
{
foreach (OracleParameter p in prms)
{
cmd.Parameters.Add(p);
}
}
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Executes a non query
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, CommandType Type)
{
return ExecuteNonQuery(cmdText, null, Type);
}
#endregion
#region Scalar
/// <summary>
/// Returns the scalar object of the query
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <param name="prms"></param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, OracleParameter[] prms, CommandType Type)
{
try
{
using (OracleConnection conn = new OracleConnection(constrg))
{
using (OracleCommand cmd = new OracleCommand(cmdText, conn))
{
cmd.CommandType = Type;
if (prms != null)
{
foreach (OracleParameter p in prms)
{
cmd.Parameters.Add(p);
}
}
conn.Open();
return cmd.ExecuteScalar();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Returns the scalar object of the query
/// </summary>
/// <param name="constrg"></param>
/// <param name="cmdText"></param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, CommandType Type)
{
return ExecuteScalar(cmdText, null, Type);
}
#endregion
}
}
