Using Dapper - C# Part 1

In this article learn how to use Dapper an open-source object-relational mapping (ORM) library for .NET and .NET Core applications. Unlike many articles out there, this one will provide source code to try everything out that is shown in an easy-to-follow way by beginning with a single table then in part two of this series will work with multiple tables.

Source code

Source code is well documented so take time to read the comments.

Clone the following GitHub repository and find source code in the following project.

🔸 Source code updated 11/3/2023

Is Dapper worth it?

Dapper is a good choice for applications that require high performance and efficiency. Dapper is also a good choice for applications that need to work with older versions of SQL Server or other databases. Dapper is also a good choice for applications that require more control over the SQL that is generated.

Is Dapper better than EF Core?

Performance: Dapper is typically more performant than EF Core due to its lightweight nature and lack of overhead. Control over SQL Queries: With Dapper, you have full control over your SQL queries, which can lead to more optimized database operations.

Note
Above two statements were stolen from the web. Could not write it any better.

Prerequisites

• Have a basic understanding of C# and SQL
• Have Microsoft Visual Studio 2022 or higher
• Familiar with and have SSMS (SQL-Server Management Studio) installed.

Table for this article

Tables for part two of this series

SQL Statements

None of the code samples have inline SQL statements, instead all statements are stored in a class.

Each statement were first created in SSMS and tested rather than writing statements in code and assume they will work which many developer do. Also, zero string concatenation, all statements are done in read-only raw string literals.

public class SqlStatements
{
    public static string InsertPeople =>
        """
        INSERT INTO dbo.Person
        (
            FirstName,
            LastName,
            BirthDate
        )
        VALUES
        (@FirstName, @LastName, @BirthDate);
        SELECT CAST(scope_identity() AS int);
        """;

}

Project type

To reach the widest audience possible, a Windows Forms project is used rather than web as the architecture is different and with console projects the code can get too much for a novice developer as I would need to create a menu system hard for some to understand.

Model

The Person class is setup for change notification using INotifyPropertyChanged interface. This is not required for Dapper but is required for the DataGridView to update when a property changes.

public class Person : INotifyPropertyChanged
{
    private int _id;
    private string _firstName;
    private string _lastName;
    private DateOnly _birthDate;

    public int Id
    {
        get => _id;
        set
        {
            if (value == _id) return;
            _id = value;
            OnPropertyChanged();
        }
    }

    public string FirstName
    {
        get => _firstName;
        set
        {
            if (value == _firstName) return;
            _firstName = value;
            OnPropertyChanged();
        }
    }

    public string LastName
    {
        get => _lastName;
        set
        {
            if (value == _lastName) return;
            _lastName = value;
            OnPropertyChanged();
        }
    }

    public DateOnly BirthDate
    {
        get => _birthDate;
        set
        {
            if (value.Equals(_birthDate)) return;
            _birthDate = value;
            OnPropertyChanged();
        }
    }

    public override string ToString() => Id.ToString();
    public event PropertyChangedEventHandler PropertyChanged;
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

Note
In the above code, change notification was done using ReSharper, not by hand.

Dapper DateOnly and TimeOnly support

Dapper does not support the new DateOnly and TimeOnly types in .NET 6.0. To get around this, a custom type handler is created.

To support DateOnly and TimeOnly the following SqlMapper.TypeHandle are provided.

public class DapperSqlDateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
{
    public override void SetValue(IDbDataParameter parameter, DateOnly date)
        => parameter.Value = date.ToDateTime(new TimeOnly(0, 0));

    public override DateOnly Parse(object value)
        => DateOnly.FromDateTime((DateTime)value);
}
public class SqlTimeOnlyTypeHandler : SqlMapper.TypeHandler<TimeOnly>
{
    public override void SetValue(IDbDataParameter parameter, TimeOnly time)
    {
        parameter.Value = time.ToString();
    }

