Dapper.NET Guide – Get Single Record using Dapper

This tutorial will demonstrate in simple steps on how to retrieve a record (single record) from a table using Dapper in .NET.

Note that we will continue using the table “Employee” which we used in the previous tutorial. The table “Employee” contains the columns Id, Name and Designation.

Problem Statement

We need to get single record from the Employee table using Dapper.

How to retrieve single record with Dapper?

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

1. Use the Employee class that was created earlier (in the previous tutorial).

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

2. Include the GetById method in the IRepository interface which takes the id as input and returns the Employee object.

public interface IEmployeeRepository
{
    List<Employee> GetAll();
    bool Add(Employee employee);
    Employee GetById(int id);
}

3. Implement the GetById method of the IEmployeeRepository in the EmployeeRepository concrete class. In the GetById method, specify the SQL Query for retrieving the data as shown below. Note again that we need to specify the SQL query explicitly in Dapper.

// Get Employee record by Id
public Employee GetById(int id)
{
    return this.db.Query<Employee>("SELECT * FROM EMPLOYEE WHERE Id=@Id", new { Id = id }).FirstOrDefault();
}

4. This will look like the almost similar to the GetAll method but then we specify the where clause by sending the parameter to the query. In Dapper, we typically use anonymous type for the parameter. In the above GetById, we used the FirstOrDefault method which returns the first element. When there are no elements, this function returns null.

5. The last step would be to invoke the GetById method of the employee repository from the page as shown below.

IEmployeeRepository employeeRepository = new EmployeeRepository();
Employee emp = employeeRepository.GetById(2);
if (emp != null)
   Console.WriteLine(emp.Name);

The Complete code of the EmployeeRepository , IEmployeeRepository and Main class is shown below.

IEmployeeRepository.cs

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

EmployeeRepository.cs

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);
     // Get Employee record by Id
        public Employee GetById(int id)
        {
            return this.db.Query<Employee>("SELECT * FROM EMPLOYEE WHERE Id=@Id", new { Id = id }).FirstOrDefault();
        }

        // Retreives the data from the table.
        public List<Employee> GetAll()
        {
            return this.db.Query<Employee>("SELECT * FROM EMPLOYEE").ToList();
        }
        // Add Employee Data
        public bool Add(Employee employee)
        {
            try
            {
                string sql = "INSERT INTO Employee(Name,Designation) values(@Name,@Designation); SELECT CAST(SCOPE_IDENTITY() as int)";
                var returnId = this.db.Query<int>(sql, employee).SingleOrDefault();
                employee.Id = returnId;
            }
            catch(Exception ex)
            {
                return false;
            }
            return true;
        }
    }
}

Program.cs

using System;
using System.Collections.Generic;
namespace DapperDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            IEmployeeRepository employeeRepository = new EmployeeRepository();
            Employee emp = employeeRepository.GetById(2);
            if (emp != null)
                Console.WriteLine(emp.Name);
            
            Console.ReadLine();
        }
        
    }
}