Use SqlReader to get back multiple tables from a stored procedure

Question

I have a user defined type, which is a datatable in an SqlServer database. I have been using Database, DbCommand, etc to call stored procedures and get a Dataset back. Datasets are handy in that they can contain multiple tables.

Now I want to pass a Datatable in, so I tried:

string _strComText = "stored_procedure_name_changed_to_protect_the_innocent";
_objCom = _objDB.GetSqlStringCommand(_strComText);
_objDB.AddInParameter(_objCom, "@BASE_ITEMIDS", DbType.Object, dtItemIds);
_objCom.CommandType = CommandType.StoredProcedure;
dataset = _objDB.ExecuteDataSet(_objCom);

But I get an exception that "@BASE_ITEMIDS" is not the correct type: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 (\"@BASE_ITEMIDS\"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata."

I've seen this done with SqlReader, but can sqlReader be used to get back multiple tables? If my first table is empty, I don't see any rows in SqlReader.

Answer 1

can sqlReader be used to get back multiple tables?

Yes. You have to read each table in sequence and call the .NextResult() method between tables.

using (var rdr = MySqlCommand.ExecuteReader())
{
    do 
    {
       while (rdr.Read())
       {
           //do something with each record
       }
    } while(rdr.NextResult());
}