Visual Basic Code Snippet - Get Data From SqlDataReader using open connection

Visual Basic Code Snippet - Get Data From SqlDataReader using open connection

This .Net Visual Basic code snippet connect connects to SQL server and executes SQL statement and return SqlDataReader. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get data using SqlDataReader. Modify the exception handling section for your project requirements.

Public Function GetSqlDataReader(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As System.Data.SqlClient.SqlDataReader
    ' Set temporary variable to create data reader
    Dim _SqlDataReader As System.Data.SqlClient.SqlDataReader = Nothing

    ' lets run the given SQL statement and create SqlDataReader
    Try
        ' Pass the connection to a command object
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        ' get query results
        _SqlDataReader = _SqlCommand.ExecuteReader()
    Catch _Exception As Exception
        ' Error occurred while trying to execute reader
        ' send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message)

        ' failed SQL execution, lets return null
        Return Nothing
    End Try

    ' SQL successfully executed, lets return the SqlDataReader
    Return _SqlDataReader
End Function

Here is a simple example showing how to use above function (GetSqlDataReader) to connect to SQL database and get SqlDataReader for given SQL statement.

' set temporary variable for database connection
Dim _SqlConnection As New System.Data.SqlClient.SqlConnection()

' assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;"

' Connect to database
Try
    _SqlConnection.Open()
Catch _Exception As Exception
    ' Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message)
End Try
            
' Check for valid open database connection before query database
If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then
    ' Lets call above function to create a new SqlDataReader
    ' using open database connection and SQL statement
        ' Pass open database connection to function
        ' Pass SQL statement to create SqlDataReader
    Dim _SqlDataReader As System.Data.SqlClient.SqlDataReader = GetSqlDataReader(_SqlConnection, "SELECT NAME, PRICE, COST FROM INVENTORY")

    ' Check we have data
    If _SqlDataReader IsNot Nothing AndAlso _SqlDataReader.HasRows Then
        ' We have data in SqlDataReader, lets loop through and display the data
        Do While _SqlDataReader.Read()
            Console.WriteLine(_SqlDataReader("NAME").ToString())
        Loop

        ' Close SqlDataReader
        _SqlDataReader.Close()
        _SqlDataReader.Dispose()
    End If

    ' close database connection
    _SqlConnection.Close();
End If

VB Keywords Used:

• SqlDataReader
• SqlConnection
• ConnectionString
• SqlCommand
• ExecuteReader
• Exception
• Do While Loop
• Close
• Dispose
• IsNot
• Nothing
• AndAlso
• If End If

Code Snippet Information:

• Applies To: .Net, VB, Visual Basic, CLI, SqlDataReader, SQL, SQL Server, SQL Client, Connection String, Database Connection, SQL Data Reader
• Programming Language : VB (Visual Basic)

External Resources:

• SqlDataReader Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• SqlCommand.ExecuteReader Method
• Exception Class

Leave a comment