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

Introduction

In this article will learn CURD (Create, Update, Read and Delete) operation which common for all applications. Here will understand Azure SQL Database, Dapper Micro-ORM (Object Relation Mapper) along with repository pattern using ASP.NET Core 6 MVC.

What is Dapper?

Dapper is a micro ORM or it is a simple object mapper framework which helps to map the native query output to a domain class.

What is Repository Pattern?

Repository Design Pattern separates the data access logic and maps it to the entities in the business logic.

Create an Azure SQL Database

Step 1 If you don't have an Azure subscription, create an Azure free account before you begin.

Step 2 Sign in to the Azure portal with your Azure account.

Step 3 From the Azure portal menu, or from the Home page, select Create a resource

Step 4 On Azure portal in the Search box, enter SQL Database. From the results list, choose SQL Databases.

Step 5 On the SQL Database section, choose Create SQL Database.

Step 6 On the Create SQL Database section provide the following information:

1. Subscription: Select the desired Azure Subscription.

2. Resource Group, For Resource group, select Create new, enter DotNetAppGroup. You could choose existing resource group if you created.

3. Database Name: A unique name is required. For Database name, enter Companies-DB.

4. Server: For Server, select Create new, and fill out the New Server form with the following values:

• Server name: Enter companies-database and add some characters for uniqueness. We can't provide an exact server name to use because server names must be globally unique for all servers in Azure, not just unique within a subscription. So, enter something like companies-database, and the portal lets you know if it's available or not.
• Location: Select a location from the dropdown list.
• Authentication method: Select Use SQL authentication.
• Server admin login: Enter company-admin.
• Password: Enter a password that meets requirements and enter it again in the Confirm password field.
• Select OK.

5. Leave Want to use elastic pool set as No

6. Compute + Storage: Select configuration database Basic 2GB Storage

7. Backup storage redundancy set as: Go redundancy backup storage

8. After providing the information above, select Review + Create. Next validation page appears with Create button. You are done. You have created your first SQL Database.

Set server firewall

1. After the database deployment completes, select SQL databases from the left-hand menu and then select Adventure on the SQL databases page. The overview page for your database opens. It displays the fully qualified server name (such as companies-database.database.windows.net) and provides options for further configuration. You can also find the firewall settings by navigating directly to your server and selecting Networking under Security.

2. Copy the fully qualified server name. You will use it when you connect to your server and its databases in other quick starts. Select Set server firewall on the toolbar.

3. Set Public network access to Selected networks to reveal the virtual networks and firewall rules.

4. Choose Add your client IP to add your current IP address to a new, server-level, firewall rule. This rule can open Port 1433 for a single IP address or for a range of IP addresses. You can also configure firewall settings by choosing Add a firewall rule.

5. Select Save

Create Database Table in Azure SQL

Once your database is created, you can use the Query editor (preview) in the Azure portal to connect to the database and query data.

1. In the portal, search for and select SQL databases, and then select your database from the list.
2. On the page for your database, select Query editor (preview) in the left menu.
3. Enter your server admin login information and select OK.

Enter the following query in the Query editor pane.

Create table Companies
(
Id int primary key identity(1,1),
CompanyName nvarchar(150),
CompanyAddress nvarchar(250),
Country nvarchar(150),
GlassdoorRating int
)

Get Connection String from Azure SQL Database

1. Navigate to the database in the Azure portal and, under Settings, select Connection strings.
2. Review the complete ADO.NET connection string.
3. Copy the ADO.NET connection string if you intend to use it.

Creating a New Project in Visual Studio 2022

Start Visual Studio software and select Create a new project.

In the Create a new project dialog, select ASP.NET Core Web App (Model-View Controller) > Next.

In the Configure your new project dialog, enter CURDAzureSQLDapperCore6MVC_Demo for Project name. It's important to name the project CURDAzureSQLDapperCore6MVC_Demo. The capitalization needs to match each namespace when code is copied. Select Next.

In the Additional information dialog, select .NET 6.0 (Long-term support). Select Create

The Visual Studio project 2022 will now generate the structure for the selected application. In this example, we are using ASP.Net MVC so we can create a controller to write the code, or so we can use the existing controller. There you can enter the code and build/run the application.

Install the Dapper, Microsoft.Data.SqlClient Library through NuGet Package Manager

The Visual Studio software provides the Nuget Package manager option to install the package directly to the solution.

In Visual Studio Select Tools > NuGet Package Manager > Manage NuGet Packages for the solution. The below screenshot shows how to open the Nuget Package Manager.

Search for the specific package Dapper, Microsoft.Data.SqlClient using the search box on the upper left. Select a package from the list to display its information, enable the Install button and a version-selection drop-down, as shown in the below screenshot. The NuGet package will be installed for your project and reference will be added, as seen in the screenshot below.

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.