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.
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.
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.
Lets add a new class for our database object. Decorate the properties with primary key such as [Key].
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.
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();
}
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;
}
}
}
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.
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);
}
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);
}
}
Create a Controller named UserController and call services there to see the output of our code.
[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));
}
}
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
Let run our project and see the results.
Insert
GETAll
We can see our newly created entity. now lets update it.
Now entity has been updated lets check it using get by id.
Its working fine. now we will test the delete endpoint.
get entity by id again and see if its deleted.
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.
Create new Classes for Dapper Fast Crud.
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();
}
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;
}
}
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)