CURD Azure SQL Database Dapper And Repository Pattern Using ASP.NET Core 6 MVC

In the above image, we can see the list of the related search items. We need to select the required option to install the package to the solution.

Add Connection String

In solution explorer select a file appsettings.json double click on this file to open “Add” connection string as shown below.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=tcp:companies-database.database.windows.net,1433;Initial Catalog=companies-DB;Persist Security Info=False;User ID=company-admin;Password=Farhan@123;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
  }
}

Add Models class

Right-click the Models folder > Add > Class. Name the file Company.cs

using System.ComponentModel.DataAnnotations;

namespace CURDAzureSQLDapperCore6MVC_Demo.Models
{
    public class Company
    {
        [Display(Name ="SL No")]
        public int Id { get; set; }

        [Display(Name ="Company Name")]
        public string CompanyName { get; set; }

        [Display(Name = "Company Address")]
        public string CompanyAddress { get; set; }

        public string Country { get; set; }

        [Display(Name = "Glassdoor Rating")]
        public int GlassdoorRating { get; set; }
    }
}

Add DBContext Folder and DapperContext Class

Right click on Project in Solution Explorer>Add New>New Folder. Rename this folder to DBContext. Now right click on DBContext folder > Add> New Item> C# class name this class DapperContext.

using System.Data;
using System.Data.SqlClient;

namespace CURDAzureSQLDapperCore6MVC_Demo.DBContext
{
    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);
    }
}

Add Repositories Folder and ICompanyRepository Interface

Right click on Project in Solution Explorer>Add New>New Folder. Rename this folder to Repositories. Now right click on DBContext folder > Add> New Item>C# Interface and name this interface ICompanyRepository.

using CURDAzureSQLDapperCore6MVC_Demo.Models;

namespace CURDAzureSQLDapperCore6MVC_Demo.Repositories
{
    public interface ICompanyRepository
    {
        Task<IEnumerable<Company>> GetCompanies();
        Task<Company> GetCompany(int? id);
        Task CreateCompany(Company company);
        Task UpdateCompany(int id, Company company);
        Task DeleteCompany(int id);
    }
}

Add Company Repository class to Implement Interface

Right click on Repositories Add> New Item>C# class and name this class to CompanyRepository.

using CURDAzureSQLDapperCore6MVC_Demo.DBContext;
using CURDAzureSQLDapperCore6MVC_Demo.Models;
using Dapper;
using System.Data;

namespace CURDAzureSQLDapperCore6MVC_Demo.Repositories
{
    public class CompanyRepository : ICompanyRepository
    {
        private readonly DapperContext context;

        public CompanyRepository(DapperContext context)
        {
            this.context = context;
        }

        public async Task<IEnumerable<Company>> GetCompanies()
        {
            var query = "SELECT * FROM Companies";

            using (var connection = context.CreateConnection())
            {
                var companies = await connection.QueryAsync<Company>(query);
                return companies.ToList();
            }
        }

        public async Task<Company> GetCompany(int? id)
        {
            var query = "SELECT * FROM Companies WHERE Id = @Id";

            using (var connection = context.CreateConnection())
            {
                var company = await connection.QuerySingleOrDefaultAsync<Company>(query, new { id });
                return company;
            }
        }
        public async Task CreateCompany(Company company)
        {
            var query = "INSERT INTO Companies (CompanyName, CompanyAddress, Country,GlassdoorRating) VALUES (@CompanyName, @CompanyAddress, @Country,@GlassdoorRating)";

            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);

            using (var connection = context.CreateConnection())
            {
                await connection.ExecuteAsync(query, parameters);
            }
        }

        public async Task UpdateCompany(int id, Company company)
        {
            var query = "UPDATE Companies SET CompanyName=@CompanyName, CompanyAddress=@CompanyAddress, Country=@Country,GlassdoorRating=@GlassdoorRating WHERE Id = @Id";

            var parameters = new DynamicParameters();
            parameters.Add("Id", company.Id, DbType.Int32);
            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);

