Databases with ADO.NET

36.1 ADO.NET Architecture

Opens a connection to the database Executes SQL/stored procedures Fast, forward-only, read-only results

Fills DataSet/DataTable, pushes changes back DataSet / DataTable In-memory, disconnected data Safe parameterized values

Providers: Microsoft.Data.SqlClient (SQL Server), Microsoft.Data.Sqlite, Npgsql (PostgreSQL), MySqlConnector, Oracle.ManagedDataAccess, System.Data.OleDb (Access/Excel), System.Data.Odbc.

36.2 Querying with a DataReader

Imports Microsoft.Data.SqlClient

Dim connStr = "Server=localhost;Database=Shop;Trusted_Connection=True;TrustServerCertificate=True"

Using conn As New SqlConnection(connStr)
    conn.Open()
    Dim sql = "SELECT Id, Name, Price FROM Products WHERE Price > @minPrice ORDER BY Name"
    Using cmd As New SqlCommand(sql, conn)
        cmd.Parameters.AddWithValue("@minPrice", 100D)          ' ALWAYS use parameters
        Using reader = cmd.ExecuteReader()
            While reader.Read()
                Dim id = reader.GetInt32(0)
                Dim name = reader.GetString(reader.GetOrdinal("Name"))
                Dim price = CDec(reader("Price"))
                Console.WriteLine($"{id}: {name} - {price:C}")
            End While
        End Using
    End Using
End Using

36.3 Insert, Update, Delete and Scalars

Using conn As New SqlConnection(connStr)
    conn.Open()
    Using cmd As New SqlCommand("INSERT INTO Products (Name, Price) VALUES (@n, @p); SELECT SCOPE_IDENTITY();", conn)
        cmd.Parameters.Add("@n", SqlDbType.NVarChar, 100).Value = "Keyboard"
        cmd.Parameters.Add("@p", SqlDbType.Decimal).Value = 49.99D
        Dim newId = Convert.ToInt32(cmd.ExecuteScalar())
        Console.WriteLine($"Inserted {newId}")
    End Using

    Using cmd As New SqlCommand("UPDATE Products SET Price = Price * 1.1 WHERE Category = @cat", conn)
        cmd.Parameters.AddWithValue("@cat", "Tech")
        Dim rows = cmd.ExecuteNonQuery()
        Console.WriteLine($"{rows} rows updated")
    End Using
End Using

36.4 Stored Procedures and Transactions

Using conn As New SqlConnection(connStr)
    conn.Open()
    Using tx = conn.BeginTransaction()
        Try
            Using cmd As New SqlCommand("usp_TransferFunds", conn, tx)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@FromId", 1)
                cmd.Parameters.AddWithValue("@ToId", 2)
                cmd.Parameters.AddWithValue("@Amount", 500D)
                Dim outParam = cmd.Parameters.Add("@NewBalance", SqlDbType.Decimal)
                outParam.Direction = ParameterDirection.Output
                cmd.ExecuteNonQuery()
                Console.WriteLine($"New balance: {outParam.Value}")
            End Using
            tx.Commit()
        Catch
            tx.Rollback()
            Throw
        End Try
    End Using
End Using

36.5 DataSet, DataTable and DataAdapter (Disconnected)

Dim table As New DataTable()
Using adapter As New SqlDataAdapter("SELECT * FROM Customers", connStr)
    adapter.Fill(table)
End Using
For Each row As DataRow In table.Rows
    Console.WriteLine(row("Name"))
Next
Dim filtered = table.Select("City = 'Pune'", "Name ASC")
Dim view As New DataView(table) With {.RowFilter = "Age > 30", .Sort = "Name"}
' In WinForms: dataGridView1.DataSource = table
' Save changes back: SqlCommandBuilder + adapter.Update(table)

' Building a DataTable manually
Dim dt As New DataTable("Orders")
dt.Columns.Add("Id", GetType(Integer))
dt.Columns.Add("Total", GetType(Decimal))
dt.Rows.Add(1, 99.5D)
Dim sum = dt.AsEnumerable().Sum(Function(r) r.Field(Of Decimal)("Total"))   ' LINQ to DataSet

36.6 Async Data Access

Using conn As New SqlConnection(connStr)
    Await conn.OpenAsync()
    Using cmd As New SqlCommand("SELECT COUNT(*) FROM Orders", conn)
        Dim count = CInt(Await cmd.ExecuteScalarAsync())
    End Using
End Using

36.7 Connection Strings and Configuration

Store connection strings in appsettings.json (.NET) or App.config (<connectionStrings>, .NET Framework: ConfigurationManager.ConnectionStrings("Shop").ConnectionString), never hardcoded with passwords.

Use integrated security, or secrets management (User Secrets, Azure Key Vault, environment variables).