Delete and Update Data 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.

Delete Data with Raw SQL Query via EDF Framework

Question: What is delete data with raw SQL query via EDM framework?

In simple terms "It provides flexibility to delete data using raw SQL queries via EDM framework".

Step 1: Create a new web application

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

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

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

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

.grid

{

margin-top: 50px;

}

</style>

</head>

<body>

<div>

<table>

<tr>

<asp:Label ID="Label1" runat="server" Text="Delete Data with Raw SQL Query via Entity Framework"

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

</td>

</tr>

<tr>

<td>

<asp:Label ID="Label2" runat="server" Text="Please Enter Employee Id" ForeColor="Brown"

Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></asp:Label>

</td>

<td>

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

</td>

</tr>

<tr>

<asp:Button ID="Button1" runat="server" Text="Delete Data" Font-Names="Verdana" Width="213px"

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

</td>

</tr>

<tr>

<asp:Label ID="Label3" runat="server" Font-Bold="true" Font-Size="Medium" Font-Names="Verdana"></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 DeleteDataSQLQueryEntityApp.Models;

using System.Data;

namespace DeleteDataSQLQueryEntityApp

{

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

{

Label3.Text = "Please Enter Some Values";

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

}

else

{

objContext.Database.ExecuteSqlCommand("delete from dbo.tblEmployee where EmpId = " + int.Parse(TextBox1.Text) + "");

Label3.Text = "Data Deleted Successfully";

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

TextBox1.Text = string.Empty;

}

#region Instance

MembersCompanyContext objContext = new CompanyContext();

#endregion

}

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

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

Update Data with Raw SQL Query via EDF Framework

Question: What is update data 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

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

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

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

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

</head>

<body>

<div>

<table>

<tr>

<asp:Label ID="Label1" runat="server" Text="Update Data 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>

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

<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 UpdateDataSQLQueryEntityApp.Models;

namespace UpdateDataSQLQueryEntityApp

{

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("update dbo.tblEmployee SET FirstName = '" + TextBox1.Text + "', LastName = '" + TextBox2.Text + "', Age = " + int.Parse(TextBox3.Text) + " where EmpId = " + int.Parse(TextBox4.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:

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