Counting Rows in Multiple Resultsets in ADO.NET

The problem is, the developer wanted to capture the number of rows affected by the action commands in an SQL batch that included both SELECT and DML statements. It turns out that the SqlDataReader returns a RecordsAffected value but it’s less than useful as it dutifully (but inaccurately) sums up the record affected values each time it’s called. Let’s look a bit deeper.

So, imaging this SQL Server Management Studio query (executed against the NorthWind sample database).

Notice how the row(s) affected values are returned as “Messages”—one for each resultset. Note that a “resultset” can contain 0 or 1 rowsets. An UPDATE or other DML operation also returns a resultset but with no rowset—just a rows affected value. The trick in ADO.NET is to capture these values as they are processed by the DataReader logic behind the scenes.

Okay, one would think that you could use the SqlDataReader object’s RecordsAffected method to capture this information as one called the NextResult method to step from one resultset to another. Well, think again. As far as I’m concerned the RecordsAffected method is less than useful as it seems to aggregate some (but not all) of the operations.

For example, when I ran my test program I got the following results:

The ListBox shown above tracks the records affected values captured during execution of the program by two means:

1. DataReader RecordsAffected method counts
2. RecordsAffected as returned by the SqlCommand.StatementCompleted event (CMD Statement entries).

As you can see, the first count reflects the 91 rows copied from the Customers table to the #Customers (temp) table.

The second count is from the StatementCompleted event. Both counters returned the same values. Great.

The third count is from the StatementCompleted event. It shows that 32 rows were returned from the second SELECT in the batch. No, the DataReader RecordsAffected count missed this count entirely for some reason.

The fourth and fifth entries are from the StatementCompleted event that shows the (correct) record  counts from the DELETE and UPDATE DML operations.

The seventh entry is from the DataReader RecordsAffected counter. It seems to be a sum of the initial 91 rows in the first SELECT INTO plus the two values returned from the DML commands—not particularly useful. I wonder if this method is used in Congress to count the number of hours spent with lobbyists?

The eighth entry from the StatementCompleted event correctly shows that 2 rows were returned by the final SELECT. Again, these rows were not included in the value returned by the DataReader RecordsAffected count for some reason.

The trick here? If you need intermediate counts as you step through the resultsets returned by a DataReader you need to hook up the SqlCommand.StatementCompleted event. This is very easy in VB.NET. Just declare the SqlCommand object WithEvents as shown in the code below.

Incidentally, in the process of building this application I discovered that the DataTable Load method closes the DataReader after having fetched the pending rowset. It would be useful if developers had the option of loading a DataTable from a DataReader one resultset at a time.

Code follows:

Imports System.Data.SqlClient
Imports System.Text

Public Class Form1
    Dim cn As SqlConnection
    Dim WithEvents cmd As SqlCommand
    Dim ds As New DataSet, i As Integer
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dr As SqlDataReader
        Try
            cn = New SqlConnection("server=demoserver;initial catalog=northwind2008;integrated security=sspi;")
            cn.Open()
            Dim strSQL As New StringBuilder
            With strSQL
                .AppendLine("SET NOCOUNT OFF")
                .AppendLine("SELECT * INTO #Customers FROM Customers")      ' Save entire table
                ' Returns a rowset
                .AppendLine("SELECT CustomerID, CompanyName, City FROM #Customers WHERE NOT Region IS NULL")
                ' Returns a rows affected value
                .AppendLine("DELETE #Customers WHERE  City = 'San Francisco'")
                ' Returns a rows affected value
                .AppendLine("UPDATE #Customers SET City = 'Dunno' WHERE CompanyName LIKE 'Island%'")
                ' Returns a rowset
                .AppendLine("SELECT * FROM #Customers WHERE City IN ('San Francisco', 'Dunno', 'Berlin')")
                .AppendLine("DROP TABLE #Customers")
            End With
            cmd = New SqlCommand(strSQL.ToString, cn)
            ' Execute query
            dr = cmd.ExecuteReader(CommandBehavior.Default)
            ' Fetch each resultset
            Do
                ListBox1.Items.Add(String.Format("DataReader RecordsAffected:{0}", dr.RecordsAffected))
                ' Store any rows returned in the resultset
                If dr.HasRows Then
                    Dim dt As New DataTable
                    dt = GetRowsFromDataReader(dr)
                    ds.Tables.Add(dt)
                End If
                ' Capture records affected
                i += 1
            Loop While dr.NextResult = True  ' Get next resultset
            ListBox1.Items.Add(String.Format("Final DataReader RecordsAffected:{0}", dr.RecordsAffected))

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            cn.Close()
            DataGridView1.DataSource = ds.Tables(0)
            DataGridView2.DataSource = ds.Tables(1)
        End Try

    End Sub
    Private Function GetRowsFromDataReader(ByRef drin As SqlDataReader) As DataTable
        Dim dtSchema As DataTable = drin.GetSchemaTable()
        Dim dt As DataTable = New DataTable(), drRow As DataRow
        Dim col As DataColumn
        Dim lCols As List(Of DataColumn) = New List(Of DataColumn)()
        ' Populate the DataRow Columns collection
        If Not dtSchema Is Nothing Then
            For Each drow As DataRow In dtSchema.Rows
                col = New DataColumn(drow("ColumnName").ToString, _
                                      CType(drow("DataType"), Type))
                col.Unique = CBool(drow("IsUnique"))
                col.AllowDBNull = CBool(drow("AllowDBNull"))
                col.AutoIncrement = CBool(drow("IsAutoIncrement"))
                lCols.Add(col)
                dt.Columns.Add(col)
            Next drow
        End If
        ' Extract the rowset and save to the DataTable row-by-row
        Do While drin.Read()
            drRow = dt.NewRow()
            For i As Integer = 0 To lCols.Count - 1
                drRow((CType(lCols(i), DataColumn))) = drin(i)
            Next i
            dt.Rows.Add(drRow)
        Loop
        Return dt
    End Function

    Private Sub cmd_StatementCompleted(ByVal sender As Object, _
        ByVal e As System.Data.StatementCompletedEventArgs) Handles cmd.StatementCompleted
        ListBox1.Items.Add(String.Format("CMD Statement record count:{0}", e.RecordCount))
    End Sub
End Class

hth