How to execute Sql Raw Query in
Linq C# .Net………………………
Note : There
is need of Linq in our project but not aware with linq syntax then a simple
user can use sql query in linq using dbml class. The whole concept is called
Sql Raw query. Means by this method all query like sql query if the user cannot
convert the query to linq then you can use it.
-- Sql Query...................
create table Employee (SrNo int primary key,Name varchar(50),Dob date,Salary int)
Source code Default.aspx……………………………
<%@ Page Language="C#"
AutoEventWireup="true"
CodeFile="Default.aspx.cs"
Inherits="_Default"
%>
<!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 runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table ><tr><td colspan="2"><b>How to execute Sql Raw Query in Linq C# .Net</b></td></tr>
<tr><td >SrNo</td><td><asp:TextBox ID="txtsrno" runat="server"></asp:TextBox></td></tr>
<tr><td >Name</td><td><asp:TextBox ID="txtname" runat="server"></asp:TextBox></td></tr>
<tr><td>Dob</td><td><asp:TextBox ID="txtdob" runat="server"></asp:TextBox></td></tr>
<tr><td >Salary</td><td><asp:TextBox ID="txtsalary" runat="server"></asp:TextBox></td></tr>
<tr><td colspan="2" ><asp:Button ID="btnInsert"
runat="server"
onclick="btnInsert_Click"
Text="Save"
/>
<asp:Button ID="btnDelete"
runat="server"
onclick="btnDelete_Click"
Text="Delete"
/>
<asp:Button ID="btnUpdate"
runat="server"
Text="Update"
onclick="btnUpdate_Click"
/>
<asp:Button ID="btnSelect"
runat="server"
onclick="btnSelect_Click"
Text="Search"
/>
</td>
</tr>
<tr><td colspan="2" >
<asp:Label ID="lblmessage"
runat="server"
Text="Label"
Visible="False"></asp:Label>
</td>
</tr>
<tr>
<td colspan="2">
<center>
<asp:GridView ID="GridView1"
runat="server">
</asp:GridView></center>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Source code Default.aspx.cs……………………………
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Drawing;
public partial class _Default :
System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs
e)
{
if(!IsPostBack)
BindGridView();
}
//create object of DataContext Class.....................
DataClassesDataContext dd = new DataClassesDataContext();
//code for insert data ................
protected void
btnInsert_Click(object sender, EventArgs e)
{
try
{
dd.ExecuteCommand("insert into Employee values({0},{1},{2},{3})",
Convert.ToInt32(txtsrno.Text), txtname.Text,
Convert.ToDateTime(txtdob.Text), Convert.ToInt32(txtsalary.Text));
BindGridView();
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "Add Successfully";
}
catch (Exception
ex)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
=ex.Message;
}
}
//code for delete data ................
protected void
btnDelete_Click(object sender, EventArgs e)
{
try
{
dd.ExecuteCommand("delete from Employee where SrNo=({0})",
Convert.ToInt32(txtsrno.Text));
BindGridView();
}
catch (Exception
ex)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= ex.Message;
}
}
//Search data with SrNo...................
protected void
btnSelect_Click(object sender, EventArgs e)
{
var v = dd.ExecuteQuery<Employee>("select * from Employee where SrNo={0}",
Convert.ToInt32(txtsrno.Text));
foreach (var emp in v)
{
txtname.Text
= emp.Name;
txtsalary.Text
= emp.Salary.ToString();
txtdob.Text
= emp.Dob.Value.ToShortDateString();
}
}
//code for bind GridView..
private void
BindGridView()
{
GridView1.DataSource
= dd.ExecuteQuery<Employee>("select srno,name,Dob,salary from Employee");
GridView1.DataBind();
}
//update data with
SrNo...................
protected void
btnUpdate_Click(object sender, EventArgs e)
{
try
{
dd.ExecuteCommand("update Employee set Name={0},Dob={1},Salary={2}
where SrNo={3}", txtname.Text, Convert.ToDateTime(txtdob.Text),
Convert.ToInt32(txtsalary.Text), Convert.ToInt32(txtsrno.Text));
BindGridView();
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Green;
lblmessage.Text
= "Update Successfully";
}
catch (Exception
ex)
{
lblmessage.Visible
= true;
lblmessage.ForeColor
= Color.Red;
lblmessage.Text
= ex.Message;
}
}
}
0 comments:
Post a Comment