public static List <USER_ARGUMENTS> SelectUserArgs(string packname, string procname)
{
OracleParameter param;
OracleConnection conn = DBConnection.Instance.GetConnection();
OracleCommand command = conn.CreateCommand();
command.CommandText = ORALCE_PACKAGE_SYSTEM + "." + ORALCE_PRS_USERARGS;
command.CommandType = CommandType.StoredProcedure;
//Log.Instance.writeLog("Line 178-Connection State:" + command.Connection.State.ToString());
param = new OracleParameter();
param.ParameterName = ORALCE_PRS_USERARGS_ARG1;
param.OracleDbType = OracleDbType.NVarchar2;
param.Value = packname;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);
param = new OracleParameter();
param.ParameterName = ORALCE_PRS_USERARGS_ARG2;
param.OracleDbType = OracleDbType.NVarchar2;
param.Value = procname;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);
param = new OracleParameter();
param.ParameterName = ORALCE_PRS_USERARGS_ARG3;
param.OracleDbType = OracleDbType.RefCursor;
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);
return(DalUtility.LoadObjectListFromDatabase <USER_ARGUMENTS>(command));
}
public static DynamicEntityList ExcuteSelectMultiDynamicObject(string packname, string procname, Object parameter)
{
List <USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);
OracleConnection conn = DBConnection.Instance.GetConnection();
if (conn.State != ConnectionState.Open)
{
return(new DynamicEntityList());
}
OracleCommand command = conn.CreateCommand();
if (!string.IsNullOrEmpty(packname))
{
command.CommandText = packname + "." + procname;
}
else
{
command.CommandText = procname;
}
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);
return(Dal4DynamicObject.LoadObjectListFromDatabase(command));
}
public static T ExcuteSelectObject <T>(string packname, string procname, Object parameter) where T : new()
{
try
{
List <USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);
OracleConnection conn = DBConnection.Instance.GetConnection();
OracleCommand command = conn.CreateCommand();
if (!string.IsNullOrEmpty(packname))
{
command.CommandText = packname + "." + procname;
}
else
{
command.CommandText = procname;
}
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);
return(DalUtility.LoadObjectFromDatabase <T>(command));
}
catch (Exception ex)
{
DBConnection.Instance.Close();
Util.Log.Instance.WriteExceptionLog(ex, "ExcuteSelectObject");
return(new T());
}
}
/// <summary>
/// Just for report
/// </summary>
/// <param name="packname"></param>
/// <param name="procname"></param>
/// <param name="parameter"></param>
/// <returns></returns>
public static DataSet ExcuteSelectDataSet(string packname, string procname, Object parameter)
{
try
{
List <USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);
OracleConnection conn = DBConnection.Instance.GetConnection();
if (conn.State != ConnectionState.Open)
{
return(new DataSet());
}
OracleCommand command = conn.CreateCommand();
if (!string.IsNullOrEmpty(packname))
{
command.CommandText = packname + "." + procname;
}
else
{
command.CommandText = procname;
}
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);
DataSet dsReturn = new DataSet();
OracleDataAdapter adap = new OracleDataAdapter(command);
adap.Fill(dsReturn);
List <USER_ARGUMENTS> lstRefCur = lstUserArg.FindAll(obj => obj.DATA_TYPE == DalUtility.ORACLE_CURSOR_TYPE);
for (int idx = 0; idx < lstRefCur.Count; idx++)
{
dsReturn.Tables[idx].TableName = lstRefCur[idx].ARGUMENT_NAME;
}
return(dsReturn);
}
catch (Exception ex)
{ throw ex; }
}
public static bool ExcuteNonQuery(string packname, string procname, Object parameter)
{
try
{
int resutl;
List <USER_ARGUMENTS> lstUserArg = DalUtility.SelectUserArgs(packname, procname);
OracleConnection conn = DBConnection.Instance.GetConnection();
OracleCommand command = conn.CreateCommand();
if (!string.IsNullOrEmpty(packname))
{
command.CommandText = packname + "." + procname;
}
else
{
command.CommandText = procname;
}
command.CommandType = CommandType.StoredProcedure;
command.BindByName = true;
DalUtility.LoadParametersFromObject(command, parameter, lstUserArg);
resutl = command.ExecuteNonQuery();
DalUtility.SetOutputValueToObject(command, parameter);
return(true);
}
catch (Exception ex)
{
DBConnection.Instance.Close();
Util.Log.Instance.WriteExceptionLog(ex, "ExcuteNonQuery");
return(false);
}
}