public class OracleProvider: IDisposable
{
#region Designer Variables
private OracleDataAdapter OrcDataAdapter;
#endregion[Designer Variable]
#region Constructor
public OracleProvider()
{
OracleConnector oDBConnection = new OracleConnector();
OrcDataAdapter = new OracleDataAdapter();
OrcDataAdapter.SelectCommand = new OracleCommand();
OrcDataAdapter.SelectCommand.Connection = new OracleConnection(OracleConnector.strDataBaseConnectionString);
OrcDataAdapter.SelectCommand.Connection.Open();
}
#endregion[Constructor]
#region Disposing
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(true); // as a service to those who might inherit from us
}
protected virtual void Dispose(bool disposing)
{
if (!disposing)
return;
if (OrcDataAdapter != null)
{
if (OrcDataAdapter.SelectCommand != null)
{
if (OrcDataAdapter.SelectCommand.Connection != null)
{
OrcDataAdapter.SelectCommand.Connection.Dispose();
}
OrcDataAdapter.SelectCommand.Dispose();
}
OrcDataAdapter.Dispose();
OrcDataAdapter = null;
}
}
#endregion[Disposing]
#region Initialization
private void Initialize()
{
OrcDataAdapter.SelectCommand.Parameters.Clear();
}
#endregion[Initialization]
#region Execute Select Function
#region === ===============================(Return Datatable Using Store - Procedure) === ====================================
protected DataSet ExecuteStoredProcedureDataTableObjectParameter(string strProcedureName, object[] parameters, out string Error_Message)
{
Error_Message = string.Empty;
DataSet dtResult = new DataSet();
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = System.Data.CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
dbCommand.Parameters.Add(parameters[i] as OracleParameter);
}
}
OrcDataAdapter.Fill(dtResult);
return dtResult;
}
catch(OracleException exception)
{
Error_Message = exception.Message;
return dtResult = null;
}
}
#endregion
#region === ===============================(Return Datatable Using Text - Command) === =======================================
protected DataTable ExecuteTextCommandDataTable(string strSql)
{
DataTable dtResult = new DataTable();
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.Text;
OrcDataAdapter.Fill(dtResult);
return dtResult;
}
catch(OracleException exception)
{
//System.Web.HttpContext.Current.Session["Error_Message"] = exception.Message;
return dtResult = null;
}
}
#endregion
#region === =============(Return OracleDataReader[
return - single - row - from - database] Using Store - Procedure) === ==============
protected OracleDataReader ExecuteStoreProcedureDataReaderObjectParameter(string strSql, object[] parameters)
{
OracleDataReader reDataReader = null;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
dbCommand.Parameters.Add(parameters[i] as OracleParameter);
}
}
reDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(OracleException exception)
{
//System.Web.HttpContext.Current.Session["Error_Message"] = exception.Message;
}
return reDataReader;
}
#endregion
#region === ==============(Return OracleDataReader[
return - single - row - from - database] Using Text - Command) === ================
protected OracleDataReader ExecuteTextCommandDataReader(string strSql)
{
OracleDataReader reDataReader = null;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.Text;
reDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch(OracleException exception)
{
//System.Web.HttpContext.Current.Session["Error_Message"] = exception.Message;
}
return reDataReader;
}
#endregion
#region === ===================(Return Object Type Single Value From Database Using Text - Command) === ======================
//Use the ExecuteScalar method to retrieve a single value from a database.
protected object ExecuteTextCommandSelectScalar(string strSql)
{
object objResult = null;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strSql;
dbCommand.CommandType = CommandType.Text;
objResult = dbCommand.ExecuteScalar();
return objResult;
}
catch(OracleException exception)
{
//System.Web.HttpContext.Current.Session["Error_Message"] = exception.Message;
return objResult;
}
}
#endregion
#endregion
#region Execute - Non - Query
#region === =======================(Execute Non Query Object - Parameter Using Store - Procedure) === ==========================
protected bool ExecuteNonQueryStoredProcedureObjectParameter(string strProcedureName, object[] parameters, out string Error_Message)
{
bool oResult = false;
Error_Message = string.Empty;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
for (int i = 0; i < parameters.Length; i++)
{
dbCommand.Parameters.Add(parameters[i] as OracleParameter);
}
}
dbCommand.ExecuteNonQuery();
#region Get Error Message
string _strErrorCode = dbCommand.Parameters["perrorcode"].Value.ToString();
if (_strErrorCode != "")
{
Int64 _intErrorCode = Convert.ToInt64(_strErrorCode);
if (_intErrorCode != 0)
{
Error_Message = dbCommand.Parameters["perrormsg"].Value.ToString();
}
if (_intErrorCode == 0)
{
Error_Message = string.Empty;
oResult = true;
}
}
#endregion
return oResult;
}
catch(OracleException SExp)
{
GenericParameter oCommon = new GenericParameter();
Error_Message = oCommon.Generate_Data_BaseError_Message(SExp.Message.ToString());
return oResult;
}
}
#endregion
#region === =======================(Execute Non Query Array - Parameter Using Store - Procedure) === ===========================
protected int ExecuteNonQueryStoredProcedureArrayParameter(string strProcedureName, ArrayList arlParams)
{
int intResult = 0;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = strProcedureName;
dbCommand.CommandType = CommandType.StoredProcedure;
if (arlParams != null)
{
for (int i = 0; i < arlParams.Count; i++)
{
dbCommand.Parameters.Add(arlParams[i]);
}
}
intResult = dbCommand.ExecuteNonQuery();
return intResult;
}
catch(OracleException exception)
{
//System.Web.HttpContext.Current.Session["Error_Message"] = exception.Message;
return intResult;
}
}
#endregion
#region === ================================(Execute Non Query Using Text - Command) === =====================================
protected int ExecuteNonQueryText(string sqlQuery)
{
int intResult = 0;
try
{
if (OrcDataAdapter == null)
{
throw new System.ObjectDisposedException(GetType().FullName);
}
Initialize();
OracleCommand dbCommand = OrcDataAdapter.SelectCommand;
dbCommand.CommandText = sqlQuery;
dbCommand.CommandType = CommandType.Text;
intResult = dbCommand.ExecuteNonQuery();
}
catch(OracleException OrException)
{
//CLSCommon oCommon = new CLSCommon();
//string _strErrorMessage = oCommon.Generate_Data_BaseError_Message(OrException.Message.ToString());
// System.Web.HttpContext.Current.Session["Error_Message"] = _strErrorMessage;
}
return intResult;
}
#endregion
#endregion
}
public class GenericParameter
{
public object CreateParameter(string name, object dataType, object parameterValue, int size, ParameterDirection direction)
{
OracleParameter parameter = new OracleParameter(name, (OracleDbType) dataType);
parameter.Value = parameterValue;
parameter.Direction = direction;
if (size >= 0)
{
parameter.Size = size;
}
return parameter;
}
public string Generate_Data_BaseError_Message(string _strErrorMessage)
{
_strErrorMessage = _strErrorMessage.Replace("\r", "");
_strErrorMessage = _strErrorMessage.Replace("\n", "");
_strErrorMessage = _strErrorMessage.Replace("/", "");
_strErrorMessage = _strErrorMessage.Replace("'", "");
return _strErrorMessage;
}
}
public class OracleConnector
{
#region Designer Variables
public static string strDataBaseConnectionString;
#endregion[Designer Variables]
#region Constructor
public OracleConnector()
{
if (strDataBaseConnectionString == null)
{
strDataBaseConnectionString = GetConnectionString("OracleConnectionString");
}
}
#endregion[Constructor]
#region Properties
private string _strDBConnectionString = strDataBaseConnectionString;
public string DBConnectionString
{
get {
return _strDBConnectionString;
}
set {
_strDBConnectionString = strDataBaseConnectionString;
}
}
#endregion[Properties]
#region Method
private string GetConnectionString(string value)
{
string conns = "";
try
{
ConnectionStringSettingsCollection cs = ConfigurationManager.ConnectionStrings;
foreach(ConnectionStringSettings c in cs)
{
if (c.Name.ToLower() == value.ToLower())
{
conns = c.ConnectionString;
}
}
if (conns == "")
{
NameValueCollection apps = ConfigurationManager.AppSettings;
for (int i = 0; i < apps.Count; i++)
{
if (apps.GetKey(i).ToString().ToLower() == value.ToLower())
{
conns = apps[i];
}
}
}
if (conns != "")
{
return conns;
}
else
{
return conns = "Invalid Connection String.";
}
}
catch(Exception exp)
{
return conns = exp.Message.ToString();
}
}
#endregion[Method]
}
public class UnitRepository: OracleProvider,
IUnitRepository
{
public bool Insert(UnitModel objSample, out string Error_Message)
{
GenericParameter oCommon = new GenericParameter();
OracleParameter[] parameters = new OracleParameter[5];
parameters[0] = oCommon.CreateParameter("punit_name", OracleDbType.NVarchar2, objSample.UnitName, 100, ParameterDirection.Input) as OracleParameter;
parameters[1] = oCommon.CreateParameter("penable_status", OracleDbType.Double, objSample.EnableStatus, 1, ParameterDirection.Input) as OracleParameter;
parameters[2] = oCommon.CreateParameter("pcreate_by", OracleDbType.NVarchar2, objSample.CreateBy, 50, ParameterDirection.Input) as OracleParameter;
parameters[3] = oCommon.CreateParameter("perrorcode", OracleDbType.Int32, DBNull.Value, 2, ParameterDirection.Output) as OracleParameter;
parameters[4] = oCommon.CreateParameter("perrormsg", OracleDbType.NVarchar2, DBNull.Value, 1000, ParameterDirection.Output) as OracleParameter;
string storeProcedure = "pkg_sebl_gbd_psims.fsp_sebl_gb_unit_insert";
bool SqlResult = this.ExecuteNonQueryStoredProcedureObjectParameter(storeProcedure, parameters, out Error_Message);
return SqlResult;
}
public bool UnitExists(string UnitName)
{
string sqlQuery = string.Format(@"select t.unit_name from Sebl_Psims_Unit_Info t where t.unit_name='{0}'", UnitName);
object oResult = this.ExecuteTextCommandSelectScalar(sqlQuery);
if (oResult != null)
return true;
else
return false;
}
public List < UnitModel > GetDetails()
{
string Error_Message = string.Empty;
List < UnitModel > objList = new List < UnitModel > ();
DataSet ds = new DataSet();
GenericParameter oCommon = new GenericParameter();
OracleParameter[] parameters = new OracleParameter[3];
parameters[0] = oCommon.CreateParameter("perrorcode", OracleDbType.Int32, DBNull.Value, 2, ParameterDirection.Output) as OracleParameter;
parameters[1] = oCommon.CreateParameter("perrormsg", OracleDbType.NVarchar2, DBNull.Value, 1000, ParameterDirection.Output) as OracleParameter;
parameters[2] = oCommon.CreateParameter("presult", OracleDbType.RefCursor, DBNull.Value, 2, ParameterDirection.Output) as OracleParameter;
string storeProcedure = "pkg_sebl_gbd_psims.fsp_sebl_gb_unit_get_all";
ds = this.ExecuteStoredProcedureDataTableObjectParameter(storeProcedure, parameters, out Error_Message);
DataTable dt = new DataTable();
dt = ds.Tables[0];
foreach(DataRow row in dt.Rows)
{
UnitModel objModel = new UnitModel();
objModel.UnitID = int.Parse(row["UnitID"].ToString());
objModel.UnitName = row["UnitName"].ToString();
objModel.CreateBy = row["CreateBy"].ToString();
objModel.CreateDate = DateTime.Parse(row["CreateDate"].ToString());
objList.Add(objModel);
}
return objList;
}
//Alternative Way To Convert DataTable To List (Do Not Delete)
//List<DataRow> list1 = dt.AsEnumerable().ToList();
//List<DataRow> list = new List<DataRow>(dt.Select());
//IEnumerable<DataRow> sequence = dt.AsEnumerable();
//PropertyName Must be Same with Return Type
//List<UnitModel> unitDetails = new List<UnitModel>();
//unitDetails = GenericMethod.DataTableToList<UnitModel>(dt);
public UnitModel GetUnitByID(string UnitID)
{
UnitModel objModel = new UnitModel();
string sqlQuery = string.Format(@"select t.unit_name,t.unit_id,t.create_by,t.enable_status from Sebl_Psims_Unit_Info t where t.unit_Id='{0}'", UnitID);
DataTable dt = this.ExecuteTextCommandDataTable(sqlQuery);
if (dt.Rows.Count > 0)
{
objModel.UnitID = int.Parse(dt.Rows[0]["unit_id"].ToString());
objModel.UnitName = dt.Rows[0]["unit_name"].ToString();
objModel.CreateBy = dt.Rows[0]["create_by"].ToString();
objModel.EnableStatus = int.Parse(dt.Rows[0]["enable_status"].ToString());
}
return objModel;
}
public bool Update(UnitModel objSample, out string Error_Message)
{
GenericParameter oCommon = new GenericParameter();
OracleParameter[] parameters = new OracleParameter[5];
parameters[0] = oCommon.CreateParameter("punit_id", OracleDbType.Int32, objSample.UnitID, 4, ParameterDirection.Input) as OracleParameter;
parameters[1] = oCommon.CreateParameter("punit_name", OracleDbType.NVarchar2, objSample.UnitName, 100, ParameterDirection.Input) as OracleParameter;
parameters[2] = oCommon.CreateParameter("pcreate_by", OracleDbType.NVarchar2, objSample.CreateBy, 50, ParameterDirection.Input) as OracleParameter;
parameters[3] = oCommon.CreateParameter("perrorcode", OracleDbType.Int32, DBNull.Value, 2, ParameterDirection.Output) as OracleParameter;
parameters[4] = oCommon.CreateParameter("perrormsg", OracleDbType.NVarchar2, DBNull.Value, 1000, ParameterDirection.Output) as OracleParameter;
string storeProcedure = "pkg_sebl_gbd_psims.fsp_sebl_gb_unit_update";
bool SqlResult = this.ExecuteNonQueryStoredProcedureObjectParameter(storeProcedure, parameters, out Error_Message);
return SqlResult;
}
public bool Delete(string Id)
{
string sqlQuery = string.Format(@"Update Sebl_Psims_Unit_Info Set enable_status=0 where unit_id='{0}'", Id);
int oResult = this.ExecuteNonQueryText(sqlQuery);
if (oResult == 1)
return true;
else return false;
}
}
|
Comments
Post a Comment