ADO.NET in .NET 10: Complete Developer Learning Guide

ADO.NET in .NET 10

A practical, beginner-to-advanced guide to database access in .NET — understand how applications connect to relational databases, execute commands, read and update data, manage transactions, and build reliable data-access layers with C# 14.

01. Introduction to Data Access

Almost every business application works with data. An e-commerce application stores products and orders, an employee portal stores employee information, and a reporting system reads business transactions from a database.

To work with this information, an application needs a way to connect to a data source, send queries, receive results, and make changes safely. This responsibility is known as data access.

The data-access layer acts as the communication boundary between application code and a data source.

In the .NET ecosystem, ADO.NET provides the foundational APIs for this work. Higher-level tools such as Dapper and Entity Framework Core build on top of the same underlying database connectivity concepts.

Learning approach: We will use SQL Server and Microsoft.Data.SqlClient for practical examples. The core ideas — connections, commands, parameters, readers and transactions — also appear in other database drivers and programming platforms.

02. What Is ADO.NET?

ADO.NET is the data-access technology in .NET that provides classes and abstractions for communicating with data sources. It allows applications to connect to databases, execute commands, retrieve results, and update stored information.

ADO.NET is not a database itself, and it is not an ORM. It is a programming model and set of APIs. A database provider implements the database-specific communication details while exposing familiar .NET objects.

What can you do with ADO.NET?

Open and manage database connections.
Execute SQL statements and stored procedures.
Read query results one row at a time.
Insert, update and delete records.
Group multiple operations into transactions.
Load data into in-memory structures such as DataTable and DataSet.

In simple terms: ADO.NET gives a .NET application the building blocks it needs to communicate with a database.

03. Where ADO.NET Fits in .NET

ADO.NET is part of the broader .NET data-access ecosystem. It can be used directly when you want control over SQL execution and data reading. Libraries such as Dapper add convenient object mapping, while EF Core provides a higher-level object-relational mapping experience.

Dapper and EF Core provide different abstractions; ADO.NET remains the lower-level foundation for many relational data-access operations.

Learning ADO.NET first helps you understand what higher-level libraries are doing behind the scenes. It is especially useful when troubleshooting SQL execution, connection behavior, transactions, or performance.

04. ADO.NET Architecture

ADO.NET is commonly explained through two major parts: a .NET data provider and the DataSet. The provider communicates with a specific data source. The DataSet represents data in memory and can work independently of an open database connection.

Conceptual overview of the provider and disconnected data components.

Core provider objects

Object	Purpose
`Connection`	Establishes a connection to the data source.
`Command`	Executes SQL statements or stored procedures.
`Parameter`	Supplies typed values separately from command text.
`DataReader`	Reads a forward-only stream of rows.
`DataAdapter`	Moves data between a data source and a DataSet/DataTable.
`Transaction`	Groups database operations into a commit-or-rollback unit.

05. Connected and Disconnected Models

ADO.NET supports two broad ways of working with data. In connected access, the application reads or works with results while a connection is open. In disconnected access, data is copied into an in-memory structure so the application can work with it without keeping the database connection open.

Connected access	Disconnected access
Commonly uses `DataReader`.	Commonly uses `DataAdapter`, `DataSet` and `DataTable`.
Reads results while the connection remains open.	Works with an in-memory copy after loading.
Forward-only and read-only reader behavior.	Can edit, filter and relate in-memory data.
Good for streaming query results and service endpoints.	Useful for data binding, offline-style editing and in-memory relationships.

For most request-oriented ASP.NET Core APIs, a command with a reader is a common starting point. A DataSet is useful when its disconnected features are actually needed.

06. Data Providers and SqlClient

A data provider implements the communication between .NET and a particular data source. The provider exposes familiar concepts such as connection, command, parameter, reader and transaction, while handling database-specific protocol details.

For SQL Server and Azure SQL in modern .NET applications, use Microsoft.Data.SqlClient. It is distributed as a NuGet package and is the recommended provider for new SQL Server development.

