Accessing Data in Visual Basic .NET

Overview

Visual Basic .NET provides a rich set of classes in the System.Data.SqlClient namespace for interacting with Microsoft SQL Server databases. This article walks through the most common tasks:

• Opening a connection
• Executing SQL statements
• Reading results
• Filling a DataSet for disconnected access

Connecting to a Database

Use SqlConnection to establish a connection. Always wrap the connection in a Using block so it is closed automatically.

Imports System.Data.SqlClient

Dim connectionString As String = _
    "Data Source=SERVERNAME;Initial Catalog=MyDatabase;Integrated Security=True"

Using connection As New SqlConnection(connectionString)
    connection.Open()
    ' Perform database operations here.
End Using

Executing Commands

Use SqlCommand for INSERT, UPDATE, DELETE, or SELECT statements. Parameterized queries protect against SQL injection.

Dim query As String = "INSERT INTO Employees (FirstName, LastName) VALUES (@First, @Last)"
Using command As New SqlCommand(query, connection)
    command.Parameters.AddWithValue("@First", firstName)
    command.Parameters.AddWithValue("@Last", lastName)
    Dim rowsAffected As Integer = command.ExecuteNonQuery()
End Using

Reading Data with SqlDataReader

SqlDataReader provides fast, forward‑only access to query results.

Dim selectSql As String = "SELECT EmployeeID, FirstName, LastName FROM Employees"
Using command As New SqlCommand(selectSql, connection)
    Using reader As SqlDataReader = command.ExecuteReader()
        While reader.Read()
            Console.WriteLine($"{reader("EmployeeID")}: {reader("FirstName")} {reader("LastName")}")
        End While
    End Using
End Using

Using SqlDataAdapter & DataSet

The SqlDataAdapter fills a DataSet, allowing you to work with data offline and later push changes back to the database.

Dim adapter As New SqlDataAdapter("SELECT * FROM Employees", connection)
Dim ds As New DataSet()
adapter.Fill(ds, "Employees")

' Modify the first row
Dim row As DataRow = ds.Tables("Employees").Rows(0)
row("LastName") = "UpdatedLastName"

Dim builder As New SqlCommandBuilder(adapter)
adapter.Update(ds, "Employees")

Complete Example

The snippet below combines the concepts above into a single, functional program.

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module AccessDataExample
    Sub Main()
        Dim cs As String = "Data Source=SERVERNAME;Initial Catalog=MyDatabase;Integrated Security=True"

        Using conn As New SqlConnection(cs)
            conn.Open()

            ' Insert a new employee
            Dim insertSql As String = "INSERT INTO Employees (FirstName, LastName) VALUES (@F,@L)"
            Using cmd As New SqlCommand(insertSql, conn)
                cmd.Parameters.AddWithValue("@F", "John")
                cmd.Parameters.AddWithValue("@L", "Doe")
                cmd.ExecuteNonQuery()
            End Using

            ' Retrieve and display all employees
            Dim selectSql As String = "SELECT EmployeeID, FirstName, LastName FROM Employees"
            Using cmd As New SqlCommand(selectSql, conn)
                Using rdr As SqlDataReader = cmd.ExecuteReader()
                    Console.WriteLine("Employees:")
                    While rdr.Read()
                        Console.WriteLine($"{rdr("EmployeeID")}: {rdr("FirstName")} {rdr("LastName")}")
                    End While
                End Using
            End Using

            ' Update using DataAdapter
            Dim da As New SqlDataAdapter("SELECT * FROM Employees", conn)
            Dim cb As New SqlCommandBuilder(da)
            Dim ds As New DataSet()
            da.Fill(ds, "Employees")

            ' Change the last name of the first row
            If ds.Tables("Employees").Rows.Count > 0 Then
                ds.Tables("Employees").Rows(0)("LastName") = "Smith"
                da.Update(ds, "Employees")
            End If

            Console.WriteLine("Update completed.")
        End Using
    End Sub
End Module