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); }