ADO.NET and Database Access

Analogy categoryAnalogies

Introduction to ADO.NET

ADO.NET is the data access layer in the .NET Framework that lets VB.NET applications talk to relational databases such as SQL Server, MySQL, or PostgreSQL. It is built around a provider model: System.Data.SqlClient targets SQL Server, System.Data.OleDb targets older Access or Excel sources, and each provider supplies its own Connection, Command, and DataReader classes that implement common interfaces like IDbConnection. A connection string identifies the server, database, and credentials needed to open a session.

🏏

Cricket analogy: Choosing a database provider is like a captain picking a bowling attack for the pitch at hand — SqlClient for a fast, well-supported SQL Server surface the way Bumrah suits a seaming Wankhede track, OleDb for legacy Access files the way a part-time spinner covers an unusual situation.

Connecting to a Database

Opening a database connection in VB.NET means instantiating a SqlConnection with a connection string and calling Open, ideally inside a Using block so the connection is closed and disposed even if an exception occurs. ADO.NET connections are drawn from an internal connection pool by default, so repeatedly opening and closing SqlConnection objects with the same connection string is cheap because the underlying TCP session is reused rather than recreated each time.

🏏

Cricket analogy: A Using block around a SqlConnection is like a fielder who always returns to their designated position after chasing a boundary — no matter how the play unfolds, cleanup happens automatically instead of leaving a gap in the field.

vbnet

Imports System.Data.SqlClient

Public Function GetCustomerName(customerId As Integer) As String
    Dim connString As String = "Server=.;Database=Sales;Trusted_Connection=True;"
    Using conn As New SqlConnection(connString)
        conn.Open()
        Using cmd As New SqlCommand("SELECT Name FROM Customers WHERE Id = @Id", conn)
            cmd.Parameters.AddWithValue("@Id", customerId)
            Dim result = cmd.ExecuteScalar()
            Return If(result IsNot Nothing, result.ToString(), String.Empty)
        End Using
    End Using
End Function

Executing Commands: Reader, NonQuery, and Scalar

SqlCommand exposes three main execution methods depending on what you expect back. ExecuteReader returns a forward-only, read-only SqlDataReader for streaming rows of a SELECT query without loading everything into memory at once. ExecuteNonQuery runs INSERT, UPDATE, or DELETE statements and returns the number of rows affected. ExecuteScalar runs a query and returns only the single value in the first column of the first row, which is ideal for aggregates like COUNT or a single lookup value.

🏏

Cricket analogy: ExecuteReader streaming rows one at a time is like a ball-by-ball radio commentary feed rather than waiting for the full scorecard PDF at match end — you process each delivery as it arrives.

Always use parameterized queries — as in cmd.Parameters.AddWithValue('@Id', customerId) — instead of concatenating user input directly into SQL strings. Parameterization prevents SQL injection attacks and lets SQL Server cache and reuse query execution plans, which also improves performance for repeated queries.

DataSets, DataAdapters, and Disconnected Data

Beyond the connected model of SqlDataReader, ADO.NET offers a disconnected model built around DataSet, DataTable, and SqlDataAdapter. A SqlDataAdapter's Fill method executes a query and populates a DataTable in memory, after which the connection can be closed while your code continues working with the in-memory copy — useful for binding to Windows Forms grids or caching data across multiple operations. The adapter's associated CommandBuilder or explicit InsertCommand, UpdateCommand, and DeleteCommand can later push changes back with a single Update call.

🏏

Cricket analogy: A DataSet is like taking a full printed scorecard away from the stadium after play — you can analyze every over on the train home without needing a live connection to the ground anymore.

A common bug is forgetting to wrap SqlConnection, SqlCommand, and SqlDataReader in Using blocks (or an equivalent Try/Finally with explicit Close/Dispose calls). Leaked connections exhaust the connection pool under load, causing 'Timeout expired' errors that are hard to diagnose because they surface far from the code that actually caused them.

Transactions

When multiple related statements must succeed or fail together — like debiting one account and crediting another — wrap them in a SqlTransaction created from conn.BeginTransaction(). Assign the transaction to each SqlCommand's Transaction property, then call transaction.Commit() if all statements succeed or transaction.Rollback() in a Catch block if any statement throws, ensuring the database never ends up in a partially updated, inconsistent state.

🏏

Cricket analogy: A transaction's all-or-nothing commit is like a DRS review that either overturns the entire umpire's decision or confirms it fully — there's no partial 'half out' result left hanging.

ADO.NET providers like SqlClient wrap connection, command, and reader objects behind common interfaces for a given database.
Always open SqlConnection objects inside a Using block to guarantee they are closed and returned to the connection pool.
Use ExecuteReader for row streaming, ExecuteNonQuery for INSERT/UPDATE/DELETE, and ExecuteScalar for single-value results.
Parameterized queries with cmd.Parameters prevent SQL injection and improve query plan reuse.
DataAdapter.Fill populates a disconnected DataTable/DataSet that can be edited offline and synced back with Update.
SqlTransaction with Commit/Rollback keeps multi-statement operations atomic and the database consistent.
Leaked, unclosed connections are a leading cause of connection pool timeout errors in production.