Dapper FastCRUD 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.FastCrud, 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 DapperFastCRUDDemo
{
    public 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 DapperFastCRUDDemo
{
    public 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=BookStoreDb;Integrated Security=True;";

The first step is to create a member of type IDbConnection with the SqlConnection by passing the connection string. The Dapper.FastCrud library provides Get and Find extension methods to retrieve data from the database. The Find method retrieves all the records from the database.

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

        return authors;
    }
}

To retrieve a single record, you can use the Get method and specify the primary key (id) as a parameter.

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

        return author;
    }
}

Let's call the above two methods from the Main method.

static void Main(string[] args)
{
    Author author1 = GetAuthor(2);
    Console.WriteLine(author1.FirstName + " " + author1.LastName);

    List<Author> authors = GetAllAuthors();

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

Let's execute the above code, and you will see the following output.

William Shakespeare
Cardinal Tom B. Erichsen
William Shakespeare
Robert T. Kiyosaki