DEV Community

Cover image for Mastering Dapper: A Comprehensive Guide to Full CRUD Operations in .NET.
Ahmed Shah
Ahmed Shah

Posted on

Mastering Dapper: A Comprehensive Guide to Full CRUD Operations in .NET.

Full Dapper CRUD Operations
In the previous post i have talked about Introduction to DAPPER in .NET Apps Unlocking the Power of Simplified Database Access
link.

In this post, we will show you how to perform simple CRUD operations with Dapper. We will create a simple ASP.NET Core application that allows users to create, read, update, and delete records in a database.
Setting up Dapper in your .NET project:
To use Dapper, Create a new .NET 5 API PROJECT.you need to install the Dapper package from NuGet. Open your project in Visual Studio and navigate to "Manage NuGet Packages." Search for "Dapper" and install the package into your project Currently Dapper support is till .NET 5.

Image

Connecting to the database:
To connect to the database, you will need to configure your connection string. Typically, this involves specifying the server, database name, authentication credentials, etc. You can store the connection string in your application configuration file (e.g., appsettings.json) and retrieve it using the .NET Configuration API.

Image
Before performing CRUD operations, it's essential to define a data model that represents your database table structure. Create a class with properties that match the columns in your table.

Image
Lets add a new class for our database object. Decorate the properties with primary key such as [Key].
Image
This code creates a class called User with three properties: Id, Name, and Description. These properties will be used to represent the data in the database.

Let create a new folder named repository where we will have our database logic for communication with database and Interface with its Corresponding class.

Image




    public interface IDapperUserRepository
    {
        Task<User> UpdateAsync(User entity);
        Task<int> InsertAsync(UserInsert entity);
        Task<int> DeleteAsync(Guid id);
        Task<User> GetAsync(Guid id);
        Task<IEnumerable<User>> GetAllAsync();
    }


Enter fullscreen mode Exit fullscreen mode

This code creates an interface called IDapperUserRepositorywith five methods: GetAsync, GetAsyncById, InsertAsync, UpdateAsync, and DeleteAsync. These methods will be used to perform CRUD operations on the User class.
Let inherit our interface with the class we have created and use the power of dependency injection built in .NET.



    public class DapperUserRepository : IDapperUserRepository
    {

        private readonly DatabaseConfigurationOptions _databaseConfiguration;
        public string ConnectionString { get; set; }

        public DapperUserRepository(IOptions<DatabaseConfigurationOptions> configuration)
        {

            _databaseConfiguration = configuration.Value;
            ConnectionString = _databaseConfiguration.ConnectionString;
        }

        public async Task<int> InsertAsync(UserInsert entity)
        {
            try
            {
                var user = new User
                {
                    Description = entity.Description,
                    Name = entity.Name,
                };
                var query = "Insert into Users (Id,Name,Description) VALUES (@Id,@Name,@Description)";
                using (var db = new SqlConnection(ConnectionString))
                {
                    await db.OpenAsync();
                    return await db.ExecuteAsync(query, user);

                }
            }
            catch (Exception e)
            {

                throw;
            }

        }
        public async Task<User> GetAsync(Guid id)
        {
            try
            {
                var query = "SELECT * FROM Users WHERE Id = @Id";
                using (var db = new SqlConnection(ConnectionString))
                {
                    await db.OpenAsync();
                    return await db.QuerySingleOrDefaultAsync<User>(query, new { Id = id });
                }
            }
            catch (Exception e)
            {

                throw;
            }
        }
        public async Task<IEnumerable<User>> GetAllAsync()
        {
            try
            {
                var sql = "SELECT * FROM users";
                using (var db = new SqlConnection(ConnectionString))
                {
                    await db.OpenAsync();
                    return await db.QueryAsync<User>(sql);
                }
            }
            catch (Exception e)
            {
                throw;
            }
        }
        public async Task<User> UpdateAsync(User entity)
        {
            try
            {
                var query = "UPDATE Users SET Name = @Name, Description = @Description WHERE Id = @Id";

                using (var db = new SqlConnection(ConnectionString))
                {
                    await db.OpenAsync();
                    await db.ExecuteAsync(query, entity);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            return await Task.FromResult(entity);
        }
        public async Task<int> DeleteAsync(Guid id)
        {
            try
            {
                var query = "DELETE FROM Users WHERE Id = @Id";
                using (var db = new SqlConnection(ConnectionString))
                {
                    await db.OpenAsync();
                    var result = await db.ExecuteAsync(query, new { Id = id });
                    return result;
                }
            }
            catch (Exception e)
            {
                throw;
            }
        }

    }


Enter fullscreen mode Exit fullscreen mode

is the above code you can see that we are communicating with database with raw queries. there are different methods to to query with database we can learn about them at dapper github project
Dapper Github.

Let create services for our repository name UserService and call our services there.

Image



 public interface IUserService
 {
     Task<IEnumerable<User>> GetAllAsync();
     Task<User> GetByIdAsync(Guid id);
     Task<int> AddAsync(UserInsert entity);
     Task<User> UpdateAsync(User entity);
     Task<int> DeleteAsync(Guid id);

 }


Enter fullscreen mode Exit fullscreen mode

Inject our IUserService into UserService



    public class UserService : IUserService
    {
        private readonly IDapperUserRepository _userRepository;

        public UserService(IDapperUserRepository userRepository)
        {
            _userRepository = userRepository;
        }   
        public async Task<int> AddAsync(UserInsert entity)
        {
          return  await _userRepository.InsertAsync(entity);
        }

        public async Task<int> DeleteAsync(Guid id)
        {
          return  await _userRepository.DeleteAsync(id);

        }

        public async Task<IEnumerable<User>> GetAllAsync()
        {
          return await _userRepository.GetAllAsync();
        }

        public async Task<User> GetByIdAsync(Guid id)
        {
            return await _userRepository.GetAsync(id);
        }

        public async Task<User> UpdateAsync(User entity)
        {
            return await _userRepository.UpdateAsync(entity);
        }
    }


Enter fullscreen mode Exit fullscreen mode

Create a Controller named UserController and call services there to see the output of our code.

Image



  [Route("api/[controller]")]
  [ApiController]
  public class UserController : ControllerBase
  {
      private readonly IUserService _userService;
      public UserController(IUserService userService)
      {
          _userService = userService;
      }
      // GET: api/<UserController>
      [HttpGet]
      public async Task<IActionResult> Get()
      {
          return Ok(await _userService.GetAllAsync());
      }

      // GET api/<UserController>/5
      [HttpGet("{id}")]
      public async Task<IActionResult> Get(Guid id)
      {
          return Ok(await _userService.GetByIdAsync(id));
      }

      // POST api/<UserController>
      [HttpPost]
      public async Task<IActionResult> Post([FromBody] UserInsert user)
      {
          return Ok(await _userService.AddAsync(user)); 
      }

      // PUT api/<UserController>/5
      [HttpPut()]
      public async Task<IActionResult> Put([FromBody] User user)
      {
          return Ok(await _userService.UpdateAsync(user));
      }

      // DELETE api/<UserController>/5
      [HttpDelete("{id}")]
      public async Task<IActionResult> Delete(Guid id)
      {
          return Ok(await _userService.DeleteAsync(id));
      }
  }




Enter fullscreen mode Exit fullscreen mode

Don't Forget to register the services repository and database connection IOptions in the startup.cs file. if you don't know about the IOptions pattern you can learn about it in my previous article series. link

Image

Let run our project and see the results.

Insert
Image
GETAll

Image
We can see our newly created entity. now lets update it.

Image
Now entity has been updated lets check it using get by id.

Image

Its working fine. now we will test the delete endpoint.

Image
get entity by id again and see if its deleted.

Image
The API returns 204 no content found it means our entity has been deleted.

We can also use stored procedures to get the entity but using dapper its take effort to write SQL queries for simple operations not the complex ones. if you need to have feeling like EF core using dapper we can use DAPPER FAST CRUD library.

FastCrud is built on top of Dapper, which is a popular ORM for .NET. you can read about it here more link.

Let install dapper fast crud library in our project and see the difference between Dapper and Dapper Fast Crud.

Image

Create new Classes for Dapper Fast Crud.
Image



    public interface IDapperFastCrudUserRepository
    {
        Task<User> UpdateAsync(User entity);
        Task<int> InsertAsync(UserInsert entity);
        Task<int> DeleteAsync(Guid id);
        Task<User> GetAsync(Guid id);
        Task<IEnumerable<User>> GetAllAsync();
    }


Enter fullscreen mode Exit fullscreen mode

The class is same we have created before for our userrepository. now let inherit it into our DapperFastCrudUserRepository



    public class DapperFastCrudUserRepository : IDapperFastCrudUserRepository
    {
        private readonly DatabaseConfigurationOptions _databaseConfiguration;
        public string ConnectionString { get; set; }

        public DapperFastCrudUserRepository(IOptions<DatabaseConfigurationOptions> configuration)
        {

            _databaseConfiguration = configuration.Value;
            ConnectionString = _databaseConfiguration.ConnectionString;
        }
        public async Task<int> DeleteAsync(Guid id)
        {
            var response = 0;
            using (IDbConnection db = new SqlConnection(ConnectionString))
            {
                var result = await db.DeleteAsync(new User { Id = id });
                if (result)
                {
                    response = 1;
                }
                return response;
            }
        }

        public async Task<IEnumerable<User>> GetAllAsync()
        {
            using (IDbConnection db = new SqlConnection(ConnectionString))
            {
                var users = await db.FindAsync<User>();

                return users;
            }
        }

        public async Task<User> GetAsync(Guid id)
        {
            using (IDbConnection db = new SqlConnection(ConnectionString))
            {
                User author = await db.GetAsync(new User { Id = id });

                return author;
            }
        }

        public async Task<int> InsertAsync(UserInsert entity)
        {
            using (IDbConnection db = new SqlConnection(ConnectionString))
            {
                var user = new User
                {
                    Name = entity.Name,
                    Description = entity.Description,
                };
                await db.InsertAsync(user);
            }
            return 1;
        }

        public async Task<User> UpdateAsync(User entity)
        {
            using (IDbConnection db = new SqlConnection(ConnectionString))
            {
                await db.UpdateAsync(entity);
            }
            return entity;
        }
    }


Enter fullscreen mode Exit fullscreen mode

Don't forget to inject the services in startup.cs and use it our services and run the project. we can see the project behaves same as before but right now we are not using raw sql queries with dapper instead some methods for crud using IDbConnection. we can have the same feeling like Entity Framework in dapper.

Top comments (0)