I need to modify the following code so that the number of rows are limited.
// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
User Id=UserID;Password=Password;");
// create the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_JOB_HISTORY.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;
// add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value = 101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =
ParameterDirection.Output;
// createt the DataAdapter from the command and use it to fill the
// DataSet
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);//Here is where I need to limit the rows
I know there is fill method which takes a maximum count.
public int Fill( DataSet dataSet, int startRecord, int maxRecords, string srcTable )
However, I don't know what should be passed to srcTable. My stored proc has one REF_CURSOR (OUT TYPES.REF_CURSOR).
Any help is much appreciated.
The srcTable
parameter is the name of the DataTable
in DataSet
object.
EDIT:
The DataSet
object automatically adds a table when you call Fill
if you have not explicitly created one. The Default name is "Table". I do not believe the DataSet
object cares about what type of data it is being filled with. It still creates the DataTable
.
Before you call Fill()
on your DataAdapter
. Add an empty table to the DataSet
with a name so you are able to access it during the Fill()
method:
ds.Tables.Add("myTableName");
Then call the proper overloaded Fill()
method like so:
da.Fill(ds, 1, 1000, "myTableName");
Or if you just use the default name of the table, or are unsure of the name of the table you created (doubtful):
da.Fill(ds, 1, 1000, ds.Tables[0].TableName);
Spcifically using your example it should look like this:
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
ds.Tables.Add();
da.Fill(ds, 1, maxRowCount, ds.Tables[0].TableName);//Here is where I need to limit the rows