3

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.

Evan L
  • 3,805
  • 1
  • 22
  • 31
Jimmy
  • 3,224
  • 5
  • 29
  • 47

1 Answers1

3

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
Evan L
  • 3,805
  • 1
  • 22
  • 31
  • As you can see from the code snippet, there is no tables added in the dataset. The output is a cursor. – Jimmy Feb 21 '14 at 15:02
  • Populating a `DataSet` with any data automatcially creates and adds a `DataTable`. Step past your `Fill` then look at the `Tables` property on the set in you debugger. Because you didn't delcare a table explicitly, you can use the `ds.Tables[0].TableName` example. – Evan L Feb 21 '14 at 15:06
  • Why should not the code throw? You just created a dataset and at that time there is no tables, still you are trying to pass the index 0 to a function? – Jimmy Feb 21 '14 at 15:45
  • Like I said, 0 is the index of the table that the *DataSet automatically creates when you call fill*. Test it and see. – Evan L Feb 21 '14 at 15:47
  • No you didn't. After fill, the dataset is populated, I got that. But in the example you are providing an empty dataset and so you are providing a null reference to the fill function. It wont work. – Jimmy Feb 21 '14 at 15:54
  • I see what you are saying, sorry. Why can't you just add a table to the DataSet before you fill it since it is *just going to add one anyway*. I think you are making this more complicated than it needs to be. – Evan L Feb 21 '14 at 15:57
  • 1
    That brings us to the original question. When a cursor is the output parameter, what is the name of the Table in the dataset? I debugged and found it is "Table". So in case of a single cursor it has to be da.Fill(ds, 1, 1000, "Table"); You mentioned this in your answer, but as an example of Table name. If you edit the answer to reflect our findings, I can mark this as answer. – Jimmy Feb 21 '14 at 17:03