Dapper is an open-source library that acts as a bridge between your .NET application and the database. Unlike traditional ORMs like Entity Framework, which can be more complex and heavyweight, Dapper focuses on speed and simplicity. It allows developers to execute SQL queries directly while still providing a convenient way to map the results to .NET objects.

Key Features of Dapper:
  • Performance: Dapper is known for its speed. It is almost as fast as using raw ADO.NET, making it an excellent choice for performance-critical applications.
  • Simplicity: The API is straightforward, allowing developers to execute SQL queries with minimal overhead.
  • Flexibility: Dapper does not impose a specific way of structuring your database or your code, giving you the freedom to design your application as you see fit.
  • Support for Multiple Databases: Dapper works with various database systems, including SQL Server, MySQL, PostgreSQL, and SQLite.

Let's Build an API's To Get Data using Dapper for better Illustration.
1- Install Dapper :
You can install Dapper via NuGet Package Manager. Run the following command in your Package Manager Console:
Install-Package Dapper

Here we learn how to fetch data from database and bind it your model.
property name should be same as it in your table . I have a table named "StudentDetails" which contains Student Details .




2- Create an simple procedure to get student data :

    Create proc GetStudentDetails
    as
    Begin
    Select * from StudentDetails
    End

3- Create an model to define student property : "StudentDetails"

    namespace DapperApp.model
    {
        public class StudentDetails
        {
            public int StudentID { get; set; }
            public string? StudentName { get; set; }

            public string? Email { get; set; }
            public DateTime DOB { get; set; }
            public string? Gender { get; set; }
            public DateTime CreatedDate { get; set; }
            public string? ModifiedBy { get; set; }
            public DateTime modified { get; set; }
        }
    }


4- Create service and Repository as given below :
 Service :
  • IStudentService :
  • using DapperApp.model;

    namespace DapperApp.Service
    {
        public interface IStudentService
        {
            public Task<IEnumerable<StudentDetails>> GetStudentDetails();
        }
    }

  • StudentService:
  • using DapperApp.model;
    using DapperApp.Repository;

    namespace DapperApp.Service
    {
        public class StudentService :IStudentService
        {
            private readonly IStudentRepository studentRepository;
            public StudentService(IStudentRepository studentRepository)
            {
                this.studentRepository = studentRepository;
            }

            public async Task<IEnumerable<StudentDetails>> GetStudentDetails()
            {
                return await this.studentRepository.GetStudentDetails();    
            }
        }
    }

Repository :
  • IStudentRepository 
  • using DapperApp.model;

    namespace DapperApp.Repository
    {
        public interface IStudentRepository
        {
            public Task<IEnumerable<StudentDetails>> GetStudentDetails();
        }
    }

  • StudentRepository
  • using System.Data;
    using Dapper;
    using DapperApp.model;
    using Microsoft.Data.SqlClient;
    namespace DapperApp.Repository
    {
        public class StudentRepository :IStudentRepository
        {
            private readonly IConfiguration _configuration;
            private readonly SqlConnection _connection;
            public StudentRepository(IConfiguration configuration)
            {
                _configuration = configuration;
                _connection = new SqlConnection(configuration.GetConnectionString("DBString"));
            }
            public async Task<IEnumerable<StudentDetails>> GetStudentDetails()
            {
                return await _connection.QueryAsync<StudentDetails>("GetStudentDetails", commandType: CommandType.StoredProcedure);
            }
        }
    }

5- Controller :

using DapperApp.Service;
using Microsoft.AspNetCore.Mvc;

namespace DapperApp.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class MasterController : ControllerBase
    {
        private readonly IStudentService _studentService;
        public MasterController(IStudentService studentService)
        {
            _studentService = studentService;
        }

        [HttpPost]
        [Route("GetStudentDetails")]
        public async Task<IActionResult> GetStudentDetails()
        {
            return Ok(await this._studentService.GetStudentDetails());

        }
    }
}


6- Add your database connectionstring to appsettings.json as given below:

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "DBString": "Data Source=DESKTOP-SURYA\\SQLEXPRESS;
Initial Catalog=yourdatabaseName;Encrypt=false;Integrated Security=True;Trust Server Certificate=True"
    }
}



7- Now Inject Service and Repository : Add below code to your program.cs file



using DapperApp.Repository;
using DapperApp.Service;

var builder = WebApplication.CreateBuilder(args);

// inject service and repository start here
builder.Services.AddTransient<IStudentService, StudentService>();
builder.Services.AddTransient<IStudentRepository, StudentRepository>();
// inject service and repository end here
builder.Services.AddControllers();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();


Now run the project : Click on GetStudentDetails API's to see Response

API's Response :

[
    {
      "studentID": 6,
      "studentName": "Nitya Thakur",
      "email": "Nitya@gmail.com",
      "dob": "2000-12-02T00:00:00",
      "gender": "female",
      "createdDate": "2024-05-22T00:16:15.993",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    },
    {
      "studentID": 9,
      "studentName": "Surya Prakash",
      "email": "Surya@gmail.com",
      "dob": "1999-12-15T00:00:00",
      "gender": "male",
      "createdDate": "2024-06-30T23:20:18.88",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    },
    {
      "studentID": 10,
      "studentName": "Suraj",
      "email": "Suraj@gmail.com",
      "dob": "1999-12-15T00:00:00",
      "gender": "male",
      "createdDate": "2024-06-30T23:23:21.867",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    },
    {
      "studentID": 11,
      "studentName": "surya",
      "email": "surya@gmail.com",
      "dob": "1999-01-15T00:00:00",
      "gender": "male",
      "createdDate": "2024-06-30T23:32:17.68",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    },
    {
      "studentID": 14,
      "studentName": "hi",
      "email": "hi@gmail.com",
      "dob": "2024-07-05T00:00:00",
      "gender": "male",
      "createdDate": "2024-07-05T00:01:00.52",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    },
    {
      "studentID": 15,
      "studentName": "sharvan",
      "email": "sharvan#gmail.com",
      "dob": "1999-12-15T00:00:00",
      "gender": "male",
      "createdDate": "2024-07-12T23:48:14.783",
      "modifiedBy": null,
      "modified": "0001-01-01T00:00:00"
    }
  ]

Leave a Reply

Your email address will not be published. Required fields are marked *


Talk to us?

Would you mind helping us.
Post your blog

F.A.Q

Frequently Asked Questions