Dapper Contrib Read Data


Most of the applications would perform the basic operation to retrieve data from the database and display the results. We have two tables in the database that contains the following data.

Database data

To retrieve the data from the database using Dapper.Contrib, let's create two classes called Author and Book.

Here is the implementation of the Author class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DapperContribDemo
{
    class Author
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        //public List<Book> Books { get; set; }
    }

}

The following is the implementation of the Book class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DapperContribDemo
{
    class Book
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Category { get; set; }
        public int AuthorId { get; set; }
    }
}

In the Program class, define the static variable, which contains the connection string of the database.

static string ConnectionString = @"Data Source=(localdb)\ProjectsV13;Initial Catalog=BookStoreContext;Integrated Security=True;";

The Dapper.Contrib library provides Get<T>(id) and GetAll<T>() extension methods to retrieve data from the database and populate data in your object model.

The following example retrieves all the authors from the database using the GetAll<T>() method.

private static void GetAllAuthors()
{
    using (IDbConnection db = new SqlConnection(ConnectionString))
    {
        List<Author> authors = db.GetAll<Author>().ToList();

        foreach (var author in authors)
        {
            Console.WriteLine(author.FirstName + " " + author.LastName);
        }
    }
}

If you want to retrieve any specific record from the database, you can use the Get method and pass the id as an argument.

private static void GetAuthor(int id)
{
    using (IDbConnection db = new SqlConnection(ConnectionString))
    {
        Author author = db.Get<Author>(id);

        Console.WriteLine(author.FirstName + " " + author.LastName);
    }
}