Use SqlReader to return multiple tables from a stored procedure

Question

I have a user-defined type that is data in a SqlServer database. I use Database, DbCommand, etc. to call stored procedures and return a Dataset. Datasets are convenient in that they can contain multiple tables.

Now I want to pass Datatable, 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 of the wrong type: "Invalid remote procedure incoming table data (TDS) protocol stream (RPC). Parameter 1 (\" @BASE_ITEMIDS \ "): Data type 0x62 (sql_variant) has an invalid type for type metadata. "

I saw this with SqlReader, but can sqlReader be used to return multiple tables? If my first table is empty, I do not see any rows in SqlReader.

Answer 1

Can sqlReader be used to return multiple tables?

Yes. You must read each table sequentially and call the .NextResult() method between the tables.

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