Provider	Typical data source
`Microsoft.Data.SqlClient`	SQL Server and Azure SQL
`Microsoft.Data.Sqlite`	SQLite
`Npgsql`	PostgreSQL
`MySqlConnector`	MySQL-compatible databases
`System.Data.Odbc`	Data sources with an ODBC driver

Important distinction: ADO.NET is the broader data-access model. Microsoft.Data.SqlClient is a SQL Server provider that implements that model. Avoid starting new SQL Server projects with the older System.Data.SqlClient namespace.

07. Create a .NET 10 Project

We will use a .NET 10 Web API project for examples because database access is a common part of backend application development.

dotnet new webapi -n AdoNetLearning
cd AdoNetLearning
dotnet run

Confirm that the .NET 10 SDK is installed. The project can use C# 14 through the .NET 10 toolchain. The examples intentionally focus on data-access concepts rather than advanced language syntax.

Suggested learning project structure

AdoNetLearning/
├── Controllers/
├── Data/
│   └── SqlConnectionFactory.cs
├── Models/
├── Repositories/
├── Services/
├── appsettings.json
└── Program.cs

This is a learning-friendly structure, not a mandatory architecture. As the application grows, organize code around features and responsibilities.

08. Install Microsoft.Data.SqlClient

Install the SQL Server provider from NuGet:

dotnet add package Microsoft.Data.SqlClient

Import the namespace in the C# files that use SQL Server provider types:

using Microsoft.Data.SqlClient;

You will commonly work with SqlConnection, SqlCommand, SqlParameter, SqlDataReader, SqlTransaction and SqlDataAdapter.

Version note: The package evolves independently of the .NET runtime. Use the current stable package version compatible with your application and review its release notes when upgrading.

09. Connection Strings

A connection string contains the information a provider needs to connect to a database. For SQL Server, it commonly includes the server, database, authentication method, encryption settings and other connection options.

Server=localhost;Database=StoreDb;
Integrated Security=True;Encrypt=True;
TrustServerCertificate=False;

For SQL Server authentication, a connection string may use a user ID and password. Do not hardcode credentials in source code or commit secrets to source control.

Store configuration outside code

A local development example can use configuration in appsettings.json. Keep real production credentials in a secure secret store or environment-based configuration.

{
  "ConnectionStrings": {
    "StoreDb": "Server=localhost;Database=StoreDb;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;"
  }
}

Read the configured value through .NET configuration:

string connectionString =
    builder.Configuration.GetConnectionString("StoreDb")
    ?? throw new InvalidOperationException(
        "Connection string 'StoreDb' was not configured.");

Security: Avoid setting TrustServerCertificate=True as a general production workaround. Configure a trusted certificate correctly. Use managed identity or another approved credential mechanism where appropriate.

10. SqlConnection

SqlConnection represents a connection to a SQL Server database. It uses a connection string to identify the target and provides methods such as Open, OpenAsync and Close.

string connectionString =
    builder.Configuration.GetConnectionString("StoreDb")!;

await using var connection =
    new SqlConnection(connectionString);

await connection.OpenAsync();

Console.WriteLine(connection.State);

Opening a connection establishes a logical database connection. In modern applications, connection pooling usually allows the provider to reuse an underlying physical connection when a compatible connection is opened again.

Connection lifecycle

Prefer using or await using so resources are disposed even when an exception occurs. Do not keep one global connection open for the lifetime of a web application.

11. SqlCommand

SqlCommand represents a SQL statement or stored procedure to execute against a database. It is associated with a connection and can optionally participate in a transaction.

const string sql = "SELECT COUNT(*) FROM dbo.Products;";

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new SqlCommand(sql, connection);

int productCount = Convert.ToInt32(
    await command.ExecuteScalarAsync());

Commands can execute text queries, data modification statements and stored procedures. The execution method should match the shape of the result you need.

