DEV Community

Cover image for Using Dapper - C# Part 1
Karen Payne
Karen Payne

Posted on • Updated on

Using Dapper - C# Part 1

#csharp #dotnetcore #tutorial #database

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

Person table

Tables for part two of this series

Tables for part two of the 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);
        """;

}
Enter fullscreen mode Exit fullscreen mode

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));
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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());

}
Enter fullscreen mode Exit fullscreen mode

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);
}
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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});

}
Enter fullscreen mode Exit fullscreen mode

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));
Enter fullscreen mode Exit fullscreen mode

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();

}
Enter fullscreen mode Exit fullscreen mode

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 );
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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();
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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);
    }
}
Enter fullscreen mode Exit fullscreen mode

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;

}
Enter fullscreen mode Exit fullscreen mode

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;

}
Enter fullscreen mode Exit fullscreen mode

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());
Enter fullscreen mode Exit fullscreen mode

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();

}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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();

}
Enter fullscreen mode Exit fullscreen mode

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();
    }

}
Enter fullscreen mode Exit fullscreen mode

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>();
    }
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

bgskurono profile image

Golang básico - Comparação de Igualdade

Bruno Gomes -

mohamed_el_laithy profile image

How Pipe Syntax Fix SQL Design Problems

Mohamed El Laithy -

hana_sato profile image

Real-Time Processing of Big Data: Tools and Best Practices

Hana Sato -

jacksonsantin profile image

Login/cadastro com firebase + Vue JS #PASSO 1️⃣

Jackson Dhanyel Santin -