    public override TimeOnly Parse(object value) => TimeOnly.FromTimeSpan((TimeSpan)value);
}

Usage for these is shown in the code samples below.

Basics

Let me convince you that Dapper is much better than using a connection and command objects.

Adding a collection of records

A common operation is to add several records to a database table. And let's keep out bulk method options as this is not what is being taught.

Using conventional method

• Requirements
  • Test the database connection
  • Create a connection object
  • Create a Transation object
  • Create a command object and parameters
  • Wrap code in a try/catch
  • Open the connection
  • Iterate throught a collection
  • Execute the command
  • Assign the primary key to each item in the collection.

public static void InsertPeople(List<Person> people)
{
    using var connection = new SqlConnection(ConnectionString);

    using var transaction = connection.BeginTransaction();
    using var command = connection.CreateCommand();
    command.Transaction = transaction;
    command.CommandText = SqlStatements.InsertPeople;
    command.Parameters.Add("@FirstName", SqlDbType.NVarChar);
    command.Parameters.Add("@LastName", SqlDbType.NVarChar);
    command.Parameters.Add("@BirthDate", SqlDbType.Date);
    try
    {
        connection.Open();

        foreach (var person in people)
        {
            command.Parameters["@FirstName"].Value = person.FirstName;
            command.Parameters["@LastName"].Value = person.LastName;
            command.Parameters["@BirthDate"].Value = person.BirthDate;
            person.Id = (int)command.ExecuteScalar();
        }
        transaction.Commit();
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        throw new Exception(ex.Message);
    }
}

Now using Dapper

Notice, less code which means less time to write and less prone to errors.

• No command object
• No command parameters which many developers get wrong in the prior code sample so this means less prone to errors.

Note
The connection must be opened manually while in other examples below Dapper handles opening a connection and closing the connection for us.

public static async Task<(bool, Exception ex)> AddRangeDapperWithKeys(List<Person> list)
{

    var (_, sqlException) = await CanConnect();
    if (sqlException is not null)
    {
        return (false, sqlException);
    }

    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    try
    {
        await using SqlConnection cn = new(ConnectionString());
        await cn.OpenAsync();

        await using (var transaction = cn.BeginTransaction())
        {
            foreach (var person in list)
            {

                var primaryKey = await transaction.ExecuteScalarAsync(
                    SqlStatements.InsertPeople, 
                    person);

                person.Id = (int)primaryKey!;
            }

            await transaction.CommitAsync();

        }

        return (list.All(p => p.Id > 0), null);
    }
    catch (Exception ex)
    {
        return (false, ex);
    }
}

Adding a single record

Using conventional method to add a single record. Pretty much the same as when adding multiple records. Only difference is no transaction.

public static async Task Add(Person person)
{
    await using SqlConnection cn = new(ConnectionString());
    await using SqlCommand cmd = new()
    {
        Connection = cn,
        CommandText = SqlStatements.InsertPeople
    };

    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = person.Id;
    cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = person.FirstName;
    cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = person.LastName;
    cmd.Parameters.Add("@BirthDate", SqlDbType.Date).Value = person.BirthDate;

    await cn.OpenAsync();

    person.Id = Convert.ToInt32(await cmd.ExecuteScalarAsync());

}

Using Dapper

In this example the connection is opened for us by Dapper.

public static async Task AddDapper(Person person)
{

    /*
     * Setup Dapper to understand DateOnly
     */
    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    await using SqlConnection cn = new(ConnectionString());

    /*
     * Dapper
     *  - First parameter is the INSERT statement
     *  - Second parameter is data to insert
     */
    person.Id = await cn.QueryFirstAsync<int>(SqlStatements.InsertPeople, person);
}

Get a single record

Using conventional method

public static async Task<Person> Get(int id)
{
    await using SqlConnection cn = new(ConnectionString());
    await using SqlCommand cmd = new()
    {
        Connection = cn,
        CommandText = SqlStatements.Get
    };

    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

    await cn.OpenAsync();
    var reader = await cmd.ExecuteReaderAsync();
    await reader.ReadAsync();

    Person person = new Person
    {
        Id = id,
        FirstName = reader.GetString(1),
        LastName = reader.GetString(2),
        BirthDate = reader.GetDateOnly(3)
    };

    return person;
}

Using Dapper

public static async Task<Person> GetDapper(int id)
{
    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    await using SqlConnection cn = new(ConnectionString());

    return await cn.QuerySingleAsync<Person>(
        SqlStatements.Get, new 
            {Id = id});

}

Important note

Note in the above method, QuerySingleAsync second parameter is an anonymous type and must match the parameter in the SQL in this case for SqlStatements.Get.

From Dapper source code

public static Task<T> QuerySingleAsync<T>(this IDbConnection cnn, string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null) =>
    QueryRowAsync<T>(cnn, Row.Single, typeof(T), new CommandDefinition(sql, param, transaction, commandTimeout, commandType, CommandFlags.None, default));

Get all record

Let's jump to Dapper only, as with the last example, no need to open the connection, Dapper handles opening and closing the connection for us.

public static List<Person> GetAll()
{
    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    using SqlConnection cn = new(ConnectionString());

    return cn.Query<Person>(SqlStatements.ReadPeople).ToList();

}

WHERE IN

Dapper makes it extremely easy to execute a WHERE IN statement. Example, given the following statement.

SELECT Id,
       FirstName,
       LastName,
       BirthDate
FROM dbo.Person
WHERE Id IN ( 1, 3, 5 );

This to be done right with plain vanilla code, without Dapper requires a bit of code to ensure parameters are protected against SQL injection as provided in the following class project I write a while back.

With Dapper, pass the keys/identifiers to QueryAsync and Dapper does the heavy lifting for you.

Client side

int[] identifiers = { 1, 3, 5 };
var people = await DataOperations.WhereInDapper(identifiers);

Backend code

public static async Task<List<Person>> WhereInDapper(int[] ids)
{

    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    await using SqlConnection cn = new(ConnectionString());

    IEnumerable<Person> result = await cn.QueryAsync<Person>(
        SqlStatements.WhereInClause, new
        {
            Ids = ids
        });

    return result.ToList();
}

Update a record

Conventional method

public static async Task<(bool, Exception ex)> Update(Person person)
{
    try
    {

        await using SqlConnection cn = new(ConnectionString());
        await using SqlCommand cmd = new()
        {
            Connection = cn, 
            CommandText = SqlStatements.UpdatePerson
        };

        cmd.Parameters.Add("@Id", SqlDbType.Int).Value = person.Id;
        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = person.FirstName;
        cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = person.LastName;
        cmd.Parameters.Add("@BirthDate", SqlDbType.Date).Value = person.BirthDate;

        await cn.OpenAsync();
        var affected = await cmd.ExecuteNonQueryAsync();

        return (affected == 1, null);

    }
    catch (Exception localException)
    {
        return (false, localException);
    }
}

Using Dapper

public static async Task<(bool, Exception ex)> UpdateDapper(Person person)
{
    try
    {

        await using SqlConnection cn = new(ConnectionString());

        var affected = await cn.ExecuteAsync(
            SqlStatements.UpdatePerson, new
        {
            person.FirstName,
            person.LastName,
            person.BirthDate,
            person.Id
        });

        return (affected == 1, null);

    }
    catch (Exception ex)
    {
        return (false, ex);
    }
}

Note, the second parameter for ExecuteAsync is an anonymous type and must match the parameters in the SQL statement.

Delete a record

Conventional method

public static async Task<bool> Remove(Person person)
{
    await using SqlConnection cn = new(ConnectionString());
    await using SqlCommand cmd = new() { Connection = cn, CommandText = SqlStatements.RemovePerson };

    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = person.Id;

    var affected = await cmd.ExecuteNonQueryAsync();

    return affected == 1;

}

Using Dapper

public static async Task<bool> RemoveDapper(Person person)
{
    await using SqlConnection cn = new(ConnectionString());

    var affected = await cn.ExecuteAsync(
        SqlStatements.RemovePerson, 
        new { person.Id }
        );

    return affected == 1;

}

Run through the project code

If Windows Forms is not your thing, then skip the following paragraph.

The average developer tends not to learn about BindingList and BindingSource when working with data in a windows form project, code done in this project never touches the DataGridView other than set the DataSource property.

Create the database

Before running the code, open databaseCreateScript.sql under the folder Scripts, adjust the path for where the database resides and then run the script best done in SSMS. paragraph

Run the project

Build/run the project which presents data created with Bogus, each person has a unique first name and last name, the birth date is random without a primary key.

Save button

Clicking the save button will dump any existing rows and reset the table identity. Next take the data from the DataGridView and save it to the database using Dapper.

var (success, exception) = 
    await DataOperations.AddRangeDapperWithKeys(
        _personList.ToList());

The method AddRangeDapperWithKeys once complete will have the primary key for each person in the collection. Next the data is read back from the database and displayed in the DataGridView.

The following method is used to read data and must setup to read DateOnly via DapperSqlDateOnlyTypeHandler() handler.

public static List<Person> GetAll()
{
    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    using SqlConnection cn = new(ConnectionString());

    return cn.Query<Person>(SqlStatements.ReadPeople).ToList();

}

Note that there is no need to open the SqlConnection connection, Dapper does this for us.

Current button

Shows how to get the current record in the DataGridView by primary key then reads the record from the database table.

Update button

Gets the primary key of the current record in the DataGridView, creates a mocked person which is passed to a method for Dapper to update.

Remove button

Shows how to get the current record in the DataGridView by primary key then uses Dapper to remove the person from the database table.

Add button

Creates a new person using Bogus package, adds the person to the database table using Dapper. On return from adding the record the person is displayed in the DataGridView.

Refresh button

Reloads the DataGridView with fresh mocked data ready to start over.

Where button
This is an example for a WHERE BETWEEN query

For this we need stable data rather than random data from Bogus, so data is read from a json file. Once read in the data is inserted into the database table.

Next, query the data using the following statement.

SELECT Id,
       FirstName,
       LastName,
       BirthDate
FROM InsertExamples.dbo.Person
WHERE YEAR(BirthDate)
BETWEEN @StartYear AND @EndYear;

Code

/// <summary>
/// Get people with birth dates between two years
/// </summary>
/// <param name="startYear">lower year</param>
/// <param name="endYear">upper year</param>
/// <returns>list of person</returns>
public static List<Person> GetBetweenDates(int startYear, int endYear)
{

    /*
     * Setup Dapper to understand DateOnly
     */
    SqlMapper.AddTypeHandler(new DapperSqlDateOnlyTypeHandler());

    using SqlConnection cn = new(ConnectionString());


    var parameters = new
    {
        StartYear = startYear, 
        EndYear = endYear
    };

    /*
     * Dapper
     *  - Query Executes a query, returning the data typed as in this case type Person
     */
    return cn.Query<Person>(SqlStatements.BirthDateBetweenYears, parameters).ToList();

}

Part 2

Will get into working with a relational database keeping things simple and Dapper.Contrib library.

Preview of what to expect.

internal class DataOperations
{

