ASP.NET Core MVC (Model-View-Controller) is a sophisticated web application framework. It offers an organized approach to developing web applications by splitting application logic into separate components. When it comes to data access, developers have several options. Dapper, a lightweight and efficient Object-Relational Mapping (ORM) library, is one of the more popular choices.
In this post, we'll look at how to build a comprehensive CRUD (Create, Read, Update, Delete) application with ASP.NET Core MVC and Dapper, combining the characteristics of both technologies to create a strong web application.
What exactly is Dapper?
Dapper is a.NET micro ORM library created by the Stack Overflow team. Dapper, in contrast to full-featured ORMs such as Entity Framework, keeps things simple and lightweight. It allows you to map database records to.NET objects without adding unnecessary complexity. Dapper is well-known for its speed and efficiency, making it an ideal candidate for high-performance applications.
Using ASP.NET Core MVC with Dapper to Create a CRUD Application
You'll need the following components to construct a comprehensive CRUD application with ASP.NET Core MVC and Dapper:
- Visual Studio or any other code editor will suffice.
- SQL Server or another database server may be used.
- ASP.NET Core MVC application.
Let us now begin the process.
Make an ASP.NET Core MVC Project.
Begin by launching Visual Studio and creating a new ASP.NET Core MVC project. You can select the "Web Application" template and the "MVC" project type.
Step 1
Step 2
Step 3
Install Dapper
Install the Dapper library via NuGet Package Manager.
Database Interconnection
Connect to your SQL Server via a database connection. To connect to the database, use the connection string in your application.
Step 1. Launch SQL Server Management Studio (SSMS).
Step 2: Establish a connection to a SQL Server instance by entering the server name and authentication credentials.
Step 3: In the Object Explorer, right-click on the destination server's "Databases" folder and select "New Database."
Step 4: In a SQL Server database, follow these steps to build a table and CRUD (build, Read, Update, Delete) stored procedures for that table.
Make a Table
CREATE TABLE dbo.Person(
Id INT PRIMARY KEY IDENTITY,
FullName NVARCHAR (100) NOT NULL,
Email NVARCHAR (100) NOT NULL,
[Address] NVARCHAR (200) NOT NULL
);
Create CRUD Stored Procedures
/* CREATE OPERATION */
CREATE PROCEDURE sp_add_person(
@name NVARCHAR(100),
@emil NVARCHAR(100),
@address NVARCHAR(200)
)
AS
BEGIN
INSERT INTO dbo.Person (FullName, Email, [Address])
VALUES (@name, @emil, @address)
END
/* READ OPERATION */
CREATE PROCEDURE sp_get_Allperson
AS
BEGIN
SELECT * FROM dbo.Person
END
/* UPDATE OPERATION */
CREATE PROCEDURE sp_update_person(
@id INT,
@name NVARCHAR(100),
@email NVARCHAR(100),
@address NVARCHAR(200)
)
AS
BEGIN
UPDATE dbo.Person
SET FullName = @name, Email = @email, [Address] = @address
WHERE Id = @id
END
/* DELETE OPERATION */
CREATE PROCEDURE sp_delete_person(@id INT)
AS
BEGIN
DELETE FROM dbo.Person WHERE Id = @id
END
Create a Data Access Layer
Start by Adding a new Class Library project to your project.
Step 1
Step 2
Step 3. Define a model class to represent the data you want to manipulate.
public class Person
{
public int Id { get; set; }
[Required]
public string? FullName { get; set; }
[Required]
public string? Email { get; set; }
public string? Address { get; set; }
}
Step 4: Using Dapper, create a data access layer. This layer will include methods for carrying out CRUD tasks on your model objects.
using Microsoft.Extensions.Configuration;
using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
namespace DapperMVC.Data.DataAccess
{
public class SqlDataAccess: ISqlDataAccess
{
private readonly IConfiguration _configuration;
public SqlDataAccess(IConfiguration configuration)
{
_configuration = configuration;
}
public async Task<IEnumerable<T>> GetData<T, P>(string spName, P parameters, string connectionId = "conn")
{
try {
string connectionString = _configuration.GetConnectionString(connectionId);
using (IDbConnection dbConnection = new SqlConnection(connectionString))
{
return await dbConnection.QueryAsync<T>(spName, parameters, commandType: CommandType.StoredProcedure);
}
}
catch (Exception ex)
{
throw;
}
}
public async Task<bool> SaveData<T>(string spName, T parameters, string connectionId = "conn")
{
try
{
using IDbConnection connection = new SqlConnection(_configuration.GetConnectionString(connectionId));
await connection.ExecuteAsync(spName, parameters, commandType: CommandType.StoredProcedure);
return true;
}
catch (Exception ex)
{
return false;
}
}
}
}
using DapperMVC.Data.DataAccess;
using DapperMVC.Data.Models.DbModel;
using Microsoft.Extensions.Configuration;
namespace DapperMVC.Data.Repository
{
public class PersonRepository : IPersonRepository
{
private readonly ISqlDataAccess _dataAccess;
private readonly IConfiguration _configuration;
public PersonRepository(ISqlDataAccess db, IConfiguration configuration)
{
_dataAccess = db;
_configuration = configuration;
}
public async Task<bool> AddAsync(Person person)
{
try
{
await _dataAccess.SaveData("sp_add_person", new
{
Name = person.FullName,
email = person.Email,
address = person.Address
});
return true;
}
catch (Exception ex)
{
return false;
}
}
public async Task<bool> UpdateAsync(Person person)
{
try
{
await _dataAccess.SaveData("sp_update_person", new
{
id = person.Id,
Name = person.FullName,
email = person.Email,
address = person.Address
});
return true;
}
catch (Exception ex)
{
return false;
}
}
public async Task<bool> DeleteAsync(int id)
{
try
{
await _dataAccess.SaveData("sp_delete_person", new { Id = id });
return true;
}
catch (Exception ex)
{
return false;
}
}
public async Task<Person?> GetByIdAsync(int id)
{
IEnumerable<Person> result = await _dataAccess.GetData<Person, dynamic>
("sp_get_person", new { Id = id });
return result.FirstOrDefault();
}
public async Task<IEnumerable<Person>> GetAllPersonAsync()
{
string query = "sp_get_Allperson";
return await _dataAccess.GetData<Person, dynamic>(query, new { });
}
}
}
Actions of the Controller
Make controller actions that communicate with your data access layer. These actions will process requests, call the appropriate data access methods, and return views.
public IActionResult Person()
{
return View();
}
[HttpGet]
public async Task<IActionResult> Add()
{
return View();
}
[HttpPost]
public async Task<IActionResult> Add(Person person)
{
try
{
if (!ModelState.IsValid)
{
return View(person);
}
bool addPerson = await _personRepo.AddAsync(person);
if (addPerson)
{
TempData["msg"] = "Successfully Added";
}
else
{
TempData["msg"] = "Could Not Added";
}
}
catch (Exception ex)
{
TempData["msg"] = "Could Not Added";
}
return RedirectToAction(nameof(Add));
}
[HttpGet]
public async Task<IActionResult> Edit(int id)
{
var person = await _personRepo.GetByIdAsync(id);
if (person == null)
{
throw new Exception();
}
return View("Edit", person);
}
[HttpPost]
public async Task<IActionResult> Edit(Person person)
{
try
{
if (!ModelState.IsValid)
{
return View(person);
}
var updateResult = await _personRepo.UpdateAsync(person);
if (updateResult)
{
TempData["msg"] = "Edit Successfully.";
return RedirectToAction(nameof(DisplayAllPerson));
}
else
{
TempData["msg"] = "Could Not Edit.";
return View(person);
}
}
catch (Exception ex)
{
TempData["msg"] = "Could Not Edit.";
return View(person);
}
}
[HttpGet]
public async Task<IActionResult> DisplayAllPerson()
{
try
{
var personAll = await _personRepo.GetAllPersonAsync();
return View(personAll);
}
catch (Exception ex)
{
return View("Error", ex);
}
}
[HttpGet]
public async Task<IActionResult> Delete(int id)
{
var deleteResult = await _personRepo.DeleteAsync(id);
return RedirectToAction(nameof(DisplayAllPerson));
}
Views
Create views for your controller actions. You can use Razor views to generate HTML content and display data to users.
Implement the CRUD Operations
Create controller actions and views to handle Create, Update, and Delete operations. You'll need forms in your views for creating and editing data and buttons or links to delete records.
Test and Debug
Test your application thoroughly, making sure all CRUD operations work as expected. Debug any issues that arise.
Output
When paired with Dapper, ASP.NET Core MVC provides a robust and efficient framework for developing CRUD apps. Because of Dapper's lightweight and high-performance data access capabilities, it is an excellent solution for applications that require speed and simplicity. You can construct a powerful CRUD application that properly manages data and delivers a seamless user experience by following the steps indicated in this article. As you gain experience with Dapper and ASP.NET Core MVC, you can add more features and functionalities to your application.