C# · SQL

Dapper

Dapper is one of the ORM tools that allows to manipulate data easily and with high performance. It extends IDbConnection interface by introducing couple of extension methods used to query tables in database. Dapper supports static as well as dynamic object binding. It also provides bulk insert functionality and can handle transactions and stored procedures.

Let’s build a simple application to manage students using Dapper to see how it works.

I decited to create ASP.NET Core Web Application using Web API template.
The project structure looks like this:

The next step is to install Dapper package from NuGet.
You can do it by using the following command in Package Manage Console:

Install-Package Dapper

After that create a database and two tables that will be used in our application: dbo.Groups and dbo.Students. Each student has to be assigned to the group:

CREATE DATABASE StudentsApp;

CREATE TABLE [dbo].[Groups] (
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH 
(PAD_INDEX = OFF,
 STATISTICS_NORECOMPUTE = OFF,
 IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Students](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](256) NOT NULL,
	[LastName] [nvarchar](256) NOT NULL,
	[GroupId] [int] NOT NULL,
 CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH 
(PAD_INDEX = OFF,
 STATISTICS_NORECOMPUTE = OFF,
 IGNORE_DUP_KEY = OFF,
 ALLOW_ROW_LOCKS = ON,
 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Students]  
WITH CHECK ADD CONSTRAINT 
[FK_Students_Groups_GroupId] FOREIGN KEY([GroupId])
REFERENCES [dbo].[Groups] ([Id])
ON DELETE CASCADE
GO

After running the above query you should receive the following structure:

Now we have to create a connection between our application and database. To do that, start with creating IDBConnection interface that has property of IDbConnection type and then build a class that implements the newly created interface:

IDBConnector.cs

public interface IDBConnector
    {
        IDbConnection Connection { get; }
    }

DBConnector.cs

 public class DBConnector : IDBConnector
    {
        private IConfiguration _configuration;
        private string _connectionString;

        public IDbConnection Connection
        {
            get
            {
                return new SqlConnection(_connectionString);
            }
        }

        public DBConnector(IConfiguration configuration)
        {
            _configuration = configuration;
            _connectionString = _configuration.GetConnectionString("DefaultConnection");
        }
    }

IConfiguration allows to get access to the connection string stored in appsettings.json:

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=.\\ALEKTREDB;Initial Catalog=StudentsApp;Integrated Security=true"
  }
}

Next go to the ConfigureServices method located inside Startup.cs and configure the container :

    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();
            services.AddTransient<IDBConnector, DBConnector>();
        }
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

            app.UseMvc();
        }
    }

Afterwards build models:

Group.cs

   public class Group
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Student.cs

  public class Student
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public Group Group { get; set; }
    }

StudentCreationModel.cs

  public class StudentCreationModel
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int GroupId { get; set; }
    }

I decided to create one helper modal – StudentCreationModel. Thanks to that we can simplify creation of the new user – we have to specify GroupId only, not the Group object.

Next step is to create two repositories – one for the students and the second one for the groups:
IRepository.cs

  public interface IRepository<TEntity> where TEntity : class
    {
        IList<TEntity> GetAll();

        TEntity Get(int id);

        void Create(TEntity model);

        void Update(TEntity model);
    }

IStudentRepository.cs

  public interface IStudentRepository : IRepository<Student>
    {
    }

