C# Dapper tutorial
last modified January 4, 2023
C# Dapper tutorial shows how to program databases in C# with Dapper. In this tutorial, we work with SQL Server. C# tutorial is a comprehensive tutorial on C# language.
Dapper
Dapper is a simple object mapper for the Microsoft .NET platform. It is a framework for mapping an object-oriented domain model to a traditional relational database.
Dapper's goals are code simplicity and performance.
Dapper has no DB specific implementation details, it works across all .NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server. Dapper was created by team at Stack Overflow.
$ dotnet add package dapper
To utilize Dapper, we add the package reference to the project
with the dotnet
tool.
$ dotnet add package System.Data.SqlClient
We include also the driver for the SQL Server.
CREATE TABLE cars(name, price) ( id INT identity(1,1) NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, price INT ) INSERT INTO cars(name, price) VALUES('Audi', 52642); INSERT INTO cars(name, price) VALUES('Mercedes', 57127); INSERT INTO cars(name, price) VALUES('Skoda', 9000); INSERT INTO cars(name, price) VALUES('Volvo', 29000); INSERT INTO cars(name, price) VALUES('Bentley', 350000); INSERT INTO cars(name, price) VALUES('Citroen', 21000); INSERT INTO cars(name, price) VALUES('Hummer', 41400); INSERT INTO cars(name, price) VALUES('Volkswagen', 21600);
In our examples, we use this table.
C# Dapper ExecuteScalar
The ExecuteScalar
method executes a query that selects
a single value.
using System.Data.SqlClient; using Dapper; namespace ExecuteScalarEx { class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); var version = con.ExecuteScalar<string>("SELECT @@VERSION"); Console.WriteLine(version); } } }
The example retrieves the version of the SQL Server database.
var version = con.ExecuteScalar<string>("SELECT @@VERSION");
The ExecuteScalar
method executes the SELECT @@VERSION
query, which returns a single value: the version of SQL Server.
$ dotnet run Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 10.0 <X6> (Build 18362: ) (Hypervisor)
C# Dapper Query
The Query
method executes a query and maps it to a
list of dynamic objects.
using System.Data.SqlClient; using Dapper; namespace RetrieveAll { class Car { public int Id { get; set; } public string Name { get; set; } public int Price { get; set; } public override string ToString() { return $"{Id} {Name} {Price}"; } } class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); var cars = con.Query<Car>("SELECT * FROM cars").ToList(); cars.ForEach(car => Console.WriteLine(car)); } } }
The example retrieves all rows from the cars
table.
var cars = con.Query<Car>("SELECT * FROM cars").ToList();
The Query
method executes the SELECT * FROM cars
statement and returns a list of objects.
cars.ForEach(car => Console.WriteLine(car));
We go through the list and print all elements to the console.
$ dotnet run 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
C# Dapper Execute
The Execute
method executes an SQL query
using System.Data.SqlClient; using Dapper; namespace UpdateRow { class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); int nOfRows = con.Execute("UPDATE dbo.[cars] SET [price] = 52000 WHERE [id] = 1"); Console.WriteLine("'UPDATE' affected rows: {0}", nOfRows); } } }
The example updates the price of a single car and returns the number of affected rows.
int nOfRows = con.Execute("UPDATE dbo.[cars] SET [price] = 52000 WHERE [id] = 1");
The UPDATE statement updates the price of a car. The Execute
method
returns the number of updated rows.
Console.WriteLine("'UPDATE' affected rows: {0}", nOfRows);
The number of updated rows is printed to the terminal.
$ dotnet run 'UPDATE' affected rows: 1
C# Dapper parameterized query
Parameterized queries increase security and performance. When we write parameterized queries, we use placeholders instead of directly writing the values into the queries.
using System.Data.SqlClient; using Dapper; namespace Parameterized { class Car { public int Id { get; set; } public string Name { get; set; } public int Price { get; set; } public override string ToString() { return $"{Id} {Name} {Price}"; } } class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); var car = con.QueryFirst<Car>("SELECT * FROM cars WHERE id=@id", new { id = 3 }); Console.WriteLine(car); } } }
The example selects a specific row from the table.
var car = con.QueryFirst<Car>("SELECT * FROM cars WHERE id=@id", new { id = 3 });
The QueryFirst
returns the first result of the SQL query. The
@id
is a placeholder to be filled. The second argument is the
parameter to fill the placeholder.
$ dotnet run 3 Skoda 9000
C# Dapper delete row
The following example deletes a row from the table.
using System.Data.SqlClient; using Dapper; namespace DeleteRow { class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); int delRows = con.Execute(@"DELETE FROM [cars] WHERE Id = @Id", new { Id = 1 }); if (delRows > 0) { Console.WriteLine("car deleted"); } } } }
The example deletes a row with the Execute
method.
C# Dapper DynamicParameters
The DynamicParameters
is a bag of parameters that can be passed to the
Dapper Query and Execute methods.
using System.Data; using System.Data.SqlClient; using Dapper; namespace InsertRow { class Program { static void Main(string[] args) { var cs = @"Server=localhost\SQLEXPRESS;Database=testdb;Trusted_Connection=True;"; using var con = new SqlConnection(cs); con.Open(); var query = "INSERT INTO cars(name, price) VALUES(@name, @price)"; var dp = new DynamicParameters(); dp.Add("@name", "BMW", DbType.AnsiString, ParameterDirection.Input, 255); dp.Add("@price", 36600); int res = con.Execute(query, dp); if (res > 0) { Console.WriteLine("row inserted"); } } } }
The example inserts a new row into the cars
table.
var dp = new DynamicParameters(); dp.Add("@name", "BMW", DbType.AnsiString, ParameterDirection.Input, 255); dp.Add("@price", 36600);
We dynamically provide the parameter values and their types.
int res = con.Execute(query, dp);
The dynamic parameters are passed as the second argument of the
Execute
method.
In this article, we have shown how to program databases in C# with the Dapper tool.
List all C# tutorials.