.NET Core Optimizing Performance with Dapper with .NET Core API

Dapper is a quick and effective micro ORM that links databases with.NET applications. Dapper is a quick and easy tool to link databases and.NET applications. It works great out of the box and is simple to use. However, by adhering to a few best practices, you can accelerate it even further. We’ll provide you with advice and examples in this post to help you get the most out of Dapper.

1. Making use of stored operations and output parameters
Employing similar processes with stored
Through the use of database server capabilities and fewer database round trips, stored procedures can reduce the amount of time required to execute sophisticated SQL logic and improve performance.

<span class="token keyword keyword-CREATE">CREATE</span> <span class="token operator">OR</span> <span class="token keyword keyword-REPLACE">REPLACE</span> <span class="token keyword keyword-PROCEDURE">PROCEDURE</span> SP_Get_User_Detail<span class="token punctuation">(</span>
    p_UserId <span class="token operator">IN</span> NUMBER<span class="token punctuation">,</span>
    p_User <span class="token keyword keyword-OUT">OUT</span> SYS_REFCURSOR<span class="token punctuation">,</span>
   <span class="token punctuation">)</span>
<span class="token keyword keyword-AS">AS</span>
<span class="token keyword keyword-BEGIN">BEGIN</span>
    <span class="token keyword keyword-OPEN">OPEN</span> p_Cursor1 <span class="token keyword keyword-FOR">FOR</span>
        <span class="token keyword keyword-SELECT">SELECT</span> Id<span class="token punctuation">,</span> Name<span class="token punctuation">,</span> Age <span class="token keyword keyword-FROM">FROM</span> Users <span class="token keyword keyword-WHERE">WHERE</span> Id <span class="token operator">=</span> p_UserId<span class="token punctuation">;</span>
<span class="token keyword keyword-END">END</span><span class="token punctuation">;</span>

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.

Database connection

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

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

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.

<span class="token keyword keyword-CREATE">CREATE</span> <span class="token operator">OR</span> <span class="token keyword keyword-REPLACE">REPLACE</span> <span class="token keyword keyword-PROCEDURE">PROCEDURE</span> SP_Get_User_Orders_Detail<span class="token punctuation">(</span>
    p_UserId <span class="token operator">IN</span> NUMBER<span class="token punctuation">,</span>
    p_User <span class="token keyword keyword-OUT">OUT</span> SYS_REFCURSOR<span class="token punctuation">,</span>
    p_Order <span class="token keyword keyword-OUT">OUT</span> SYS_REFCURSOR
<span class="token punctuation">)</span>
<span class="token keyword keyword-AS">AS</span>
<span class="token keyword keyword-BEGIN">BEGIN</span>
    <span class="token keyword keyword-OPEN">OPEN</span> p_User <span class="token keyword keyword-FOR">FOR</span>
        <span class="token keyword keyword-SELECT">SELECT</span> Id<span class="token punctuation">,</span> Name<span class="token punctuation">,</span> Age <span class="token keyword keyword-FROM">FROM</span> Users <span class="token keyword keyword-WHERE">WHERE</span> Id <span class="token operator">=</span> p_UserId<span class="token punctuation">;</span>
    <span class="token keyword keyword-OPEN">OPEN</span> p_Order <span class="token keyword keyword-FOR">FOR</span>
        <span class="token keyword keyword-SELECT">SELECT</span> Id<span class="token punctuation">,</span> UserId<span class="token punctuation">,</span> ProductName<span class="token punctuation">,</span> Price <span class="token keyword keyword-FROM">FROM</span> Orders <span class="token keyword keyword-WHERE">WHERE</span> UserId <span class="token operator">=</span> p_UserId<span class="token punctuation">;</span>
<span class="token keyword keyword-END">END</span><span class="token punctuation">;</span>

p_UserId IN NUMBER,

p_User OUT SYS_REFCURSOR,

p_Order OUT SYS_REFCURSOR

)

AS

BEGIN

OPEN p_User FOR

OPEN p_Order FOR

SELECT Id, UserId, ProductName, Price FROM Orders WHERE UserId = p_UserId;

END;

Executing a Stored Procedure with Dapper.

3. Efficient query writing in delete functionalities

StoredProcedure

Executing a Stored Procedure with Dapper Functionality.

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

You may greatly improve the data access layer’s performance by using Dapper with.NET Core. You may improve the efficiency and maintainability of your applications by managing numerous result sets, utilizing stored procedures with output parameters, and creating effective queries for delete functions. You can get the most out of Dapper by adhering to these best practices.

Together, we developed and mastered the new method.

ASP.NET 8.0.4 Hosting Recommendation

ASP.NET is a powerful platform for creating web applications and services. You must be comfortable with JavaScript, HTML, CSS, and C# before developing a web application in ASP.NET. On the market, there are thousands of web hosting companies providing ASP.NET Hosting. But, only very few web hosting companies could provide high quality ASP.NET hosting solution.

ASP.NET is the best development language in Windows platform, which is released by Microsoft and widely used to build all types of dynamic Web sites and XML Web services. With this article, we’re going to help you to find the best ASP.NET Hosting solution in Europe based on reliability, features, price, performance and technical support. After we reviewed about 30+ ASP.NET hosting providers in Europe, our Best ASP.NET Hosting Award in Europe goes to HostForLIFE.eu, one of the fastest growing private companies and one of the most reliable hosting providers in Europe.