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

By

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:

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: an int.
  • 1, ProductName: text, a string.
  • 2, UnitPrice: a money column, a decimal. It's NULL for 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 in Aniseed Syrup: 10.00.
  • Read each column with the Get method that matches its type.
  • When a row's price is NULL, its line is the name, a colon and a space, then no price, as in Chef 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.

Key terms

Cheat sheets