CRUD Operations With Stored Procedures Via Entity Data Model Framework (EDM)

Today, in this article let's play around with one of the good interesting features, stored procedures with EDM Framework.

Let's get this started now.

Create a New Database and Table in SQL SERVER 2008 .

Step 1: Database Name: Candidate.

Step 2: Table Name: Student.

The Design View of the Student Table looks like this:

Let's now work towards creating some SP's.

Step 3: The complete script of the Select Stored Procedure looks like this:

Use Candidate
Go
Create Procedure Select_Student
As
Begin
Select StudentId, FirstName, LastName, Age from Student
End

Step 4: The complete script of the Insert Stored Procedure looks like this:

Use Candidate
Go
Create Procedure insert_Student(@FirstName nvarchar(50), @LastName nvarchar(50), @Age int)
As
Begin
insert into Student(FirstName, LastName, Age)values (@FirstName, @LastName, @Age)
End

Step 5: The complete script of the Update Stored Procedure looks like this:

Use Candidate
Go
Create Procedure update_Student(@Id int, @FirstName nvarchar(50), @LastName nvarchar(50), @Age int)
As
Begin
Update Student SET FirstName = @FirstName, LastName = @LastName, Age = @Age where StudentId = @Id
End

Step 6: The complete script of the Delete Stored Procedure looks like this:

Use Candidate
Go
Create Procedure delete_Student(@Id int)
As
Begin
delete from Student where StudentId = @Id
End

We are now done dealing with the SQL Server part.

Now let's get our Visual Studio started up.

The steps to add the EDM Framwork to our application is as below.

Step 7: Select the Entity Data Model Framework from the Data Tab.

Step 8: Click on Next and Select Generate from database.

Step 9: Select the Database and give a Specific Name for the Entity

Step 10: Click on Next and Select Respective Tables and SP's from List. Finally Click Finish.

Now, we haven't began the full bound code part. Let's also finish off the stored procedure mapping and function definition.

Step 11: Right-click on the Respective Table of the Model1.edmx designer and Select Stored Procedure Mapping.

Step 12: Map the respective columns with the table definition.

Step 13: Let's import a function and create an insert function.

Step 14: Let's create a select function.

Step 15: Let's create an update function.

Step 16: Let's create a delete function.

Step 17: The complete code of the WebForm1.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="CRUD_SProc.WebForm1" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</table><br /><br />
<table>
<tr>
<td>
<asp:Label ID="Label2" runat="server" Text="Please Enter FirstName: " ForeColor="Brown" Font-Bold="true" Font-Italic="true"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label3" runat="server" Text="Please Enter LastName: " ForeColor="Brown" Font-Bold="true" Font-Italic="true"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>
<asp:Label ID="Label4" runat="server" Text="Please Enter Age: " ForeColor="Brown" Font-Bold="true" Font-Italic="true"></asp:Label>
</td>
<td>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2">
<asp:Button ID="Button1" runat="server" Text="Insert Data"
onclick="Button1_Click" BackColor="Orange" Font-Bold="true" />
<br /><br /></td>
</tr>
<tr>
<td colspan="2">
<asp:LinkButton ID="LinkButton1" runat="server" Text="Click Here To Update"
onclick="LinkButton1_Click" BackColor="Gray" Font-Bold="true"></asp:LinkButton>
</td></tr>
</table>
<br />
<br />
<table>
<tr>
<td colspan="3">
<asp:Label ID="Label5" runat="server" ForeColor="Red" Font-Bold="true"></asp:Label>

</td>
</tr>
</table>
</center>

</div>
</form>
</body>
</html>

Step 18: The complete code of WebForm1.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CRUD_SProc
{
public partial class WebForm1 : System.Web.UI.Page
{
CandidateEntities2 obj_Entities = new CandidateEntities2();
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
select();
}
}

