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.