12. Command Execution Methods

ADO.NET provides different execution methods for different result types.

Method	Use it when	Typical result
`ExecuteReader()`	The query returns rows.	`SqlDataReader`
`ExecuteScalar()`	You need one value, usually from the first column of the first row.	`object?`
`ExecuteNonQuery()`	You execute INSERT, UPDATE, DELETE or other commands where a rowset is not needed.	Number of affected rows in common DML scenarios

ExecuteScalar example

const string sql = "SELECT COUNT(*) FROM dbo.Products;";

await using var command = new SqlCommand(sql, connection);
int count = Convert.ToInt32(await command.ExecuteScalarAsync());

ExecuteNonQuery example

const string sql =
    "UPDATE dbo.Products SET IsActive = 0 WHERE Id = @id;";

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@id", SqlDbType.Int).Value = 12;

int affectedRows = await command.ExecuteNonQueryAsync();

ExecuteReader example

const string sql = "SELECT Id, Name FROM dbo.Products;";

await using var command = new SqlCommand(sql, connection);
await using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    int id = reader.GetInt32(0);
    string name = reader.GetString(1);
}

13. Parameterized Queries

Parameters send values separately from the SQL command text. They help prevent SQL injection and allow the provider to handle values using the appropriate database types.

Unsafe string concatenation

// Do not build SQL by concatenating user input.
string sql = "SELECT * FROM dbo.Users WHERE Email = '"
             + email + "'";

Safe parameterized query

const string sql =
    "SELECT Id, Email FROM dbo.Users WHERE Email = @email;";

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@email", SqlDbType.NVarChar, 256).Value = email;

await using var reader = await command.ExecuteReaderAsync();

The parameter is a value, not executable SQL text.

Use explicit parameter types and sizes where practical. Parameters cannot replace SQL identifiers such as table names or column names; if dynamic identifiers are required, select them from a strict allow-list.

14. SqlDataReader

SqlDataReader provides a forward-only, read-only stream of rows. It is commonly used when an application needs to process query results efficiently without loading everything into a DataSet.

const string sql =
    "SELECT Id, Name, Price FROM dbo.Products WHERE IsActive = @active;";

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@active", SqlDbType.Bit).Value = true;

await using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    var product = new Product
    {
        Id = reader.GetInt32(reader.GetOrdinal("Id")),
        Name = reader.GetString(reader.GetOrdinal("Name")),
        Price = reader.GetDecimal(reader.GetOrdinal("Price"))
    };
}

Important reader characteristics

Rows are read sequentially by calling Read or ReadAsync.
The reader is forward-only and read-only.
The connection remains occupied while the reader is active.
Dispose the reader promptly before reusing the connection for another operation.
Check for database NULL values with IsDBNull before using typed getters on nullable columns.

int descriptionOrdinal = reader.GetOrdinal("Description");

string? description = reader.IsDBNull(descriptionOrdinal)
    ? null
    : reader.GetString(descriptionOrdinal);

15. Insert, Update and Delete

Most business applications need the four basic data operations: create, read, update and delete (CRUD). ADO.NET uses commands and parameters to perform these operations.

Insert

const string sql = """
    INSERT INTO dbo.Products (Name, Price)
    VALUES (@name, @price);
    """;

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@name", SqlDbType.NVarChar, 120).Value = "Keyboard";
command.Parameters.Add("@price", SqlDbType.Decimal).Value = 2499m;

int rows = await command.ExecuteNonQueryAsync();

Update

const string sql = """
    UPDATE dbo.Products
    SET Name = @name, Price = @price
    WHERE Id = @id;
    """;

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@name", SqlDbType.NVarChar, 120).Value = "Mechanical Keyboard";
command.Parameters.Add("@price", SqlDbType.Decimal).Value = 3299m;
command.Parameters.Add("@id", SqlDbType.Int).Value = 10;

int rows = await command.ExecuteNonQueryAsync();

