ASP.NET Core Dapper Stored Procedure

You can see how easy it is to call a stored procedure using Dapper in asp.net core. It is a simple Object Mapping Framework or a Micro-ORM that helps you to Map SQL return data in your ViewModel class.

 In the .NET framework, Dapper is an ORM for the fast, lightweight, and since we get to write your SQL own queries, it provides a lot of flexibility.

In the source code project learn how to use Dapper with ASP.NET Core and Repository Pattern and map SQL return data in ViewModel class.

What is Dapper?

Dapper is an ORM for the .NET framework. We also describe it's best lightweight, fast and since we get to write your SQL own queries, it provides a lot of flexibility. Dapper provides methods that make it easy to communicate with your database.

Some of the methods are:

• Execute: this method executes a command and returns the affected rows which rows you want to request. It’s usually used to perform INSERT, UPDATE, and DELETE operations.

• Query: this method executes a query and maps the result and it is used to fetch multiple objects from the SQL database.

• QueryFirst: this method executes a query and maps the first result that matches the parameters in the query. This is used if you need just one item that matches the provided specifications.

• QueryFirstOrDefault: this functions like the first top 1 query but returns a default value if the sequence contains no elements.

• QuerySingle: it executes a query and maps the result provided that there is only one item in the sequence. If there is not exactly one element in the sequence, it throws an exception when no element or more than one element is returned.

• QuerySingleOrDefault: this method works like link query SingleOrDefault() but returns a default value if no item is returned from the SQL database.

• QueryMultiple: this method can execute many queries simultaneously with one command and map results.

In this source code, We are going to learn how to use Dapper in the ASP.NET Core MVC project. We are going to talk about Dapper overall, how to use different types of queries and executions in Service and HomeController, how to execute stored procedures using Dapper, and how to create multiple queries inside a transaction. To avoid using Dapper queries directly inside the controller, you are going to create a simple repository layer to wrap the logic up.

Step 1.

Create New Project in ASP.NET Core

How to Create New Project in asp.net core?

• 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.

Add Dapper library into your project from the Nuget Package Manager.

Step 3.

Create a new service IDapper and Dabber class in your project and put the code in your IDapper.cs and Dapper.cs class.

Step 4.

Add BD Connection

How to Connect Database?

Step 5.

Make the Connection setup in the Startup.cs file. 

Step 6.

Create database, function view page controller, etc

For more details, you can visit this site