ADO.NET Objects

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:

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:

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".

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
• SqlCommand
• SqlDataReader

Cheat sheets

• ADO.NET Objects