Delete

const string sql = "DELETE FROM dbo.Products WHERE Id = @id;";

await using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@id", SqlDbType.Int).Value = 10;

int rows = await command.ExecuteNonQueryAsync();

Always validate inputs and apply authorization before executing a data modification. A successful command execution does not prove that the caller was allowed to modify that record.

16. Stored Procedures

A stored procedure is a named database routine that can contain SQL statements, parameters and control-flow logic. ADO.NET can execute a stored procedure by setting the command type and supplying its parameters.

await using var command =
    new SqlCommand("dbo.GetProductById", connection);

command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ProductId", SqlDbType.Int).Value = 10;

await using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    string name = reader.GetString(reader.GetOrdinal("Name"));
}

Input and output parameters

Input parameters provide values to a procedure. Output parameters allow the procedure to return a value through a parameter after execution.

await using var command =
    new SqlCommand("dbo.GetProductCount", connection);

command.CommandType = CommandType.StoredProcedure;

var output = new SqlParameter("@TotalCount", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};

command.Parameters.Add(output);

await command.ExecuteNonQueryAsync();

int totalCount = (int)output.Value;

Use the stored-procedure contract defined by your database team. Match parameter names, SQL types, sizes and directions exactly.

17. SqlParameter and Data Types

Parameters should match the database column or procedure parameter type. Explicit typing improves clarity and avoids unwanted implicit conversions or unexpected size inference.

SQL Server type	ADO.NET type	Common .NET type
`int`	`SqlDbType.Int`	`int`
`bigint`	`SqlDbType.BigInt`	`long`
`nvarchar`	`SqlDbType.NVarChar`	`string`
`bit`	`SqlDbType.Bit`	`bool`
`decimal`	`SqlDbType.Decimal`	`decimal`
`datetime2`	`SqlDbType.DateTime2`	`DateTime`
`uniqueidentifier`	`SqlDbType.UniqueIdentifier`	`Guid`

For nullable database values, use DBNull.Value when assigning a SQL NULL through a parameter:

command.Parameters.Add("@description", SqlDbType.NVarChar, 500)
    .Value = description is null ? DBNull.Value : description;

Remember that C# null and database NULL are related concepts but are not the same runtime value in ADO.NET parameter assignment.

18. DataAdapter and DataSet

DataAdapter acts as a bridge between a data source and an in-memory DataSet or DataTable. It can fill in-memory tables and, when configured with appropriate commands, send changes back to the database.

var dataSet = new DataSet();

await using var connection = new SqlConnection(connectionString);

using var adapter = new SqlDataAdapter(
    "SELECT Id, Name, Price FROM dbo.Products", connection);

adapter.Fill(dataSet, "Products");

DataTable products = dataSet.Tables["Products"]!;

DataSet can contain multiple tables, relations and constraints. It is useful when an application needs a disconnected, in-memory representation of related data.

When to use it: Consider DataSet/DataAdapter for legacy applications, desktop data binding, in-memory relational operations or workflows that genuinely need disconnected editing. For many modern Web APIs, a reader or a higher-level data-access library is a simpler fit.

19. DataTable, DataRow and DataView

A DataTable represents an in-memory table with columns and rows. A DataRow represents one row, and a DataView provides a customizable view for sorting and filtering rows.

var table = new DataTable("Products");

table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Price", typeof(decimal));

table.Rows.Add(1, "Keyboard", 2499m);
table.Rows.Add(2, "Mouse", 899m);

DataView view = new DataView(table)
{
    RowFilter = "Price > 1000",
    Sort = "Name ASC"
};

These objects are useful for in-memory data manipulation and binding scenarios. They are not replacements for database indexes or SQL filtering when the data set is large.

20. ADO.NET Transactions

A transaction groups multiple database operations into one logical unit. The operations are committed together when successful or rolled back when the workflow fails.

