Optimizing Performance with Dapper with .NET Core API

Dot net core

Introduction

Dapper is a fast and efficient micro ORM for connecting .NET applications to databases. Dapper is a fast and efficient tool for connecting .NET applications to databases. It is easy to use and performs well right from the start. However, you can make it even faster by following some best practices. In this article, we'll show you tips and examples to help you get the best performance with Dapper

1. Using output parameters with stored procedures

Using stored procedures with dapper

Stored procedures can encapsulate complex SQL logic and optimize performance by reducing the number of database round trips and leveraging database server capabilities.

CREATE OR REPLACE PROCEDURE SP_Get_User_Detail(
    p_UserId IN NUMBER,
    p_User OUT SYS_REFCURSOR,
   )
AS
BEGIN
    OPEN p_Cursor1 FOR
        SELECT Id, Name, Age FROM Users WHERE Id = p_UserId;
END;

Executing a Stored Procedure with Dapper.

public async Task<User> GetUserDetailById(int Id)
{
    string Sql = "SP_Get_User_Detail";
    using (var connection = new OracleConnection(connectionString))
    {
        var parameters = new OracleDynamicParameters();
        parameters.Add("p_UserId", id, OracleDbType.Int32, ParameterDirection.Input);
        parameters.Add("p_User", OracleDbType.RefCursor, ParameterDirection.Output);
        var users = await connection.QueryAsync<User>(
            Sql,
            param: parameters,
            commandType: CommandType.StoredProcedure
        ).ToListAsync();
        return users;
    }
}

Database connection

using (var connection = new OracleConnection(connectionString))

The function creates a new OracleConnection using the provided connectionString.
The using statement ensures the connection is properly disposed of when the function completes.

Parameters

var parameters = new OracleDynamicParameters();
parameters.Add("p_UserId", id, OracleDbType.Int32, ParameterDirection.Input);

The function creates an OracleDynamicParameters object and adds a parameter named p_UserId of type OracleDbType.Int32 with the id value passed to the function.

Executing the procedure

var parameters = new OracleDynamicParameters();
parameters.Add("p_UserId", id, OracleDbType.Int32, ParameterDirection.Input);

The function uses Dapper's Query method to execute the stored procedure named SP_Get_User_Detail.

2. Handling multiple result sets with stored procedures

Stored Procedure with multiple results return.

CREATE OR REPLACE PROCEDURE SP_Get_User_Orders_Detail(
    p_UserId IN NUMBER,
    p_User OUT SYS_REFCURSOR,
    p_Order OUT SYS_REFCURSOR
)
AS
BEGIN
    OPEN p_User FOR
        SELECT Id, Name, Age FROM Users WHERE Id = p_UserId;
    OPEN p_Order FOR
        SELECT Id, UserId, ProductName, Price FROM Orders WHERE UserId = p_UserId;
END;

Executing a Stored Procedure with Dapper.

Public Async Task<UserandOrderRespone>GetUserDetailById(int Id){
UserandOrderRespone finalresponse = new UserandOrderRespone ();
string Sql=”SP_Get_User_Orders_Detail”;
using (var connection = new OracleConnection(connectionString))
        {
            var parameters = new OracleDynamicParameters();
            parameters.Add("p_UserId", id, OracleDbType.Int32, ParameterDirection.Input);
             parameters.Add("p_User",SYS_REFCURSOR, ParameterDirection.Output);
             parameters.Add("p_Order ",SYS_REFCURSOR, ParameterDirection.Output);
            var users = connection.QueryMultiple(Sql,
                param: parameters,
                commandType: CommandType.StoredProcedure).ToList();
         finalresponse.users=users .Read<User>().FirstOrDefault();
 finalresponse.order=users .Read<Order>().FirstOrDefault();
      }
return  finalresponse;
}

3. Efficient query writing in delete functionalities

StoredProcedure

CREATE OR REPLACE PROCEDURE SP_DeleteUser (
    p_UserId IN INT
)
AS
BEGIN
    DELETE FROM Users
    WHERE UserId = p_UserId;
    COMMIT;
END SP_DeleteUser;

Executing a Stored Procedure with Dapper Functionality.

public async Task<bool> DeleteUserById(int Id)
{
    string Sql = "SP_DeleteUser";
    using (var connection = new OracleConnection(connectionString))
    {
        var parameters = new OracleDynamicParameters();
        parameters.Add("p_UserId", id, OracleDbType.Int32, ParameterDirection.Input);
        try
        {
            await connection.ExecuteAsync(Sql, param: parameters);
            return true;
        }
        catch(Exception ex)
        {
            return false;
        }
    }
}

public async Task<bool> DeleteUserById(int id) { ... } : Asynchronously deletes a user from the database by ID
string sql = "SP_DeleteUser": Defines the name of the stored procedure to execute.
using (var connection = new OracleConnection(connectionString)) { ... }: Creates an OracleConnection and adds an input parameter (p_UserId).
await connection.ExecuteAsync(sql, param: parameters, commandType: CommandType.StoredProcedure): Executes the stored procedure asynchronously.
try { ... } catch (Exception ex) { ... }: Returns true if the deletion is successful; otherwise, returns false if an exception occurs

Conclusion

Using Dapper with .NET Core can significantly enhance the performance of your data access layer. By using stored procedures with output parameters, handling multiple result sets, and writing efficient queries for delete functionalities, you can make your applications more efficient and maintainable. Following these best practices will help you get the best performance from Dapper.

We learned the new technique and evolved together.

Happy coding!