Data Access: ADO.NET

Key ideas: connection pooling (automatic: open late, close early), parameters always, CommandType.StoredProcedure, output parameters, ExecuteScalar/ExecuteNonQuery/ExecuteReader, SqlBulkCopy for bulk inserts, DataTable/DataSet/DataAdapter for disconnected scenarios and WinForms binding, DbProviderFactory for provider-agnostic code.

Providers: SQL Server (Microsoft.Data.SqlClient), SQLite (Microsoft.Data.Sqlite), PostgreSQL (Npgsql), MySQL (MySqlConnector), Oracle (Oracle.ManagedDataAccess.Core), ODBC/OleDb (Access, Excel, legacy).

Resilience: transient fault retries (SQL Azure), command timeouts, deadlock retry (error 1205).

Imports Microsoft.Data.SqlClient

Public Class ProductRepository
    Private ReadOnly _connStr As String
    Public Sub New(connStr As String)
        _connStr = connStr
    End Sub

    Public Async Function GetByCategoryAsync(category As String, ct As Threading.CancellationToken) As Task(Of List(Of Product))
        Const sql = "SELECT Id, Name, Price FROM dbo.Products WHERE Category = @cat ORDER BY Name"
        Dim result As New List(Of Product)
        Using conn As New SqlConnection(_connStr)
            Await conn.OpenAsync(ct)
            Using cmd As New SqlCommand(sql, conn)
                cmd.Parameters.Add("@cat", SqlDbType.NVarChar, 50).Value = category
                Using rdr = Await cmd.ExecuteReaderAsync(ct)
                    While Await rdr.ReadAsync(ct)
                        result.Add(New Product With {
                            .Id = rdr.GetInt32(0),
                            .Name = rdr.GetString(1),
                            .Price = rdr.GetDecimal(2)})
                    End While
                End Using
            End Using
        End Using
        Return result
    End Function

    Public Async Function TransferStockAsync(fromId As Intege
…