How to Bind, Edit, Update and Delete in Gridview Control using LINQ

Introduction: In this pots I will explain how we can Bind, Edit, Update and Delete the Gridview Data control using LINQ.

Description:

Create a table name LINQ_TABLE.

Now go to Visual studio>File>New website>Asp.net empty web site.

Now go to Solution Explorer, right click on website>Add new item>Linq to Sql classes.

Now you see App_code folder will added to application and a dataclasses added with extension .dbml in App_code folder.

Now check the web.config file of application. It self create a connectionstring for database.

Now connect the database to server explorer. After that drag and drop the table from database.

Add a web form to application. Go to Solution Explorer, right click on website>Add new item> Web from.

Drag and drop a Gridview Data control from from Toolbox>Data.

<asp:GridView ID="grduser" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"

onrowediting="GridView1_RowEditing"

onrowcancelingedit="grduser_RowCancelingEdit"

onrowupdating="grduser_RowUpdating" onrowdeleting="grduser_RowDeleting"

AllowPaging="True" onpageindexchanging="grduser_PageIndexChanging" PageSize="8"

<asp:TemplateField HeaderText="Username">

<asp:TextBox ID="txtuser" runat="server" Text='<%# Eval("USERNAME") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lbluser" runat="server" Text='<%# Eval("USERNAME") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="First Name">

<asp:TextBox ID="txtfirst" runat="server" Text='<%# Eval("FIRST_NAME") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lblfirst" runat="server" Text='<%# Eval("FIRST_NAME") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Last Name">

<asp:TextBox ID="txtlast" runat="server" Text='<%# Eval("LAST_NAME") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lbllast" runat="server" Text='<%# Eval("LAST_NAME") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Date Of Birth">

<asp:TextBox ID="TextBox4" runat="server" Text='<%# Eval("DATE_BIRTH") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lblbirth" runat="server" Text='<%# Eval("DATE_BIRTH") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Sex">

<asp:TextBox ID="TextBox5" runat="server" Text='<%# Eval("SEX") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lblsex" runat="server" Text='<%# Eval("SEX") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Age">

<asp:TextBox ID="txtage" runat="server" Text='<%# Eval("AGE") %>'></asp:TextBox>

</EditItemTemplate>

<asp:Label ID="lblage" runat="server" Text='<%# Eval("AGE") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Profile Image">

<asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("IMAGE") %>' Height="150px" Width="150px"/>

</ItemTemplate>

</asp:TemplateField>

<asp:CommandField ShowEditButton="True" />

<asp:CommandField ShowDeleteButton="True" />

</Columns>

</asp:GridView>

Now go to .aspx.cs page.

DataClassesDataContext db = new DataClassesDataContext();

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

Bindgrid();

}

private void Bindgrid()

{

var bind = from c in db.LINQ_TABLEs

select c;

grduser.DataSource = bind;

grduser.DataBind();

}

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

grduser.EditIndex = e.NewEditIndex;

Bindgrid();

}

protected void grduser_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

grduser.EditIndex = -1;

Bindgrid();

}

protected void grduser_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

try

{

GridViewRow row = grduser.Rows[e.RowIndex];

TextBox txtuser = (TextBox)row.FindControl("txtuser");

TextBox txtfirst = (TextBox)row.FindControl("txtfirst");

TextBox txtlast = (TextBox)row.FindControl("txtlast");

TextBox txtbirth = (TextBox)row.FindControl("TextBox4");

TextBox txtsex = (TextBox)row.FindControl("TextBox5");

TextBox txtage = (TextBox)row.FindControl("txtage");

int ID = Convert.ToInt32(grduser.DataKeys[e.RowIndex].Value);

LINQ_TABLE tb = new LINQ_TABLE();

tb = db.LINQ_TABLEs.First(x => x.ID == ID);

tb.USERNAME = txtuser.Text;

tb.FIRST_NAME = txtfirst.Text;

tb.LAST_NAME = txtlast.Text;

tb.DATE_BIRTH = txtbirth.Text;

tb.AGE = txtage.Text;

tb.SEX = txtsex.Text;

db.SubmitChanges();

grduser.EditIndex = -1;

Bindgrid();

}

catch (Exception ex)

{

}

protected void grduser_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

try

{

int ID = Convert.ToInt32(grduser.DataKeys[e.RowIndex].Value);

LINQ_TABLE tb = new LINQ_TABLE();

tb = db.LINQ_TABLEs.First(x => x.ID == ID);

db.LINQ_TABLEs.DeleteOnSubmit(tb);

db.SubmitChanges();

Bindgrid();

}

catch (Exception ex)

{

}

protected void grduser_PageIndexChanging(object sender, GridViewPageEventArgs e)

{

grduser.PageIndex = e.NewPageIndex;

Bindgrid();

}

In VB

Dim db As New DataClassesDataContext()

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Bindgrid()

End If

End Sub

Private Sub Bindgrid()

Dim bind = From c In db.LINQ_TABLEs

grduser.DataSource = bind

grduser.DataBind()

End Sub

Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)

grduser.EditIndex = e.NewEditIndex

Bindgrid()

End Sub

Protected Sub grduser_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)

grduser.EditIndex = -1

Bindgrid()

End Sub

Protected Sub grduser_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)

Try

Dim row As GridViewRow = grduser.Rows(e.RowIndex)

Dim txtuser As TextBox = DirectCast(row.FindControl("txtuser"), TextBox)

Dim txtfirst As TextBox = DirectCast(row.FindControl("txtfirst"), TextBox)

Dim txtlast As TextBox = DirectCast(row.FindControl("txtlast"), TextBox)

Dim txtbirth As TextBox = DirectCast(row.FindControl("TextBox4"), TextBox)

Dim txtsex As TextBox = DirectCast(row.FindControl("TextBox5"), TextBox)

Dim txtage As TextBox = DirectCast(row.FindControl("txtage"), TextBox)

Dim ID As Integer = Convert.ToInt32(grduser.DataKeys(e.RowIndex).Value)

Dim tb As New LINQ_TABLE()

tb = db.LINQ_TABLEs.First(Function(x) x.ID = ID)

tb.USERNAME = txtuser.Text

tb.FIRST_NAME = txtfirst.Text

tb.LAST_NAME = txtlast.Text

tb.DATE_BIRTH = txtbirth.Text

tb.AGE = txtage.Text

tb.SEX = txtsex.Text

db.SubmitChanges()

grduser.EditIndex = -1

Bindgrid()

Catch ex As Exception

End Try

End Sub

Protected Sub grduser_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs)

Try

Dim ID As Integer = Convert.ToInt32(grduser.DataKeys(e.RowIndex).Value)

Dim tb As New LINQ_TABLE()

tb = db.LINQ_TABLEs.First(Function(x) x.ID = ID)

db.LINQ_TABLEs.DeleteOnSubmit(tb)

db.SubmitChanges()

Bindgrid()

Catch ex As Exception

End Try

End Sub

Protected Sub grduser_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

grduser.PageIndex = e.NewPageIndex

Bindgrid()

End Sub

Now run the project and check the result.

Wednesday, May 15, 2013

How to Bind, Edit, Update and Delete in Gridview Control using LINQ

Ø What is LINQ? Its advantage, disadvantage and types

Ø How to insert data into database using LINQ?

Ø How to Bind Gridview Data Control in LINQ?

Ø How to Bind Dropdownlist in Asp.net using LINQ?

1 comment:

Popular Posts

Labels