protected void select()
{
GridView1.DataSource = obj_Entities.selectStudentData();
GridView1.DataBind();

}
protected void insert()
{
Label5.Text = "";
if (string.IsNullOrEmpty(TextBox1.Text) || string.IsNullOrEmpty(TextBox2.Text) || string.IsNullOrEmpty(TextBox3.Text))
{
Label5.Text = "Please Enter Some Values";
}
else
{
obj_Entities.insertStudentData(TextBox1.Text, TextBox2.Text, Convert.ToInt32(TextBox3.Text));
obj_Entities.SaveChanges();
select();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
}
}

protected void Button1_Click(object sender, EventArgs e)
{
insert();

}

protected void LinkButton1_Click(object sender, EventArgs e)
{
Response.Redirect("~/WebForm2.aspx");
}

}
}

Step 19: The complete code of WebForm2.aspx looks like this:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="CRUD_SProc.WebForm2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</td>
</tr>
</table>
</center>
</div>
</form>
</body>
</html>

Step 20: The complete code of WebForm2.aspx.cs looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace CRUD_SProc
{
public partial class WebForm2 : System.Web.UI.Page
{
CandidateEntities2 obj_Entities = new CandidateEntities2();
protected void Page_Load(object sender, EventArgs e)
{

}

public void update()
{
Label5.Text = "";
if (string.IsNullOrEmpty(TextBox1.Text) || string.IsNullOrEmpty(TextBox2.Text) || string.IsNullOrEmpty(TextBox3.Text) || string.IsNullOrEmpty(TextBox4.Text))
{
Label5.Text = "Please Enter Some Values";
}
else
{
obj_Entities.updateStudentData(Convert.ToInt32(TextBox1.Text), TextBox2.Text, TextBox3.Text, Convert.ToInt32(TextBox4.Text));
obj_Entities.SaveChanges();
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
Response.Redirect("~/WebForm1.aspx");

}
}

protected void Button1_Click(object sender, EventArgs e)
{
update();
}

protected void delete()
{
Label7.Text = "";
if (string.IsNullOrEmpty(TextBox5.Text))
{
Label7.Text = "Please Enter Some Values";
}
else
{
obj_Entities.deleteStudentData(Convert.ToInt32(TextBox5.Text));
obj_Entities.SaveChanges();
TextBox5.Text = "";
Response.Redirect("~/WebForm1.aspx");
}
}

protected void Button2_Click(object sender, EventArgs e)
{
delete();
}
}
}

Step 21: The output of the application looks like this:

Step 22: The Insert Operation Application Output looks like this:

Step 23: The Update Operation Application Output looks like this:

Step 24: The Delete Operation Application Output looks like this:

I hope this article is useful for you.

15 Comments

Join the conversation! Your thoughts help the community grow.

Sanjay Kumar13y ago
Thank you.
Santosh Yadav13y ago
great work.
Santosh Yadav13y ago
great work.
Santosh Yadav13y ago
great work thanks.
Dorababu Meka14y ago
Hi how we will get complex as per you did in order to select data
Dorababu Meka14y ago
Hi I unable to get the parameters in stored procedure mapping, I am using Mysql database can you help me. This is my routine in mysql -- -------------------------------------------------------------------------------- -- Routine DDL -- -------------------------------------------------------------------------------- DELIMITER $$ CREATE DEFINER=`root`@`%` PROCEDURE `uspInsertUsers1`(_Id varchar(50), _Name varchar(50),_Address varchar(50)) BEGIN insert into tblEntity(entityID,Name,Address)values(_Id, _Name,_FirstName,_Address); END
nallya14y ago
thanks
sandeep raju dantuluri14y ago
good explanation..nice article
- Vijay Prativadi14y ago
  Thanks Sandeep!!!
Manoj Singh Panwar14y ago
Its pleasure to find this article. Thanks for sharing.
- Vijay Prativadi14y ago
  Thanks Manoj!!!
sanjay gupta14y ago
Very Helpful
- Vijay Prativadi14y ago
  Thanks Sanjay!!
Adora Krause14y ago
Hi Vijay. You have presented your article in such a way that anyone can understand it easily.
- Vijay Prativadi14y ago
  Thanks Adora!!!