Get Return Value From Stored Procedure Using Dapper ORM In ASP.NET MVC

Background

Sometimes in an  we need to give user acknowledgment of their raised request or transaction reference number instantly from database which is generated against that user request. So for this purpose we need to take return value from stored procedure which is either string or integer or anything as per application requirement. Now in this article we will learn how to get return value from stored procedure using Dapper ORM in ASP.NET MVC with one scenario-based sample MVC application.

Scenario

Let's consider ABC housing society provides different services to their flat owners.  Since ABC housing society is very big it's difficult to manage complaints manually of their flat customers, so they decided to build the sample application which covers the following scenario .

• User can raise the complaint type and short description using Text boxes .
• The unique ComplaintId need to be generated instantly after raising the complaint to track status.
• The ComplaintId should be the combination of first four characters of complaint type text and Database auto generated number.

So based on preceding scenario let's start building application step by step so beginners can also understand .

Step 1: Create an 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:
   
   
   
   
3. 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,

• Installing Dapper ORM Into MVC 5 Project

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 Model class should be in 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 name or without 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

1. 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,

 

 

Now click on Add complaint button, It will shows the following message with ComplaintId which is returned by stored procedure after successfully adding the complaint as,

 

 

 

I hope from all thepreceding 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:

• Download the Zip file of the sample application for a better understanding.
• 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.

Read more articles on ASP.NET:

• Save DataTable Into ViewState and Bind Gridview Without DataBase Using ASP.Net
• Insert Bulk Records Into DataBase Using ASP.Net C#