For example, transferring money between two accounts requires both the debit and credit to succeed. A transaction prevents the database from keeping only one side of the transfer.

A transaction protects the consistency of a group of related database changes.

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using var transaction =
    (SqlTransaction)await connection.BeginTransactionAsync();

try
{
    await using var debit = new SqlCommand(
        "UPDATE dbo.Accounts SET Balance = Balance - @amount WHERE Id = @id",
        connection, transaction);

    debit.Parameters.Add("@amount", SqlDbType.Decimal).Value = 500m;
    debit.Parameters.Add("@id", SqlDbType.Int).Value = 1;
    await debit.ExecuteNonQueryAsync();

    await using var credit = new SqlCommand(
        "UPDATE dbo.Accounts SET Balance = Balance + @amount WHERE Id = @id",
        connection, transaction);

    credit.Parameters.Add("@amount", SqlDbType.Decimal).Value = 500m;
    credit.Parameters.Add("@id", SqlDbType.Int).Value = 2;
    await credit.ExecuteNonQueryAsync();

    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Keep transactions short. Do not hold database locks while waiting for user input, making unrelated network calls or performing lengthy calculations.

21. Asynchronous Database Operations

Database work is I/O-bound: the application sends a request to a database server and waits for a response. Asynchronous APIs allow a .NET thread to return to the runtime while the I/O operation is pending, which is especially important for scalable web applications.

Synchronous API	Asynchronous API
`Open()`	`OpenAsync()`
`ExecuteReader()`	`ExecuteReaderAsync()`
`ExecuteNonQuery()`	`ExecuteNonQueryAsync()`
`ExecuteScalar()`	`ExecuteScalarAsync()`
`Read()`	`ReadAsync()`

public async Task> GetProductsAsync(
    CancellationToken cancellationToken)
{
    var products = new List();

    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync(cancellationToken);

    const string sql =
        "SELECT Id, Name, Price FROM dbo.Products;";

    await using var command = new SqlCommand(sql, connection);
    await using var reader =
        await command.ExecuteReaderAsync(cancellationToken);

    while (await reader.ReadAsync(cancellationToken))
    {
        products.Add(new Product
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        });
    }

    return products;
}

Use async database APIs in ASP.NET Core request paths and pass cancellation tokens through the database calls. Async does not make the SQL query itself faster; it improves how the application handles waiting for I/O.

22. Connection Pooling and Lifetime

Opening a database connection for every request may sound expensive, but SQL Server providers commonly use connection pooling. When a connection is closed or disposed, its underlying physical connection can be returned to a pool for reuse.

Use a short-lived connection per unit of work.
Dispose connections, commands and readers promptly.
Use consistent connection strings when you intend operations to share a pool.
Do not share one SqlConnection concurrently across requests or threads.
Investigate pool exhaustion, long-running queries and leaked readers if connection waits increase.

23. Exceptions, Timeouts and Cancellation

Database operations can fail because of network issues, invalid SQL, authentication problems, constraint violations, unavailable servers or command timeouts. Handle failures at an appropriate application boundary and log useful diagnostic context without exposing secrets.

try
{
    await connection.OpenAsync(cancellationToken);

    await using var command = new SqlCommand(sql, connection)
    {
        CommandTimeout = 30
    };

    await command.ExecuteNonQueryAsync(cancellationToken);
}
catch (SqlException ex)
{
    logger.LogError(ex, "Database operation failed.");
    throw;
}

Control	Purpose
Connection timeout	Limits the wait while establishing a connection or obtaining one from the pool.
Command timeout	Limits how long a command may execute before timing out.
CancellationToken	Allows the caller to request cancellation of pending asynchronous work.

Cancellation is cooperative and may not stop server-side work instantly. Use sensible timeouts and understand which operations are safe to retry. Do not blindly retry non-idempotent writes.

24. Bulk Copy with SqlBulkCopy

SqlBulkCopy is designed to efficiently load a large number of rows into a SQL Server table. It is often more suitable than executing one INSERT command per row when importing large data sets.

