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: