Insert and Update Stored Proc With Raw SQL Query Via EDF Framework

Introduction
 
Today, in this article let's play around with one of the interesting and most useful concepts in EDM Framework.

Insert Stored Proc with Raw SQL Query via EDF Framework
 
Question: What is insert stored proc with raw SQL query via EDM framework?
 
In simple terms "It provides flexibility to insert data using raw SQL queries via EDM framework".

Step 1: Create a new web application

Insert-Stored-Proc-with-Raw-SQL-Query1.png

Step 2: Set up a new EDM framework with reverse engineering to the project

Insert-Stored-Proc-with-Raw-SQL-Query2.png

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

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="InsertSPSQLQueryEntityApp.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="Insert Stored Proc with Raw SQL Query via EDM Framework"

                            Font-Bold="true"></asp:Label>

                    </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" align="center">

                        <asp:Button ID="Button1" runat="server" Text="Insert Data" OnClick="Button1_Click"

                            BackColor="Orange" Font-Bold="true" /><br />

                        <br />

                    </td>

                </tr>

            </table>

            <br />

            <br />

            <table>

                <tr>

                    <td colspan="3">

                        <asp:Label ID="Label5" runat="server" Font-Bold="true"></asp:Label>

                    </td>

                </tr>

            </table>

        </center>

    </div>

    </form>

</body>

</html>

Step 4: 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;

using InsertSPSQLQueryEntityApp.Models;

namespace InsertSPSQLQueryEntityApp

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            if (string.IsNullOrEmpty(TextBox1.Text) || string.IsNullOrEmpty(TextBox2.Text) || string.IsNullOrEmpty(TextBox3.Text))

            {

                Label5.Text = "Please Enter Some Values";

                Label5.ForeColor = System.Drawing.Color.Red;

            }

            else

            {

                objContext.Database.ExecuteSqlCommand("EXEC [dbo].[InsertEmpData] '" + TextBox1.Text + "', '" + TextBox2.Text + "', " + int.Parse(TextBox3.Text) + "");

                Label5.Text = "Data Successfully Inserted";

                Label5.ForeColor = System.Drawing.Color.Green;

                TextBox1.Text = string.Empty;

                TextBox2.Text = string.Empty;

                TextBox3.Text = string.Empty;

            }

        }

        #region Instance

        MembersCompanyContext objContext = new CompanyContext();

        #endregion

    }

}

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


Insert-Stored-Proc-with-Raw-SQL-Query3.png


Step 6: The inserted data output of the application looks like this:


Insert-Stored-Proc-with-Raw-SQL-Query4.png

Update Stored Proc with Raw SQL Query via EDF Framework
 
Question: What is update stored proc with raw SQL query via EDM framework?
 
In simple terms "It provides flexibility to update data using raw SQL queries via EDM framework".
 
Step 1: Create a new web application

Update-Stored-Proc-with-Raw-SQL-Query1.png

Step 2: Set up a new EDM framework with reverse engineering to the project

Update-Stored-Proc-with-Raw-SQL-Query2.png

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

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="UpdateSPSQLQueryEntityApp.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">

    <center>

        <div>

            <table>

                <tr>

                    <td colspan="2">

                        <asp:Label ID="Label1" runat="server" Text="Update Stored Proc. with Raw SQL Query via EDM Framework"

                            Font-Bold="true" Font-Size="Large" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="Label6" runat="server" Text="Please Enter Employee Id" Font-Size="Large"

                            Font-Names="Verdana" Font-Italic="true"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td>

                        <asp:Label ID="Label2" runat="server" Text="Please Enter FirstName" Font-Size="Large"

                            Font-Names="Verdana" 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" Font-Size="Large"

                            Font-Names="Verdana" 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" Font-Size="Large" Font-Names="Verdana"

                            Font-Italic="true"></asp:Label>

                    </td>

                    <td>

                        <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

                    </td>

                </tr>

                <tr>

                    <td colspan="2" align="center">

                        <asp:Button ID="Button2" runat="server" Text="Update Data" Font-Names="Verdana" Width="166px"

                            BackColor="Orange" Font-Bold="True" OnClick="Button2_Click" />

                    </td>

                </tr>

                <tr>

                    <td colspan="2" align="center">

                        <asp:Label ID="Label5" runat="server" Font-Bold="true" Font-Names="Verdana" ForeColor="Maroon"></asp:Label>

                    </td>

                </tr>

            </table>

        </div>

    </center>

    </form>

</body>

</html>

Step 4: 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;

using UpdateSPSQLQueryEntityApp.Models;

namespace UpdateSPSQLQueryEntityApp

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void Page_Load(object sender, EventArgs e)

        {

        }

        protected void Button2_Click(object sender, EventArgs e)

        {

            if (string.IsNullOrEmpty(TextBox1.Text) || string.IsNullOrEmpty(TextBox2.Text) || string.IsNullOrEmpty(TextBox3.Text) || string.IsNullOrEmpty(TextBox4.Text))

            {

                Label5.Text = "Please Enter Some Values";

                Label5.ForeColor = System.Drawing.Color.Red;

            }

            else

            {

                objContext.Database.ExecuteSqlCommand("EXEC [dbo].[UpdateData] '" + int.Parse(TextBox4.Text) + "', '" + TextBox1.Text + "', " + TextBox2.Text + " , " + int.Parse(TextBox3.Text) + " ");

                Label5.Text = "Data Updated Successfully";

                Label5.ForeColor = System.Drawing.Color.Green;

                TextBox1.Text = string.Empty;

                TextBox2.Text = string.Empty;

                TextBox3.Text = string.Empty;

                TextBox4.Text = string.Empty;

            }

        }

        #region Instance

        MembersCompanyContext objContext = new CompanyContext();

        #endregion

    }

}

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


Update-Stored-Proc-with-Raw-SQL-Query3.png


Step 6: The updated data output of the application looks like this:


Update-Stored-Proc-with-Raw-SQL-Query4.png

Up Next
    Ebook Download
    View all
    Learn
    View all
    MVC Corporation is consulting and IT services based company.