ASP.NET Core 6 API: How to use Dapper With ASP.NET Core API

-

In this article, we will see an implementation of using Dapper in ASP.NET Core API for working with data access to perform CRUD operations. 

What is Dapper?

Dapper is a popular simple object mapping tool. It is Object Relational Mapping (ORM). Practically this falls into the category of the micro-ORM.  We use Dapper in our application to communicate with the database to perform CRUD operations. The Dapper has huge performance benefits because it does not translate queries written in .NET to SQL. One of the recommended practices is that the parameterized queries are supported by Dapper which helps to avoid SQL injections and we must always follow this as a practice. Technically, Dapper extends IDbConnection so that it can support multiple database providers. There are the following useful extension methods 

  • Query: The extension method that is used to execute the query and map result.
  • QueryFirst: This method is used to execute the query and map the first result.
  • QueryFirstOrDefault: This method is used to execute a query and map the first result and if the sequence does not contain any element then the Default value will be returned.
  • QuerySingleOrDefault: This method executes a query and maps the result or a default value if the sequence is empty. This method throws an exception if there is more than one element in the sequence. 
  • QuerySingle: This method executes a query and maps the result. If there is not exactly one element in the sequence then this method throws an exception.
  • QueryMutiple: This is an extension method that is used to execute multiple queries using the same command object and map the result.
  • Execute: This is an extension method that we use to execute the command one or more times and return the number of affected rows.   

The code of this article is implemented using Visual Studio Enterprise 2022 for Mac.  The scrips showed in Listing 1 show statements scripts for creating tables.    


Create Database UCompany;

Use UCompany;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
	[DeptNo] [int] NOT NULL,
	[DeptName] [varchar](200) NOT NULL,
	[Location] [varchar](100) NOT NULL,
	[Capacity] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Department] ADD PRIMARY KEY CLUSTERED 
