ADO.NET Tutorial: Data Access with .NET from Scratch (2026)
Before Entity Framework, before Dapper, before any ORM, there was ADO.NET. I cut my teeth on data access using SqlConnection, SqlCommand, and DataSet — and understanding ADO.NET made me a better developer. When you know what happens under the hood of your ORM, you make better decisions about connection management and query optimization.
ADO.NET is the core data access technology in .NET, providing classes for connecting to databases, executing commands, and retrieving results. It supports connected architectures (DataReader) and disconnected architectures (DataSet). Every higher-level library — EF Core, Dapper, NHibernate — uses ADO.NET under the hood.
Managing Connections with SqlConnection
SqlConnection manages the physical connection to SQL Server. ADO.NET implements connection pooling by default — connections are cached and reused. Always wrap connections in using blocks. Use integrated security or managed identities in production.
string cs = builder.Configuration.GetConnectionString("Default");
using var connection = new SqlConnection(cs);
await connection.OpenAsync();
using var command = new SqlCommand("SELECT COUNT(*) FROM Products", connection);
int count = (int)await command.ExecuteScalarAsync();Executing Commands with SqlCommand
SqlCommand represents a SQL statement or stored procedure. Always use parameterized queries to prevent SQL injection. ExecuteNonQuery returns rows affected. ExecuteScalar returns first column of first row. ExecuteReader returns a DataReader. Use async methods in modern apps.
using var command = new SqlCommand(
"INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", connection);
command.Parameters.AddWithValue("@Name", product.Name);
command.Parameters.AddWithValue("@Price", product.Price);
int rowsAffected = await command.ExecuteNonQueryAsync();Reading Data with SqlDataReader
SqlDataReader provides forward-only, read-only data stream — the fastest retrieval method. Call Read() to advance. Access columns by ordinal or name. Use typed Get methods (GetInt32, GetString) for performance. Check IsDBNull before reading nullable columns.
using var reader = await command.ExecuteReaderAsync();
var products = new List();
while (await reader.ReadAsync())
{
products.Add(new Product
{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
Price = reader.GetDecimal(2)
});
} Working with DataSet and DataTable
DataSet holds in-memory tables, relationships, and constraints. SqlDataAdapter fills a DataSet and maps changes back to the database. DataTable represents a single table. DataView sorts and filters without modifying data.
var adapter = new SqlDataAdapter(
"SELECT * FROM Products; SELECT * FROM Categories", cs);
var ds = new DataSet();
adapter.Fill(ds);
ds.Tables[0].TableName = "Products";
ds.Tables[1].TableName = "Categories";Transactions and Concurrency
BeginTransaction starts a transaction on a connection. Assign it to each SqlCommand. Commit or Rollback. Optimistic concurrency uses WHERE clauses with original values in UPDATE statements to detect conflicts.
using var tx = connection.BeginTransaction();
try
{
var cmd = new SqlCommand("UPDATE Inventory SET Qty=Qty-1 WHERE ProductId=@Id", connection, tx);
cmd.Parameters.AddWithValue("@Id", productId);
await cmd.ExecuteNonQueryAsync();
await tx.CommitAsync();
}
catch
{
await tx.RollbackAsync();
throw;
}Performance Best Practices and Async Patterns
Trust connection pooling. Use SqlBulkCopy for bulk loads. Prefer stored procedures for complex logic. Use async methods with ConfigureAwait(false). Profile queries with SQL Server Profiler and check execution plans.
using var bulkCopy = new SqlBulkCopy(cs);
bulkCopy.DestinationTableName = "Products";
bulkCopy.BatchSize = 1000;
var table = new DataTable();
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Price", typeof(decimal));
foreach (var p in products) table.Rows.Add(p.Name, p.Price);
await bulkCopy.WriteToServerAsync(table);Frequently Asked Questions
Should I use ADO.NET or Entity Framework Core?
Use ADO.NET for maximum performance and fine-grained SQL control. Use EF Core for rapid development and LINQ queries. Many projects use both — they are not mutually exclusive.
What is connection pooling and how does it work?
Connection pooling reuses physical database connections to avoid setup overhead. The pool is identified by the connection string. Connections return to the pool when closed.
How do I prevent SQL injection with ADO.NET?
Always use parameterized queries with SqlCommand.Parameters. Never concatenate user input into SQL strings. Parameters treat values as data, not executable code.
What is the difference between ExecuteReader and ExecuteScalar?
ExecuteReader returns a forward-only result set for multiple rows. ExecuteScalar returns a single value (first column of first row), ideal for aggregates.
Originally published on Ayodhyyya. Last updated June 1, 2026.