# ADO.NET Objects Use SqlConnection to connect, SqlCommand to run SQL, and SqlDataReader to read rows, choosing ExecuteReader, ExecuteScalar or ExecuteNonQuery for each kind of statement. Journeyman · C# · T-SQL · Updated November 3, 2026 ## After this lesson you can - Open a SqlConnection and run a SqlCommand - Read rows with a SqlDataReader, converting each column to the right C# type ## Key takeaways - Run a `SELECT` that returns rows with `ExecuteReader()`, one value with `ExecuteScalar()`, and `INSERT`, `UPDATE` or `DELETE` with `ExecuteNonQuery()`. - `ExecuteScalar()` returns an `object`, so cast it to the type you know it is, such as `(int)`. - `reader.Read()` moves to the next row and returns `false` when there are no more, so it fits a `while` loop. - `GetInt32(0)`, `GetString(1)` and the other `Get` methods read a column by its position, from 0. Use the one that matches the column's SQL type. - A `Get` method can't read a `NULL`: check a column that allows `NULL` with `IsDBNull` first. Each step of a query has its own object. They all come from the `Microsoft.Data.SqlClient` namespace, so a file that uses them starts with: ```csharp using Microsoft.Data.SqlClient; ``` ## SqlConnection A `SqlConnection` is one connection to the database, made from a connection string: ```csharp using SqlConnection connection = new SqlConnection(connectionString); connection.Open(); ``` `Open()` connects. If the server isn't running or the database doesn't exist, this is the line that fails. When the `using` variable goes out of scope, the connection closes. ## SqlCommand A `SqlCommand` holds one SQL statement and the connection to run it on: ```csharp string sql = "SELECT ProductName, UnitPrice FROM Products WHERE CategoryId = 2;"; using SqlCommand command = new SqlCommand(sql, connection); ``` How you run it depends on what the statement gives back: | Method | Use it for | Returns | |---|---|---| | `ExecuteReader()` | a `SELECT` that returns rows | a `SqlDataReader` | | `ExecuteScalar()` | a `SELECT` that returns one value, such as a count | that value, as an `object` | | `ExecuteNonQuery()` | `INSERT`, `UPDATE` and `DELETE` | the number of rows affected, an `int` | ```csharp using Microsoft.Data.SqlClient; string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;"; using SqlConnection connection = new SqlConnection(connectionString); connection.Open(); using SqlCommand countCommand = new SqlCommand("SELECT COUNT(*) FROM Products;", connection); int productCount = (int)countCommand.ExecuteScalar(); Console.WriteLine($"Products: {productCount}"); ``` ```text Products: 77 ``` `ExecuteScalar` returns an `object`, because it could be any type, so you cast it to the type you know it is. ## SqlDataReader A `SqlDataReader` reads the rows of a result **one at a time, forward only**. `Read()` moves to the next row and returns `true`, or returns `false` when there are no more rows, so it fits a `while` loop exactly: ```csharp using Microsoft.Data.SqlClient; string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;"; using SqlConnection connection = new SqlConnection(connectionString); connection.Open(); string sql = "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE CategoryId = 2 ORDER BY ProductName;"; using SqlCommand command = new SqlCommand(sql, connection); using SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int id = reader.GetInt32(0); string name = reader.GetString(1); decimal price = reader.GetDecimal(2); Console.WriteLine($"{id,3} {name,-34} {price,8:C}"); } ``` ```text 3 Aniseed Syrup $10.00 4 Chef Anton's Cajun Seasoning $22.00 5 Chef Anton's Gumbo Mix $21.35 15 Genen Shouyu $15.50 6 Grandma's Boysenberry Spread $25.00 44 Gula Malacca $19.45 65 Louisiana Fiery Hot Pepper Sauce $21.05 66 Louisiana Hot Spiced Okra $17.00 8 Northwoods Cranberry Sauce $40.00 77 Original Frankfurter grüne Soße $13.00 61 Sirop d'érable $28.50 63 Vegie-spread $43.90 ``` The numbers in `GetInt32(0)`, `GetString(1)` and `GetDecimal(2)` are the column's position in the `SELECT`, counting from 0. You can also read a column by its name, `reader["ProductName"]`, which gives an `object` you then cast: `(string)reader["ProductName"]`. Names are easier to read; positions are slightly faster. Pick one style and use it consistently. ## SQL types and C# types Each `Get` method returns one C# type, so choose the one that matches the column: | SQL Server type | C# type | Reader method | |---|---|---| | `int` | `int` | `GetInt32` | | `smallint` | `short` | `GetInt16` | | `nvarchar`, `nchar` | `string` | `GetString` | | `money`, `decimal` | `decimal` | `GetDecimal` | | `bit` | `bool` | `GetBoolean` | | `datetime` | `DateTime` | `GetDateTime` | Use the wrong one, such as `GetInt32` on a `money` column, and the program stops with an `InvalidCastException`. ## When a column is NULL A `Get` method can't return `NULL`: reading a `NULL` stops the program with a `SqlNullValueException`. For a column that allows `NULL`, check first with `IsDBNull`: ```csharp string? region = reader.IsDBNull(3) ? null : reader.GetString(3); ``` `string?` is a string that may be `null`, the C# way of saying "no value". ## Check yourself 2 questions to try on the lesson page. ## So what `SqlConnection`, `SqlCommand` and `SqlDataReader` are the three tools behind all data access in .NET. Even when you later use helper libraries that hide them, these are what's underneath. ## Key terms - [SqlConnection](https://www.ask-codey.com/glossary/sqlconnection) - [SqlCommand](https://www.ask-codey.com/glossary/sqlcommand) - [SqlDataReader](https://www.ask-codey.com/glossary/sqldatareader) ## Cheat sheets - [ADO.NET Objects](https://www.ask-codey.com/cheat-sheets/querying-a-database-with-ado-net-ado-net-objects-cheat-sheet) Source: https://www.ask-codey.com/lessons/querying-a-database-with-ado-net-ado-net-objects