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:


Entity1.png
 

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.

Entity2.gif

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

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

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

Entity5.png
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.

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

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

Entity8.gif
 

Entity9.png

Step 14:  
Let's create a select function.

Entity10.png

Step 15:
Let's create an update function.

Entity11.png

Step 16:
Let's create a delete function.

Entity12.png

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">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title
>
</head>
<
body>
    <form id="form1" runat="server">
    <div>
    <center>
    <table>
    <tr>
    <td>
        <asp:Label ID="Label1" runat="server" Text="CRUD with Stored Proc using EF Framework" Font-Bold="true"></asp:Label>
    </td>
    </tr>
    <tr>
    <td>
        <asp:GridView ID="GridView1" runat="server" BackColor="#CCCCCC"  AutoGenerateColumns="true"
            BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px" CellPadding="4"
            CellSpacing="2" ForeColor="Black" onload="Page_Load"
            >
            <FooterStyle BackColor="#CCCCCC" />
            <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
            <RowStyle BackColor="White" />
            <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#F1F1F1" />
            <SortedAscendingHeaderStyle BackColor="#808080" />
            <SortedDescendingCellStyle BackColor="#CAC9C9" />
            <SortedDescendingHeaderStyle BackColor="#383838" />
        </asp:GridView>
    </td>
    </tr
>

    </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">

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title
>
</head>
<
body>
    <form id="form1" runat="server">
    <div>
    <center>
    <table>
    <tr>
    <td>
        <asp:Label ID="Label1" runat="server" Text="Please Enter Student Id: " 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="Label2" runat="server" Text="Please Enter FirstName: " 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="Label3" runat="server" Text="Please Enter LastName: " ForeColor="Brown" Font-Bold="true" Font-Italic="true"></asp:Label>
    </td>
    <td>
        <asp:TextBox ID="TextBox3" 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="TextBox4" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td colspan="2">
        <asp:Button ID="Button1" runat="server" Text="Update Changes"
            onclick="Button1_Click" BackColor="Orange" Font-Bold="true" Width="117px" />
    </td>
    </tr>
    </table>
    <table>
    <tr>
    <td>
        <asp:Label ID="Label5" runat="server" ForeColor="Red" Font-Bold="true"></asp:Label>
    </td>
    </tr>
    </table
>

    <br />
    <br />
    <table>
    <tr>
    <td>
        <asp:Label ID="Label6" runat="server" Text="Please Enter Student Id: " ForeColor="Brown" Font-Bold="true" Font-Italic="true" ></asp:Label>
    </td>
    <td>
        <asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
    </td>
    </tr>
    <tr>
    <td colspan="2">
        <asp:Button ID="Button2" runat="server" Text="Delete" Width="117px" 
            BackColor="Orange" Font-Bold="true" onclick="Button2_Click"/>
    </td>
    </tr>
    <tr>
    <td colspan="2">
        <asp:Label ID="Label7" runat="server" ForeColor="Red" Font-Bold="true"></asp:Label
>

    </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:

Entity13.png
Step 22: The Insert Operation Application Output looks like this:

Entity14.png
 


 

Entity15.png
 

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

Entity16.png
 

Entity17.png
 

Entity18.png
 

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

Entity19.png
 


 

Entity20.png
 

I hope this article is useful for you.