MENU

How To Use Dapper To Get Return Value Using Stored Procedure

Filed Under: , , on
In this article, we will learn how to return the value from a stored procedure using the Dapper ORM in ASP.NET MVC.

Scenario

Let's consider the use case for ABC Housing Society, which provides different services to its flat owners. Since ABC Housing Society is very big, it's difficult to manage complaints manually from their flat customers, so they decided to build the sample application, which covers the following scenario:
  • A user can raise the complaint type and short description using text boxes.
  • The unique Complaint ID needs to be generated instantly after raising the complaint to track status.
  • The ComplaintId should be the combination of the first four characters of the complaint type text and Database auto-generated number.
So based on the preceding scenario, let's start building the application step by step so beginners can also understand.

Step 1: Create an ASP.NET MVC Application.

Now let us start with a step by step approach from the creation of a simple MVC application as in the following:
  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
  2. "File", then "New" and click "Project", then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click OK. After clicking, the following window will appear:
  1. As shown in the preceding screenshot, click on Empty template and check MVC option, then click OK. This will create an empty MVC web application.

Step 2 : Add The Reference of Dapper ORM into Project.

Now next step is to add the reference of Dapper ORM into our created MVC Project. Here are the steps:
  1. Right click on Solution ,find Manage NuGet Package manager and click on it.
  2. After as shown into the image and type in search box "dapper".
  3. Select Dapper as shown into the image .
  4. Choose version of dapper library and click on install button

After installing the Dapper library, it will be added into the References of our solution explorer of MVC application such as:


If wants to learn how to install correct Dapper library , watch my video tutorial using following link,
I hope you have followed the same steps and installed dapper library.

Step 3: Create Model Class.

Now let's create the model class named ComplaintModel.cs by right clicking on model folder as in the following screenshot:


Note:

It is not mandatory that the Model class should be in the Model folder, it is just for better readability you can create this class anywhere in the solution explorer. This can be done by creating different folder names or without a folder name or in a separate class library.

ComplaintModel.cs class code snippet: 

public class ComplaintModel  
    {   [Display(Name = "Complaint Type")]  
        [Required]  
        public string ComplaintType { get; set; }  
        [Display(Name = "Complaint Description")]  
        [Required]  
        public string ComplaintDesc { get; set; }  
    }

Step 4 : Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:


After clicking on Add button it will show the window. specify the Controller name as Complaint with suffix Controller:

Note:

 The controller name must be having suffix as 'Controller' after specifying the name of controller.

Step 5 : Create Table and Stored procedure.

Now before creating the views let us create the table name ComplaintDetails in database according to store the complaint details:



I hope you have created the same table structure as shown above. Now create the stored procedures to get the return value  as in the following code snippet:
Create PROCEDURE AddComplaint  
(  
@ComplaintType varchar(100),  
@ComplaintDesc varchar(150),  
@ComplaintId varchar(20)=null out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
  
Declare @ComplaintRef varchar(30)  
--Getting unquie Id  
select @ComplaintRef=isnull(max(Id),0)+1 from ComplaintDetails  
--Generating the unique reference number and seeting to output parameter  
Set @ComplaintId=Upper(LEFT(@ComplaintType,4))+convert(Varchar,@ComplaintRef)  
  
INSERT INTO [dbo].[ComplaintDetails]  
           (  
            [ComplaintId]  
           ,[ComplaintType]  
           ,[ComplaintDesc]  
           )  
     VALUES  
           (  
          @ComplaintId,  
          @ComplaintType,  
          @ComplaintDesc  
           )  
END
Run the above script in sql it will generates the stored procedure to get the return value .

Step 6: Create Repository class.

Now create Repository folder and Add ComplaintRepo.cs class for database related operations, Now create method in ComplaintRepo.cs to get the output parameter value from stored procedure as in the following code snippet:
public class ComplaintRepo  
  {  
      SqlConnection con;  
      //To Handle connection related activities  
      private void connection()  
      {  
          string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();  
          con = new SqlConnection(constr);  
      }  
      //To Add Complaint details  
      public string AddComplaint(ComplaintModel Obj)  
      {  
          DynamicParameters ObjParm = new DynamicParameters();  
          ObjParm.Add("@ComplaintType", Obj.ComplaintType);  
          ObjParm.Add("@ComplaintDesc", Obj.ComplaintDesc);  
          ObjParm.Add("@ComplaintId", dbType:DbType.String,direction:ParameterDirection.Output,size:5215585);  
          connection();  
          con.Open();  
          con.Execute("AddComplaint",ObjParm,commandType:CommandType.StoredProcedure);  
          //Getting the out parameter value of stored procedure  
          var ComplaintId = ObjParm.Get<string>("@ComplaintId");  
          con.Close();  
          return ComplaintId;  
  
      }  
  }
Note
  • In the above code we are manually opening and closing connection, however you can directly pass the connection string to the dapper without opening it. Dapper will automatically handle it. 

Step 7: Create Method into the ComplaintController.cs file.

Now open the ComplaintController.cs and create the following action methods:
public class ComplaintController : Controller  
   {  
       // GET: complaint  
       public ActionResult AddComplaint()  
       {  
           return View();  
       }  
       [HttpPost]  
       public ActionResult AddComplaint(ComplaintModel ObjComp)  
       {  
           try  
           {  
               ComplaintRepo Obj = new ComplaintRepo();  
               //Getting complaintId and assigning   
               //to ViewBag with custom message to show user  
               ViewBag.ComplaintId = "Complaint raised successfully, Your complaintId is " + Obj.AddComplaint(ObjComp);  
           }  
           catch (Exception)  
           {  
               //Assigning custom message to ViewBag to show users, If any error occures.  
               ViewBag.ComplaintId="Error while raising complaint, Please check details";  
           }  
           return View();  
       }  
   }

Step 8 : Creating strongly typed view named AddComplaint using ComplaintModel class.

Right click on View folder of created application and choose add view, select ComplaintModel class and create scaffolding template to create view to raise the user complaints as


Click on Add button then it will create the view named AddComplaint, Now open the AddComplaint.cshtml view , Then following default code you will see which is generated by MVC scaffolding template as,

AddComplaint.cshtml  

 
@model GetReturnValueUsingDapperInMVC.Models.ComplaintModel   
@{  
    ViewBag.Title = "www.compilemode.com";  
}   
@using (Html.BeginForm())   
{  
    @Html.AntiForgeryToken()  
      
    <div class="form-horizontal">      
        <hr />  
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
        <div class="form-group">  
            @Html.LabelFor(model => model.ComplaintType, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.ComplaintType, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.ComplaintType, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.ComplaintDesc, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.ComplaintDesc, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.ComplaintDesc, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10">  
                <input type="submit" value="Add Complaint" class="btn btn-primary" />  
            </div>  
        </div>  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10 text-success">  
                @ViewBag.ComplaintId  
            </div>  
        </div>  
    </div>  
}  
<script src="~/Scripts/jquery-1.10.2.min.js"></script>  
<script src="~/Scripts/jquery.validate.min.js"></script>  
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
After adding model, view , controller and Repository folder our final solution explorer will be look like as follows,


Now we have done all coding to upload files .

Step 9 : Now run the application.

After running the application initial screen will be look like as follows,



Now click on Add Complaint button without entering the details then the following error message shows which we have defined in model class as

Now enter the proper details as,



I hope from all the preceding example we have learned how to get return value from stored procedure using Dapper ORM in ASP.NET MVC with real time scenario based sample MVC application.

Note:
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills.
  • Configure the database connection in the web.config file depending on your database server location.
  • You can use  DropDownList for complaint types which might be come from master table.

Summary

I hope this article is useful for all readers. If you have any suggestions please contact me.

Related Articles:

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode