CRUD (Create, Read, Update, Delete) operations in ASP.NET Core using Dapper. Dapper is a lightweight ORM (Object-Relational Mapping) tool that can be used to simplify database operations in.NET applications.
To get started, you'll need to have ASP.NET Core and Dapper installed in your project. You can install Dapper via NuGet by running the following command in the NuGet Package Manager Console:
Install-Package Dapper
Once you have everything set up, let's dive into the code. Here's an example of how to perform CRUD operations using Dapper in ASP.NET Core:
- Create a Model class representing your data entity. For example, let's create a
Productclass:
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
- Create a repository class that will handle the database operations using Dapper. Here's an example of a
ProductRepositoryclass:
using Dapper;
using Microsoft.Extensions.Configuration;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
public class ProductRepository
{
private readonly string _connectionString;
public ProductRepository(IConfiguration configuration)
{
_connectionString = configuration.GetConnectionString("DefaultConnection");
}
public IDbConnection Connection
{
get
{
return new SqlConnection(_connectionString);
}
}
public async Task<IEnumerable<Product>> GetAllProductsAsync()
{
using (IDbConnection conn = Connection)
{
conn.Open();
string query = "SELECT * FROM Products";
return await conn.QueryAsync<Product>(query);
}
}
public async Task<Product> GetProductByIdAsync(int id)
{
using (IDbConnection conn = Connection)
{
conn.Open();
string query = "SELECT * FROM Products WHERE Id = @Id";
return await conn.QuerySingleOrDefaultAsync<Product>(query, new { Id = id });
}
}
public async Task<int> AddProductAsync(Product product)
{
using (IDbConnection conn = Connection)
{
conn.Open();
string query = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT CAST(SCOPE_IDENTITY() as int)";
return await conn.ExecuteScalarAsync<int>(query, product);
}
}
public async Task<bool> UpdateProductAsync(Product product)
{
using (IDbConnection conn = Connection)
{
conn.Open();
string query = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id";
int rowsAffected = await conn.ExecuteAsync(query, product);
return rowsAffected > 0;
}
}
public async Task<bool> DeleteProductAsync(int id)
{
using (IDbConnection conn = Connection)
{
conn.Open();
string query = "DELETE FROM Products WHERE Id = @Id";
int rowsAffected = await conn.ExecuteAsync(query, new { Id = id });
return rowsAffected > 0;
}
}
}
In the ProductRepository class, we establish a database connection, define methods for performing CRUD operations, and use Dapper's extension methods for executing SQL queries.
- In your ASP.NET Core controller, inject the
ProductRepositoryand use it to perform CRUD operations. Here's an example of a controller that uses theProductRepository:
csharp
using Microsoft.AspNetCore.Mvc;
using System.Threading.Tasks;
Top comments (0)