            using (var connection = context.CreateConnection())
            {
                await connection.ExecuteAsync(query, parameters);
            }
        }
        public async Task DeleteCompany(int id)
        {
            var query = "DELETE FROM Companies WHERE Id = @Id";
            using (var connection = context.CreateConnection())
            {
                await connection.ExecuteAsync(query, new { id });
            }
        }
    }
}

Add a controller

In Solution Explorer, right-click Controllers > Add > Controller.

In the Add New Scaffolded Item dialog box, select MVC Controller – MVC Controller with read/write actions > Add.

In the Add New Item - Companies dialog, enter CompaniesController.cs and select Add.

Replace the contents of Controllers/ CompaniesController.cs with the following code:

using CURDAzureSQLDapperCore6MVC_Demo.Models;
using CURDAzureSQLDapperCore6MVC_Demo.Repositories;
using Microsoft.AspNetCore.Mvc;

namespace CURDWithDapperCore6MVC_Demo.Controllers
{
    public class CompaniesController : Controller
    {
        private readonly ICompanyRepository repository;

        public CompaniesController(ICompanyRepository repository)
        {
            this.repository = repository;
        }
        public async Task<IActionResult> Index()
        {
            return View(await repository.GetCompanies());
        }
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var company = await repository.GetCompany(id);
            if (company == null)
            {
                return NotFound();
            }
            return View(company);
        }
        public ActionResult Create()
        {
            return View();
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create(Company company)
        {
            if (ModelState.IsValid)
            {
                await repository.CreateCompany(company);
                return RedirectToAction(nameof(Index));
            }
            return View();
        }

        // GET: CompaniesController/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var company = await repository.GetCompany(id);
            if (company == null)
            {
                return NotFound();
            }
            return View(company);
        }

        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, Company company)
        {
            if (id != company.Id)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                await repository.UpdateCompany(id, company);
                return RedirectToAction(nameof(Index));
            }
            return View();
        }

        public async Task<IActionResult> Delete(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var company = await repository.GetCompany(id);
            if (company == null)
            {
                return NotFound();
            }
            return View(company);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> ConfirmDelete(int id)
        {
            await repository.DeleteCompany(id);
            return RedirectToAction(nameof(Index));
        }
    }
}

Add a view

Right-click on the Action in CompaniesController, and then Add View.

In the Add New Scaffolded Item dialog:

• Select Razor View Select Add
• View Name: Index
• Template: List
• Model Class: Company
• Select Add

Replace the contents of the Views/Companies/Index.cshtml Razor view file with the following:

@model IEnumerable<CURDAzureSQLDapperCore6MVC_Demo.Models.Company>

@{
    ViewData["Title"] = "Index";
}

<h2 class="text-capilization text-center">List of Companies</h2>

<p>
    <a asp-action="Create" class="btn btn-primary"> <i class="fa-solid fa-circle-plus"></i> Add New</a>
</p>
<table class="table table-bordered">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Id)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.CompanyName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.CompanyAddress)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Country)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.GlassdoorRating)
            </th>
            <th>Action(s)</th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Id)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CompanyName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CompanyAddress)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Country)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.GlassdoorRating)
            </td>
            <td>
                 <a href="@Url.Action("Details",new { id=item.Id})" class="btn btn-sm btn-primary"><i class="fa-solid fa-eye"></i></a>
                 <a href="@Url.Action("Edit",new { id=item.Id})" class="btn btn-sm btn-info"><i class="fa-solid fa-pen-to-square"></i></a>
                 <a href="@Url.Action("Delete",new { id=item.Id})" class="btn btn-sm btn-danger"><i class="fa-solid fa-trash"></i></a>
            </td>
        </tr>
}
    </tbody>
</table>

Note: Similarly Add all action view like Details, Create, Edit and Delete

Now its time to build and run your application Ctrl+F5

Conclusion

The above article has taught us. How we can use dapper with repository pattern. Dapper is Micro ORM whereas Entity Framework is ORM. Dapper is faster than entity framework. Hope you enjoyed article. Happy Coding.