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

Answer 2

Hi, I'm facing a similar issue while using ExecuteNonQuery with a user defined table type as input parameter. When ADO.Net is used directly, it is working fine, the code for which is given below.

public void SetTest_DB() { string connString = ConfigurationManager.ConnectionStrings["Connection String"].ConnectionString; SqlConnection conn = new SqlConnection(connString);

        try
        {              

            conn.Open();  
            SqlCommand cmd      = new SqlCommand("USP_Emp_Save", conn);
            cmd.CommandType     = CommandType.StoredProcedure;
            // Create a parameter using the new type
            SqlParameter param  = cmd.Parameters.Add("@p_employee", SqlDbType.Structured);
            param.Value         = CreateEmpTable(2, 2);
            cmd.ExecuteNonQuery();

        }
        catch (Exception)
        {
            throw;
        }
        finally
        {
            if (ConnectionState.Closed != conn.State)
                conn.Close();

            if (null != conn)
                conn.Dispose();
        }   
    }

We had a requirement to use Enterprise Library 4.1's data block. We had included all SP details like, SP Name, parameter names, Data types direction, size etc in an XML file. We had written a wrapper to fetch SP details from this XML and create connection and commands and keep them in cache. On each call the command is taken from Cache and then the value for each parameter is updated and send to DB by executing proper Execute method, say here, ExecuteNonQuery. I've not included the code here for this logic as we have restriction for that. Once this is executed, the following error occurs. "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Data type 0x62 (sql_variant) has an invalid type for type-specific metadata."

When I debugged, the DBCommand and Connection are same in both approach.

Any points to this would be very grateful.

Thanks in advance, Shyam SS