How To create Generic Repository using Dapper in .NET Core?
Dapper is an ORM (Object-Relational Mapper) or, more precisely, a Micro ORM, which we can use to communicate with the database in our projects. We can write SQL statements using Dapper as we would in the SQL Server. Dapper performs well because it doesn’t translate queries we write in .NET to SQL.
It is important to know that Dapper is SQL Injection safe because we can use parameterized queries, which we should always do. One more important thing is that Dapper supports multiple database providers. It extends ADO.NET’s IDbConnection and provides useful extension methods to query our database. Of course, we have to write queries compatible with our database provider.
Company.cs
using System.ComponentModel.DataAnnotations;
namespace DapperApp.Model
{
public class Company
{
public int Id { get; set; }
[Display(Name = "Company Name")]
[Required(ErrorMessage = "Company Name is required")]
public string CompanyName { get; set; }
[Display(Name = "Company Address")]
[Required(ErrorMessage = "Company Address is required")]
public string CompanyAddress { get; set; }
[Required(ErrorMessage = "Country is required")]
public string Country { get; set; }
[Display(Name = "Glassdoor Rating")]
[Range(1, 5, ErrorMessage = "Glassdoor Rating must be between 1 and 5")]
public int GlassdoorRating { get; set; }
}
}
HomeController.cs
using Dapper;
using DapperApp.Model;
using DapperApp.Repository;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Data;
namespace DapperApp.ControllerApp
{
public class HomeController : Controller
{
readonly IGenericRepository _genericController;
public HomeController(IGenericRepository genericController)
{
_genericController = genericController;
}
// GET: HomeController
public async Task<IActionResult> Index()
{
var query = "SELECT * FROM Companies";
var companies = await _genericController.GetData<Company>(query);
return View(companies);
}
// GET: HomeController/Details/5
public async Task<IActionResult> Details(int id)
{
var query = "SELECT * FROM Companies WHERE Id = @Id";
if (id == null)
{
return NotFound();
}
var company = await _genericController.GetDataById<Company>(query, id);
if (company == null)
{
return NotFound();
}
return View(company);
}
// GET: HomeController/Create
public ActionResult Create()
{
return View();
}
// POST: HomeController/Create
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("Id,CompanyName,CompanyAddress,Country,GlassdoorRating")] Company company)
{
var parameters = new DynamicParameters();
parameters.Add("CompanyName", company.CompanyName, DbType.String);
parameters.Add("CompanyAddress", company.CompanyAddress, DbType.String);
parameters.Add("Country", company.Country, DbType.String);
parameters.Add("GlassdoorRating", company.GlassdoorRating, DbType.Int32);
var query = "INSERT INTO Companies (CompanyName, CompanyAddress, Country,GlassdoorRating) VALUES (@CompanyName, @CompanyAddress, @Country, @GlassdoorRating)";
if (ModelState.IsValid)
{
await _genericController.CreateData(query, parameters);
return RedirectToAction("Index");
}
return View(company);
}
// GET: HomeController/Edit/5
public ActionResult Edit(int id)
{
return View();
}
// POST: HomeController/Edit/5
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(int id, [Bind("Id,CompanyName,CompanyAddress,Country,GlassdoorRating")] Company company)
{
if (id != company.Id)
{
return NotFound();
}
var parameters = new DynamicParameters();
parameters.Add("CompanyName", company.CompanyName, DbType.String);
parameters.Add("CompanyAddress", company.CompanyAddress, DbType.String);
parameters.Add("Country", company.Country, DbType.String);
parameters.Add("GlassdoorRating", company.GlassdoorRating, DbType.Int32);
parameters.Add("Id", company.Id, DbType.Int32);
var query = "UPDATE Companies SET CompanyName = @CompanyName, CompanyAddress = @CompanyAddress, Country = @Country, GlassdoorRating = @GlassdoorRating WHERE Id = @Id";
if (ModelState.IsValid)
{
await _genericController.UpdateData(query, parameters);
return RedirectToAction("Index");
}
return View(company);
}
// GET: HomeController/Delete/5
public ActionResult Delete(int id)
{
return View();
}
// POST: HomeController/Delete/5
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> DeleteConfirmed(int id)
{
var query = "DELETE FROM Companies WHERE Id = @Id";
await _genericController.DeleteData(query, id);
return RedirectToAction("Index");
}
}
}
DapperContext.cs
using System.Data;
using Microsoft.Data.SqlClient;
namespace DapperApp.Context
{
public class DapperContext
{
private readonly IConfiguration _configuration;
private readonly string _connectionString;
public DapperContext(IConfiguration configuration)
{
_configuration = configuration;
_connectionString = _configuration.GetConnectionString("DefaultConnection");
}
public IDbConnection CreateConnection() => new SqlConnection(_connectionString);
}
}
IGenericRepository.cs
using DapperApp.Model;
namespace DapperApp.Repository
{
public interface IGenericRepository
{
Task CreateData(string query, DynamicParameters parameters);
Task DeleteData(string query, int id);
Task<T> GetDataById<T>(string query, int id);
Task<IEnumerable<T>> GetData<T>(string SQL, string DbName = "TEST");
Task UpdateData(string query, DynamicParameters parameters);
}
}
GenericRepository.cs
using DapperApp.Context;
using DapperApp.Model;
using DapperApp.Repository;
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
namespace DapperApp.Repository
{
public class GenericRepository: IGenericRepository
{
readonly DapperContext _context;
public GenericRepository(DapperContext context)
{
this._context = context;
}
public async Task<IEnumerable<T>> GetData<T>(string SQL, string DbName = "TEST")
{
using (var connection = _context.CreateConnection())
{
var companies = await connection.QueryAsync<T>(SQL);
return companies.ToList();
}
}
public async Task<T> GetDataById<T>(string query, int id)
{
using (var connection = _context.CreateConnection())
{
var company = await connection.QuerySingleOrDefaultAsync<T>(query, new { id });
return company;
}
}
public async Task CreateData(string query, DynamicParameters parameters)
{
using (var connection = _context.CreateConnection())
{
await connection.ExecuteAsync(query, parameters);
}
}
public async Task UpdateData(string query, DynamicParameters parameters)
{
using (var connection = _context.CreateConnection())
{
await connection.ExecuteAsync(query, parameters);
}
}
public async Task DeleteData(string query, int id)
{
using (var connection = _context.CreateConnection())
{
await connection.ExecuteAsync(query, new { id });
}
}
}
}