.NET Core  

Mastering Connection Pooling with Dapper in ASP.NET Core Web API

Introduction

In modern web development, one of the key challenges when working with databases is efficiently managing database connections. This is where connection pooling comes in. Connection pooling is a method used to reuse database connections rather than repeatedly opening and closing connections for every query. This not only reduces overhead but also improves performance, especially in high-traffic applications.

In this article, we will dive into how connection pooling works, how Dapper handles connection pooling by leveraging ADO.NET in an ASP.NET Core Web API application, and provide a step-by-step guide to implement it with a practical Products model example.

What is Connection Pooling?

Connection pooling is a technique used to manage database connections efficiently. Instead of opening a new database connection every time your application needs to perform a query, a connection pool maintains a set of reusable database connections. When the application needs a database connection, it gets one from the pool. Once the query is executed, the connection is returned to the pool instead of being closed, making it available for future use.

Advantages of Connection Pooling.

  • Reduced Overhead: Creating and closing connections can be expensive. By reusing open connections, the application avoids the overhead of repeatedly establishing new connections.
  • Improved Performance: With pooled connections, the application can reuse existing connections, leading to faster query execution.
  • Resource Management: Connection pooling helps limit the number of active connections, preventing the database from becoming overwhelmed with too many concurrent connections.
  • Scalability: As the application scales, connection pooling allows it to handle more simultaneous database requests efficiently.

In .NET, connection pooling is handled by ADO.NET, which is automatically enabled when you use database providers like SQL Server or PostgreSQL.

Why Use Connection Pooling in Your Application?

Connection pooling provides numerous benefits to your application.

  • Performance Boost: With connection pooling, your application avoids the need to repeatedly connect and disconnect from the database. This can significantly improve the performance of high-traffic applications.
  • Optimized Resource Usage: Connection pooling limits the number of open database connections, preventing the server from being overloaded.
  • Scalability: As your application grows, connection pooling allows it to handle a higher volume of database requests without degrading performance.

Dapper, as a lightweight Object-Relational Mapper (ORM) in the .NET ecosystem, leverages ADO.NET's connection pooling features. This makes it an ideal choice for high-performance applications that require efficient data access.

How Dapper Uses Connection Pooling?

Dapper does not directly manage connection pooling. Instead, it uses ADO.NET, which automatically handles connection pooling for supported databases. When you create a database connection using Dapper, it uses SQLConnection (or NpgsqlConnection for PostgreSQL), which is backed by ADO.NET's connection pooling mechanism.

When an IDbConnection is created using Dapper, connection pooling is enabled by the database provider (such as SQL Server or PostgreSQL), ensuring that the database connections are reused rather than opened and closed repeatedly. This happens automatically when using Dapper, as long as you are using a connection string that enables pooling.

Setting Up Connection Pooling with Dapper in ASP.NET Core Web API

Step 1. Install Required NuGet Packages.

First, you need to install the necessary NuGet packages. You need Dapper and the database provider (SQL Server, PostgreSQL, etc.).

For SQL Server, install the following packages.

dotnet add package Dapper
dotnet add package Microsoft.Data.SqlClient

For PostgreSQL, install.

dotnet add package Dapper
dotnet add package Npgsql

Step 2. Configure Connection Pooling in appsettings.json.

To enable connection pooling, you need to configure the connection string in your appsettings.json file.

For SQL Server.

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;Max Pool Size=100;Min Pool Size=10;"
  }
}

For PostgreSQL

{
  "ConnectionStrings": {
    "DefaultConnection": "Host=myserver;Database=mydatabase;Username=myuser;Password=mypassword;Pooling=true;MinPoolSize=10;MaxPoolSize=100;"
  }
}

In these examples,

  • Max Pool Size: Specifies the maximum number of connections allowed in the pool.
  • Min Pool Size: Specifies the minimum number of connections to keep open in the pool.
  • Pooling=true: Enables connection pooling (enabled by default).

Step 3. Set Up Dependency Injection for Dapper.

Now, configure Dapper in your Program.cs (ASP.NET Core 6.0 or later) or Startup.cs (for earlier versions).

In Program.cs (for ASP.NET Core 6.0 and later)

using Microsoft.Data.SqlClient;  // For SQL Server
using Dapper;
using System.Data;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddControllers();

