Calling SQL Server from C#: ADO.NET, Dapper and EF Core
Use BuildMart from a C# program three ways: ADO.NET with SqlConnection, SqlCommand and parameters; Dapper, which maps rows to objects; and Entity Framework Core with LINQ, change tracking and the SQL it generates. Plus stored procedures, transactions, SQL injection in C#, and the N + 1 query problem.
| Library | What it is | You write |
|---|---|---|
Microsoft.Data.SqlClient) | ||
dotnet new console -n ShopToolscd ShopToolsdotnet add package Microsoft.Data.SqlClientdotnet add package Dapperdotnet add package Microsoft.EntityFrameworkCore.SqlServerThe connection string
Server=localhost(or.\SQLEXPRESSfor SQL Server Express): the server.Database=BuildMart: the database to start in.Integrated Security=True: log in as the Windows user running the program, so no password is needed.TrustServerCertificate=True: the driver encrypts connections by default and checks the server’s certificate. A development server has a self-signed one, so this accepts it. On a real server, install a proper certificate instead.
ADO.NET: connection, command, reader
SqlConnection, create a SqlCommand with the SQL, add its parameters, and read the rows with a SqlDataReader:
using System.Data;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString);await connection.OpenAsync(); await using var command = new SqlCommand( """ SELECT Name, Price FROM dbo.Products WHERE Price < @MaxPrice ORDER BY Price, Name; """, connection);command.Parameters.Add(new SqlParameter("@MaxPrice", SqlDbType.Decimal) { Precision = 10, Scale = 2, Value = 10m }); await using var reader = await command.ExecuteReaderAsync();while (await reader.ReadAsync()){ string name = reader.GetString(0); decimal price = reader.GetDecimal(1); Console.WriteLine($"{name,-18} {price,6}");}Sand 25 kg 4.10 Wall plugs (100) 4.50 Safety goggles 6.80 Timber 2x4 2.4 m 6.80 Cement 25 kg 7.20 Tape measure 8 m 9.75
await usingcloses the reader, command and connection when they go out of scope, even if an exception is thrown. A connection left open is a leak.Closing a connection does not really disconnect. The driver keeps it in a connection pool and hands it to the next OpenAsyncwith the same connection string, so open late and close early: it is cheap.GetString(0),GetDecimal(1)read columns by position, typed. A NULL column must be checked withIsDBNullfirst.The Asyncmethods free the thread while SQL Server works, which matters in web applications that serve many requests at once.
ExecuteScalarAsync returns the first column of the first row, and ExecuteNonQueryAsync runs an INSERT, UPDATE or DELETE and returns the number of rows changed:
using System.Data;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString);await connection.OpenAsync(); await using var count = new SqlCommand("SELECT COUNT(*) FROM dbo.Products WHERE Stock = 0;", connection);int outOfStock = (int)(await count.ExecuteScalarAsync())!;Console.WriteLine($"Out of stock: {outOfStock}"); await using var restock = new SqlCommand("UPDATE dbo.Products SET Stock = Stock + @Qty WHERE Stock = 0;", connection);restock.Parameters.Add("@Qty", SqlDbType.Int).Value = 25;int changed = await restock.ExecuteNonQueryAsync();Console.WriteLine($"Restocked: {changed} products");Out of stock: 2 Restocked: 2 products
Parameters, always
using System.Data;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString);await connection.OpenAsync(); string input = "x' OR 1 = 1 --"; // typed into a search box // Wrong: the input becomes part of the SQL.await using var bad = new SqlCommand( $"SELECT COUNT(*) FROM dbo.Customers WHERE LastName = '{input}';", connection);Console.WriteLine($"Interpolated: {await bad.ExecuteScalarAsync()} customers"); // Right: the SQL is fixed; the input travels separately as a value.await using var good = new SqlCommand( "SELECT COUNT(*) FROM dbo.Customers WHERE LastName = @LastName;", connection);good.Parameters.Add("@LastName", SqlDbType.NVarChar, 40).Value = input;Console.WriteLine($"Parameterized: {await good.ExecuteScalarAsync()} customers");Interpolated: 8 customers Parameterized: 0 customers
Stored procedures and errors
CommandType.StoredProcedure and no SQL text. An output parameter needs its direction set. Here is a small procedure that places a one-line order or refuses with error 50001:
CREATE OR ALTER PROCEDURE dbo.AddOrder @CustomerId int, @ProductId int, @Quantity int, @OrderId int OUTPUTASBEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRANSACTION; UPDATE dbo.Products SET Stock = Stock - @Quantity WHERE ProductId = @ProductId AND Stock >= @Quantity; IF @@ROWCOUNT = 0 THROW 50001, 'Not enough stock.', 1; INSERT INTO dbo.Orders (CustomerId, OrderDate, Status) VALUES (@CustomerId, '2024-06-25', 'Pending'); SET @OrderId = SCOPE_IDENTITY(); INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice) SELECT @OrderId, ProductId, @Quantity, Price FROM dbo.Products WHERE ProductId = @ProductId; COMMIT;END;using System.Data;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString);await connection.OpenAsync(); await TryOrder(3, 2, 1); // one drillawait TryOrder(3, 9, 5); // five boxes of wall plugs: none in stock async Task TryOrder(int customerId, int productId, int quantity){ await using var cmd = new SqlCommand("dbo.AddOrder", connection) { CommandType = CommandType.StoredProcedure }; cmd.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId; cmd.Parameters.Add("@ProductId", SqlDbType.Int).Value = productId; cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity; SqlParameter orderId = cmd.Parameters.Add("@OrderId", SqlDbType.Int); orderId.Direction = ParameterDirection.Output; try { await cmd.ExecuteNonQueryAsync(); Console.WriteLine($"Order {orderId.Value} placed"); } catch (SqlException ex) when (ex.Number == 50001) { Console.WriteLine($"Refused (error {ex.Number}): {ex.Message}"); }}Order 11 placed Refused (error 50001): Not enough stock.
SqlException. ex.Number is the error number, so the program can react to the ones it expects (50001 here, or 1205 for a deadlock victim worth retrying) and let the rest propagate.
Transactions from C#
using System.Data;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString);await connection.OpenAsync(); await using (var tx = (SqlTransaction)await connection.BeginTransactionAsync()){ try { await using var order = new SqlCommand( "INSERT INTO dbo.Orders (CustomerId, OrderDate, Status) OUTPUT inserted.OrderId VALUES (@C, @D, 'Pending');", connection, tx); order.Parameters.Add("@C", SqlDbType.Int).Value = 5; order.Parameters.Add("@D", SqlDbType.Date).Value = new DateTime(2024, 6, 22); int orderId = (int)(await order.ExecuteScalarAsync())!; Console.WriteLine($"Inserted order {orderId}"); await using var line = new SqlCommand( "INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice) VALUES (@O, @P, 1, 9.75);", connection, tx); line.Parameters.Add("@O", SqlDbType.Int).Value = orderId; line.Parameters.Add("@P", SqlDbType.Int).Value = 99; // no such product await line.ExecuteNonQueryAsync(); await tx.CommitAsync(); } catch (SqlException ex) { await tx.RollbackAsync(); Console.WriteLine($"Rolled back after error {ex.Number}"); }} await using var check = new SqlCommand("SELECT COUNT(*) FROM dbo.Orders;", connection);Console.WriteLine($"Orders in the table: {await check.ExecuteScalarAsync()}");Inserted order 11 Rolled back after error 547 Orders in the table: 10
catch, which rolled it back: still 10 orders. If the code forgot to commit or roll back, disposing the transaction at the end of its using block rolls it back.
Dapper: your SQL, mapped to objects
QueryAsync<T> to the connection: you give it SQL and an object whose properties are the parameters, and it creates one T per row, matching columns to properties (or constructor parameters) by name:
using Dapper;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString); // Dapper opens it when needed var cheap = await connection.QueryAsync<ProductRow>( "SELECT ProductId, Name, Price FROM dbo.Products WHERE Price < @MaxPrice ORDER BY Price, Name;", new { MaxPrice = 10m });foreach (var p in cheap) Console.WriteLine($"{p.ProductId,2} {p.Name,-18} {p.Price,6}"); int tools = await connection.ExecuteScalarAsync<int>( "SELECT COUNT(*) FROM dbo.Products WHERE CategoryId = @Id;", new { Id = 1 });Console.WriteLine($"Tools: {tools}"); int repriced = await connection.ExecuteAsync( "UPDATE dbo.Products SET Price = Price * 1.05 WHERE CategoryId = @Id;", new { Id = 5 });Console.WriteLine($"Repriced: {repriced}"); record ProductRow(int ProductId, string Name, decimal Price);5 Sand 25 kg 4.10 9 Wall plugs (100) 4.50 12 Safety goggles 6.80 6 Timber 2x4 2.4 m 6.80 4 Cement 25 kg 7.20 3 Tape measure 8 m 9.75 Tools: 3 Repriced: 2
new { MaxPrice = 10m } becomes the parameter @MaxPrice. Dapper is a good fit when you like writing SQL and want little in between. Note that a C# string becomes an nvarchar parameter; for a varchar column, pass new DbString { Value = …, IsAnsi = true, Length = 10 }.
Entity Framework Core: the model
DbContext that knows how they map. For an existing database you can generate it with dotnet ef dbcontext scaffold; here it is written by hand for BuildMart’s tables:
using Microsoft.EntityFrameworkCore;using Microsoft.EntityFrameworkCore.Diagnostics;using Microsoft.Extensions.Logging; public class Category{ public int CategoryId { get; set; } public string Name { get; set; } = "";} public class Product{ public int ProductId { get; set; } public string Name { get; set; } = ""; public int? CategoryId { get; set; } public Category? Category { get; set; } public decimal Price { get; set; } public int Stock { get; set; } public string Unit { get; set; } = ""; public DateOnly AddedOn { get; set; }} public class Customer{ public int CustomerId { get; set; } public string FirstName { get; set; } = ""; public string LastName { get; set; } = ""; public string? City { get; set; } public string? Email { get; set; } public string? Phone { get; set; } public DateOnly JoinedOn { get; set; }} public class Order{ public int OrderId { get; set; } public int CustomerId { get; set; } public Customer Customer { get; set; } = null!; public DateOnly OrderDate { get; set; } public string Status { get; set; } = ""; public List<OrderItem> Items { get; } = [];} public class OrderItem{ public int OrderId { get; set; } public int ProductId { get; set; } public Product Product { get; set; } = null!; public int Quantity { get; set; } public decimal UnitPrice { get; set; }} public class BuildMartContext(Action<string>? log = null) : DbContext{ public DbSet<Category> Categories => Set<Category>(); public DbSet<Product> Products => Set<Product>(); public DbSet<Customer> Customers => Set<Customer>(); public DbSet<Order> Orders => Set<Order>(); public DbSet<OrderItem> OrderItems => Set<OrderItem>(); protected override void OnConfiguring(DbContextOptionsBuilder options) { options.UseSqlServer( "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"); if (log is not null) // show each SQL command EF Core sends options.LogTo(log, [RelationalEventId.CommandExecuted], LogLevel.Information, DbContextLoggerOptions.None); } protected override void OnModelCreating(ModelBuilder model) { model.Entity<Category>().Property(c => c.CategoryId).ValueGeneratedNever(); model.Entity<Product>().Property(p => p.Price).HasPrecision(10, 2); model.Entity<Customer>().Property(c => c.Phone).IsUnicode(false).HasMaxLength(20); model.Entity<Order>().Property(o => o.Status).IsUnicode(false).HasMaxLength(10); model.Entity<OrderItem>().HasKey(i => new { i.OrderId, i.ProductId }); model.Entity<OrderItem>().Property(i => i.UnitPrice).HasPrecision(10, 2); }}DbSet named Products maps to the table Products, a property called ProductId is the key, and CategoryId next to a Category property is the foreign key. OnModelCreating adds what conventions cannot guess, such as decimal precision, varchar columns (IsUnicode(false)), and the two-column key of OrderItems.
Querying with LINQ
ToQueryString() shows the SQL without running it:
using Microsoft.EntityFrameworkCore; await using var db = new BuildMartContext(); decimal maxPrice = 10m;var query = db.Products .Where(p => p.Price < maxPrice) .OrderBy(p => p.Price).ThenBy(p => p.Name) .Select(p => new { p.Name, Category = p.Category!.Name, p.Price }); Console.WriteLine(query.ToQueryString());Console.WriteLine(); foreach (var row in await query.ToListAsync()) Console.WriteLine($"{row.Name,-18} {row.Category,-9} {row.Price,6}");DECLARE @maxPrice decimal(10,2) = 10.0; SELECT [p].[Name], [c].[Name] AS [Category], [p].[Price] FROM [Products] AS [p] LEFT JOIN [Categories] AS [c] ON [p].[CategoryId] = [c].[CategoryId] WHERE [p].[Price] < @maxPrice ORDER BY [p].[Price], [p].[Name] Sand 25 kg Building 4.10 Wall plugs (100) Fixings 4.50 Safety goggles Safety 6.80 Timber 2x4 2.4 m Timber 6.80 Cement 25 kg Building 7.20 Tape measure 8 m Tools 9.75
Select asked for, and the C# variable sent as a parameter. EF Core never pastes values into the SQL text, so LINQ queries are safe from injection.
Changing data: tracking and SaveChanges
SaveChangesAsync, and EF Core works out the UPDATE, wrapping all changes in a transaction. For changes to many rows at once, ExecuteUpdateAsync runs a single UPDATE without loading anything. The context below logs each command it sends:
using Microsoft.EntityFrameworkCore; await using var db = new BuildMartContext(log: Console.WriteLine); Product drill = await db.Products.SingleAsync(p => p.ProductId == 2);drill.Price = 94.50m; // only a C# change so farawait db.SaveChangesAsync(); // now the UPDATE is sent int repriced = await db.Products .Where(p => p.CategoryId == 5) .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.05m));Console.WriteLine($"Repriced: {repriced}");Executed DbCommand (39ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] SELECT TOP(2) [p].[ProductId], [p].[AddedOn], [p].[CategoryId], [p].[Name], [p].[Price], [p].[Stock], [p].[Unit] FROM [Products] AS [p] WHERE [p].[ProductId] = 2 Executed DbCommand (15ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Precision = 10) (Scale = 2) (DbType = Decimal)], CommandType='Text', CommandTimeout='30'] SET IMPLICIT_TRANSACTIONS OFF; SET NOCOUNT ON; UPDATE [Products] SET [Price] = @p0 OUTPUT 1 WHERE [ProductId] = @p1; Executed DbCommand (14ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] UPDATE [p] SET [p].[Price] = [p].[Price] * 1.05 FROM [Products] AS [p] WHERE [p].[CategoryId] = 5 Repriced: 2
SingleAsync asks for TOP(2), so it can complain if two rows match), an UPDATE of only the column that changed (OUTPUT 1 lets EF Core check that one row really was updated), and one set-based UPDATE for the paint. The timings in brackets will differ on your machine. When you only read data, add .AsNoTracking() to the query to skip the tracking work.
Raw SQL in EF Core
FromSql takes an interpolated string and turns every {value} into a parameter. FromSqlRaw takes plain text, and glued-in input is injectable again:
using Microsoft.EntityFrameworkCore; await using var db = new BuildMartContext(log: Console.WriteLine);string input = "x' OR 1 = 1 --"; var safe = db.Customers.FromSql($"SELECT * FROM dbo.Customers WHERE LastName = {input}");Console.WriteLine($"FromSql: {await safe.CountAsync()} customers"); var risky = db.Customers.FromSqlRaw("SELECT * FROM dbo.Customers WHERE LastName = '" + input + "'");Console.WriteLine($"FromSqlRaw with +: {await risky.CountAsync()} customers");Executed DbCommand (43ms) [Parameters=[p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM (
SELECT * FROM dbo.Customers WHERE LastName = @p0
) AS [c]
FromSql: 0 customers
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*)
FROM (
SELECT * FROM dbo.Customers WHERE LastName = 'x' OR 1 = 1 --'
) AS [c]
FromSqlRaw with +: 8 customersp0, and no customer has that odd surname. FromSqlRaw pasted it into the SQL, where OR 1 = 1 matched everyone. EF Core wraps your SQL in a subquery to add the COUNT, and the line break after it is why the attacker’s -- comment did not swallow the closing bracket. The C# compiler also warned about the second query: EF Core’s analyzer reports EF1003 when FromSqlRaw is given a concatenated string.
The N + 1 problem
using Microsoft.EntityFrameworkCore; int commands = 0;await using var db = new BuildMartContext(log: _ => commands++); // N + 1: one query for the orders, then one more for every order.var orders = await db.Orders.OrderBy(o => o.OrderId).ToListAsync();foreach (var o in orders){ int lines = await db.OrderItems.CountAsync(i => i.OrderId == o.OrderId);}Console.WriteLine($"Loop: {orders.Count} orders, {commands} commands"); // One query: ask for exactly what you need, and let SQL Server do the work.commands = 0;var summary = await db.Orders .OrderBy(o => o.OrderId) .Select(o => new { o.OrderId, Lines = o.Items.Count, Total = o.Items.Sum(i => i.Quantity * i.UnitPrice) }) .ToListAsync();Console.WriteLine($"Projection: {summary.Count} orders, {commands} command"); foreach (var s in summary.Take(3)) Console.WriteLine($" order {s.OrderId}: {s.Lines} lines, {s.Total:0.00}");Loop: 10 orders, 11 commands Projection: 10 orders, 1 command order 1: 2 lines, 38.00 order 2: 2 lines, 113.00 order 3: 2 lines, 112.80
.Include(o => o.Items) loads the orders and their lines in one query (EF Core joins them), and in Dapper or ADO.NET, write one query with a join or GROUP BY instead of a query per row. Whatever the library, watch the number of commands, not just the SQL of each one.
Which one to use?
| ADO.NET | Dapper | EF Core | |
|---|---|---|---|
Show a hint
SELECT COALESCE(City, N'(none)') AS City, COUNT(*) AS Customers … GROUP BY City ORDER BY Customers DESC, City, and map it to a record with City and Customers.
Show the solution
using Dapper;using Microsoft.Data.SqlClient; const string connectionString = "Server=localhost;Database=BuildMart;Integrated Security=True;TrustServerCertificate=True"; await using var connection = new SqlConnection(connectionString); var cities = await connection.QueryAsync<CityCount>( """ SELECT COALESCE(City, N'(none)') AS City, COUNT(*) AS Customers FROM dbo.Customers GROUP BY City ORDER BY Customers DESC, City; """); foreach (var c in cities) Console.WriteLine($"{c.City,-10} {c.Customers}"); record CityCount(string City, int Customers);Leeds 3 York 2 (none) 1 Bradford 1 Harrogate 1
Remember
Microsoft.Data.SqlClient is the driver; Dapper and EF Core are built on it. Keep connection strings in configuration; open connections late, close them early (with using) and let pooling make that cheap. Always pass values as parameters with the right type and size; never interpolate input into SQL text. T-SQL errors arrive as SqlException; ex.Number tells you which (50001, 547, 1205 …). A SqlTransaction must be given to every command on its connection; commit, or roll back in catch. Dapper: your SQL, rows mapped to objects, parameters from an anonymous object. EF Core: LINQ becomes parameterized SQL; tracked entities are saved with SaveChanges; ExecuteUpdate changes many rows at once. Watch the number of round trips: a query inside a loop is the N + 1 problem.