ASP.NET Core Web API · Data

ADO.NET Integration

Some API endpoints need full control over SQL — a high-traffic checkout, a legacy schema, a hand-tuned query. ADO.NET gives you that control. Here the ShopSmart API places orders with plain SqlClient, inside a transaction that either reserves all items or none.

Intermediate 9 min Lesson 12 of 37Examples target .NET 9.

Before you start:Service Layer ASP.NET Core MVC: ADO.NET

0/37 in ASP.NET Core Web API

On this page · 9 min · Intermediate

Interview questions

In simple words#

Using ADO.NET in an API is cooking from scratch: you write each SQL query yourself — fast and fully controlled.

Repository: Holds the SQL code.
Parameters: Prevent SQL injection.
async: Use ExecuteReaderAsync in APIs.

Learning objectives#

Inject a data class that reads its connection string from configuration
Read rows with SqlDataReader into DTOs
Run several commands in one SqlTransaction
Pass the request’s CancellationToken down to the database

Explanation#

Register a data class (scoped or singleton — it only holds a connection string) and inject IConfiguration or an options class to get the connection string.

Open a pooled SqlConnection per operation with await using, run parameterised SqlCommands, and map rows from SqlDataReader into DTOs.

A transaction makes several commands atomic: BeginTransactionAsync, pass the transaction to every command, then CommitAsync — or RollbackAsync if any step fails. Here, UPDATE … WHERE Stock >= @quantity reserves stock safely even with concurrent orders.

Accept a CancellationToken in actions (ASP.NET Core binds it to the request) and pass it to OpenAsync, ExecuteReaderAsync and friends. If the client disconnects, the database work stops too.

Return problem details for expected failures (not enough stock → 409) and let the transaction guarantee nothing partial was saved.

Syntax#

C# Illustrative

1await using var connection = new SqlConnection(connectionString);
2await connection.OpenAsync(ct);
3await using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(ct);
4await using var command = new SqlCommand(sql, connection, transaction);
5…
6await transaction.CommitAsync(ct);

Code example: Placing orders with SqlClient and a transaction#

C#Data/SqlOrderData.cs Illustrative

1// appsettings.json
2{
3  "ConnectionStrings": {
4    "Shop": "Server=(localdb)\\mssqllocaldb;Database=ShopSmartApi_Ado;Trusted_Connection=True;TrustServerCertificate=True"
5  }
6}
7

8// Program.cs
9using System.Net.Http.Json;
10using Microsoft.Data.SqlClient;
11

12var builder = WebApplication.CreateBuilder(args);
13builder.Logging.ClearProviders();                                    // keep the demo output clean
14builder.Services.AddControllers();
15builder.Services.AddScoped<SqlOrderData>();
16var app = builder.Build();
17

18// Demo setup: a fresh database with two products
19const string Master = @"Server=(localdb)\mssqllocaldb;Database=master;Trusted_Connection=True;TrustServerCertificate=True";
20await using (var setup = new SqlConnection(Master))
21{
22    await setup.OpenAsync();
23    await new SqlCommand("DROP DATABASE IF EXISTS ShopSmartApi_Ado; CREATE DATABASE ShopSmartApi_Ado;", setup).ExecuteNonQueryAsync();
24    setup.ChangeDatabase("ShopSmartApi_Ado");
25    await new SqlCommand("""
26        CREATE TABLE Products (Id INT PRIMARY KEY, Name NVARCHAR(100) NOT NULL, Price DECIMAL(10,2) NOT NULL, Stock INT NOT NULL);
27        CREATE TABLE Orders (Id INT IDENTITY PRIMARY KEY, Email NVARCHAR(200) NOT NULL, Total DECIMAL(12,2) NOT NULL,
28                             PlacedAt DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME());
29        INSERT INTO Products VALUES (1, 'Wireless Mouse', 799, 10), (2, 'USB-C Hub', 1499, 5);
30        """, setup).ExecuteNonQueryAsync();
31}
32

33app.MapControllers();
34app.Urls.Add("http://127.0.0.1:5212");
35await app.StartAsync();
36

