LINQ to SQL Sample 1:
Open Visual Studio 2008--- file ---new--- website--- and create a new website
Then right click your project---click Add New Item
Choose LINQ to SQL Classes---- And name your .dbml file
The .dbml file will look like this,
Now go to View---Server Explorer and add a new database connection as displayed below,
Now drag and drop the tables.Here I am using a table by name tbl_Student
Now go to the Default.aspx page and place a GridView
Now in the Page_Load event (ie in the Default.aspx.cs file)--- type the following code displayed below,
(Note: Initially it will show some error markings, make sure that you have to build your solution file before moving into the next step.To build Solution---right click on the project solution and click build solution option)
Now type the following code and see the output.
SAMPLE 2:
SIMPLE INSERT,UPDATE AND DELETE OPERATIONS IN GRIDVIEW USING LINQ CONCEPT:
CREATE A .dbml file as shown in the above sample.(ie in sample1)
Eg:- Here i have created a .dbml file by name DISPSTUDENT.dbml
Create a .aspx and .aspx.cs file as shown below,
LINQGRID.aspx:
(Design View)
(Source view)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LINQGRID.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 style="height: 364px">
Name:
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
Reg.No:
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
Address:
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="467px" AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True" onrowcancelingedit="rowCancel"
onrowdeleting="rowDel" onrowediting="rowEdit" onrowupdating="rowUpdate"
AutoGenerateColumns="False">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:TemplateField HeaderText="Student RegNo">
<ItemTemplate>
<asp:Label ID="lblStudID" runat="server" Text='<%# Bind("Stud_ID") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudID" runat="server" Text='<%# Bind("Stud_ID") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<Columns>
<asp:TemplateField HeaderText="Student Name">
<ItemTemplate>
<asp:Label ID="lblStudName" runat="server" Text='<%# Bind("Stud_Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudName" runat="server" Text='<%# Bind("Stud_Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<Columns>
<asp:TemplateField HeaderText="Student Address">
<ItemTemplate>
<asp:Label ID="lblStudAddr" runat="server" Text='<%# Bind("Stud_Addr") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudAddr" runat="server" Text='<%# Bind("Stud_Addr") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
</form>
</body>
</html>
LINQGRID.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
string studID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
fillGrid();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
tbl_Student insertStud = new tbl_Student();
insertStud.Stud_ID = TextBox2.Text;
insertStud.Stud_Name = TextBox1.Text;
insertStud.Stud_Addr = TextBox3.Text;
disp.tbl_Students.InsertOnSubmit(insertStud);
disp.SubmitChanges();
fillGrid();
}
public void fillGrid()
{
//For displaying the data
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
//if it shows error build the solution and try
var studentDetails = from s in disp.tbl_Students
select s;
GridView1.DataSource = studentDetails;
GridView1.DataBind();
}
protected void rowDel(object sender, GridViewDeleteEventArgs e)
{
//For Performing Deletion operation
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
studID = GridView1.Rows[e.RowIndex].Cells[1].Text;
var deleteStudentDetails = from studDetails in disp.tbl_Students
where studDetails.Stud_ID == studID
select studDetails;
foreach (var deleteDetails in deleteStudentDetails)
{
disp.tbl_Students.DeleteOnSubmit(deleteDetails);
}
disp.SubmitChanges();
fillGrid();
}
protected void rowEdit(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
fillGrid();
}
protected void rowCancel(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
fillGrid();
}
protected void rowUpdate(object sender, GridViewUpdateEventArgs e)
{
//For Performing Update Operation
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
TextBox sID = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudID");
TextBox sName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudName");
TextBox sAddr = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudAddr");
var updateStudentDetails = (from studDetails in disp.tbl_Students
where studDetails.Stud_ID == sID.Text
select studDetails).Single();
tbl_Student updateStudent = new tbl_Student();
updateStudentDetails.Stud_Name = sName.Text;
updateStudentDetails.Stud_Addr = sAddr.Text;
disp.SubmitChanges();
GridView1.EditIndex = -1;
fillGrid();
}
}
Thats it. Now your gridview runs more efficiently and fastly with LINQ.
It is also a disconnected approach for handling the data.
CREATE A .dbml file as shown in the above sample.(ie in sample1)
Eg:- Here i have created a .dbml file by name DISPSTUDENT.dbml
Create a .aspx and .aspx.cs file as shown below,
LINQGRID.aspx:
(Design View)
(Source view)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="LINQGRID.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 style="height: 364px">
Name:
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
Reg.No:
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
Address:
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button" />
<br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333"
GridLines="None" Width="467px" AutoGenerateDeleteButton="True"
AutoGenerateEditButton="True" onrowcancelingedit="rowCancel"
onrowdeleting="rowDel" onrowediting="rowEdit" onrowupdating="rowUpdate"
AutoGenerateColumns="False">
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<Columns>
<asp:TemplateField HeaderText="Student RegNo">
<ItemTemplate>
<asp:Label ID="lblStudID" runat="server" Text='<%# Bind("Stud_ID") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudID" runat="server" Text='<%# Bind("Stud_ID") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<Columns>
<asp:TemplateField HeaderText="Student Name">
<ItemTemplate>
<asp:Label ID="lblStudName" runat="server" Text='<%# Bind("Stud_Name") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudName" runat="server" Text='<%# Bind("Stud_Name") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<Columns>
<asp:TemplateField HeaderText="Student Address">
<ItemTemplate>
<asp:Label ID="lblStudAddr" runat="server" Text='<%# Bind("Stud_Addr") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="txtStudAddr" runat="server" Text='<%# Bind("Stud_Addr") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<EditRowStyle BackColor="#999999" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</div>
</form>
</body>
</html>
LINQGRID.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class _Default : System.Web.UI.Page
{
string studID;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
fillGrid();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
tbl_Student insertStud = new tbl_Student();
insertStud.Stud_ID = TextBox2.Text;
insertStud.Stud_Name = TextBox1.Text;
insertStud.Stud_Addr = TextBox3.Text;
disp.tbl_Students.InsertOnSubmit(insertStud);
disp.SubmitChanges();
fillGrid();
}
public void fillGrid()
{
//For displaying the data
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
//if it shows error build the solution and try
var studentDetails = from s in disp.tbl_Students
select s;
GridView1.DataSource = studentDetails;
GridView1.DataBind();
}
protected void rowDel(object sender, GridViewDeleteEventArgs e)
{
//For Performing Deletion operation
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
studID = GridView1.Rows[e.RowIndex].Cells[1].Text;
var deleteStudentDetails = from studDetails in disp.tbl_Students
where studDetails.Stud_ID == studID
select studDetails;
foreach (var deleteDetails in deleteStudentDetails)
{
disp.tbl_Students.DeleteOnSubmit(deleteDetails);
}
disp.SubmitChanges();
fillGrid();
}
protected void rowEdit(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
fillGrid();
}
protected void rowCancel(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
fillGrid();
}
protected void rowUpdate(object sender, GridViewUpdateEventArgs e)
{
//For Performing Update Operation
DISPSTUDENTDataContext disp = new DISPSTUDENTDataContext();
TextBox sID = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudID");
TextBox sName = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudName");
TextBox sAddr = (TextBox)GridView1.Rows[e.RowIndex].FindControl("txtStudAddr");
var updateStudentDetails = (from studDetails in disp.tbl_Students
where studDetails.Stud_ID == sID.Text
select studDetails).Single();
tbl_Student updateStudent = new tbl_Student();
updateStudentDetails.Stud_Name = sName.Text;
updateStudentDetails.Stud_Addr = sAddr.Text;
disp.SubmitChanges();
GridView1.EditIndex = -1;
fillGrid();
}
}
Thats it. Now your gridview runs more efficiently and fastly with LINQ.
It is also a disconnected approach for handling the data.
Hi Vijay Vignesh,
ReplyDeleteYour post was pretty useful for me.
I have a problem while creating dbml
it says:
"Error 1 The custom tool 'MSLinqToSQLGenerator' failed. Could not retrieve the current project. D:\LINQ\App_Code\DataClasses.dbml D:\LINQ\
"
Can you help me ?
Now you have reset the packages for this issue.
ReplyDeleteIn the VS2008 Command Prompt type the following command,
"devenv/ResetSkipPkgs"
then open your LinqToSql files.
(OR)
In the registry try to delete all subkeys under
HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\9.0\Packages