// Register the SQL connection with connection pooling
builder.Services.AddScoped<IDbConnection>(sp =>
    new SqlConnection(builder.Configuration.GetConnectionString("DefaultConnection"))); // For SQL Server
    // new NpgsqlConnection(builder.Configuration.GetConnectionString("DefaultConnection"))); // For PostgreSQL

var app = builder.Build();

app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();

Here, we are configuring an IDbConnection to be injected into the controller. This ensures that Dapper will use a pooled connection when interacting with the database.

Step 4. Create CRUD Operations for Products.

Now let’s create the Product model and implement CRUD operations using Dapper in a controller.

First, we need to create the product model.

namespace ConnectionPoolingInDapperDotNetCore.Model
{
    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }
        public int StockQuantity { get; set; }
        public string Description { get; set; }
    }
}

Now we will write the controller code in the application.

using ConnectionPoolingInDapperDotNetCore.Model;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using System.Data;

namespace ConnectionPoolingInDapperDotNetCore.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ProductsController : ControllerBase
    {
        private readonly IDbConnection _dbConnection;

        // Constructor injection of IDbConnection
        public ProductsController(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        // GET: api/products
        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var query = "SELECT * FROM Products";
            var products = await _dbConnection.QueryAsync<Product>(query);
            return Ok(products);
        }

        // GET: api/products/5
        [HttpGet("{id}")]
        public async Task<IActionResult> Get(int id)
        {
            var query = "SELECT * FROM Products WHERE Id = @Id";
            var product = await _dbConnection.QueryFirstOrDefaultAsync<Product>(query, new { Id = id });

            if (product == null)
                return NotFound();

            return Ok(product);
        }

        // POST: api/products
        [HttpPost]
        public async Task<IActionResult> Create([FromBody] Product product)
        {
            var query = "INSERT INTO Products (Name, Price, StockQuantity, Description) VALUES (@Name, @Price, @StockQuantity, @Description)";
            var result = await _dbConnection.ExecuteAsync(query, product);

            if (result == 0)
                return BadRequest("Failed to create product");

            return CreatedAtAction(nameof(Get), new { id = product.Id }, product);
        }

        // PUT: api/products/5
        [HttpPut("{id}")]
        public async Task<IActionResult> Update(int id, [FromBody] Product product)
        {
            var query = "UPDATE Products SET Name = @Name, Price = @Price, StockQuantity = @StockQuantity, Description = @Description WHERE Id = @Id";
            var result = await _dbConnection.ExecuteAsync(query, new { product.Name, product.Price, product.StockQuantity, product.Description, Id = id });

            if (result == 0)
                return NotFound();

            return NoContent();
        }

        // DELETE: api/products/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> Delete(int id)
        {
            var query = "DELETE FROM Products WHERE Id = @Id";
            var result = await _dbConnection.ExecuteAsync(query, new { Id = id });

            if (result == 0)
                return NotFound();

            return NoContent();
        }
    }
}

Here

  • Dapper’s QueryAsync and ExecuteAsync methods are used to perform database queries and commands.
  • We inject the IDbConnection instance that uses connection pooling into the controller to interact with the database efficiently.

Best Practices for Connection Pooling

While connection pooling is enabled by default in ADO.NET, here are some best practices.

  • Reuse Connections: Avoid opening and closing connections unnecessarily. Reuse open connections for multiple queries.
  • Minimize Connection Lifetime: Keep the connection open only as long as necessary. Use using statements to ensure connections are properly disposed of.
  • Adjust Pool Size: Depending on the load, adjust Max Pool Size and Min Pool Size to ensure the optimal number of connections.
  • Monitor the Pool: Regularly monitor connection pool usage to detect any issues like connection exhaustion or bottlenecks.

GitHub Project Link

https://github.com/SardarMudassarAliKhan/ConnectionPoolingInDapperDotNetCore

Conclusion

In this article, we've explored how to implement connection pooling using Dapper in an ASP.NET Core Web API. By leveraging ADO.NET's built-in connection pooling, Dapper automatically reuses database connections, improving the performance and scalability of your application.

We also created a Products model and implemented CRUD operations using Dapper, providing a clear and efficient approach to database access in an API.

With connection pooling enabled, your application will handle database operations more efficiently, reducing overhead and allowing it to scale gracefully. By following the best practices outlined in this article, you can ensure that your application makes optimal use of database connections, providing faster and more reliable responses for users.

Membership not found