StudentRepository.cs

   public class StudentRepository : IStudentRepository
    {
        private readonly IDBConnector _connector;

        public StudentRepository(IDBConnector connector)
        {
            _connector = connector;
        }

        public void Create(Student model)
        {
            using (_connector.Connection)
            {
                string query =
                    @"INSERT INTO [dbo].[Students]
                    ([FirstName], [LastName], [GroupId])
                     VALUES (@FirstName, @LastName, @GroupId)";

                var result = _connector.Connection.Execute(query, new
                {
                    model.FirstName,
                    model.LastName,
                    model.Group.Id
                });
            }
        }

        public Student Get(int id)
        {
            using (_connector.Connection)
            {
                string query =
                     @"SELECT s.[Id],
                       s.[FirstName],
                       s.[LastName],
                       s.[GroupId],
	                   g.[Id],
	                   g.[Name]
                  FROM [dbo].[Students] s
                  LEFT JOIN [dbo].[Groups] g
                  ON s.[GroupId]=g.[Id]

                  WHERE  s.Id=@StudentId";

                return _connector.Connection.Query<Student, Group, Student>
                (query, (student, group) =>
                {
                    student.Group = group;
                    return student;
                },
                splitOn: "GroupId", 
                param: new { StudentId = id }).FirstOrDefault();
            }
        }

        public IList<Student> GetAll()
        {
            using (_connector.Connection)
            {
                string query =
                     @"SELECT s.[Id],
                       s.[FirstName],
                       s.[LastName],
                       s.[GroupId],
	                   g.[Id],
	                   g.[Name]
                  FROM [dbo].[Students] s
                  LEFT JOIN [dbo].[Groups] g
                  ON s.[GroupId]=g.[Id]";

                return _connector.Connection.Query<Student, Group, Student>
                (query, (student, group) =>
                {
                    student.Group = group;
                    return student;
                },
                splitOn: "GroupId").ToList();
            }
        }

        public void Update(Student model)
        {
            using (_connector.Connection)
            {
                string query =
                    @"UPDATE [dbo].[Students]
                    SET
                    FirstName = @FirstName,
                    LastName = @LastName,
                    GroupId = @GroupId
                    WHERE Id = @Id";

                var result = _connector.Connection.Execute(query, new
                {
                    Id = model.Id,
                    FirstName = model.FirstName,
                    LastName = model.LastName,
                    GroupId = model.Group.Id
                }
                );
            }
        }
    }

IGroupRepository.cs

  public interface IGroupRepository : IRepository<Group>
    {
    }

GroupRepository.cs

  public class GroupRepository : IGroupRepository
    {
        private readonly IDBConnector _connector;

        public GroupRepository(IDBConnector connector)
        {
            _connector = connector;
        }

        public void Create(Group model)
        {
            using (_connector.Connection)
            {
                string query =
                    @"INSERT INTO [dbo].[Groups]
                    ([Name]) VALUES (@Name)";

                var result = _connector.Connection.Execute(query, new
                {
                    model.Name
                });
            }
        }

        public Group Get(int id)
        {
            using (_connector.Connection)
            {
                string query =
                     @"SELECT g.[Id],
                       g.[Name]
                       FROM [dbo].[Groups]
                       WHERE  g.Id=@Id";

                return _connector.Connection.QueryFirst<Group>
                (query, param: new { Id = id });
            }
        }

        public IList<Group> GetAll()
        {
            using (_connector.Connection)
            {
                string query =
                     @"SELECT g.[Id],
                       g.[Name]
                       FROM [dbo].[Groups]";

                return _connector.Connection.Query<Group>(query).ToList();
            }
        }

        public void Update(Group model)
        {
            using (_connector.Connection)
            {
                string query =
                    @"UPDATE [dbo].[Groups]
                    SET
                    Name = @Name
                    WHERE Id = @Id";

                var result = _connector.Connection.Execute(query, new
                {
                    Id = model.Id,
                    Name = model.Name
                }
                );
            }
        }
    }

By using Query() and QueryFirst() extension methods we can retrieve data from the database and map them to application models. Creating complex objects with Dapper is also possible. To do that, we have to specify which objects should be used as input parameters and what object we want to be returned. In Lambda expression define how our objects should be combined. Parameter called “splitOn” is used to set starting column of the second object.
To make any write operation, we used Execute() method.

Now it’s time to create service layer:
IStudentService.cs

 public interface IStudentService
    {
        IList<Student> GetAll();

        Student Get(int id);

        void Create(StudentCreationModel model);

        void ChangeFirstName(int id, string firstName);

        void ChangeLastName(int id, string lastName);

        void ChangeGroup(int id, int groupId);
    }

StudentService.cs

    public class StudentService : IStudentService
    {
        private IStudentRepository _studentRepository;

        public StudentService(IStudentRepository studentRepository)
        {
            _studentRepository = studentRepository;
        }

        public void ChangeFirstName(int id, string firstName)
        {
            var student = _studentRepository.Get(id);
            student.FirstName = firstName;
            _studentRepository.Update(student);
        }

        public void ChangeGroup(int id, int groupId)
        {
            var student = _studentRepository.Get(id);
            student.Group.Id = groupId;
            _studentRepository.Update(student);
        }

        public void ChangeLastName(int id, string lastName)
        {
            var student = _studentRepository.Get(id);
            student.LastName = lastName;
            _studentRepository.Update(student);
        }

        public void Create(StudentCreationModel creationModel)
        {
            var model = new Student
            {
                FirstName = creationModel.FirstName,
                LastName = creationModel.LastName,
                Group = new Group
                {
                    Id = creationModel.GroupId
                }
            };

            _studentRepository.Create(model);
        }

        public Student Get(int id)
        {
            return _studentRepository.Get(id);
        }

        public IList<Student> GetAll()
        {
            return _studentRepository.GetAll();
        }
    }

IGroupService.cs

  public interface IGroupService
    {
        IList<Group> GetAll();

        Group Get(int id);

        void Create(string model);

        void ChangeName(int id, string name);
    }

GroupService.cs

  public class GroupService : IGroupService

    {
        private IGroupRepository _groupRepository;

        public GroupService(IGroupRepository groupRepository)
        {
            _groupRepository = groupRepository;
        }

        public void ChangeName(int id, string name)
        {
            var group = _groupRepository.Get(id);
            group.Name = name;
            _groupRepository.Update(group);
        }

        public void Create(string group)
        {
            var model = new Group
            {
                Name = group
            };
            _groupRepository.Create(model);
        }

        public Group Get(int id)
        {
            return _groupRepository.Get(id);
        }

        public IList<Group> GetAll()
        {
            return _groupRepository.GetAll();
        }
    }

Come back to the Startup.cs and add new services:

 services.AddTransient<IGroupService, GroupService>();
 services.AddTransient<IStudentService, StudentService>();
 services.AddTransient<IStudentRepository, StudentRepository>();
 services.AddTransient<IGroupRepository, GroupRepository>();

In the end create Controllers:
StudentsController.cs

[Route("api/[controller]")]
    public class StudentsController : Controller
    {
        private IStudentService _studentService;

        public StudentsController(IStudentService studentService)
        {
            _studentService = studentService;
        }

        [HttpGet("{id}")]
        public Student Get(int id)
        {
            return _studentService.Get(id);
        }

        [HttpGet]
        public IEnumerable<Student> GetAll()
        {
            return _studentService.GetAll();
        }

        [HttpPost]
        public void Post([FromBody]StudentCreationModel student)
        {
            _studentService.Create(student);
        }

        [HttpPut("{id}/firstName")]
        public void ChangeFirstName(int id, [FromBody]string firstName)
        {
            _studentService.ChangeFirstName(id, firstName);
        }

        [HttpPut("{id}/lastName")]
        public void ChangeLastName(int id, [FromBody]string lastName)
        {
            _studentService.ChangeLastName(id, lastName);
        }

        [HttpPut("{id}/group")]
        public void ChangeGroup(int id, [FromBody]int groupId)
        {
            _studentService.ChangeGroup(id, groupId);
        }
    }

GroupsController.cs

[Route("api/[controller]")]
    public class GroupsController : Controller
    {
        private IGroupService _groupService;

        public GroupsController(IGroupService groupService)
        {
            _groupService = groupService;
        }

        [HttpGet("{id}")]
        public Group Get(int id)
        {
            return _groupService.Get(id);
        }

        [HttpGet]
        public IEnumerable<Group> GetAll()
        {
            return _groupService.GetAll();
        }

        [HttpPost]
        public void Post([FromBody]string group)
        {
            _groupService.Create(group);
        }

        [HttpPut("{id}")]
        public void Put(int id, [FromBody]string group)
        {
            _groupService.ChangeName(id, group);
        }
    }

Leave a Reply

Your email address will not be published. Required fields are marked *