Core objects and the connected model — ADO.NET — Connected & Disconnected Data Access

1. Core objects and the connected model

Basic★ Must knowLesson 1 of 9

Object	Role
`SqlConnection`	Connection to the DB (pooled)
`SqlCommand`	SQL statement or stored procedure
`SqlParameter`	Typed parameter value — prevents SQL injection
`SqlDataReader`	Fast, forward-only, read-only stream of rows
`SqlDataAdapter`	Bridge between DB and `DataSet`
`DataSet` / `DataTable`	In-memory relational data
`SqlTransaction`	Groups commands atomically
`SqlBulkCopy`	High-speed bulk insert

csharp

const string cs = "Server=.;Database=Shop;Integrated Security=True;TrustServerCertificate=True";

await using var conn = new SqlConnection(cs);
await conn.OpenAsync(ct);

await using var cmd = new SqlCommand("SELECT Id, Name, Price FROM Products WHERE CategoryId = @cat", conn);
cmd.Parameters.Add("@cat", SqlDbType.Int).Value = categoryId;

await using var reader = await cmd.ExecuteReaderAsync(ct);
var list = new List<Product>();
while (await reader.ReadAsync(ct))
{
    list.Add(new Product(
        reader.GetInt32(0),
        reader.GetString(1),
        reader.GetDecimal(2)));
}

Use ordinal getters (GetInt32(0)) or cache reader.GetOrdinal("Name") for speed; check reader.IsDBNull(i) before reading nullable columns.