find and solve || findandsolve.com
Please wait.....

How to use Dapper in ASP.NET Core

Download source code from here
How to use Dapper in ASP.NET Core

Dapper is an Object-Relational Mapper (ORM) or to be more precise a Micro ORM, which you can use to communicate with the database in your projects. By using Dapper, you can write SQL statements as in your c# code.


Dapper is available in the NuGet library, can be used with any type of .NET project. We know that  It is a simple Object Mapping Framework or a Micro-ORM that helps you to Map the Data from the Result of an SQL Query to a .NET Class efficiently.


What is Dapper


Dapper is simple/ micro object-Relational Mapper (ORM) for the .NET world. It's available in the NuGet library that can be added to any .NET project for database operations. ORM  means the entire database can be operated in terms of Interfaces, OO classes, etc


ORM creates a "virtual database" in terms of classes and provides methods to work with those classes. Which is architected to focus on the most important task of working with database tables instead of tracking changes, modifying the database schema, creating, something like that.


The most important thing that The Dapper is in production use at Stack Overflow. it was introduced by the StackOverflow team to address their issues and open source.


Why Dapper is Important in c# programming language?


  • Dapper is used with any .NET project.  High performance Quite lightweight
  • Drastically reduces the database access code.
  • Focus on getting database tasks done instead of being full-on ORM.
  • Dapper work with- SQL Server, PostgreSQL, Oracle, SQLite, MySQL, or other databases.
  • For an existing database, using Dapper is an optimal choice.


How Does Dapper Work?

  • Creates an IDbConnection Object.
  • Write a query to perform CRUD Operations
  • Passes a Query as Parameter in the Execute Method.


Dapper Performance in asp.net core

The Dapper is Second Fastest ORM when compared with all Object-relational mappings.


Pre-requites

  •  Install .NET Core 5.0 or above SDK from here
  • Install the latest version of Visual Studio 209 or greater than from here 
  • SQL Server 2017/2019  or other versions from here  


Using Dapper in ASP.NET Core applications


Step 1.


Create New Application in visual studio 2019

  • Open your visual studio 2019 or greater than 2019
  • Click on Create a New Project
  • Select ASP.NET Core Web Application and then Next
  • Provide a Project name and confirm or change the Location. Select Create
  • Select the latest version of ASP.NET Core in the drop-down (.NET Core 5.0)and then select Web Application
  • Under Authentication, select Change and set the authentication to Individual User Accounts and then click on Create button



Step 2.

Now add the ASP.NET Core Libraries to set up the database and also Dapper library into your project from the Nuget Package Manager as given below.



When right-click on your main project "DapperExample" you will see as given below.



Step 3.

Now Create the Services folder under the "DapperExample" project and add one Interface(IDapper.cs) and one Class(Dapper.cs) to it.



Step 4.

Put the below code in your dapper.cs interface to where to perform the CRUD Operations in your project.

using System.Collections.Generic;
using System. Data;
using System.Data.Common;
namespace DapperExample.Service
{
    public interface IDapper: IDisposable
    {
        DbConnection GetDbconnection();
        T Edit<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        T Save<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
        T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure);
    }
}


Put the given below code in your Dapper.cs File where the actual method implementation takes place in it for every method which you're already declared in Interface

using Dapper;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System. Data;
using System.Data.Common;
using System.Data.SqlClient;
using System. Linq;

namespace DapperExample.Service
{
        public class Dapperr : IDapper
        {
            private readonly IConfiguration _config;
            private string Connectionstring = "DefaultConnection";
            public Dapper(IConfiguration config)
            {
                _config = config;
            }
            public void Dispose()
            {
            }
            public int Execute(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
            {                using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring)); var result = db.Query(sp, parms, commandType: commandType); return 1;
            }
            public T Edit<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.Text)
            {
                using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
                return db.Query<T>(sp, parms, commandType: commandType).FirstOrDefault();
            }
            public List<T> GetAll<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
            {
                using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
                return db.Query<T>(sp, parms, commandType: commandType).ToList();
            }
            public DbConnection GetDbconnection()
            {
                return new SqlConnection(_config.GetConnectionString(Connectionstring));
            }
            public T Save<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
            {
                T result;
                using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
                try
                {
                    if (db.State == ConnectionState.Closed)
                        db.Open();
                    using var tran = db.BeginTransaction();
                    try
                    {
                       result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
                       tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw ex;
                    }
                }
                catch (Exception ex)
                {                     throw ex;
                }
                finally
                {
                    if (db.State == ConnectionState.Open)
                        db.Close();
                }                 return result;
            }
            public T Update<T>(string sp, DynamicParameters parms, CommandType commandType = CommandType.StoredProcedure)
            {
                T result;
                using IDbConnection db = new SqlConnection(_config.GetConnectionString(Connectionstring));
                try
                {
                    if (db.State == ConnectionState.Closed)
                        db.Open();
                    using var tran = db.BeginTransaction();
                    try
                    {
                      result = db.Query<T>(sp, parms, commandType: commandType, transaction: tran).FirstOrDefault();
                       tran.Commit();
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        throw ex;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    if (db.State == ConnectionState.Open)
                        db.Close();
                }
                return result;
            }
        }
    }