(
	[DeptNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employee](
	[EmpNo] [int] NOT NULL,
	[EmpName] [varchar](400) NOT NULL,
	[Designation] [varchar](100) NOT NULL,
	[Salary] [int] NOT NULL,
	[DeptNo] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee] ADD PRIMARY KEY CLUSTERED 
(
	[EmpNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD FOREIGN KEY([DeptNo])
REFERENCES [dbo].[Department] ([DeptNo])
GO

Listing 1: Database Scripts

Step 1: Open Visual Studio and create an ASP.NET Core 6 API Project and name this project as Core_Dapper. Since we will be using Dapper and SQL Data Providers, we need to install the following two Nuget Packages for the project

dotnet add package Dapper

dotnet add package Microsoft.Data.SqlClient 

The Dapper package provides an access to the ORM which we can use to perform CRUD Operations with the database.

Step 2: In the project add a new folder and name it Models. In this folder add a class file and name it as ModelClasses.cs. In this class file, add the Department and Employee classes that we will be used for mapping with Department and Employee table. The code for these classes is shown in Listing 2


namespace Core_Dapper.Models
{
    public abstract class EntityBase { }

    public class Department:EntityBase
    {
        public int DeptNo { get; set; }
        public string DeptName { get; set; } = string.Empty;
        public string Location { get; set; } = string.Empty;
        public int Capacity { get; set; }
        public List<Employee> Employees { get; set; } = new List<Employee>();
    }

    public class Employee : EntityBase
    {
        public int EmpNo { get; set; }
        public string EmpName { get; set; } = string.Empty;
        public string Designation { get; set; } = string.Empty;
        public int Salary { get; set; }
        public int DeptNo { get; set; }
    }
}

Listing 2: Department and Employees classes

Listing 2, shows the Department and Employee classes are derived from the EntityBase abstract class. We will be using the EntityBase abstract class for defining Constraints for a generic interface which we will be creating in the forthcoming steps.     

Step 3: Let's modify the appsettings.json file where we will be defining the database connection string and queries to perform CRUD operations as shown in Listing 3


"ConnectionStrings": {
    "DbConnection": "Data Source=[MY-SERVER];Initial Catalog=UCompany;User Id=[USER-NAME];Password=[PASSWORD];Encrypt=false"
  },
  "Queries": {
    "AllDept": "Select DeptNo,DeptName,Location,Capacity from Department",
    "DeptByNo": "Select DeptNo,DeptName,Location,Capacity from Department where DeptNo=@DeptNo",
    "CreateDept": "Insert into Department (DeptNo,DeptName,Location,Capacity) Values (@DeptNo,@DeptName,@Location,@Capacity)",
    "UpdateDept": "Update Department Set DeptName=@DeptName,Location=@Location,Capacity=@Capacity where DeptNo=@DeptNo",
    "DeleteDept": "Delete Department where DeptNo=@DeptNo"
  }

Listing 3: appsettings.json

Please note that you replace the database server name, user id, and password. One more thing is that we have Encrypt=false in the connection string. This is since from the Microsoft.Data.SqlClient package version 4, if the database is not using the encryption, then the connection to the database will fail and the runtime exception will be thrown. We have also added queries for performing CRUD operations this will avoid any hardcoding in the code.

Step 4: In the project add a new folder and name it DataAccess. In this folder, we will add the Dapper Context class that will be used to connect to the database server by reading the connection string from appsettings.json file. In this folder add a new class file and name it CompanyDapperContext.cs. In this class file, add the code as shown in Listing 4


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

namespace Core_Dapper.DataAccess
{
    public class CompanyDapperContext
    {
        private readonly IConfiguration configuration;
        private readonly string connStr;
        public CompanyDapperContext(IConfiguration configuration)
        {
            this.configuration = configuration;
            connStr = this.configuration.GetConnectionString("DbConnection");
        }
        public IDbConnection CreateConnection()
            => new SqlConnection(connStr);
    }
}

Listing 4: The Datapper Context class

The CreateConnection() method returns an instance of the SqlConnection class. This will be used to connect to the database server.

Step 5: Since we are using a Repository pattern here, we need to add an interface and the repository service classes in the project. In the project, add a new folder named Services. In this folder add an interface file and name it IServiceRepository.cs. In this file add the code as shown in Listing 5


using Core_Dapper.Models;

namespace Core_Dapper.Services
{
    public interface IServiceRepository<TEntity, in TPk> where TEntity : EntityBase
    {
        Task<IEnumerable<TEntity>> GetAsync();
        Task<TEntity> GetAsync(TPk id);
        Task<TEntity> CreateAsync(TEntity entity);
        Task<TEntity> UpdateAsync(TPk id, TEntity entity);
        Task<bool> DeleteAsync(TPk id);
    }
}

Listing 5: The IServiceRepository interface

The interface shown in Listing 5, is a multi-type generic interface, the TEntity generic parameter is applied with the constraints as EntityBase, which means that TEntity can be either EntityBase or any class derived from EntityBase. The interface has a definition for methods that will be used for performing CRUD Operations. The TPk generic type will be an input parameter to methods defined in the interface.

In the Services folder add a new class file and name it DepartmentServiceRepository.cs. In this class file, we will add the DepartmentServiceRepository class. The code for this class is shown in Listing 6


using Core_Dapper.DataAccess;
using Core_Dapper.Models;
using Dapper;
using Microsoft.AspNetCore.Identity;
using static Dapper.SqlMapper;

namespace Core_Dapper.Services
{
    public class DepartmentServiceRepository : IServiceRepository<Department, int>
    {
        private readonly CompanyDapperContext _context;
        private readonly IConfiguration _configuration;

        public DepartmentServiceRepository(CompanyDapperContext context, IConfiguration configuration)
        {
            _context = context;
            _configuration = configuration;
        }

        async Task<Department> IServiceRepository<Department, int>.CreateAsync(Department entity)
        {
            try
            {
                var query = _configuration["Queries:CreateDept"].ToString();
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@DeptNo", entity.DeptNo, System.Data.DbType.Int32);
                queryParameters.Add("@DeptName", entity.DeptName, System.Data.DbType.String);
                queryParameters.Add("@Location", entity.Location, System.Data.DbType.String);
                queryParameters.Add("@Capacity", entity.Capacity, System.Data.DbType.Int32);

                using (var conn = _context.CreateConnection())
                {
                    await conn.ExecuteAsync(query,queryParameters);    
                    return entity;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        async Task<bool> IServiceRepository<Department, int>.DeleteAsync(int id)
        {
            bool isDeleted = false;
            try
            {
                var query = _configuration["Queries:DeleteDept"].ToString();
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@DeptNo", id, System.Data.DbType.Int32);
                

                using (var conn = _context.CreateConnection())
                {
                    int result = await conn.ExecuteAsync(query, queryParameters);
                    if (result != 0)
                        isDeleted= true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isDeleted;
        }

        async Task<IEnumerable<Department>> IServiceRepository<Department, int>.GetAsync()
        {
            try
            {
                var query = _configuration["Queries:AllDept"].ToString();
                using (var conn = _context.CreateConnection())
                {
                    var departments = await conn.QueryAsync<Department>(query);
                    return departments;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        async Task<Department> IServiceRepository<Department, int>.GetAsync(int id)
        {
            try
            {
                var query = _configuration["Queries:DeptByNo"].ToString();
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@DeptNo", id, System.Data.DbType.Int32);
                using (var conn = _context.CreateConnection())
                {
                    var department = await conn.QuerySingleOrDefaultAsync<Department>(query, queryParameters);
                    return department;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        async Task<Department> IServiceRepository<Department, int>.UpdateAsync(int id, Department entity)
        {
            try
            {
                var query = _configuration["Queries:UpdateDept"].ToString();
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@DeptNo", entity.DeptNo, System.Data.DbType.Int32);
                queryParameters.Add("@DeptName", entity.DeptName, System.Data.DbType.String);
                queryParameters.Add("@Location", entity.Location, System.Data.DbType.String);
                queryParameters.Add("@Capacity", entity.Capacity, System.Data.DbType.Int32);

                using (var conn = _context.CreateConnection())
                {
                   int result =  await conn.ExecuteAsync(query, queryParameters);
                    if (result != 0)
                        return entity;
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}

Listing 6: The DepartmentServiceRepository class

As shown in the code of Listing 6, the DepartmentServiceRepository class is a constructor injected with CompanyDapperContext class and IConfiguration interface. Using the CompanyDapperContext the connection will be established with the SQL Server Database and the IConfiguration interface will be used to read appsettings.json file, which is used to read the Queries section of the appseetings.json file where we have database queries for performing CRUD operations. The DepartmentServiceRepository class implements the IServiceRepository interface using the Department class as a TEntity type parameter and the integer as the TPk type parameter. The DepartmentServiceRepository class implements methods of the IServiceRepository interface. Each of the methods of this class uses the CreateConnection() method of the CompanyDapperContext class to establish a connection with the database server by returning the Connection object. The Dapper package provides various extension methods e.g. ExecuteAsync(), QueryAsync(), QuerySingleOrDefaultAsync(), etc. methods to perform database query execution. The DynamicParameters class used by methods of the class is used to define parameters so that these parameters can be passed with parameterized queries. 

Now let's take an example of the CreateAsync() method of the DepartmentServiceRepository  class as shown in Listing 7,


async Task<Department> IServiceRepository<Department, int>.CreateAsync(Department entity)
        {
            try
            {
                var query = _configuration["Queries:CreateDept"].ToString();
                var queryParameters = new DynamicParameters();
                queryParameters.Add("@DeptNo", entity.DeptNo, System.Data.DbType.Int32);
                queryParameters.Add("@DeptName", entity.DeptName, System.Data.DbType.String);
                queryParameters.Add("@Location", entity.Location, System.Data.DbType.String);
                queryParameters.Add("@Capacity", entity.Capacity, System.Data.DbType.Int32);

                using (var conn = _context.CreateConnection())
                {
                    await conn.ExecuteAsync(query,queryParameters);    
                    return entity;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

Listing 7: The CreateAsync() method

The method uses the _configuration object to read CreateDept query define in Queries from the appsettings.json. Further, the DynamicParameters class is used to define parameters that will be passed to the parameterized query. The method uses the _context object of the CompanyDapperContext class to call the CreateConnection() method. As we discuss in Listing 4, the CreateConneciton() method returns an instance of the SqlConnection class. The Dapper adds ExecuteAsync() method to the Connection instance, this method accepts the query and dynamic parameters instance as input parameters and with these parameters, execution of the query will take place on the database table. Likewise, other methods are implemented as shown in listing 6 for performing Update,  Delete, etc. operations.         

Step 6: Modify the Program.cs to register CompanyDapperContext  and DepartmentServiceRepository classes in the dependency container as shown in Listing 8


builder.Services.AddSingleton<CompanyDapperContext>();
builder.Services.AddScoped<IServiceRepository<Department,int>,
DepartmentServiceRepository>();

Listing 8: The registration in the dependency container   

Step 7: Let's add an API controller named DepartmentController in the Controllers folder. We will be injecting IServiceRepository interface using a constructor in this controller class. Listing 9 shows the DepartmentController class with the HTTP methods


using Core_Dapper.Models;
using Core_Dapper.Services;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;

namespace Core_Dapper.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class DepartmentController : ControllerBase
    {
        private readonly IServiceRepository<Department, int> deptRepo;
        public DepartmentController(IServiceRepository<Department, int> deptRepo)
        {
            this.deptRepo = deptRepo;
        }

        [HttpGet]
        public async Task<IActionResult> GetAsync()
        {
            try
            {
                var result = await deptRepo.GetAsync();
                return Ok(result);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }
        [HttpGet("{id}")]
        public async Task<IActionResult> GetAsync(int id)
        {
            try
            {
                var result = await deptRepo.GetAsync(id);
                return Ok(result);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }

        [HttpPost]
        public async Task<IActionResult> PostAsync(Department department)
        {
            try
            {
                var result = await deptRepo.CreateAsync(department);
                return Ok(result);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }
        [HttpPut("{id}")]
        public async Task<IActionResult> PutAsync(int id,Department department)
        {
            try
            {
                var result = await deptRepo.UpdateAsync(id,department);
                return Ok(result);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }

        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteAsync(int id)
        {
            try
            {
                var result = await deptRepo.DeleteAsync(id);
                return Ok(result);
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }
    }
}

Listing 9: The DepartmentController class

The controller class defined HTTP Methods for processing HTTP GET, POST, PUT, and DELETE requests for performing CRUD operations.

Run the Project, and the browser will show the Swagger Page to test the API, alternatively, you can use Postman or AdvancedRESTClient to test APIs.   Figure 1 shows the Get response 



Figure 1: The Get response

Likewise, you can test other methods.

The code for this article can be downloaded from this link.      

Conclusion: The Dapper is a lightweight and high-performance Micro-ORM that we can easily use for Modern Web Apps as well as API Apps. 

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications. This is writt
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFileProvider service
Read more

ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-
Image
In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common. Oracle Data Provider for .NET Entity Framework Core The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.    Implementation To implement code
Read more