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 = DatabaseUtilityForSearch.OpenConnection();
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();
DatabaseUtilityForSearch.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 = DatabaseUtilityForSearch.OpenConnection();
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();
DatabaseUtilityForSearch.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 = DatabaseUtilityForSearch.OpenConnection();
command.CommandTimeout = 0;
adapter.SelectCommand = command;
adapter.Fill(dataSet);
DatabaseUtilityForSearch.closeConnection(command.Connection);
return(dataSet);
}
public static int PopulateData(string storedProcedureName, Dictionary <string, object> inputParameter, Dictionary <string, object> outputParameter, Dictionary <string, object> outputParameterValues)
{
OracleCommand command = new OracleCommand();
command.CommandText = storedProcedureName;
command.CommandType = CommandType.StoredProcedure;
command.Connection = DatabaseUtilityForSearch.OpenConnection();
command.CommandTimeout = 0;
foreach (KeyValuePair <string, object> items in inputParameter)
{
OracleParameter parameter = new OracleParameter(items.Key, items.Value);
parameter.Direction = ParameterDirection.Input;
command.Parameters.Add(parameter);
}
foreach (KeyValuePair <string, object> items in outputParameter)
{
ArrayList parameterItem = (ArrayList)items.Value;
OracleParameter parameter = new OracleParameter(items.Key, (OracleDbType)parameterItem[0], (int)parameterItem[1]);
parameter.Direction = ParameterDirection.Output;
command.Parameters.Add(parameter);
}
int noOfRecord = command.ExecuteNonQuery();
DatabaseUtilityForSearch.closeConnection(command.Connection);
foreach (OracleParameter parameter in command.Parameters)
{
if (parameter.Direction == ParameterDirection.Output)
{
outputParameterValues.Add(parameter.ParameterName, parameter.Value);
}
}
return(noOfRecord);
}
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 = DatabaseUtilityForSearch.OpenConnection();
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);
DatabaseUtilityForSearch.closeConnection(command.Connection);
return(dataTable);
}