    public static async Task<DataTable> Books1()
    {
        await using var cn = new SqlConnection(ConnectionString());

        cn.Open();
        DataTable table = new();

        table.Load(await cn.ExecuteReaderAsync(SqlStatements.GetBooks));

        return table;
    }
    public static async Task<IEnumerable<Book>> BooksContainer()
    {
        await using var cn = new SqlConnection(ConnectionString());
        return await cn.QueryAsync<Book>(SqlStatements.GetBooks);
    }

    /// <summary>
    /// Get all books
    /// </summary>
    /// <returns>Books with category</returns>
    public static async Task<IEnumerable<Book>> Books()
    {
        await using var cn = new SqlConnection(ConnectionString());
        IEnumerable<Book> books = await cn.QueryAsync<Book, Categories, Book>(SqlStatements.GetBooksWithCategories,
            (book, category) =>
            {
                book.Category = category; return book;
            }, splitOn: nameof(Book.CategoryId));

        return books;
    }

    /// <summary>
    /// Get book by identifier with category
    /// </summary>
    /// <param name="id">Identifier to locate book with</param>
    /// <returns>Book</returns>
    public static async Task<Book> GetBook(int id)
    {
        await using var cn = new SqlConnection(ConnectionString());
        return cn.Query<Book, Categories, Book>(SqlStatements.GetBookWithCategories,
            (book, category) =>
            {
                book.Category = category; return book;
            }, 
            new { Id = id },
            splitOn: nameof(Book.CategoryId))
            .FirstOrDefault();
    }

    public static async Task<Book> GetBookAsync(int id)
    {
        await using var cn = new SqlConnection(ConnectionString());
        var result= await cn.QueryAsync<Book, Categories, Book>(SqlStatements.GetBookWithCategories,
                (book, category) =>
                {
                    book.Category = category; return book;
                },
                new { Id = id },
                splitOn: nameof(Book.CategoryId));
        return result.FirstOrDefault();
    }

}

And

public partial class DataOperations
{
    /// <summary>
    /// Get all <see cref="Customer"/> in database table
    /// </summary>
    public static async Task<List<Customer>> GetAllContrib()
    {
        await using SqlConnection cn = new(ConnectionString());
        return (List<Customer>)await cn.GetAllAsync<Customer>();
    }
}