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
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
SELECTthat returns rows withExecuteReader(), one value withExecuteScalar(), andINSERT,UPDATEorDELETEwithExecuteNonQuery(). ExecuteScalar()returns anobject, so cast it to the type you know it is, such as(int).reader.Read()moves to the next row and returnsfalsewhen there are no more, so it fits awhileloop.GetInt32(0),GetString(1)and the otherGetmethods read a column by its position, from 0. Use the one that matches the column's SQL type.- A
Getmethod can't read aNULL: check a column that allowsNULLwithIsDBNullfirst.
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:
using Microsoft.Data.SqlClient;
SqlConnection
A SqlConnection is one connection to the database, made from a connection string:
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:
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 |
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( quot;Products: {productCount}");
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:
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( quot;{id,3} {name,-34} {price,8:C}");
}
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:
string? region = reader.IsDBNull(3) ? null : reader.GetString(3);
string? is a string that may be null, the C# way of saying "no value".
Do it: turn the reader's rows into a price list
The store's price page shows one line of text for each Condiments product. The rows come from this query:
SELECT ProductId, ProductName, UnitPrice
FROM Products
WHERE CategoryId = 2
ORDER BY ProductName;
Its columns, by position:
0,ProductId: anint.1,ProductName: text, astring.2,UnitPrice: amoneycolumn, adecimal. It'sNULLfor a product that has no price yet.
The practice box can't reach SQL Server, so the bottom of Program.cs has a stand-in for SqlDataReader, named StandInReader. Its rows are written in code, but its Read, GetInt32, GetString, GetDecimal and IsDBNull work as SqlDataReader's do. Leave it as it is. The tests make one like this:
StandInReader reader = new StandInReader(
new object[] { 3, "Aniseed Syrup", 10.00m },
new object[] { 4, "Chef Anton's Cajun Seasoning", 22.00m },
new object[] { 15, "Genen Shouyu", 15.50m });
For those three rows, PriceList(reader) returns a list of three lines:
| Row | ProductName (column 1) | UnitPrice (column 2) | Its line |
|---|---|---|---|
| 1st | Aniseed Syrup | 10.00 | Aniseed Syrup: 10.00 |
| 2nd | Chef Anton's Cajun Seasoning | 22.00 | Chef Anton's Cajun Seasoning: 22.00 |
| 3rd | Genen Shouyu | 15.50 | Genen Shouyu: 15.50 |
Your job: write the method List<string> PriceList(StandInReader reader).
- Read the rows one at a time, in order, until there are no more.
- For each row, add one line to the list: the product's name, a colon and a space, then its price as the reader gives it (no
$), as inAniseed Syrup: 10.00. - Read each column with the
Getmethod that matches its type. - When a row's price is
NULL, its line is the name, a colon and a space, thenno price, as inChef Anton's Gumbo Mix: no price. - When there are no rows, return an empty list.
Practiced live on the lesson page: write the C# right there and check it.
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.