public static DataTable SelectDataWithInputParameters(string storedProcedureName, Dictionary <string, object> inputParameter)
{
DataSet dataSet = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommand command = new OracleCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
command.Connection = DatabaseUtility.OpenConnectionDR();
adapter.SelectCommand = command;
foreach (KeyValuePair <string, object> items in inputParameter)
{
OracleParameter parameter = new OracleParameter(items.Key, items.Value == null ? DBNull.Value : items.Value);
// parameter.OracleDbType = OracleDbType.Varchar2;
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
}
OracleParameter parameter1 = new OracleParameter();
parameter1.OracleDbType = OracleDbType.RefCursor;
parameter1.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter1);
command.CommandTimeout = 0;
adapter.Fill(dataSet);
//SqlDataReader dataReader = command.ExecuteReader();
DatabaseUtility.closeConnection(command.Connection);
return(dataSet.Tables[0]); //dataReader;
}
//Fill Data With No Inputs
public static DataTable SelectData(string storedProcedureName)
{
DataSet dataSet = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommand command = new OracleCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
command.Connection = DatabaseUtility.OpenConnectionDR();
adapter.SelectCommand = command;
OracleParameter parameter = new OracleParameter();
parameter.OracleDbType = OracleDbType.RefCursor;
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
command.CommandTimeout = 0;
adapter.Fill(dataSet);
//SqlDataReader dataReader = command.ExecuteReader();
DatabaseUtility.closeConnection(command.Connection);
return(dataSet.Tables[0]); //dataReader;
}
public static DataSet SelectDataReturningDataSetNoInputs(string storedProcedureName)
{
DataSet dataSet = new DataSet();
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommand command = new OracleCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
command.Connection = DatabaseUtility.OpenConnectionDR();
command.CommandTimeout = 0;
adapter.SelectCommand = command;
adapter.Fill(dataSet);
DatabaseUtility.closeConnection(command.Connection);
return(dataSet);
}
public static DataTable SelectData(DataTable dataTable, string storedProcedureName, Dictionary <string, object> inputParameter)
{
OracleDataAdapter adapter = new OracleDataAdapter();
OracleCommand command = new OracleCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcedureName;
command.Connection = DatabaseUtility.OpenConnectionDR();
command.CommandTimeout = 0;
adapter.SelectCommand = command;
foreach (KeyValuePair <string, object> items in inputParameter)
{
OracleParameter parameter = new OracleParameter(items.Key, items.Value == null ? DBNull.Value : items.Value);
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
}
adapter.Fill(dataTable);
DatabaseUtility.closeConnection(command.Connection);
return(dataTable);
}