Skip to main content

Use Dapper ORM With ASP.NET Core

Dapper.NET is not just another ORM tool, it’s considered as the king of ORM. Because it’s fast, easy to integrate, requires fewer lines of code, supports bulk data insertion and supports static and dynamic object binding. And Dapper ORM can be used with .NET. So in this post, let’s find out how to use Dapper ORM with ASP.NET Core for CRUD operations.

Use Dapper ORM With ASP.NET Core

For this post, we will be using SQL Server as a database with Dapper ORM. So let’s first create the database. Open SQL Server Management Studio and create a new database called “DapperDemo” or you can name it anything of your choice. And create a table named “Products”.

CREATE TABLE [dbo].[Products](
	[ProductID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](max) NULL,
	[Quantity] [int] NULL,
	[Price] [float] NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Okay. Let’s create an ASP.NET Core Web API project. The default Web API template comes with a controller named “Value”. We don’t need this for demo, so I deleted it. To use Dapper ORM, we need to install following nuget package.

  • “Dapper”: “1.50.2”

To test our WEB API, also add Swagger. If you don’t know about using Swagger then read how to add swagger to the ASP.NET Core WEB API

So your project.json should look like this,

"dependencies": {
  "Microsoft.NETCore.App": {
    "version": "1.0.0",
    "type": "platform"
  },
  "Microsoft.ApplicationInsights.AspNetCore": "1.0.0",
  "Microsoft.AspNetCore.Mvc": "1.0.0",
  "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0",
  "Microsoft.AspNetCore.Server.Kestrel": "1.0.0",
  "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.0.0",
  "Microsoft.Extensions.Configuration.FileExtensions": "1.0.0",
  "Microsoft.Extensions.Configuration.Json": "1.0.0",
  "Microsoft.Extensions.Logging": "1.0.0",
  "Microsoft.Extensions.Logging.Console": "1.0.0",
  "Microsoft.Extensions.Logging.Debug": "1.0.0",
  "Microsoft.Extensions.Options.ConfigurationExtensions": "1.0.0",
  "Swashbuckle": "6.0.0-beta901",
  "Dapper": "1.50.2"
},

Now, we need to add Product entity model. So create a folder named “Model” and create a new class “Product.cs” as follows,

public class Product
{
    [Key]
    public int ProductId { get; set; }
    public string Name { get; set; }
    public int Quantity { get; set; }
    public double Price { get; set; }
}

Setting up Dapper is very easy process and similarly using it is also very simple and straightforward. You need to follow 3 simple steps to use it.

  • Create an IDBConnection object with a connection string.
  • Form your raw SQL query.
  • Pass the query to Dapper and it’s done.

So let’s create ProductRepository for all database calls. Create a folder named “Repository” and create a new class “ProductRepository.cs” as follows,

public class ProductRepository
{
    private string connectionString;
    public ProductRepository()
    {
        connectionString = @"Server=localhost;Database=DapperDemo;Trusted_Connection=true;";
    }

    public IDbConnection Connection
    {
        get  {
            return new SqlConnection(connectionString);
        }
    }

    public void Add(Product prod)
    {
        using (IDbConnection dbConnection = Connection)
        {
            string sQuery = "INSERT INTO Products (Name, Quantity, Price)"
                            + " VALUES(@Name, @Quantity, @Price)";
            dbConnection.Open();
            dbConnection.Execute(sQuery, prod);
        }
    }

    public IEnumerable<Product> GetAll()
    {
        using (IDbConnection dbConnection = Connection)
        {
            dbConnection.Open();
            return dbConnection.Query<Product>("SELECT * FROM Products");
        }
    }

    public Product GetByID(int id)
    {
        using (IDbConnection dbConnection = Connection)
        {
            string sQuery = "SELECT * FROM Products" 
                           + " WHERE ProductId = @Id";
            dbConnection.Open();
            return dbConnection.Query<Product>(sQuery, new { Id = id }).FirstOrDefault();
        }
    }

    public void Delete(int id)
    {
        using (IDbConnection dbConnection = Connection)
        {
             string sQuery = "DELETE FROM Products" 
                          + " WHERE ProductId = @Id";
            dbConnection.Open();
            dbConnection.Execute(sQuery, new { Id = id });
        }
    }

    public void Update(Product prod)
    {
        using (IDbConnection dbConnection = Connection)
        {
            string sQuery = "UPDATE Products SET Name = @Name,"
                           + " Quantity = @Quantity, Price= @Price" 
                           + " WHERE ProductId = @ProductId";
            dbConnection.Open();
            dbConnection.Query(sQuery, prod);
        }
    }
}

There are CRUD methods defined using raw SQL queries for all DB operations. As mentioned earlier, in every method

  • Create IDbConnection object.
  • Form the Query.
  • Open the connection and execute the query.

Now, lets create a new controller “ProductController.cs” as follows,

[Route("api/[controller]")]
public class ProductController : Controller
{
    private readonly ProductRepository productRepository;
    public ProductController()
    {
        productRepository = new ProductRepository();
    }
    // GET: api/values
    [HttpGet]
    public IEnumerable<Product> Get()
    {
        return productRepository.GetAll();
    }

    // GET api/values/5
    [HttpGet("{id}")]
    public Product Get(int id)
    {
        return productRepository.GetByID(id);
    }

    // POST api/values
    [HttpPost]
    public void Post([FromBody]Product prod)
    {
        if (ModelState.IsValid)
            productRepository.Add(prod);
    }

    // PUT api/values/5
    [HttpPut("{id}")]
    public void Put(int id, [FromBody]Product prod)
    {
        prod.ProductId = id;
        if (ModelState.IsValid)
            productRepository.Update(prod);
    }

    // DELETE api/values/5
    [HttpDelete("{id}")]
    public void Delete(int id)
    {
        productRepository.Delete(id);
    }
}

This controller has methods for GET, POST, PUT and DELETE. That’s all to code. Now, let’s just run the application and execute the GET API. Since the table is empty, so you should see following.
dapper_get_product_api
Now, let’s add a product via Post API.

dapper_post_product_api
And now call the GET Product API again and you should see that product you just added is returned.

dapper_get_product_api_2

Here is the gif version of all 5 methods.

Use Dapper ORM With ASP.NET Core

That’s it. Dapper.NET ORM is considered to be the fastest ORM as it directly executes the RAW SQL without any additional overhead. We saw how easy it was to integrate and use it with ASP.NET Core. If you want to support any other database providers like SQLite, then you need to change in public IDbConnection Connection property.

public IDbConnection Connection
{
    get 
    {
        return new SqliteConnection(connectionString);
    }
}

You will also need to include the SQLite nuget package "Microsoft.Data.Sqlite": "1.0.0" in your project.json. Of course, based on database provider the SQL query syntax may differ. Hope you liked it. Just for your information Project.json for ASP.NET Core is going away.

If you are interested in Entity Framework Core, then read my posts about EF Core.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

11 thoughts on “Use Dapper ORM With ASP.NET Core

    1. I think that’s not necessary because of the using statements. On using blocks, variables for a certain class, this MUST implement the IDisposable interface.

  1. Dapper forces you to write raw SQL in the code, and if your’re looking for DAL that is similar to Dapper but with real isolation from the database (SQL commands generation), take a look to the NReco.Data – this is new library that was specially designed for .NET Core – for example, it implements lightweight DataTable replacement for schema-less data access.

  2. I’m curious as to why you are specifically opening the connection prior to making the call, was this because you were running into some problems and had to add it or some other reason?

    dbConnection.Open(); <——-
    return dbConnection.Query(sQuery, new { Id = id }).FirstOrDefault();

    If you remove the dbConnection.Open() it still successful so why did you add it?

Leave a Reply to Abid Nisar Cancel reply

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

three × five =