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");
}
}
}
Dr GomathiPosted Jan 3, 2024, 2:56 PM
Clearly explained. Thanks for this article.