Dapper description.
Dapper is a tiny ORM for .Net, it can be used to run SQL queries and map their result set to static or dynamic .Net objects. There are at least two reasons that can motivate you to use it for accessing SQL database:
  • if there is no need to use all the rich functionality offered by a full-blown ORM, no need to load complex domain models along with their relations, for example, the client can need only to execute some queries
  • it can exempt you from creating your own wrappers around the ADO.NET
Dapper is lightweight and straightforward, its main functionality is exposed by the System.Data.IDbConnection extension methods.
Setup.
The ORM will be used from a .Net Core console application written in C#(application must reference the Dapper library). Below there are displayed three tables populated with some test data used to demonstrate Dapper functionality.
sample-data
Executing SQL.
Dapper expose Execute(string sql, ...) extension method for executing SQL statements, thus it is possible in a very easy way to create some tables and insert test data. The code example is listed below:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Dapper;

namespace DapperExample
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("-connection-string-"))
            {
                  var createDataSql =
                  @"CREATE TABLE dbo.Author(Id int, Name nvarchar(50))
                  INSERT dbo.Author values(1, 'Erica Smith')
                  INSERT dbo.Author values(2, 'Vanesa Miler')

                  CREATE TABLE dbo.Book(Id int, Title nvarchar(50))
                  INSERT dbo.Book values(1, 'Beautiful autumn')
                  INSERT dbo.Book values(2, 'Summer is best')
                  INSERT dbo.Book values(3, 'Spring is stunning')

                  CREATE TABLE dbo.AuthorBook(AuthorId int, BookId int)
                  INSERT dbo.AuthorBook values(1, 1)
                  INSERT dbo.AuthorBook values(2, 2)
                  INSERT dbo.AuthorBook values(2, 3)";

                  connection.Execute(createDataSql);
            }
            Console.WriteLine("Press any key to exit..");
            Console.ReadKey();
        }
    }
}
The code for deleting the persisted data is listed below:

var deleteDataSql =
@"DROP TABLE dbo.AuthorBook
DROP TABLE dbo.Book
DROP TABLE dbo.Author";

connection.Execute(deleteDataSql);
Reading rows using static type.
Reading all books that have authors is very simple:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;

namespace DapperExample
{
    public class TitleAuthor
    {
        public string Title { get; set; }
        public string Author { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var authorBooks = GetAuthorsBooks().Result;
            foreach (var data in authorBooks)
                Console.WriteLine($"\"{data.Title}\" by {data.Author}");
            Console.WriteLine("Press any key to exit..");
            Console.ReadKey();
        }

