ASP.NET Core MVC CRUD Application with Dapper

Introduction

ASP.NET Core MVC (Model-View-Controller) is a powerful framework for building web applications. It provides a structured way to create web applications, separating the application logic into different components. When it comes to data access, developers have a variety of choices. One of the increasingly popular options is to use Dapper, a lightweight and efficient Object-Relational Mapping (ORM) library.

In this article, we will explore how to create a complete CRUD (Create, Read, Update, Delete) application using ASP.NET Core MVC and Dapper, leveraging the strengths of both technologies to build a robust web application.

What is Dapper?

Dapper is a micro ORM library for .NET that was developed by the Stack Overflow team. Unlike full-featured ORMs like Entity Framework, Dapper keeps it simple and lightweight. It provides a way to map database records to .NET objects without introducing excessive complexity. Dapper is known for its speed and efficiency, making it an excellent choice for performance-critical applications.

Building a CRUD Application with ASP.NET Core MVC and Dapper

To build a complete CRUD application with ASP.NET Core MVC and Dapper, you'll need the following components-

1. Visual Studio or any other code editor.
2. SQL Server or another database server.
3. ASP.NET Core MVC project.

Let's dive into the process.

Create a New ASP.NET Core MVC Project

Start by creating a new ASP.NET Core MVC project in Visual Studio. You can choose the "Web Application" template and select "MVC" as the project type.

Step 1

Step 2

Step 3

Install Dapper

Install the Dapper library via NuGet Package Manager.

Database Connection

Set up a database connection to your SQL Server. Use the connection string in your application to connect to the database.

Step 1. Open SQL Server Management Studio (SSMS).

Step 2. Connect to a SQL Server instance by providing the server name and authentication details.

Step 3. In the Object Explorer, right-click on "Databases" under the target server, then select "New Database."

 

Step 4. To create a table and CRUD (Create, Read, Update, Delete) stored procedures for that table in an SQL Server database, follow these steps.

Create 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. Implement a data access layer using Dapper. This layer will contain methods for performing CRUD operations 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 { });
        }
    }
}

Controller Actions

Create controller actions that interact with your data access layer. These actions will handle requests, invoke the corresponding 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

Conclusion

ASP.NET Core MVC, combined with Dapper, provides a powerful and efficient framework for building CRUD applications. Dapper's lightweight and high-performance data access capabilities make it a great choice for applications that require speed and simplicity. By following the steps outlined in this article, you can create a robust CRUD application that effectively manages data and provides a seamless user experience. As you become more familiar with Dapper and ASP.NET Core MVC, you can further enhance your application with additional features and functionalities.

FAQs

1. What is Dapper, and how does it differ from Entity Framework?

Dapper is a micro ORM that offers lightweight, high-performance data access. It differs from Entity Framework by providing a simpler and more manual approach to data mapping, making it a preferred choice for some applications due to its efficiency.

2. Can I use Dapper with other database systems, or is it limited to SQL Server?

Dapper is not limited to SQL Server; it can be used with various database systems, including PostgreSQL, MySQL, SQLite, and others. It works with any database system that has an ADO.NET provider.

3. Are there any security considerations when using Dapper in an MVC application?

Yes, it's essential to use parameterized queries to prevent SQL injection attacks when using Dapper. Always validate and sanitize user inputs before passing them to your Dapper queries.

4. How can I handle validation and error handling in an ASP.NET MVC application using Dapper?

You can implement validation using Data Annotations in your model classes and use ModelState for error handling. Additionally, you should handle database-related exceptions when executing Dapper queries and provide appropriate error messages to users.

5. Are there any best practices for structuring the project when building an ASP.NET MVC CRUD application with Dapper?

It's a good practice to follow the separation of concerns and use a layered architecture, separating the data access layer from the business logic and presentation layers. Additionally, consider using dependency injection for managing dependencies.

6. Can I use Dapper in combination with other ORMs like Entity Framework in the same project?

While it's possible to use multiple ORMs in the same project, it can lead to complexity and potential conflicts. It's generally better to choose one ORM that best fits your application's needs.

7. Are there any limitations to using Dapper that developers should be aware of?

Dapper is not a full-featured ORM, so it doesn't provide some advanced features like automatic change tracking or lazy loading. Developers need to write more manual code for mapping data between the database and the application.