asp.net MVC

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

How to insert data using stored procedure in asp.net mvc dapper example?, someone asked me to explain?

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.

create a table with following fields and name as 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:

how to get return value from stored procedure in c#

Post your comments / questions