        public static async Task<IEnumerable<TitleAuthor>> GetAuthorsBooks()
        {
            using (var connection = new SqlConnection(" your connection string "))
            {
                var authorsBooks = await connection.QueryAsync<TitleAuthor>(
               @"SELECT b.Title, a.Name as Author
	        FROM dbo.Book b
                    JOIN dbo.AuthorBook ab  ON b.Id = ab.BookId
                    JOIN dbo.Author a ON a.Id = ab.AuthorId");
                return authorsBooks;
            }
        }
    }
}
Console output:
select-map-TitleAuthor
Notice that every row from the resulting table was mapped to an instance of a specially created POCO named AuthorBook, the class can have any valid class name, the essential is that it should expose public getters and setters whose names coincide with the names of fields returned by the corresponding SELECT statement.
Reading rows using dynamic type.
Reading the same books can be even simpler if for storing the result it is used a dynamic type:

...
public static async Task<IEnumerable<dynamic>> GetAuthorsBooks()
{
    using (var connection = new SqlConnection(" your connection string "))
    {
        var authorsBooks = await connection.QueryAsync<dynamic>(
                    @$"SELECT b.Title, a.Name as Author
                       FROM dbo.Book b
                           JOIN dbo.AuthorBook ab  ON b.Id = ab.BookId
                           JOIN dbo.Author a ON a.Id = ab.AuthorId");
        return authorsBooks;
    }
}
...

In this case, there is no need to create any POCO because the returned dynamic type will contain members inferred from the fields of the result table. The side effects of returning a dynamic type are:
  • changing the SQL SELECT statement will directly alter the members of the resulting dynamic type
  • the client is not able to deduce dynamic type members
Using SQL parameters.
Sometimes it is necessary to build the SQL statement on the fly based on some input values, in this case escaping SQL statements manually is not recommended because of the SQL injection risks. Instead, it is possible to encapsulate the filter conditions and pass them as an optional argument to the Dapper extension method. The most straightforward approach is to use an anonymous type whose members names match the name of SQL parameter names. Suppose we want to return only the rows that satisfy any of two conditions:
  • author Id equals to 1
  • book title has the string fragment summer in its title
The code fragment for filtering the result set:

...
public static async Task<IEnumerable<dynamic>> GetAuthorsBooks()
{
       using (var connection = new SqlConnection("-connection-string-"))
       {
              var parameters = new { AuthorId = 1, TitleFragment = "Summer" };
              var authorsBooks = await connection.QueryAsync<dynamic>(
              @$"SELECT b.Title, a.Name as Author
	         FROM dbo.Book b
                         JOIN dbo.AuthorBook ab  ON b.Id = ab.BookId
                         JOIN dbo.Author a ON a.Id = ab.AuthorId
                 WHERE a.Id = @AuthorId OR b.Title LIKE '%' + @TitleFragment + '%'", parameters);
                return authorsBooks;
       }
}
...

The console output is shown in the next picture:
sql-parameter-result
Reading multiple results.
Dapper exposes also extension methods for reading several results included in one result set. Suppose there is need to find an author along with all the books owned by that author. The code below shows how to read multiple results:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;

namespace DapperExample
{
    class Program
    {
        public class Author
        {
            public string Name { get; set; }
            public IEnumerable<string> Books { get; set; }
        }

        static void Main(string[] args)
        {
            var author = GetAuthorData(2).Result;
            Console.WriteLine($"Author name: {author.Name}");
            Console.WriteLine("Books:");
            foreach (var book in author.Books)
                Console.WriteLine($"\t\"{book}\"");
            Console.WriteLine("Press any key to exit..");
            Console.ReadKey();
        }

        public static async Task<Author> GetAuthorData(int authorId)
        {
            using (var connection = new SqlConnection("-connection-string-"))
            {
                var parameters = new { AuthorId = authorId };

                var reader = await connection.QueryMultipleAsync(
                    @$"SELECT Name
	               FROM dbo.Author 
                       WHERE Id = @AuthorId

                       SELECT b.Title 
	               FROM dbo.Book b
                           JOIN dbo.AuthorBook ab  ON b.Id = ab.BookId
                       WHERE ab.AuthorId = @AuthorId

                       SELECT GETDATE()", parameters);

                var name = await reader.ReadFirstOrDefaultAsync<string>();
                var books = await reader.ReadAsync<string>();
                var serverTime = await reader.ReadFirstAsync<DateTime>();
                Console.WriteLine($"Server time: {serverTime}");

                return new Author
                {
                    Name = name,
                    Books = books
                };
            }
        }
    }
}
Console output:
multiple-result-set.png
Multi-mapping query.
Dapper provides several overloads of a method named Query that allows performing a multi-mapping query. Let's use an overload that is expecting two input types. The code below is selecting book and author data, then it maps it to an instance of a class Book and an instance of the class Author. Pay attention that second parameter of Query method is a function that maps the input types to the single return type.


using System;
using System.Data.SqlClient;
using System.Linq;
using Dapper;

namespace DapperExample
{
    class Program
    {
        public class Author
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

        public class Book
        {
            public int Id { get; set; }
            public string Title { get; set; }
            public Author Author { get; set; }
        }

        static void Main(string[] args)
        {
            using (var connection = new SqlConnection("-connection-string-"))
            {
                const string sql =
                @"SELECT a.*, b.*
	          FROM dbo.Book b
                        JOIN dbo.AuthorBook ab  ON b.Id = ab.BookId
                        JOIN dbo.Author a ON a.Id = ab.AuthorId";

                var books = connection.Query<Author, Book, Book>(sql, (author, book) => { book.Author = author; return book; }).ToList();
                foreach (var book in books)
                    Console.WriteLine($"Book id: {book.Id}, title: \"{book.Title}\", author: {book.Author.Name}");
            }

            Console.WriteLine("Press any key to exit..");
            Console.ReadKey();
        }
    }
}
Console output:
multi-mapping-query.png