CODEWITHSUNDEEP
c#sqldatareader
HarshSharma
HarshSharma

Reputation: 660

SQLDataReader is not reading the second table returned by stored procedure

I am using the below code to read and populate two C# objects using datareader. But I am unable to read the second table.

using (var myConnection = new SqlConnection(ConnectionString))
{
     var sqlCommand = "usp_GetFileListforPurging";
     var cmd = new SqlCommand(sqlCommand, myConnection) { CommandType = CommandType.StoredProcedure };
     cmd.CommandTimeout = Timeout == 0 ? 30 : Timeout * 30;
     myConnection.Open();
     using (var reader = cmd.ExecuteReader())
     {
          _tableAllSet.Load(reader); //read's the first table
          reader.NextResult(); //But this is returning false, although my SP is returning two tables
          _tableTrueSet.Load(reader);
     }
     myConnection.Close();
}

Below is the snip of data returned by SP

enter image description here

Upvotes: 1

Views: 260

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1062865

DataTable.Load already progresses the reader, at the end - essentially:

        if (!reader.IsClosed && !reader.NextResult())
        {
            reader.Close();
        }

(citation from reference source)

So: don't call NextResult yourself when using Load, as that will cause the second grid to be skipped.

Upvotes: 2

Related Questions