ADO.NET Tutorial
Fast, direct SQL Server access from C#: connections, commands, readers, parameters, procedures, transactions and Dapper.
- Topics
- 32
- Full lessons
- 32
- Est. hours
- ~6
- Interview Qs
- 16
Overview
ADO.NET is the data access layer built into .NET. You open a connection, run SQL or stored procedures with a command, and read the results — with full control over every query that reaches the database.
Entity Framework Core, Dapper and every other .NET data library are built on top of ADO.NET. This track teaches the connected model (SqlConnection, SqlCommand, SqlDataReader), the disconnected model (DataSet, DataTable, SqlDataAdapter), and the production skills: parameters, transactions, async, bulk loading and clean repository design.
What you will learn
- Connect to SQL Server and use connection pooling correctly
- Run safe, parameterised queries and stored procedures
- Read data with SqlDataReader and DataTable
- Use transactions, async, bulk copy and the repository pattern
Prerequisites
You will get the most from this track if you already know:
Learning path
The recommended sequence, from beginner to advanced. Each stage builds on the one before.
- 1Getting Started · 6 topics · BeginnerIntroduction to ADO.NET · ADO.NET Architecture · Data Providers · Connection Strings · SqlConnection · Connection Pooling
- 2Executing Commands · 8 topics · Beginner → IntermediateSqlCommand · ExecuteNonQuery · ExecuteScalar · SqlDataReader · Parameterized Queries · SQL Injection Prevention · Stored Procedures · Output Parameters and Return Values
- 3Disconnected Data · 3 topics · IntermediateDataSet and DataTable · SqlDataAdapter · DataView
- 4Advanced · 7 topics · Intermediate → AdvancedTransactions · Async Data Access · Multiple Result Sets · Handling NULL Values · Error Handling · SqlBulkCopy · Table-Valued Parameters
- 5Architecture · 5 topics · AdvancedConfiguration and Dependency Injection · Mapping Rows to Objects · Repository Pattern · Dapper · ADO.NET vs Entity Framework
- 6Practice · 3 topics · AdvancedPerformance Best Practices · Complete CRUD Project · Interview Questions
Complete topic list
32 topicsGetting Started
6 topics- Introduction to ADO.NETBeginner5m
- ADO.NET ArchitectureBeginner5m
- Data ProvidersBeginner5m
- Connection StringsBeginner5m
- SqlConnectionBeginner5m
- Connection PoolingBeginner5m
Executing Commands
8 topics- SqlCommandBeginner5m
- ExecuteNonQueryBeginner5m
- ExecuteScalarIntermediate5m
- SqlDataReaderIntermediate5m
- Parameterized QueriesIntermediate5m
- SQL Injection PreventionIntermediate5m
- Stored ProceduresIntermediate5m
- Output Parameters and Return ValuesIntermediate5m
Disconnected Data
3 topics- DataSet and DataTableIntermediate5m
- SqlDataAdapterIntermediate5m
- DataViewIntermediate5m
Advanced
7 topics- TransactionsIntermediate5m
- Async Data AccessIntermediate5m
- Multiple Result SetsIntermediate5m
- Handling NULL ValuesIntermediate5m
- Error HandlingIntermediate5m
- SqlBulkCopyAdvanced5m
- Table-Valued ParametersAdvanced5m
Architecture
5 topics- Configuration and Dependency InjectionAdvanced5m
- Mapping Rows to ObjectsAdvanced5m
- Repository PatternAdvanced5m
- DapperAdvanced5m
- ADO.NET vs Entity FrameworkAdvanced5m
Practice
3 topics- Performance Best PracticesAdvanced5m
- Complete CRUD ProjectAdvanced5m
- Interview QuestionsAdvanced15m
Code example
A taste of the track, from Introduction to ADO.NET.
// dotnet new console -n ShopData && cd ShopData
// dotnet add package Microsoft.Data.SqlClient
using Microsoft.Data.SqlClient;
const string cs = "Server=localhost;Database=ShopDB;Integrated Security=True;TrustServerCertificate=True;";
using var conn = new SqlConnection(cs); // 1. connection
conn.Open();
using var cmd = new SqlCommand("SELECT Id, Name, Price FROM Products ORDER BY Id", conn); // 2. command
using var reader = cmd.ExecuteReader(); // 3. execute
while (reader.Read()) // 4. read row by row
{
Console.WriteLine(quot;{reader.GetInt32(0)}. {reader.GetString(1)} - ₹{reader.GetDecimal(2)}");
}
// 5. using closes reader, command and connection automatically
1. Laptop - ₹55000.00 2. Mouse - ₹1400.00 3. Keyboard - ₹2200.00
Practice exercises
Change the program to print only products with Stock = 0.
Which model would you use for (a) a CSV export of 1 million rows, (b) a small editable grid of 50 settings?
Which NuGet packages would you install for PostgreSQL and for SQLite?
Write a connection string for database ShopDB on server db01 using SQL login app_user (password from a variable).
Write a method IsDatabaseUpAsync that returns true if SELECT 1 succeeds.
Set the minimum pool size to 5 and maximum to 200 in a connection string.
Show all 31 exercises
Use one command in a loop to set Stock = 0 for product Ids 4, 5 and 6.
Write an UPDATE that changes a product’s price and returns false when the product does not exist.
Return the name of the most expensive product, or "none" if the table is empty.
Read Id and Name of products with Price above a given amount into a List<(int, string)>.
Write the parameter for a VARCHAR(20) column called Sku without causing an NVARCHAR conversion.
Fix this code: new SqlCommand("DELETE FROM Products WHERE Id = " + id, conn).
Call dbo.GetProductsByCategory for category 2 with @MinStock = 10.
Add an output parameter @NewStock INT to read the remaining stock after the order.
Compute the total price of all non-deleted rows using row.Field<decimal>.
Load only products in category 1 using a parameter in the adapter’s SelectCommand.
Show only modified rows in a view.
Rewrite the order with TransactionScope for async code.
Add a 5-second timeout to the query using a CancellationTokenSource linked to the request token.
Write a loop that prints the number of rows in each result set, whatever the count of sets.
Read a nullable DECIMAL column DiscountPrice into a decimal? using the helper.
Add a catch that turns error 547 into the message "This category is still used by products" when deleting a category.
Bulk copy into dbo.ProductsStaging instead and then insert only new names into Products.
Write a stored procedure dbo.DeleteProducts that accepts @Ids dbo.IdList and deletes those products.
Set the production connection string with an environment variable.
Write a static Map method for ProductDto from a reader positioned on a row with Id, Name, Price.
Add a SearchAsync(string term) method to the interface and implementation.
Update a product’s price with Dapper and return whether a row changed.
Choose a tool for: (a) importing 500k rows nightly, (b) a typical admin CRUD screen, (c) a complex reporting query.
Which index would help ORDER BY Name with OFFSET/FETCH on Products?
Add a GET /api/products/search?q= endpoint using the SearchAsync method from the repository lesson.
Common interview questions
- What is ADO.NET?
- What is the difference between the connected and disconnected models?
- What do ExecuteNonQuery, ExecuteScalar and ExecuteReader return?
- Why should you always use parameters?
- Why wrap connections in using?
- How does connection pooling work?
- How do you call a stored procedure with an output parameter?
- How do you handle NULL from the database?
- How do you use a transaction in ADO.NET?
- Why use async ADO.NET methods in web apps?
- What is the difference between DataSet and DataTable?
- How do you insert 100,000 rows quickly?
- What are table-valued parameters?
- Which SqlException numbers do you commonly handle?
- When would you choose ADO.NET or Dapper over EF Core?
- How do you structure ADO.NET code in an ASP.NET Core app?