Search Asp.Net Here

Update Delete in Database using Linq to Sql in Asp.Net GridView




Update Delete data using Linq to Sql From Asp.net GridView Control


What is Linq to Sql in .Net Framework 3.5

LINQ to SQL is a new feature in .NET Framework version 3.5 that provides a way to manage relational data as objects. In LINQ to SQL, the  relational database is mapped to an object model for the developer. At run time  LINQ to SQL translates Language Integrate Queries into SQL  sends them to the database for the execution. When  database returns the result, LINQ to SQL convert them back to objects so that developer can work in his  programming language.

Edit Data In GridView using Linq to Sql

In this example I am showing how to update and delete data in database using Linq to Sql Feature of .Net Framework 3.5. GridView Control is used to Display and Edit the data in Database using Linq to Sql. Source Code of the example is also attatched with this post.

Nortwind Database’s Tables name Category and Products are used in this Example

Modeling Database using Linq to Sql



Go to
Website-> Add New Item-> Select Linq to Sql Classes


Name this class Northwind.dbml



Visual Studio Automatically opens the designer of norhtwind.dbml class.

Now open solution explorer and drag drop Category and Products table of northwind database














Linq to sql will automatically gets the relation of the tables draged in it’s designer window.

Drag drop a DropDownList, button and GridView control to your web page or the code is given below.
<div>

&nbsp;

        Select Category&nbsp;

        <asp:DropDownList ID="DropDownList1" runat="server">

        </asp:DropDownList>

        &nbsp;&nbsp;

        <asp:Button ID="Button1" runat="server" onclick="Button1_Click"

            Text="Show Products" />

        <br />

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

            CellPadding="4" ForeColor="#333333" onrowediting="GridView1_RowEditing"

            onrowcancelingedit="GridView1_RowCancelingEdit"

            onrowupdating="GridView1_RowUpdating" Width="776px">

            <RowStyle BackColor="#EFF3FB" />

        <Columns>

            <asp:TemplateField HeaderText="Product ID">

                <ItemTemplate>

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

                </ItemTemplate>

                <EditItemTemplate>

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

                </EditItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Product Name">

                <ItemTemplate>

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

                </ItemTemplate>

                <EditItemTemplate>

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

                </EditItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Unit Price">

                <ItemTemplate>

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

                </ItemTemplate>

                 <EditItemTemplate>

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

                </EditItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="Units In Stock">

                <ItemTemplate>

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

                </ItemTemplate>

                 <EditItemTemplate>

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

                </EditItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="">

                <ItemTemplate>

        <asp:LinkButton ID="edit" runat="server" Text="Edit" CommandName="edit"></asp:LinkButton>

                </ItemTemplate>

                 <EditItemTemplate>

                     <asp:LinkButton ID="update" runat="server" Text="Update" CommandName="update"

                         style="color: #FFFFFF"></asp:LinkButton><br />

                     <asp:LinkButton ID="cancel" runat="server" Text="Cancel" CommandName="cancel"

                         style="color: #FFFFFF"></asp:LinkButton>

                </EditItemTemplate>

            </asp:TemplateField>

        </Columns>

            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />

            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />

            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />

            <EditRowStyle BackColor="#2461BF" />

            <AlternatingRowStyle BackColor="White" />

        </asp:GridView>

    </div>




Following namespaces are used in this example

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;


Following Method is used to bind the DropDownList control with the category table of northwind database .

  private void get_categories()

    {

        NorthwindDataContext
db = new NorthwindDataContext();

        var cat
= from p in
db.Categories select p;

        DropDownList1.DataSource = cat.ToList();

        DropDownList1.DataValueField = "CategoryID";

        DropDownList1.DataTextField = "CategoryName";

        DropDownList1.DataBind();

    }

Call the above mehtod in the page load event of web page

    protected void Page_Load(object
sender, EventArgs e)

    {

        if
(Page.IsPostBack == false)

            get_categories();

    }

Binding the gridview control with products of selected category in the DropDownList

    protected void Button1_Click(object
sender, EventArgs e)

    {

        List<Product> prd = grid_bind();

        GridView1.DataSource = prd;

        GridView1.DataBind();
}


The grid_bind() method is used to get all the products of the selected category from the Product table of the databae using Linq to sql.

    private List<Product>
grid_bind()

    {

        int
catid = Convert.ToInt32(DropDownList1.SelectedValue);

        NorthwindDataContext
db = new NorthwindDataContext();

        var
products = from p in
db.Products where p.CategoryID == catid select p;

        List<Product> prd = products.ToList();

        return
prd;

    }

    protected void GridView1_RowEditing(object
sender, GridViewEditEventArgs e)

    {

        GridView1.EditIndex = e.NewEditIndex;

        GridView1.DataSource = grid_bind();

        GridView1.DataBind();

    }

    protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)

    {

        GridView1.EditIndex = -1;

        GridView1.DataSource = grid_bind();

        GridView1.DataBind();

    }

    Updating the Selected Record in Database (Product table) using Linq to Sql From GridView

protected void
GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

    {

        int
prdid,prdinstock;

        string
prdname;

        decimal
prdunitprice;



        prdname = ((TextBox)(GridView1.Rows[e.RowIndex].FindControl("ename"))).Text;

        prdunitprice =Convert.ToDecimal(
((TextBox)(GridView1.Rows[e.RowIndex].FindControl("eprice"))).Text);

        prdinstock = Convert.ToInt32(((TextBox)(GridView1.Rows[e.RowIndex].FindControl("estock"))).Text);



        //Product prd
= dc.Products.Single(p => p.ProductID == prdid);

        //or

        Product
prd = (from p in
dc.Products where p.ProductID == prdid select p).Single();

        prd.ProductName = prdname;

        prd.UnitPrice = prdunitprice;

        prd.UnitsInStock =Convert.ToInt16(prdinstock);

        //updating the
database with the changes

dc.SubmitChanges();



        GridView1.DataSource = grid_bind();

        GridView1.DataBind();

    }

Deleting A product from Product table using Linq to Sql in Asp.Net.



  protected void GridView1_RowDeleting(object
sender, GridViewDeleteEventArgs e)

    {

        int
prdid = Convert.ToInt32(((Label)(GridView1.Rows[e.RowIndex].FindControl("id"))).Text);

        NorthwindDataContext db = new NorthwindDataContext();

        Product
prd = (from p in
db.Products where p.ProductID == prdid select p).Single();

        db.Products.DeleteOnSubmit(prd);

        db.SubmitChanges();

        List<Product> product = grid_bind();

        GridView1.DataSource = product;

        GridView1.DataBind();



Comments :

3 comments to “Update Delete in Database using Linq to Sql in Asp.Net GridView”
Anonymous said...
on 

Err DropDownList1.DataSource = cat.ToList();

ANIL BABU Mandla said...
on 

Use same method not use DropDown list control
display two tables data in a single Gridview And also perform Update,delete operations,
any methos plz help me the complete simple example

Anonymous said...
on 

ERRPR: prdid value is not assigned........

Post a Comment