In this article i will show you how you can perform insert,update,delete operation in mvc3 by using stored procedure.
In this we article i have created a table called AreaTable in my MS Sql server DB. After that i have created three stored
procedure for insert,update and delete. They are as follows.
Create New Table:
CREATE TABLE [dbo].[AreaTable](
[AreaID] [int] IDENTITY(1,1) NOT NULL,
[AreaName] [varchar](50) NOT NULL,
[ImageUrl] [varchar](100) NULL,
[IsDeleted] [int] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Create Insert Store Procedure:
Create PROCEDURE [dbo].[InsertArea]
@AreaName varchar(50),
@ImageUrl varchar(50),
@IsDeleted int
AS
BEGIN
insert into dbo.AreaTable (AreaName,ImageUrl,IsDeleted)
values(@AreaName,@ImageUrl,@IsDeleted)
END
Create Update Store Procedure:
CREATE PROCEDURE [dbo].[UpdateAreaa]
@AreaID int,
@AreaName varchar(50),
@ImageUrl varchar(50),
@IsDeleted int
AS
BEGIN
Update AreaTable set AreaName=@AreaName,ImageUrl=@ImageUrl,
IsDeleted=@IsDeleted where AreaID=@AreaID
END
Create Delete Store Procedure:
CREATE PROCEDURE [dbo].[DeleteArea]
@AreaID int
AS
BEGIN
delete from AreaTable where AreaID=@AreaID
END
For creating table and SP just run the above query in you sql sqever DB.
Now we will come to our main task. Open your visual studio .net and create a new MVC3 project.
Select project and click ok button. As you press ok a new window will open in this you have to select type of project. select internet application and click ok.
After creating project we will add an entity file in our project. In this entity file we will include our table and Store procedures.
For creating the model file just follow the step.
Step 1:
Right click on you project and select add->New Item. click on new item as window will open.
Now form list select ADO.NET Entity Data Model. Rename the file and click ok. As you click on a new window will open where we have to select the generate db option and click next.
After next-> a window will open here you have to make the DB connection by clicking on new connection.
Now select the DB and click ok. As you click ok you will return to previous window.
In this window you have to define name for entity file name. Now click ok we will come to next window. In this window we have to select the table and SP from the Table and sp collection.
Here we have to define the model name. after this click on finish.
Table which you have selected will appear in your .edmx file .
But for checking the SP you have open you model browser .
Now we will create function for using our insert, Update, Delete SP. For that just right click on you .edmx file and select Add->Function import. A new window will open. Here we have to define function name by selecting the desired SP and click ok button
Now come to project for writing the code.
Select you Model folder and add a new model class . For this select the model folder right click-> Add->New item. Now window will open in this you have to select the class file change name and click ok.
Now add the below code.
public class Class1
{
public int areaid { get; set; }
public string areaname { get; set; }
public string imageurl { get; set; }
public int isdeleted { get; set; }
}
public class ListClass1
{
public List<Class1> objclass1model { get; set; }
}
This is property we are defining.
Now come to your controller. in this first we have to create an instance for accessing the entity properties.
public class demoController : Controller
{
//
// GET: /demo/
demoEntities objdemoEntities = new demoEntities();
}
Now come to your index method Create view for this action result method. In index view add the below code and use the below code in controller.
public ActionResult Index()
{
ListClass1 objListClass1 = new ListClass1();
List<Class1> objClass1 = new List<Class1>();
objClass1 = GetAreaRecord();
objListClass1.objclass1model = objClass1;
return View(objListClass1);
}
public List<Class1> GetAreaRecord()
{
List<Class1> objClass1 = new List<Class1>();
var areadetail = objdemoEntities.AreaTables;
if (areadetail != null)
{
foreach (var item in areadetail)
{
objClass1.Add(new Class1
{
areaid = item.AreaID,
areaname = item.AreaName,
imageurl = item.ImageUrl,
isdeleted = item.IsDeleted
});
}
}
return objClass1;
}
In above code we have accessed all the records which we have saved in our DB. Now for displaying the record. Add the below code in your index view.
@model insert_update_delete.Models.ListClass1
@{
ViewBag.Title = "Index";
}
<h2>
<a href="/demo/SaveRecord/">
Add New Record</a>
</h2>
<br />
<table width="40%" border="1">
<tr style="font-weight:bold;">
<td>Area Name</td>
<td>Image URL</td>
<td>Is Deleted</td>
<td></td>
<td></td>
</tr>
@foreach (var item in Model.objclass1model)
{
<tr>
<td>@item.areaname</td>
<td>@item.imageurl</td>
<td>@item.isdeleted</td>
<td><a href="/demo/UpdateRecord/?areaid=@item.areaid">Update</a></td>
<td><a href="/demo/DeleteRecordFunction/?areaid=@item.areaid">Delete</a></td>
</tr>
}
</table>
in above code we are creating grid for displaying record.
Now add an action method for creating the insert form. Add the below code and add the below code.
[HttpGet]
public ActionResult SaveRecord()
{
return View();
}
Now right click and add new view. you view will get generated. Add the below code in your view.
@model insert_update_delete.Models.Class1
@{
ViewBag.Title = "SaveRecord";
}
@if (ViewData["savestatus"] != null)
{
string status = ViewData["savestatus"].ToString();
if (status == "1")
{
<script language="javascript">
alert("data saves successfully.");
</script>
}
else
{
<script language="javascript">
alert("error while saving.");
</script>
}
}
@using (Html.BeginForm("SaveRecord", "demo"))
{
<h2>
Insert New Record</h2>
<div>
Area Name</div>
<div>@Html.TextBoxFor(m => m.areaname, new { @style = "width:200px;" })</div>
<div>
Flag Url</div>
<div>@Html.TextBoxFor(m => m.imageurl, new { @style = "width:200px;" })</div>
<div>
Is Deleted</div>
<div>@Html.TextBoxFor(m => m.isdeleted, new { @style = "width:200px;" })</div>
<div>
<input type="submit" name="submit" value="Save" />
<a href="/demo/index/">Back</a>
</div>
}
just check the bold part in the code. This for accessing the model property which we have defined in our model class file.
Now add the post method which will get fired when we click on button to save the data.
[HttpPost]
public ActionResult SaveRecord(Class1 objClass1)
{
SaveRecordFunction(objClass1);
return View();
}
public void SaveRecordFunction(Class1 objClass1)
{
try
{
//Call your insert function to save record
objdemoEntities.Insert(objClass1.areaname, objClass1.imageurl, objClass1.isdeleted);
ViewData["savestatus"] = "1";
}
catch
{
ViewData["savestatus"] = "0";
}
}
In above code check the bold part. In this the objdemoEntity is the instant of our entity file. With the help of this entity we are accessing the Insert save function which have created against the Insert SP.
Now run the application.
Above window will open. click on add new record. a new window will open. in this add the record and click on save you will get success or error message.
After success full insert message will come.
Now click on back button You will get record in you grid.
Now we will move for update. Add the below code and add view as i have explained for insert.
[HttpGet]
public ActionResult UpdateRecord(int areaid=0)
{
Class1 objClass1 = new Class1();
objClass1 = GetRecord(areaid);
return View(objClass1);
}
[HttpPost]
public ActionResult UpdateRecord(Class1 objClass1)
{
UpdateRecordFunction(objClass1);
return View(objClass1);
}
public Class1 GetRecord(int areaid)
{
Class1 objClass1 = new Class1();
var areadetail = objdemoEntities.AreaTables.Where(m => m.AreaID == areaid);
if (areadetail != null)
{
foreach (var item in areadetail)
{
objClass1.areaid = item.AreaID;
objClass1.areaname = item.AreaName;
objClass1.imageurl = item.ImageUrl;
objClass1.isdeleted = item.IsDeleted;
}
}
return objClass1;
}
public void UpdateRecordFunction(Class1 objClass1)
{
try
{
//Call your update function to update record
objdemoEntities.Update(objClass1.areaid, objClass1.areaname, objClass1.imageurl, objClass1.isdeleted);
ViewData["updatestatus"] = "1";
}
catch
{
ViewData["updatestatus"] = "0";
}
}
In above httpget method we have defined a parameter int areaid=0 this is used for getting the area id in request query string when user redirect from main to update page after clicking on update.
In url have passed areaid=5 which are getting in our method.
just check the screenshot.
HTML code:
@model insert_update_delete.Models.Class1
@{
ViewBag.Title = "Update Record";
}
@if (ViewData["updatestatus"] != null)
{
string status = ViewData["updatestatus"].ToString();
if (status == "1")
{
<script language="javascript">
alert("data updated successfully.");
</script>
}
else
{
<script language="javascript">
alert("error while updateing.");
</script>
}
}
@using (Html.BeginForm("UpdateRecord", "demo"))
{
<h2>
Update Record</h2>
<div>
Area Name</div>
<div>@Html.TextBoxFor(m => m.areaname, new { @style = "width:200px;" })
@Html.HiddenFor(m => m.areaid)
</div>
<div>
Flag Url</div>
<div>@Html.TextBoxFor(m => m.imageurl, new { @style = "width:200px;" })</div>
<div>
Is Deleted</div>
<div>@Html.TextBoxFor(m => m.isdeleted, new { @style = "width:200px;" })</div>
<div>
<input type="submit" name="submit" value="Update" />
<a href="/demo/index/">Back</a>
</div>
}
Now make some changes in name and click update . you will get the success message. click ok and clickon back button you will get the output.
check the output in above image.
Now will go for delete. Do same as we have dome for insert and update.
[HttpGet]
public ActionResult DeleteRecordFunction(int areaid)
{
try
{
//Call your delete function to delete record
objdemoEntities.Delete(areaid);
ViewData["deletetatus"] = "1";
}
catch
{
ViewData["deletetatus"] = "0";
}
return RedirectToAction("/index/");
}
in above code we are accessing the areaid as we have accessed in update. After deletion we are redirecting to index page for checking the effect.
check as we are clicking on delete the delete meth is getting called and we are getting id as of area. and we have passed it to delete function which we have created for our SP. At last performing operation we are redirecting to index page.
Now we have done. Just download the code and try by your self. before starting just create table and SP`s. and change you connection string in your web.config.
<connectionStrings>
<add name="demoEntities" connectionString="metadata=res://*/Dotnetpools.csdl|res:
//*/Dotnetpools.ssdl|res://*/Dotnetpools.msl;provider=System.Data.SqlClient;provider connection string="Data Source=*********;Initial Catalog=DotnetPools;Persist Security Info=True;User ID=*******;Password=*****;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" />
</connectionStrings>
