How to insert data using stored procedure in asp.net mvc dapper example?

In this article, I will show you how to insert data and get a return value Reference ID from stored procedure in c# asp.net mvc dapper. The reference Id will be generated from database and it is the combination of the first four letters of the ItemName and Itemid.

Step 1: To begin install dapper in the project by running the following command in the nuget package manager console.

PM> Install-Package Dapper

Step 2:  Create a SQL table with the following columns as shown in the below figure and name it an item.

Step 3: Create a stored procedure and name it as dbo.usp_InsertItem. Copy and paste it in SQL server and run.

CREATE PROCEDURE [dbo].[usp_InsertItem]  
(  
@ItemName varchar(100),  
@Description varchar(150),  
@RefID varchar(20)=null out  
)  
AS  
BEGIN  
SET NOCOUNT ON;  
DECLARE @ItemRef varchar(30)  
--Getting unquieId  
SELECT @ItemRef=isnull(max(ItemId),0)+1 FROM Item  
--Generating theunique reference number and seeting to output parameter  
Set @RefID=Upper(LEFT(@ItemName,4))+convert(VARCHAR,@ItemRef)  

INSERT INTO [dbo].[Item]  
           (  
            [RefID]  
           ,[ItemName]  
           ,[Description]  
           )  
     VALUES  
           (  
          @RefID,  
          @ItemName,  
          @Description  
           )  
END  

Step 4: Right click on the model folder and create a class and name it as ItemModel. Copy and paste the following code.

    public class ItemModel
    {
        [Display(Name = "ItemName")]
        [Required]
        public string ItemName { get; set; }
        [Display(Name = "ItemDescription")]
        [Required]
        public string Description { get; set; }  
    }

Step 5: Create another class ItemRepository in the model folder. Copy and paste the following code on it.

public class ItemRepository
    {
        SqlConnection con;
        //ToHandle connection related activities  
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["SqlConn"].ToString();
            con = new SqlConnection(constr);
        }
        //To AddComplaint details  
        public string AddItem(ItemModel Obj)
        {
            DynamicParameters ObjParm = new DynamicParameters();
            ObjParm.Add("@ItemName",Obj.ItemName);
            ObjParm.Add("@Description",Obj.Description);
            ObjParm.Add("@RefID",dbType: DbType.String,direction: ParameterDirection.Output, size: 5215585);
            connection();
            con.Open();
            con.Execute("usp_InsertItem",ObjParm, commandType: CommandType.StoredProcedure);
            //Gettingthe out parameter value of stored procedure 
            var RefID = ObjParm.Get<string>("@RefID");
            con.Close();
            return RefID;
        } 
    }

Step 6: Right click on the controller folder and create a new controller and name it as ItemController. Inside the ItemController copy and paste the following code.

     public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        public ActionResult Index(ItemModel ObjComp)
        {
            try
            {
                ItemRepository Obj = new ItemRepository();
                //toViewBag with custom message to show user 
                ViewBag.RefID = "Item inserted successfully, Your RefID is " + Obj.AddItem(ObjComp);
            }
            catch (Exception)
            {
                //errorto show users, If any error occures.  
                ViewBag.RefID = "Error while inserting item, Please checkdetails";
            }
            return View();
        }

Step 7: Right click on the index method and create a view named as index. Copy and paste the following code.

@modelMVC_tutorials.Models.ItemModel
@{  
    ViewBag.Title = "insert using Dapper";  
}
@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.ItemName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.ItemName, new { htmlAttributes = new { @class = "form-control" }})
                @Html.ValidationMessageFor(model => model.ItemName, "", new { @class = "text-danger" })
            </div>
        </div>
 
        <div class="form-group">
            @Html.LabelFor(model => model.Description,htmlAttributes: new { @class = "control-labelcol-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Description, new { htmlAttributes = new { @class = "form-control" }})
               @Html.ValidationMessageFor(model=> model.Description, "", new { @class = "text-danger" })
            </div>
        </div>
        <br />
        <div class="form-group">
            <div class="col-md-offset-2col-md-10">
                <input type="submit" value="Add Item" class="btn btn-primary" />
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2col-md-10 text-success">
                @ViewBag.RefID
            </div>
        </div>
    </div>  
}
<script src="http://code.jquery.com/jquery-latest.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Description: Now run the application and entering the details of item and click the Add item button. The record will be saved successfully and also generate a RefID.

Dapper example: