How to Multiple Result Sets in ADO.NET

The DataReader Object in ADO.NET provides a stream-based, forward-only, read-only retrieval of query results from the Data Source. It is specifically designed for retrieving data and does not allow for data modification operations.

When the ExecuteReader method is executed in the SqlCommand Object, it instantiates a SqlClient.SqlDataReader Object, which represents the result set obtained from the execution of the SQL statement.

Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()

In certain scenarios, there might be a need to execute multiple SQL statements and retrieve multiple result sets. In such cases, the SqlDataReader Object is capable of returning multiple ResultSets. This can occur when a batch of SQL statements is executed or when a stored procedure generates multiple result sets.

NextResult()

To handle these multiple ResultSets, the SqlDataReader provides a method called NextResult(). This method allows you to advance to the next ResultSet within the SqlDataReader Object. By calling NextResult(), you can move to the next set of results, if available.

SqlDataReader.NextResult()

Each time NextResult() is invoked, the SqlDataReader moves to the next ResultSet and makes it available for reading. This enables you to access the subsequent set of results, retrieve data from it, and perform any necessary operations. The NextResult() method returns a boolean value, indicating whether there are more ResultSets available or not.

In the following source code demonstrating how to get multiple result sets from SqlDataReader() .

Full Source VB.NET
Imports System.Data.SqlClient Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim connetionString As String Dim sqlCnn As SqlConnection Dim sqlCmd As SqlCommand Dim sql As String connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password" sql = "Select top 2 * from product; select top 2 * from ordermaster; select top 2 * from orderdetails" sqlCnn = New SqlConnection(connetionString) Try sqlCnn.Open() sqlCmd = New SqlCommand(sql, sqlCnn) Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader() While sqlReader.Read() MsgBox("From first SQL - " & sqlReader.Item(0) & " - " & sqlReader.Item(1)) End While sqlReader.NextResult() While sqlReader.Read() MsgBox("From second SQL - " & sqlReader.Item(0) & " - " & sqlReader.Item(1)) End While sqlReader.NextResult() While sqlReader.Read() MsgBox("From third SQL - " & sqlReader.Item(0) & " - " & sqlReader.Item(1)) End While sqlReader.Close() sqlCmd.Dispose() sqlCnn.Close() Catch ex As Exception MsgBox("Can not open connection ! ") End Try End Sub End Class

Conclusion

The SqlDataReader Object in ADO.NET provides a means of retrieving query results from a Data Source. It is capable of handling multiple ResultSets, and the NextResult() method allows you to move from one ResultSet to another. This functionality proves useful in scenarios where multiple SQL statements or stored procedures are executed, resulting in multiple sets of data to be retrieved and processed.






Related Topics
  1. ADO.NET Connection Object
  2. ADO.NET SQL Server Connection
  3. ADO.NET OLEDB Connection
  4. ADO.NET ODBC Connection
  1. ADO.NET Command
  2. ADO.NET ExecuteNonQuery in SqlCommand Object
  3. ADO.NET ExecuteNonQuery in OleDbCommand Object
  4. ADO.NET ExecuteScalar in SqlCommand Object
  5. ADO.NET ExecuteScalar in OleDbCommand Object
  6. ADO.NET ExecuteReader in SqlCommand Object
  7. ADO.NET ExecuteReader in OleDbCommand Object
  8. How to ADO.NET DataReader
  9. How to ADO.NET SqlDataReader
  10. How to ADO.NET OleDbDataReader
  11. Getting Schema Informations from SqlDataReader
  12. Getting Schema Informations from OleDbDataReader
  13. What is DataAdapter
  14. What is SqlDataAdapter
  15. What is OleDbDataAdapter
  16. Vb.NET ExecuteReader and ExecuteNonQuery

Related Links
  1. Stored Procedures in VB.NET
  2. Stored Procedure with Parameter