Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
NextResult Method
Collapse the table of content
Expand the table of content

IDataReader.NextResult Method

Advances the data reader to the next result, when reading the results of batch SQL statements.

Namespace:  System.Data
Assembly:  System.Data (in System.Data.dll)

'Declaration
Function NextResult As Boolean

Return Value

Type: System.Boolean
true if there are more rows; otherwise, false.

Used to process multiple results, which can be obtained by executing batch SQL statements.

By default, the data reader is positioned on the first result.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Data.Odbc

Module Module1
   Dim connectionTypeName As String = "SqlClient" 
   Dim connectionString As String = "Data Source=(local);Initial Catalog=AdventureWorks2012;Integrated Security=SSPI" 

   ' Dim connectionTypeName As String = "Odbc" 
   ' Dim connectionString As String = "Driver={SQL Server Native Client 11.0};Server=(local);Database=AdventureWorks2012;Trusted_Connection=Yes" 

   ' Dim connectionTypeName As String = "OleDb" 
   ' Dim connectionString As String = "Provider=SQLNCLI11;Data Source=(local);Initial Catalog=AdventureWorks2012;Integrated Security=SSPI" 

   Sub Main()
      Using connection As IDbConnection = DatabaseConnectionFactory.GetConnection(connectionTypeName, connectionString)
         Dim command As IDbCommand = connection.CreateCommand()
         command.Connection = connection

         'these 2 queries are executed as a single batch and return 2 separate results
         command.CommandText =
             "SELECT CountryRegionCode, Name FROM Person.CountryRegion;" +
             "SELECT CountryRegionCode, StateProvinceCode, Name, StateProvinceID FROM Person.StateProvince;"

         connection.Open()

         Using reader As IDataReader = command.ExecuteReader()
            'process the first result
            displayCountryRegions(reader)

            'use NextResult to move to the second result and verify it is returned 
            If Not reader.NextResult() Then 
               Throw New InvalidOperationException("Expected second result (StateProvinces) but only one was returned")
            End If 

            'process the second result
            displayStateProvinces(reader)

            reader.Close()
         End Using

         connection.Close()
      End Using 
   End Sub 

   Sub displayCountryRegions(reader As IDataReader)
      If Not reader.FieldCount = 2 Then 
         Throw New InvalidOperationException("First resultset (CountryRegions) must contain exactly 2 columns")
      End If 

      While reader.Read()
         Console.WriteLine("CountryRegionCode={0}, Name={1}" _
             , reader.GetString(reader.GetOrdinal("CountryRegionCode")) _
             , reader.GetString(reader.GetOrdinal("Name")))
      End While 
   End Sub 

   Sub displayStateProvinces(reader As IDataReader)

      If Not reader.FieldCount = 4 Then 
         Throw New InvalidOperationException("Second resultset (StateProvinces) must contain exactly 4 columns")
      End If 

      While reader.Read()
         Console.WriteLine("CountryRegionCode={0}, StateProvinceCode={1}, Name={2}, StateProvinceID={3}" _
             , reader.GetString(reader.GetOrdinal("CountryRegionCode")) _
             , reader.GetString(reader.GetOrdinal("StateProvinceCode")) _
             , reader.GetString(reader.GetOrdinal("Name")) _
             , reader.GetInt32(reader.GetOrdinal("StateProvinceID")))
      End While 
   End Sub 

   Class DatabaseConnectionFactory
      Shared Function GetSqlConnection(connectionString As String) As SqlConnection
         Return New SqlConnection(connectionString)
      End Function 

      Shared Function GetOdbcConnection(connectionString As String) As OdbcConnection
         Return New OdbcConnection(connectionString)
      End Function 

      Shared Function GetOleDbConnection(connectionString As String) As OleDbConnection
         Return New OleDbConnection(connectionString)
      End Function 

      Public Shared Function GetConnection(connectionTypeName As String, connectionString As String) As IDbConnection

         Select Case connectionTypeName
            Case "SqlClient" 
               Return GetSqlConnection(connectionString)
            Case "Odbc" 
               Return GetOdbcConnection(connectionString)
            Case "OleDb" 
               Return GetOleDbConnection(connectionString)
            Case Else 
               Throw New ArgumentException("Value must be SqlClient, Odbc or OleDb", "connectionTypeName")
         End Select 

      End Function 
   End Class 
End Module

.NET Framework

Supported in: 4.6, 4.5, 4, 3.5, 3.0, 2.0, 1.1

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

XNA Framework

Supported in: 3.0, 2.0, 1.0
Show:
© 2015 Microsoft