Step 5.

Create a DataContext Folder under your project "DapperExample" and Add the DbConnection.cs class in it.



Put the Code in your DbConnection.cs class file to connect with the DbContext and also to make a connection with the Database.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
namespace DapperExample.DataContext
{
    public class DbConnection :DbContext
    {
        private IConfigurationRoot _config;
        public DbConnection(IConfigurationRoot config, DbContextOptions options) : base(options)
        {
            _config = config;
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer(_config["ConnectionStrings:DefaultConnection"]);
            //connection string get by appsetting.json
        }
    }
}


Step 6.

Put the  Connection String in the appsettings.json file is given below:

{

  "ConnectionStrings": {
    "windowsAuthentication": true,
    //this is windows athenticaiton
    "DefaultConnection": "Data Source=<sql-server-name>;Database=<database-name>;Trusted_Connection=True;"
    //"SqlAuthintication": "Server=SQ-9KQH8Q0;Database=abc;User ID=server;Password=password120;Trusted_Connection=True;Connection Timeout=30;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*"
}

For more details Connect  the Database to ASP.NET Core


Step 7.

Make the Connection setup in the Startup.cs file. 

using DapperExample.Service;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using System.Data.Common;
namespace DapperExample
{
    public class Startup
    {
        private IConfigurationRoot _config;
        public Startup(IConfiguration configuration, IWebHostEnvironment env)
        {
            var ConfigBuilder = new ConfigurationBuilder().SetBasePath(env.ContentRootPath)
                         .AddJsonFile("appsettings.json");
            _config = ConfigBuilder.Build();
        }
        public IConfiguration Configuration { get; }
        public IWebHostEnvironment environment;
        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton(_config);
            services.AddDbContext<DataContext.DbConnection>();
            //Register dapper in scope
            services.AddScoped<IDapper, Dapperr>();
            services.AddControllersWithViews();
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }
            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllerRoute(
                    name: "default",
                    pattern: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}



Step 8.

Add the StudentInfoViewModel.cs class under your Models folder which acts as an object mapping with our existing SQL Database.


Open your StudentInfoViewMoel.cs and paste this code as given below

namespace DapperExample.Models
{
    public class StudentInfoViewModel
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Location { get; set; }
        public string Email { get; set; }
    }
}


Create a table StudentInfo in SQL Database to access the table data using Dapper from this Core so you have created a table name with Student database.

The following script creates a database table, insert data in the table and select a record from the table example in the SQL server

--create a database
create database Student
--create a table in the Student database
Create Table StudentInfo
(
Id INT IDENTITY PRIMARY KEY,
FirstName NVARCHAR(200),
LastName NVARCHAR(200),
Email NVARCHAR(200),
Location NVARCHAR(200)
)
--insert record in StudentInfo Table
INSERT INTO StudentInfo VALUES('Sylvia','Neupane','[email protected]','kathmandu')
INSERT INTO StudentInfo VALUES('Rahul','Sharma','[email protected]','Pokhara')
INSERT INTO StudentInfo VALUES('Rupa','Pathak','[email protected]','Butwal')
INSERT INTO StudentInfo VALUES('Debin','Neupane','[email protected]','Mirmee')
--SQL select Statement
Select *from StudentInfo





Step 9.

Open your HomeController.cs file and paste the code as given below.

using Dapper;
using DapperExample.Models;
using DapperExample.Service;
using Microsoft.AspNetCore.Mvc;
using System.Data;
using System.Threading.Tasks;
namespace DapperExample.Controllers
{
    public class HomeController: Controller
    {
        private readonly IDapper _dapper;
        public HomeController(IDapper dapper)
        {             _dapper = dapper;
        }
        public IActionResult Index()
        {
            var result = Task.FromResult(_dapper.GetAll<StudentInfoViewModel>("SELECT * FROM StudentInfo", null, commandType: CommandType.Text));
            return View(result.Result);
        }
        public IActionResult Create()
        {
            return View();
        }
        [HttpPost]
        public IActionResult Create(StudentInfoViewModel model)
        {
            var dbparams = new DynamicParameters();
            dbparams.Add("FirstName", model.FirstName, DbType.String);
            dbparams.Add("LastName", model.LastName, DbType.String);
            dbparams.Add("Email", model.FirstName, DbType.String);
            dbparams.Add("Location", model.LastName, DbType.String);
            var result = Task.FromResult(_dapper.Save<int>("[dbo].[SpSaveStudentInfo]"
                , dbparams,
                commandType: CommandType.StoredProcedure));
            return RedirectToAction("Index");
        }
        public IActionResult Edit(int Id)
        {
            var result = Task.FromResult(_dapper.Edit<StudentInfoViewModel>($"Select * from [StudentInfo] where Id = {Id}", null, commandType: CommandType.Text));
            return View(result.Result);
        }
        [HttpPost]
        public IActionResult Edit(StudentInfoViewModel model)
        {
            var dbPara = new DynamicParameters();
            dbPara.Add("Id", model.Id);
            dbPara.Add("FirstName", model.FirstName, DbType.String);
            dbPara.Add("LastName", model.LastName, DbType.String);
            dbPara.Add("Email", model.Email, DbType.String);
            dbPara.Add("Location", model.Location, DbType.String);
            _dapper.Update<int>("[dbo].[SpUpateStudentInfo]",
                            dbPara,                             commandType: CommandType.StoredProcedure);
            return RedirectToAction("Index");
        }
        public IActionResult Delete(int Id)
        {
            _dapper.Execute($"Delete StudentInfo Where Id ={Id}", null, commandType: CommandType.Text);
            return RedirectToAction("Index");
        }
    }
}



Step 10.


Get All Student Information

First is a simple endpoint that is going to return all the available Student Information from the database. you will use Dapper for this example.

Open your Index.cshtml view page and paste this code as given below.

@model IEnumerable<DapperExample.Models.StudentInfoViewModel>
@{
    ViewData["Title"] = "Home Page";
    int i = 1;
}
<div class="text-center">
    <h2>
        Welcome to Dapper Example in .Net Core
    </h2>
</div>
<div class="container">
    <div class="row">
        <div class="table-responsive">
            <a class="mx-0" asp-area="" asp-controller="Home" asp-action="Create">Create New</a>
            <table class="table">
                <thead>
                    <tr>
                        <th>S.N</th>
                        <th>First Name</th>
                        <th>Last Name</th>
                        <th>Email</th>
                        <th>Location</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    @foreach (var item in Model)
                    {
                        <tr>
                            <td>@i</td>
                            <td>@item.FirstName</td>
                            <td>@item.LastName</td>
                            <td>@item.Email</td>                             <td>@item.Location</td>
                            <td>
                                <a href="Home/Edit/@item.Id">Edit</a> ||
                                <a href="Home/Delete/@item.Id">Delete</a>
                            </td>
                        </tr>
                    }
                </tbody>
            </table>
        </div>
     </div>
</div>


Now you can run the application and call the respective methods to fetch the data or to add the data to the existing database using Dapper.

This would return the list of available Student Information list from the database by using Dapper. The Query execution time is always meant to be much better than other ORMs. But the main point to note is that dapper ideally returns the data at a flat level. It is not powerful enough to fill the child nested objects. 

Output


Step 11.

Save Student Information

Add Create.cshtml from HomeController.cs like as given below.

Right-Click inside above created ActionMethod and Click on "Add View


When you click on Razor View-Empty you will see like as given below



Place this code in your created view page (Create.cshtml) like as given below.

@model DapperExample.Models.StudentInfoViewModel
@{
    ViewData["Title"] = "Home Page";
}
<div class="text-center">
    <h2>
       Add New Student Information
    </h2>
</div>
<div class="container">
    <div class="row">
        <form asp-controller="Home" asp-action="Create">
            @Html.AntiForgeryToken()
            <span class="alert-danger">
                @Html.ValidationSummary(true)
            </span>
            <div class="form-group">
                <label asp-for="FirstName"></label>
                <input asp-for="FirstName" class="form-control" />
                <span asp-validation-for="FirstName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="LastName"></label>
                <input asp-for="LastName" class="form-control" />
                <span asp-validation-for="LastName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Email"></label>
                <input asp-for="Email" class="form-control" />
                <span asp-validation-for="Email" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Location"></label>
                <input asp-for="Location" class="form-control" />
                <span asp-validation-for="Location" class="text-danger"></span>
            </div>
            <div class="modal-footer">
                <input type="submit" class="btn btn-primary" value="Summit" />
            </div>
        </form>
    </div>
</div>


Before post view page data first you need to create a stored procedure in your SQL server like as given below

CREATE PROCEDURE SpSaveStudentInfo
(
 @FirstName NVARCHAR(200),
 @LastName NVARCHAR(200),
 @Email NVARCHAR(200),
 @Location NVARCHAR(200)
)
AS
BEGIN
INSERT INTO StudentInfo
(
 FirstName,
 LastName,
 Email,
 Location
)
VALUES
(
 @FirstName,
 @LastName,
 @Email,
 @Location
)
END

Now you can run the application and Fille all student information from Create.cshtml view page the respective methods to save the data in an existing database (Student)  using Dapper.

Output




Step 12.


Edit Student Information By Id

Add newEdit.cshtml View page from HomeController.cs like as Step 11 and put this code like a given below.

@model DapperExample.Models.StudentInfoViewModel
@{
    ViewData["Title"] = "Home Page";
}
<div class="text-center">
    <h2>
        Update Student Information
    </h2>
</div>
<div class="container">
    <div class="row">
        <form asp-controller="Home" asp-action="Edit">
            @Html.AntiForgeryToken()
            <span class="alert-danger">
                @Html.ValidationSummary(true)
                @Html.HiddenFor(x => x.Id)
            </span>
            <div class="form-group">
                <label asp-for="FirstName"></label>
                <input asp-for="FirstName" class="form-control" />
                <span asp-validation-for="FirstName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="LastName"></label>
                <input asp-for="LastName" class="form-control" />
                <span asp-validation-for="LastName" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Email"></label>
                <input asp-for="Email" class="form-control" />
                <span asp-validation-for="Email" class="text-danger"></span>
            </div>
            <div class="form-group">
                <label asp-for="Location"></label>
                <input asp-for="Location" class="form-control" />
                <span asp-validation-for="Location" class="text-danger"></span>
            </div>
            <div class="modal-footer">
                <input type="submit" class="btn btn-primary" value="Update" />
            </div>
        </form>
    </div>
</div>


Output


Before updating your view page data first you need to create a stored procedure in your SQL server like as given below

CREATE PROCEDURE SpUpateStudentInfo
(
 @Id INT,
 @FirstName NVARCHAR(200),
 @LastName NVARCHAR(200),
 @Email NVARCHAR(200),
 @Location NVARCHAR(200)
)
AS
BEGIN
UPDATE StudentInfo SET
 [email protected],
 [email protected],
 [email protected],
 [email protected]
 WHERE [email protected]
END


Step 13.

Delete Student Information by ID

No need to create a view page only call Delete Action Method like as given below which is already added in Index.cshtml view page

 <a href="Home/Delete/@item.Id">Delete</a>


Summary

In this dapper article, you learned about a really simple and powerful approach to take advantage of the two most powerful ORMs for ASP.NET Core, which is Entity Framework Core and Dapper. Additionally, you talked about Transactions and built an application that can make use of both the ORMs keeping transactions in mind. As a result, you have an application that is intelligent enough to roll back the changes if at all any exceptions occur in the process.


Learn C# Programming step by step

CRUD Application from Code First approach in asp.net Core

CRUD in ASP.NET Core Web API Using angular

POST data using Ajax in asp net Core

Uploading File using Ajax in Asp.Net Core

Login and Registration using Identity in ASP.NET Core

Connect Database to ASP.NET Core

Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

If findandsolve.com felt valuable to you, feel free to share it.

Comments



Report Response