vb-database

Visual Basic Database Patterns

ADO.NET and Entity Framework patterns for VB.NET with focus on connection management, parameterized queries, and async operations.

ADO.NET Patterns

Connection Management

' ✅ Good: Using statement ensures disposal
Public Async Function GetCustomersAsync() As Task(Of List(Of Customer))
    Dim customers = New List(Of Customer)()

    Using connection = New SqlConnection(connectionString)
        Await connection.OpenAsync()

        Using command = New SqlCommand("SELECT * FROM Customers WHERE IsActive = @isActive", connection)
            command.Parameters.AddWithValue("@isActive", True)

            Using reader = Await command.ExecuteReaderAsync()
                While Await reader.ReadAsync()
                    customers.Add(New Customer With {
                        .Id = reader.GetInt32(0),
                        .Name = reader.GetString(1),
                        .Email = reader.GetString(2)
                    })
                End While
            End Using
        End Using
    End Using

    Return customers
End Function

Parameterized Queries (Critical for SQL Injection Prevention)

' ✅ Good: Parameterized query
Public Async Function FindCustomerAsync(email As String) As Task(Of Customer)
    Using connection = New SqlConnection(connectionString)
        Await connection.OpenAsync()

        Dim sql = "SELECT * FROM Customers WHERE Email = @email"
        Using command = New SqlCommand(sql, connection)
            command.Parameters.Add("@email", SqlDbType.NVarChar, 255).Value = email

            Using reader = Await command.ExecuteReaderAsync()
                If Await reader.ReadAsync() Then
                    Return MapCustomer(reader)
                End If
            End Using
        End Using
    End Using

    Return Nothing
End Function

' ❌ BAD: String concatenation (SQL injection risk!)
Dim sql = $"SELECT * FROM Customers WHERE Email = '{email}'"  ' NEVER DO THIS!

Transaction Management