37using var http = new HttpClient { BaseAddress = new Uri("http://127.0.0.1:5212") };
38async Task ShowStock() => Console.WriteLine(
quot;Stock: {string.Join(", ",
39    (await http.GetFromJsonAsync<List<ProductDto>>("/api/products"))!.Select(p => 
quot;{p.Name} {p.Stock}"))}");
40

41await ShowStock();
42var ok = await http.PostAsJsonAsync("/api/orders", new { email = "asha@example.com", lines = new[] { new { productId = 1, quantity = 2 }, new { productId = 2, quantity = 1 } } });
43Console.WriteLine(
quot;Order 1: {(int)ok.StatusCode} {await ok.Content.ReadAsStringAsync()}");
44var tooMany = await http.PostAsJsonAsync("/api/orders", new { email = "ravi@example.com", lines = new[] { new { productId = 1, quantity = 1 }, new { productId = 2, quantity = 9 } } });
45Console.WriteLine(
quot;Order 2: {(int)tooMany.StatusCode} (stock for product 1 was reserved, then rolled back)");
46await ShowStock();
47

48await app.StopAsync();
49SqlConnection.ClearAllPools();
50await using (var cleanup = new SqlConnection(Master))
51{
52    await cleanup.OpenAsync();
53    await new SqlCommand("DROP DATABASE ShopSmartApi_Ado;", cleanup).ExecuteNonQueryAsync();
54}
55

56// Data/SqlOrderData.cs
57using System.Data;
58using Microsoft.Data.SqlClient;
59

60public record ProductDto(int Id, string Name, decimal Price, int Stock);
61public record OrderLineRequest(int ProductId, int Quantity);
62public record PlaceOrderRequest(string Email, List<OrderLineRequest> Lines);
63

64public class SqlOrderData(IConfiguration configuration)
65{
66    private readonly string _connectionString = configuration.GetConnectionString("Shop")!;
67

68    public async Task<List<ProductDto>> GetProductsAsync(CancellationToken ct)
69    {
70        await using var connection = new SqlConnection(_connectionString);
71        await connection.OpenAsync(ct);
72        await using var command = new SqlCommand("SELECT Id, Name, Price, Stock FROM Products ORDER BY Id", connection);
73        await using var reader = await command.ExecuteReaderAsync(ct);
74        var products = new List<ProductDto>();
75        while (await reader.ReadAsync(ct))
76            products.Add(new ProductDto(reader.GetInt32(0), reader.GetString(1), reader.GetDecimal(2), reader.GetInt32(3)));
77        return products;
78    }
79

80    // Returns the new order id, or null when any product lacks stock (nothing is saved then).
81    public async Task<int?> PlaceOrderAsync(PlaceOrderRequest request, CancellationToken ct)
82    {
83        await using var connection = new SqlConnection(_connectionString);
84        await connection.OpenAsync(ct);
85        await using var transaction = (SqlTransaction)await connection.BeginTransactionAsync(ct);
86

87        decimal total = 0;
88        foreach (var line in request.Lines)
89        {
90            await using var reserve = new SqlCommand("""
91                UPDATE Products SET Stock = Stock - @quantity
92                OUTPUT INSERTED.Price
93                WHERE Id = @id AND Stock >= @quantity
94                """, connection, transaction);
95            reserve.Parameters.Add("@quantity", SqlDbType.Int).Value = line.Quantity;
96            reserve.Parameters.Add("@id", SqlDbType.Int).Value = line.ProductId;
97            if (await reserve.ExecuteScalarAsync(ct) is not decimal price)
98            {
99                await transaction.RollbackAsync(ct);                  // undo the lines already reserved
100                return null;
101            }
102            total += price * line.Quantity;
103        }
104

105        await using var insert = new SqlCommand(
106            "INSERT INTO Orders (Email, Total) OUTPUT INSERTED.Id VALUES (@email, @total)", connection, transaction);
107        insert.Parameters.Add("@email", SqlDbType.NVarChar, 200).Value = request.Email;
108        insert.Parameters.Add("@total", SqlDbType.Decimal).Value = total;
109        var orderId = (int)(await insert.ExecuteScalarAsync(ct))!;
110

111        await transaction.CommitAsync(ct);
112        return orderId;
113    }
114}
115

116// Controllers/ShopController.cs
117using Microsoft.AspNetCore.Mvc;
118

119[ApiController]
120[Route("api")]
121public class ShopController(SqlOrderData data) : ControllerBase
122{
123    [HttpGet("products")]
124    public Task<List<ProductDto>> Products(CancellationToken ct) => data.GetProductsAsync(ct);
125

126    [HttpPost("orders")]
127    public async Task<IActionResult> PlaceOrder(PlaceOrderRequest request, CancellationToken ct) =>
128        await data.PlaceOrderAsync(request, ct) is { } id
129            ? Created(
quot;/api/orders/{id}", new { id })
130            : Problem(statusCode: StatusCodes.Status409Conflict, title: "Not enough stock", detail: "No changes were made.");
131}

Expected output

Stock: Wireless Mouse 10, USB-C Hub 5
Order 1: 201 {"id":1}
Order 2: 409 (stock for product 1 was reserved, then rolled back)
Stock: Wireless Mouse 8, USB-C Hub 4

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

SqlOrderData reads the connection string from configuration and opens a pooled connection per call.
The first order reserves stock for both lines, inserts the order and commits.
The second order reserves product 1, then cannot reserve 9 hubs; RollbackAsync undoes the first reservation.
The API answers 409 with problem details, and the stock list proves nothing changed.
CancellationToken parameters flow from ASP.NET Core into every database call.

Real-world use case#

ShopSmart’s flash-sale checkout uses exactly this pattern: an atomic conditional UPDATE per line inside a transaction, so thousands of concurrent buyers can never oversell the 500 discounted items.

Common mistakes#

Checking stock with SELECT, then updating

Between the SELECT and the UPDATE another request can buy the last item. Make the check part of the UPDATE (WHERE Stock >= @quantity).

Forgetting to pass the transaction to commands

A command without the transaction fails (“ExecuteReader requires the command to have a transaction”) or runs outside it.

Ignoring CancellationToken

Without it, abandoned requests keep the database busy. Pass it everywhere.

Best practices#

Connection string from configuration
Parameterised commands only
Transactions for multi-step writes
CancellationToken on every async call

Remember this#

Begin → commands with the transaction → Commit / Rollback.

Conditional UPDATE = race-free stock reservation.

CancellationToken in, CancellationToken down.

Practice exercise#

Your task: Add GET /api/orders/{id} that returns { id, email, total } using ExecuteReaderAsync, or 404 when the order does not exist.

Need a hint?

SELECT Id, Email, Total FROM Orders WHERE Id = @id, then if (!await reader.ReadAsync(ct)) return NotFound();

Show solution

C# Illustrative

1// Controllers/OrdersController.cs
2using System.Data;
3using Microsoft.AspNetCore.Mvc;
4using Microsoft.Data.SqlClient;
5

6[ApiController]
7[Route("api/orders")]
8public class OrdersController(IConfiguration configuration) : ControllerBase
9{
10    [HttpGet("{id:int}")]
11    public async Task<IActionResult> Get(int id, CancellationToken ct)
12    {
13        await using var connection = new SqlConnection(configuration.GetConnectionString("Shop"));
14        await connection.OpenAsync(ct);
15        await using var command = new SqlCommand("SELECT Id, Email, Total FROM Orders WHERE Id = @id", connection);
16        command.Parameters.Add("@id", SqlDbType.Int).Value = id;
17        await using var reader = await command.ExecuteReaderAsync(ct);
18        if (!await reader.ReadAsync(ct)) return NotFound();
19        return Ok(new { id = reader.GetInt32(0), email = reader.GetString(1), total = reader.GetDecimal(2) });
20    }
21}

Mini quiz#

ADO.NET Integration quizMultiple choice Question 1 of 3

Why reserve stock with UPDATE … WHERE Stock >= @quantity instead of SELECT then UPDATE?

Score so far: 0/0

Want more? Practise all ASP.NET Core Web API MCQs · ASP.NET Core Web API interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.

On this page

Related tutorials

Interview questions

In simple words#

Learning objectives#

Explanation#

Syntax#

Code example: Placing orders with SqlClient and a transaction#

Step-by-step explanation#

Real-world use case#

Common mistakes#

Best practices#

Remember this#

Practice exercise#

Mini quiz#

Related topics#

My notes#