Can the Sequence of RecordSets in a Multiple RecordSet ADO.Net resultset be determined, controlled?

Question

I am using code similar to this Support / KB article to return multiple recordsets to my C# program.

But I don't want C# code to be dependant on the physical sequence of the recordsets returned, in order to do it's job.

So my question is, "Is there a way to determine which set of records from a multiplerecordset resultset am I currently processing?"

I know I could probably decipher this indirectly by looking for a unique column name or something per resultset, but I think/hope there is a better way.

P.S. I am using Visual Studio 2008 Pro & SQL Server 2008 Express Edition.

Answer 1

Because you're explicitly stating in which order to execute the SQL statements the results will appear in that same order. In any case if you want to programmatically determine which recordset you're processing you still have to identify some columns in the result.

Answer 2

No, because the SqlDataReader is forward only. As far as I know, the best you can do is open the reader with KeyInfo and inspect the schema data table created with the reader's GetSchemaTable method (or just inspect the fields, which is easier, but less reliable).

I spent a couple of days on this. I ended up just living with the physical order dependency. I heavily commented both the code method and the stored procedure with !!!IMPORTANT!!!, and included an #If...#End If to output the result sets when needed to validate the stored procedure output.

The following code snippet may help you.

Helpful Code

        Dim fContainsNextResult As Boolean
        Dim oReader As DbDataReader = Nothing

        oReader = Me.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.KeyInfo)

#If DEBUG_ignore Then

        'load method of data table internally advances to the next result set
        'therefore, must check to see if reader is closed instead of calling next result

        Do
            Dim oTable As New DataTable("Table")
            oTable.Load(oReader)
            oTable.WriteXml("C:\" + Environment.TickCount.ToString + ".xml")
            oTable.Dispose()
        Loop While oReader.IsClosed = False

        'must re-open the connection
        Me.SelectCommand.Connection.Open()

        'reload data reader
        oReader = Me.SelectCommand.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.KeyInfo)

#End If

       Do

            Dim oSchemaTable As DataTable = oReader.GetSchemaTable

            '!!!IMPORTANT!!! PopulateTable expects the result sets in a specific order
            '   Therefore, if you suddenly start getting exceptions that only a novice would make
            '   the stored procedure has been changed!

            PopulateTable(oReader, oDatabaseTable, _includeHiddenFields)

            fContainsNextResult = oReader.NextResult

        Loop While fContainsNextResult