using var bulkCopy = new SqlBulkCopy(connection)
{
    DestinationTableName = "dbo.ImportedProducts",
    BatchSize = 1000
};

bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Price", "Price");

await bulkCopy.WriteToServerAsync(dataTable);

Bulk copy is useful for data imports, ETL-style workloads and large batch transfers. Validate incoming data, define column mappings deliberately and consider transaction behavior and batch sizing for the workload.

25. Security and Performance

Security essentials

Use parameterized queries for values; never concatenate untrusted input into SQL.
Keep connection strings and credentials out of source control.
Use least-privilege database accounts.
Use encrypted connections and validate server certificates.
Do not expose raw SQL errors, credentials or connection strings to API clients.
Validate input and enforce authorization before data changes.
Allow-list dynamic table names, column names and sort directions when they cannot be parameterized.

Performance essentials

Select only the columns the application needs.
Filter and paginate in SQL instead of loading unnecessary rows.
Use DataReader for efficient forward-only reading.
Use asynchronous I/O for scalable request handling.
Keep transactions short and dispose resources promptly.
Measure query plans, indexes, network transfer and application allocations.
Use bulk copy for suitable large imports.

Important: ADO.NET parameters protect values, not arbitrary SQL fragments. For dynamic ordering or identifiers, map user choices to a fixed set of known SQL fragments.

26. ADO.NET in ASP.NET Core Architecture

In a layered ASP.NET Core application, controllers should not become a place for long SQL statements and database plumbing. A common design places data-access code in a repository or dedicated data service and keeps HTTP concerns in the API layer.

One possible layering approach for an ASP.NET Core API using ADO.NET directly.

Use dependency injection to provide a connection factory or repository. Keep SQL parameterized, return application-friendly models, and avoid leaking provider-specific types into unrelated layers unless there is a clear reason.

27. ADO.NET vs Dapper vs EF Core

These technologies solve related but different problems. The best choice depends on the amount of control, abstraction and productivity your application needs.

Area	ADO.NET	Dapper	EF Core
Abstraction	Low-level data-access APIs	Micro-ORM over ADO.NET	Full ORM
SQL control	Direct	Direct SQL with mapping helpers	LINQ and generated SQL; raw SQL also available
Object mapping	Usually written by developer	Maps query results to objects	Tracks and maps entities
Change tracking	Manual	Not provided as an EF-style unit of work	Built-in for tracked entities
Best fit	Fine-grained control and foundational learning	Lightweight SQL-centric applications	Domain models, LINQ queries and change tracking

Understanding ADO.NET makes it easier to reason about Dapper and EF Core. In real projects, choose based on requirements rather than assuming one tool is always superior.

28. Practical Mini Project: Product Repository

Build a small data-access component that retrieves a product by ID and inserts a product. This exercise brings together connection management, parameterized SQL, asynchronous execution and object mapping.

Product model

public sealed class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
}

Repository implementation

public sealed class ProductRepository(
    IConfiguration configuration)
{
    private readonly string _connectionString =
        configuration.GetConnectionString("StoreDb")
        ?? throw new InvalidOperationException(
            "StoreDb connection string is missing.");

    public async Task GetByIdAsync(
        int id, CancellationToken cancellationToken)
    {
        const string sql = """
            SELECT Id, Name, Price
            FROM dbo.Products
            WHERE Id = @id;
            """;

        await using var connection =
            new SqlConnection(_connectionString);

        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection);
        command.Parameters.Add("@id", SqlDbType.Int).Value = id;

        await using var reader =
            await command.ExecuteReaderAsync(cancellationToken);

        if (!await reader.ReadAsync(cancellationToken))
            return null;

        return new Product
        {
            Id = reader.GetInt32(reader.GetOrdinal("Id")),
            Name = reader.GetString(reader.GetOrdinal("Name")),
            Price = reader.GetDecimal(reader.GetOrdinal("Price"))
        };
    }

    public async Task CreateAsync(
        Product product, CancellationToken cancellationToken)
    {
        const string sql = """
            INSERT INTO dbo.Products (Name, Price)
            VALUES (@name, @price);
            """;

        await using var connection =
            new SqlConnection(_connectionString);

        await connection.OpenAsync(cancellationToken);

        await using var command = new SqlCommand(sql, connection);
        command.Parameters.Add("@name", SqlDbType.NVarChar, 120)
            .Value = product.Name;
        command.Parameters.Add("@price", SqlDbType.Decimal)
            .Value = product.Price;

        return await command.ExecuteNonQueryAsync(cancellationToken);
    }
}

