Querying a Database with ADO.NET: an overview

After this lesson you can

• Name the three steps of running a query from C#, and the ADO.NET object for each

Key takeaways

• Every query from C# takes three steps: connect with a SqlConnection, run the SQL with a SqlCommand, and read the rows with a SqlDataReader.
• The SQL is a plain C# string. Get the query right in SSMS first, then paste it into your code.
• Open a connection, do the work, and close it as soon as you can. using closes each object for you.

With the driver and the connection string in place, a C# program can work with the database. Whatever the query, it always takes the same three steps:

It's the same thing you do by hand in SSMS: connect to localhost, type a query and press F5, then read the results grid. ADO.NET does each of those steps in code.

A first look

using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

// 1. Connect
using SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

// 2. Run the SQL
string sql = "SELECT CategoryName FROM Categories ORDER BY CategoryName;";
using SqlCommand command = new SqlCommand(sql, connection);
using SqlDataReader reader = command.ExecuteReader();

// 3. Read the results
while (reader.Read())
{
    Console.WriteLine(reader.GetString(0));
}

Beverages
Condiments
Confections
Dairy Products
Grains/Cereals
Meat/Poultry
Produce
Seafood

The SQL is a plain C# string, the same query you'd type in SSMS. A good habit: get the query right in SSMS first, then paste it into your code.

Connections are borrowed, not kept

Opening a connection to a server takes time and uses the server's resources, so a program opens a connection, does its work, and closes it again as soon as it can. The using keyword in front of each object does the closing: when the program leaves the block, or here the end of the program, each object is disposed, and the connection is closed. You'll see using on every connection, command and reader.

Check yourself

1 question to try on the lesson page.

So what

Connect, run, read: every page that shows data from a database, in any app you build, starts with these three steps.

Key terms

• ADO.NET
• using Statement

Cheat sheets

• Querying a Database with ADO.NET: an overview