Dapper.NET Guide – Inserting Data

This tutorial will demonstrate in simple steps on how to insert data to the table using Dapper in .NET.

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 add or insert the data to the Employee table using Dapper and verify if the data is inserted successfully or not.

How to Insert Data with Dapper?

Follow the below steps to perform insert operation to 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 Add method in the IRepository interface.

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

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

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;
}

4. Note that in the values section, we have used @<parameter1>. These are the parameters being passed to the query. The name of the parameter is same as the property name in the Employee class and hence it is easier for us to later pass the complete employee object to the Dapper’s Query method . We have also used the SCOPE_IDENTITY() so that we get the employee object’s id after insertion.

var returnId = this.db.Query<int>(sql, employee).SingleOrDefault();

5. The last step would be to create the employee object and call the add method of the employee repository.

IEmployeeRepository employeeRepository = new EmployeeRepository();
Employee employee1 = new Employee
{
    Name = "Senthil Kumar",
    Designation = ".NET Developer"
};
Employee employee2 = new Employee
{
    Name = "Norton Stanley",
    Designation = "Android Developer"
};
employeeRepository.Add(employee1);
employeeRepository.Add(employee2);

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();
    }
}

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);
        // 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 employee1 = new Employee
            {
                Name = "Senthil Kumar",
                Designation = ".NET Developer"
            };
            Employee employee2 = new Employee
            {
                Name = "Norton Stanley",
                Designation = "Android Developer"
            };
            employeeRepository.Add(employee1);
            employeeRepository.Add(employee2);
            List<Employee> employees = employeeRepository.GetAll();
            foreach(var emp in employees)
            {
                Console.WriteLine(emp.Name);
            }
            Console.ReadLine();
        }
        
    }
}