builder.Services.AddScoped();

Practice tasks

Add a method to return products with pagination.
Add update and delete methods using parameters.
Return a generated identity value using OUTPUT INSERTED.Id or an appropriate scalar query.
Add validation and authorization at the service/API boundary.
Write tests for successful results, missing records and database failures.
Use a real SQL Server integration environment to verify SQL behavior.

Learning objective: Focus on understanding the complete request-to-database flow. A repository is one possible organization pattern, not a requirement for every application.

29. Common Mistakes and Troubleshooting

Problem	What to check
Cannot open SQL connection	Verify server name, database, network access, authentication and encryption configuration.
Invalid object name	Check the database selected by the connection string, schema and table name.
Parameter missing	Confirm parameter name, spelling, type and whether it was added to the command.
Conversion failed	Compare SQL column types with parameter and reader types.
Reader is already open	Dispose the current reader before issuing another command on the same connection, or use a separate connection when appropriate.
Connection pool exhausted	Check for undisposed connections/readers, long-running operations and pool configuration.
Command timeout	Inspect query plans, indexes, blocking, network latency and command timeout settings.
Unexpected NULL exception	Check `IsDBNull` before using typed reader getters.
Update changes too many rows	Review the WHERE clause and validate the target record and authorization scope.
SQL injection vulnerability	Replace concatenated values with parameters and allow-list dynamic identifiers.

30. Best Practices Checklist

Use Microsoft.Data.SqlClient for new SQL Server development.
Keep connection strings and secrets outside source code.
Open connections only when needed and dispose them promptly.
Use asynchronous APIs in asynchronous application flows.
Pass cancellation tokens through database operations.
Use parameters for all untrusted values.
Specify parameter types and sizes deliberately.
Choose the correct execution method for the expected result.
Use readers for forward-only result processing.
Use transactions for operations that must succeed or fail together.
Keep SQL and data-access responsibilities out of controllers.
Log failures with useful context but never log secrets.
Measure performance with representative data and workloads.
Test important queries against the actual database provider.

31. Interview Questions and Answers

1. What is ADO.NET?

ADO.NET is the .NET data-access technology that provides APIs for connecting to data sources, executing commands, retrieving results and modifying data.

2. What are the main components of ADO.NET?

Common components include Connection, Command, Parameter, DataReader, DataAdapter, DataSet, DataTable and Transaction.

3. What is a data provider?

A data provider implements communication with a particular data source and exposes connection, command, reader and related objects.

4. What is the difference between connected and disconnected architecture?

Connected access reads results while a connection is open, commonly using DataReader. Disconnected access loads data into in-memory structures such as DataSet and DataTable.

5. What is SqlConnection?

It represents a connection to SQL Server and provides methods to open, close and manage the connection.

6. What is SqlCommand?

It represents a SQL statement or stored procedure to execute against a database connection.

7. What is the difference between ExecuteReader, ExecuteScalar and ExecuteNonQuery?

ExecuteReader returns rows, ExecuteScalar returns a single value, and ExecuteNonQuery is used for commands where a rowset is not required.

8. What is SqlDataReader?

It provides a forward-only, read-only stream of query results and commonly processes one row at a time.

