Introduction to ADO.NET

ADO.NET is .NETs library for accessing databases. This article will go through the abstraction layer upon which all different DB engine providers are based on. By learning this abstraction, you can access any database using .NET.

There are two levels of abstraction in ADO.NET. The interfaces and the abstract base classes. I typically go for the interfaces, but there is a problem: the interfaces do not support async operations. Therefore, you need to use DbCommand instead of IDbCommand to be able to do asynchronous operations.

IDbConnection

At the bottom, we have the IDbConnection interface, an abstraction for the database connection. Its primary purpose is to wrap the connection and, as an abstract factory, to be able to create all other db access classes.

There are two primary usages for the connection. The first is to be able to begin transactions, and the second is to create command objects that wrap SQL statements.

Creating a connection is DB provider-specific but is typically done like this:

// SqlConnection is for SQL Server
var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

IDbTransaction

The transaction interface is used to wrap transactions. Rolling back is done automatically on disposal.

using var transaction = connection.BeginTransaction();

// Do something here

transaction.Commit();

If the code in the block fails, the commit will never be called, and the transaction will, therefore, be rolled back thanks to the using statement.

Do note that every time you have an active transaction, you must attach it to all commands created from the same connection.

await using var cmd = connection.CreateCommand();
command.Transaction = transaction;

IDbCommand

Commands wrap SQL statements. It can be a single or multiple statement. Use the DB engine delimiter to separate them (typically semicolons).

An annoying thing with the interfaces is that you can't directly create parameters with names and values. Instead, you need to do something like this:

var p = cmd.CreateParameter();
p.ParameternName = "userId";
p.Value = userId;
cmd.Parameters.Add(p);

It creates quite cluttered code where the intent is lost in the noise. I always create an extension method:

public static class CommandExtensions
{
    public static IDataParameter CreateParameter(this IDbCommand command, string name, object value)
    {
        var p = command.CreateParameter();
        p.ParameternName = "userId";
        p.Value = userId;
        command.Parameters.Add(p);
        return p;
    }
}

Which allows me to write cleaner commands:

await using var cmd = connection.CreateCommand();

cmd.CommandText = "UPDATE Users SET Discount = @percentage";
cmd.AddParameter("percentage", 50);
await cmd.ExecuteNonQueryAsync();

To execute multiple statements directly:

await using var cmd = connection.CreateCommand();

cmd.CommandText = @"UPDATE Users SET Discount = @percentage; 
                    DELETE Users WHERE Discount > @hamstringPercentage";
cmd.AddParameter("percentage", 50);
cmd.AddParameter("hamstringPercentage", 90);
await cmd.ExecuteNonQueryAsync();

That works well for all kinds of statements. Just remember that parameters must be unique. For many insert statements, you can name them like userId1, userId2 etc.

IDataReader

The reader is a stream-based record reader. It doesn't directly read the entire query result but streams it from the server when you request more rows. Therefore, it's quite efficient even for large result sets.

await using var cmd = connection.CreateCommand();

cmd.CommandText = "Update Users WHERE Discount > @limit";
cmd.AddParameter("limit", 30);

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    // Get casted value in first column.
    var userId = reader.GetInt32(0);
          
    // ..or..
    userId =  (int)reader["Id"];
}

Some functions give you correctly typed values but require the column index. Or you can use the index accessor, which returns an object.

Switching between result sets

When using a command for multiple result sets, you need to move between them when reading:

await using var cmd = connection.CreateCommand();

cmd.CommandText = @"SELECT * FROM Users; 
                    SELECT * FROM Posts";

await using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine("User: " + reader["UserName"]);
}

// This line moves to the next result
await reader.NextResultAsync();

while (await reader.ReadAsync())
{
    Console.WriteLine("Posrt: " + reader["Title"]);
}

Summary

No code above is DB provider-specific, meaning it should work interchangeably with all DB engines if you use SQL standard statements.

Extension methods

As I mentioned, you need a few extension methods to make everything easier.

public static class DbTransactionExtensions
{
    public static DbCommand CreateCommand(this IDbTransaction transaction)
    {
        var cmd = transaction.Connection!.CreateCommand();
        cmd.Transaction = transaction;
        return (DbCommand)cmd;
    }
}

public static class DbCommandExtensions
{
    public static IDbDataParameter AddParameter(this IDbCommand command, string parameterName, object value)
    {
        var p = command.CreateParameter();
        p.ParameterName = parameterName;
        p.Value = value;
        command.Parameters.Add(p);
        return p;
    }

    public static DbCommand CreateDbCommand(this IDbConnection connection)
    {
        var cmd = connection.CreateCommand();
        return (DbCommand)cmd;
    }
}

Complete example

A complete example is from creating the connection to executing a SQL query.

await using var connection = new SqlConnection("Server=;Database=AdoNetDemo;Trusted_Connection=True;");
await connection.OpenAsync();

await using var transaction = connection.BeginTransaction();
await using var command = transaction.CreateCommand();

command.CommandText = "SELECT * FROM Users";

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    Console.WriteLine("User " + reader["UserName"]);
}

Want to learn more? Read my article about building a simple data mapper based on ADO.NET.