Net-informations.com
SiteMap  | About    

How to Multiple Result Sets in ADO.NET

The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. When the ExecuteReader method in SqlCommand Object execute , it instantiate a SqlClient.SqlDataReader Object.

Dim sqlReader As SqlDataReader = sqlCmd.ExecuteReader()

In some situations we need to pass multiple SQL statements to the Command Object. In this situations the SqlDataReader returns multiple ResultSets also. For retrieveing multiple ResultSets from SqlDataReader we use the NextResult() method of the SqlDataReader.

SqlDataReader.NextResult()

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




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

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"

You have to replace the string with your realtime variables.





net-informations.com (C) 2016    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.