9. What is SqlDataAdapter?

It bridges a data source and disconnected DataSet or DataTable objects by filling data and applying configured updates.

10. What is a DataSet?

It is an in-memory data representation that can contain multiple DataTable objects, relations and constraints.

11. Why are parameterized queries important?

They separate values from SQL command text, helping prevent SQL injection and supporting typed parameter handling.

12. What is a transaction?

A transaction groups operations so they can be committed together or rolled back if the workflow fails.

13. What is connection pooling?

It allows the provider to reuse underlying physical connections, reducing the overhead of repeatedly establishing connections.

14. Why use asynchronous ADO.NET methods?

They allow application threads to remain available while database I/O is pending, improving scalability for I/O-bound applications.

15. What is the difference between System.Data.SqlClient and Microsoft.Data.SqlClient?

System.Data.SqlClient is the older provider namespace. Microsoft.Data.SqlClient is the separately distributed provider recommended for new SQL Server development.

16. When would you use DataReader instead of DataSet?

Use DataReader when you need efficient forward-only reading. Use DataSet when you need disconnected in-memory tables, relationships or editing.

17. What is SqlBulkCopy?

It is a SQL Server provider feature for efficiently copying large numbers of rows into a SQL Server table.

18. Does ADO.NET prevent SQL injection automatically?

No. Developers must use parameters for values and safely allow-list dynamic SQL identifiers or fragments.

19. Is ADO.NET an ORM?

No. It is a lower-level data-access model. Dapper and EF Core provide higher-level object mapping and ORM capabilities.

20. Why should database connections be disposed?

Disposal releases the logical connection and returns the underlying resource to the provider's pool when pooling is enabled.

32. Official Documentation and Further Learning

Use Microsoft Learn and the provider release notes to verify API behavior, supported frameworks and configuration details as the platform evolves.

33. Final Thought

ADO.NET teaches you how a .NET application communicates with a database at a fundamental level. Once you understand connections, commands, parameters, readers, transactions and resource lifetimes, database code becomes easier to reason about and troubleshoot.

Start with a simple parameterized query, learn to read results safely, then build CRUD operations and introduce transactions and asynchronous execution. Later, explore Dapper and EF Core with a clear understanding of the foundations they build upon.

Skills Tutorials – Learn • Build • Grow Together

Programming

ADO.NET in .NET 10 – Complete Developer Learning Guide

01. Introduction to Data Access

02. What Is ADO.NET?

What can you do with ADO.NET?

03. Where ADO.NET Fits in .NET

04. ADO.NET Architecture

Core provider objects

05. Connected and Disconnected Models

06. Data Providers and SqlClient

07. Create a .NET 10 Project

Suggested learning project structure

08. Install Microsoft.Data.SqlClient

09. Connection Strings

Store configuration outside code

10. SqlConnection

Connection lifecycle

11. SqlCommand

12. Command Execution Methods

ExecuteScalar example

ExecuteNonQuery example

ExecuteReader example

13. Parameterized Queries

Unsafe string concatenation

Safe parameterized query

14. SqlDataReader

Important reader characteristics

15. Insert, Update and Delete

Insert

Update

Delete

16. Stored Procedures

Input and output parameters

17. SqlParameter and Data Types

18. DataAdapter and DataSet

19. DataTable, DataRow and DataView

20. ADO.NET Transactions

21. Asynchronous Database Operations

22. Connection Pooling and Lifetime

23. Exceptions, Timeouts and Cancellation

24. Bulk Copy with SqlBulkCopy

25. Security and Performance

Security essentials

Performance essentials

26. ADO.NET in ASP.NET Core Architecture

27. ADO.NET vs Dapper vs EF Core

28. Practical Mini Project: Product Repository

Product model

Repository implementation

Practice tasks

29. Common Mistakes and Troubleshooting

30. Best Practices Checklist

31. Interview Questions and Answers

32. Official Documentation and Further Learning

33. Final Thought