Architecting a Decoupled Data Access Layer in VB.NET with ADO.NET

The Persistence Coupling Problem

Many legacy Visual Basic .NET applications embed SQL queries directly within UI event handlers or business logic methods. This creates a tight coupling where a simple database schema change requires modifying code across the entire application, increasing the risk of regression and making unit testing impossible without a live database connection.

The goal is to isolate persistence logic into a dedicated Data Access Layer (DAL). By using a Repository pattern, the business layer interacts with objects (POCOs) rather than database rows, ensuring that the application remains maintainable as the schema evolves.

The Minimalist DAL Design

The smallest suitable design for a VB.NET DAL consists of three components: Plain Old CLR Objects (POCOs) to hold data, Interface definitions to define data operations, and Concrete Implementations that handle the ADO.NET plumbing.

Avoid using Shared (static) database connections. In a multi-user or multi-threaded environment, a shared connection leads to race conditions and "Connection is already open" exceptions. Instead, instantiate a new connection for every discrete operation.

Trust Boundaries and Data Integrity

The boundary between the Business Logic Layer (BLL) and the DAL is a critical trust boundary. All data passing from the UI or BLL into the DAL must be treated as untrusted. To prevent SQL injection, never use string concatenation to build queries.

Use SqlParameter objects to ensure the database engine treats input as data, not executable code. This is the primary defense mechanism for any ADO.NET implementation.

Implementation Example: The User Repository

The following example demonstrates a secure implementation of a user retrieval method. This should be placed in a separate Class Library project targeting .NET Framework or .NET 6+.

Public Class UserRepository
    Private ReadOnly _connectionString As String

    Public Sub New(connectionString As String)
        _connectionString = connectionString
    End Sub

    Public Function GetUserById(userId As Integer) As User
        Dim user As User = Nothing
        ' Using blocks ensure the connection and command are disposed of correctly
        Using conn As New SqlConnection(_connectionString)
            Using cmd As New SqlCommand("SELECT UserName, Email FROM Users WHERE UserID = @uid", conn)
                ' Parameterized query prevents SQL injection
                cmd.Parameters.Add(New SqlParameter("@uid", SqlDbType.Int) With {.Value = userId})
                
                Try
                    conn.Open()
                    Using reader As SqlDataReader = cmd.ExecuteReader()
                        If reader.Read() Then
                            user = New User With {
                                .UserName = reader("UserName").ToString(),
                                .Email = reader("Email").ToString()
                            }
                        End If
                    End Using
                Catch ex As SqlException
                    ' Log exception here
                    Throw New DataAccessException("Database error occurred while fetching user.", ex)
                End Try
            End Using
        End Using
        Return user
    End Using

Operational Checks and Failure Modes

To ensure the DAL is production-ready, implement the following operational safeguards:

• Connection Disposal: Use the Using statement. This translates to a Try...Finally block that calls .Dispose(), ensuring the connection is returned to the connection pool even if an exception occurs.
• Transient Faults: Network hiccups or SQL Azure throttling can cause temporary failures. Implement a retry loop (e.g., 3 attempts with exponential backoff) specifically for SqlException error codes related to timeouts or deadlocks.
• Schema Evolution: Avoid SELECT *. Explicitly naming columns prevents the application from breaking when new columns are added to the table or when the column order changes.

Verification and Testing

To verify the implementation, perform the following checks:

• Leak Test: Run a loop of 1,000 requests to the DAL and monitor the sys.dm_exec_connections DMV in SQL Server. If the connection count climbs without dropping, the Using blocks are missing or failing.
• Injection Test: Pass a string like ' OR 1=1 -- into a text-based parameter. The query should return zero results or throw a type-mismatch error, rather than returning all users.
• Failure Simulation: Manually disable the network adapter or stop the SQL Server service during a long-running transaction to verify that the Catch block handles the disconnection gracefully without crashing the application.

Conditions for Redesign

This ADO.NET-based architecture is optimal for structured relational data. However, the design should be reconsidered if:

• NoSQL Transition: If the data moves to a document store (like MongoDB), the Repository interfaces remain, but the concrete implementations must be replaced entirely.
• Distributed Transactions: If the application requires atomic updates across multiple physical databases, you must move from SqlConnection to TransactionScope (System.Transactions).
• Complex Mapping: If the number of tables exceeds 50, the manual mapping of SqlDataReader to POCOs becomes a maintenance burden; at this point, migrating to a micro-ORM like Dapper is recommended.