Trying to load second result set from a stored procedure

Question

I have a customer setup that uses stored procedures to return data from its SQL Server database.

Those stored procedures are all built the same way - they take a bunch of input parameters, and they return:

• the first is just a single row, single column with the result code (type INT) - 0 for success, some other values otherwise; if the value is 0, then there's a second result set that contains the actual data

• the second result set can be anything - any number of columns and rows

I'm trying to create a "generic" way to interface with this system, and my attempt is this:

• create a class that takes stored procedure name and input parameters
• a return type that contains both an ErrorCode INT property as well as a DataTable results property

However, I'm having trouble getting this to work with ADO.NET and SQL Server 2008 R2.

My code boils down to this:

public MyResultType CallService(string procedureName, MyParameters parameters)
{
     MyResultType result = new MyResultType { ErrorCode = 0 };

     using (SqlConnection conn = new SqlConnection(_connectionString))
     using (SqlCommand cmd = new SqlCommand(procedureName, conn))
     {
         cmd.CommandType = CommandType.StoredProcedure;
         SetupParameters(cmd, parameters);

         // open connection, execute the stored procedure
         conn.Open();

         using (SqlDataReader rdr = cmd.ExecuteReader())
         {
             // get the first result set - the status code, one row, one column of type INT
             while (rdr.Read())
             {
                 result.ErrorCode = rdr.GetInt32(0);
             }

             // if we got a "0" (success) -> go to the next result set and load it into the table
             if(result.ErrorCode == 0 && rdr.NextResult())
             {
                 result.ResultTable = new DataTable();
                 result.ResultTable.Load(rdr);

                 int colCount = result.ResultTable.Columns.Count;
                 int rowCount = result.ResultTable.Rows.Count;
              }

              rdr.Close();
         }

         conn.Close();
   }

   return result;
}

My issue is: the call to the stored procedure works just fine, the error code = 0 is picked up just fine, the data table is created and the number of columns is the expected value - but there are NO ROWS loaded...

I've been trying everything I can think of to get those rows into the DataTable - no luck. And of course - if I execute this very same stored procedure in SQL Server Management Studio, everything works just fine, I get my ErrorCode=0 and my result set of 18 columns and 5 rows - no problem....

What am I missing? Can anyone spot the problem in my code? Why aren't the rows of the second result set loaded (but the columns are being detected, it seems)?

Answer 1

The code as published works fine - there was a difference between how I call it from C# and in SQL Server Management Studio : NULL handling.

I had some input parameters set to int and thus provided a 0 value to the stored procedure, while it really expected to get a NULL if the value isn't defined......

Stupid rookie mistake..... SORRY GUYS! And thanks for your inputs!

Answer 2

The DataTable.Load method implicitly calls the NextResult method, so combining that with your explicit call, it is advancing to a resultset that isn't there. You should remove your own call to NextResult if you want to use DataTable.Load or loop through and fill a datatable yourself.