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

Vijay Prativadi
12y
23.3k
0
0

Article

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");
            }
        }