Dapper is an open-source Object Relational Mapper (ORM) for .NET. It was written by the people over at Stack Overflow. It is a NuGet library that will extend your IDbConnection interface with easy-to-use extensions.

More Learning Resources

• For more information you could go over to Dapper Tutorial website.
• Here is a YouTube video on Dapper. It’s called Forget the big mapper, switch to Dapper – Matthew D. Groves
• There is a PDF file that is tutorial of Dapper written by those at StackOverflow. It’s called Learning Dapper.NET.

There is no IQueryable in Dapper. What does this mean? This means you cannot use LINQ. You are back to SQL strings to work with your databases. Maybe you prefer that anyway, particularly if you know your SQL. Know that it is very difficult for programmers to write bullet-proof LINQ provider. LINQ is not the answer for any situation. Dapper is really just five extension methods, plus five more asynchronous methods. You as the coder will need to create a connection using a connection string before you can use the extension methods.

• Execute
• ExecuteReader
• ExecuteScalar
• Query
• QueryMultiple
• (plus the Async versions of the above)

Why Dapper?

Dapper is fast. It’s almost as fast as ADO.NET (which is the fastest), but the code is much shorter to write than ADO.NET. Dapper has been tested extensively by StackOverflow, so it’s reliable. Dapper works with (compatible) SQL Server, MySQL, SQLite, PostgreSQL, Oracle, and all ADO.NET providers. You could use Entity Framework if you wanted to use LINQ. There are other options.

Execute

Below is just a piece of code for illustration. It is code that works and is inside a WPF project.

IDbConnection GetConnection()
{
    return new SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=MyApp1;Integrated Security=true;");
}

So we already have got our connection based on our connection string, as shown above. Now we’ll employ the using keyword so that it will automatically get disposed of when we’re done with it. Then we Execute the connection with our SQL statement and parameter. We use an anonymous object initializer, new { Name = txtBoxName.Text }.

    try  // this is inside a click event
    {
        using (IDbConnection conn = GetConnection())
        {
            string sql = "INSERT INTO Customers (FirstName) VALUES (@Name)";
            conn.Execute(sql, new { Name = txtBoxName.Text }); // parameters    
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Database Error: " + ex.Message, "DapperPusherYouTube",
            MessageBoxButton.OK, MessageBoxImage.Warning);
    }

The Execute method syntax/pseudocode is as follows. The only required parameter is the first one sql.

connection.Execute(
    string sql,
    object param,
    IDbTransaction Transaction,
    int? commandTimeout,
    CommandType commandType
    )

Execute Scalar

This is good for single pieces of data you need to get, such as a count of records, the average, minimum or maximum amount as well asa the total amount and so on.

var returnValue = connection.ExecuteScalar<T>(
    string sql,
    object param,
    IDbTransaction Transaction,
    int? commandTimeout,
    CommandType commandType
    )