Dapper.NET Guide – Basic List Query with Dapper

Retrieving Data from the database and displaying the result is one the basic operation that most of the application would perform. In this tutorial, let’s have a look at the basic list query in Dapper which gets the data in to the list from database.

Assume that the database has a table called “Employee” with the columns Id, Name and Designation as shown below.

Problem Statement

We need to retrieve the data from the Employee table using Dapper and display it in the console window.

How to perform Basic List Query with Dapper?

Follow the below steps to retrieve the data from the employee table using Dapper.

1. Create a class called Employee and add the properties Id, Name and Designation.

namespace DapperDemo
{
    public class Employee
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Designation { get; set; }
    }
}

2. Create an interface for the repository with the GetAll() method for the repository.

using System.Collections.Generic;
namespace DapperDemo
{
    public interface IEmployeeRepository
    {
        List<Employee> GetAll();
    }
}

3. Create the concrete class for the Employee repository and implement the methods of the Employee Repository interface.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
namespace DapperDemo
{
    public class EmployeeRepository : IEmployeeRepository
    {
        private IDbConnection db = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
        // Retreives the data from the table.
        public List<Employee> GetAll()
        {
            return this.db.Query<Employee>("SELECT * FROM EMPLOYEE").ToList();
        }
        
    }
}

4. In the above class, we have only implemented the GetAll method which retrieves the data from the Employee table. We should create a member of type IDbConnection with the SqlConnection by passing the connection string from the application configuration file.

The Configuration File entry for the connection string will look like the one shown below.

<connectionStrings>
    <add name="conn" connectionString="Integrated Security=SSPI;Persist Security Info=False;;Initial Catalog=DapperDemo;Data Source=.SQLEXPRESS;"/>
</connectionStrings>

Add the namespaces Dapper to use the dapper methods like Query as shown in the above example. The Query method executes a query and returning the data typed as per T. In the above example, its gets the Employee list.

5. The last step would be to call the repository to get the data and then display it on the screen.

using System;
using System.Collections.Generic;
namespace DapperDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            IEmployeeRepository employeeRepository = new EmployeeRepository();
            List<Employee> employees = employeeRepository.GetAll();
            foreach(var emp in employees)
            {
                Console.WriteLine(emp.Name);
            }
            Console.ReadLine();
        }
    }
}