Visual Basic Code Snippet - Get DataTable using open connection

Visual Basic Code Snippet - Get DataTable using open connection

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

Public Function GetDataTable(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As DataTable
    ' Pass the connection to a command object
    Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)
    Dim _SqlDataAdapter As New System.Data.SqlClient.SqlDataAdapter()
    _SqlDataAdapter.SelectCommand = _SqlCommand

    Dim _DataTable As New DataTable()
    _DataTable.Locale = System.Globalization.CultureInfo.InvariantCulture

    ' Adds or refreshes rows in the DataSet to match those in the data source
    Try
        _SqlDataAdapter.Fill(_DataTable)
    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)

        Return Nothing
    End Try

    Return _DataTable
End Function

Here is a simple example showing how to use above function (GetDataTable) to connect to SQL database and get DataTable 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 _DataTable As DataTable = GetDataTable(_SqlConnection, "SELECT * FROM sampletable")

    ' Check we have data
    If _DataTable IsNot Nothing Then
        dataGridView1.DataSource = _DataTable
    End If

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

VB Keywords Used:

• DataTable
• SqlDataAdapter
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

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

External Resources:

• DataTable Class
• SqlDataAdapter Class
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Leave a comment