Dapper.NET Guide – Update Record

This tutorial will demonstrate on how to update a record 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 update a record that that was retrieved earlier using Dapper.

How to update a record with Dapper?

Follow the below steps to retrieve single record from the employee table and then update it 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 Update 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);
    bool Update(Employee employee);
}

3. Implement the Update method of the IEmployeeRepository in the EmployeeRepository concrete class. In the Update method, specify the SQL Query for updating the data as shown below.

public bool Update(Employee employee)
{
    string query = "UPDATE EMPLOYEE SET NAME = @Name WHERE Id = @Id";
    var count = this.db.Execute(query, employee);
    return count > 0;
}

4. Note that in the Update method we have used the db. Execute method instead of db. Query method which was used in the insert method. This is because during the record insertion, we needed to return the inserted record id which is not needed in the update method. We just need to specify the parameter which needs to be updated via its property name using the @ symbol and call the execute method to update the record. For example , @Name refers to the Name property and @Id refers to the Id property in the Employee class.

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

IEmployeeRepository employeeRepository = new EmployeeRepository();
Employee emp = employeeRepository.GetById(2);
emp.Name = "Norton Samuel Stanley";
employeeRepository.Update(emp);
Employee emp2 = employeeRepository.GetById(2);
if (emp2 != null)
    Console.WriteLine(emp2.Name);

Console.ReadLine();

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);
     bool Update(Employee employee);
    }
}

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;
        }
        // Update the employee record
        public bool Update(Employee employee)
        {
            string query = "UPDATE EMPLOYEE SET NAME = @Name WHERE Id = @Id";
            var count = this.db.Execute(query, employee);
            return count > 0;
        }
    }
}

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);
            emp.Name = "Norton Samuel Stanley";
            employeeRepository.Update(emp);
            Employee emp2 = employeeRepository.GetById(2);
            if (emp2 != null)
                Console.WriteLine(emp2.Name);
            
            Console.ReadLine();
        }
        
    }
}