C# ADO.NET
last modified October 5, 2026
ADO.NET is the .NET data-access API for working with relational databases. This tutorial uses Microsoft.Data.SqlClient, the current provider for Microsoft SQL Server and Azure SQL. It covers connections, commands, parameters, readers, asynchronous operations, and transactions.
Prerequisites
The examples require a running SQL Server or Azure SQL database and a database user that can create and modify the sample table. Create a console project and add the provider package:
dotnet new console -n AdoNetDemo cd AdoNetDemo dotnet add package Microsoft.Data.SqlClient
Set ConnectionString to a connection string for your environment. Do not commit passwords; use user secrets, environment variables, or a managed identity in deployed applications. Database execution is not required to compile these examples.
Opening a connection
SqlConnection represents a connection to SQL Server. Opening a connection is an I/O operation, so modern applications should use OpenAsync. The using declaration disposes the connection even when an exception occurs.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
Console.WriteLine($"Connected to {connection.Database}");
$ dotnet run Connected to SampleDb
Replace the server, database, and credentials with values from your SQL Server installation. For Azure SQL, use encryption and certificate validation appropriate for your deployment; TrustServerCertificate=True is convenient for local development, not a production security recommendation.
Creating a table
SqlCommand sends SQL to the server. ExecuteNonQueryAsync is used when the statement does not return rows. The following idempotent script creates a small table for the remaining examples.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
const string sql = """
IF OBJECT_ID(N'dbo.Products', N'U') IS NULL
BEGIN
CREATE TABLE dbo.Products
(
Id int IDENTITY(1, 1) NOT NULL CONSTRAINT PK_Products PRIMARY KEY,
Name nvarchar(100) NOT NULL,
Price decimal(10, 2) NOT NULL
);
END;
""";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
await using var command = new SqlCommand(sql, connection);
await command.ExecuteNonQueryAsync();
Console.WriteLine("Products table is ready.");
$ dotnet run Products table is ready.
Raw SQL is appropriate for schema setup, but values supplied by users must be parameters rather than string-concatenated into SQL.
Inserting with parameters
Parameters protect values from SQL injection and let the provider send the correct data separately from the command text. Use ExecuteScalarAsync when the statement returns one value, such as the generated identity.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
const string sql = """
INSERT INTO dbo.Products (Name, Price)
OUTPUT INSERTED.Id
VALUES (@name, @price);
""";
await using var command = new SqlCommand(sql, connection);
command.Parameters.AddWithValue("@name", "Notebook");
command.Parameters.Add("@price", System.Data.SqlDbType.Decimal).Value = 4.95m;
var id = (int)(await command.ExecuteScalarAsync())!;
Console.WriteLine($"Inserted product {id}.");
$ dotnet run Inserted product 1.
For frequently executed commands, specify parameter types and sizes explicitly instead of relying on inference. Never put a name or price directly into the SQL string.
Reading rows
SqlDataReader reads a result set one row at a time, which avoids loading the complete result into memory. The ordinal or column-name accessors return values from the current row.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
await using var command = new SqlCommand(
"SELECT Id, Name, Price FROM dbo.Products ORDER BY Id;", connection);
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
var id = reader.GetInt32(0);
var name = reader.GetString(1);
var price = reader.GetDecimal(2);
Console.WriteLine($"{id}: {name} ({price:C})");
}
$ dotnet run 1: Notebook ($4.95)
Keep the connection and reader open only for the duration of the read. If a column can contain SQL NULL, check reader.IsDBNull(ordinal) before calling a typed getter.
Updating and deleting
Use ExecuteNonQueryAsync for updates and deletes. Its return value is the number of affected rows, which can be checked to detect a missing record or an unexpected filter.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
await using var update = new SqlCommand(
"UPDATE dbo.Products SET Price = @price WHERE Id = @id;", connection);
update.Parameters.Add("@price", System.Data.SqlDbType.Decimal).Value = 5.25m;
update.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = 1;
var updated = await update.ExecuteNonQueryAsync();
await using var delete = new SqlCommand(
"DELETE FROM dbo.Products WHERE Id = @id;", connection);
delete.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = 1;
var deleted = await delete.ExecuteNonQueryAsync();
Console.WriteLine($"Updated: {updated}; deleted: {deleted}");
$ dotnet run Updated: 1; deleted: 1
Transactions
A transaction groups multiple commands into one atomic unit. Commit only after all related work succeeds; roll back when an exception occurs. The await using declaration disposes the transaction.
using Microsoft.Data.SqlClient;
const string ConnectionString =
"Server=localhost;Database=SampleDb;User Id=appuser;Password=change-me;Encrypt=True;TrustServerCertificate=True;";
await using var connection = new SqlConnection(ConnectionString);
await connection.OpenAsync();
await using var transaction = (SqlTransaction)await connection.BeginTransactionAsync();
try
{
await using var command = new SqlCommand(
"UPDATE dbo.Products SET Price = Price * 0.9 WHERE Id = @id;",
connection, transaction);
command.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = 1;
await command.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
Every command that participates in the transaction must receive the same connection and transaction. In real code, catch exceptions at an appropriate application boundary and log them without exposing credentials.
Handling errors and closing resources
Database calls can fail because a server is unavailable, credentials are invalid, a command times out, or a constraint is violated. Catch SqlException when you can recover or add useful context, and let it propagate when the caller should decide. Connections are pooled automatically, so disposing a connection returns it to the pool rather than necessarily closing the physical socket.
Prefer asynchronous methods in server applications, use command timeouts suitable for the operation, and select only the columns you need. ADO.NET does not track objects for you: map reader values explicitly or use a separate data-access library when you need higher-level object mapping.
Source
Microsoft ADO.NET for SQL Server
Microsoft.Data.SqlClient API
Commands and parameters
In this article we have used ADO.NET and Microsoft.Data.SqlClient to connect to SQL Server, execute parameterized commands, read data, and control transactions from C#.
Author
List all C# tutorials.