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



click to Read More

Select Multipal rows in GridView




Select and Delete Multiple Rows in Gridview control

How to Select and delete Multiple Rows of GridView using CheckBox

GridView Control allow to select only one row at a time but But a little logic is required to select more than one row or Multiple rows in GridView control. if we require to select Multiple row and delete them in a single click than we can do this by writing a little code.

In my example I am using CheckBox control of asp.net . A CheckBox control is added in every row of grid view ( using TemplateField) and any GridView row can be selected by selecting CheckBox control of that particular row.




Following sqlserver table (tbemp) is used in the example


Here is code for .aspx page

<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" GridLines="None" BackColor="White" BorderColor="White" BorderStyle="Ridge" BorderWidth="2px" CellSpacing="1">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="cb" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp NO.">
<ItemTemplate>
<asp:Label ID="lb1" Text='<%#Eval("eno") %>' runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp Name">
<ItemTemplate>
<%#Eval("ename") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp Address">
<ItemTemplate >
<%#Eval("eadd") %>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Emp Salary">
<ItemTemplate>
<%#Eval("esal") %>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#C6C3C6" ForeColor="Black" />
<RowStyle BackColor="#DEDFDE" ForeColor="Black" />
<SelectedRowStyle BackColor="#9471DE" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#C6C3C6" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#E7E7FF" />
</asp:GridView>

</div>
<br />
<asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton1_Click">Select All</asp:LinkButton>
 |
<asp:LinkButton ID="LinkButton2" runat="server" OnClick="LinkButton2_Click">Deselct All</asp:LinkButton>
 |
<asp:LinkButton ID="LinkButton3" runat="server" OnClick="LinkButton3_Click">Delete</asp:LinkButton>
<br />
<asp:Label ID="Label1" runat="server"></asp:Label>

Code for .cs file (Replace the Page_Load event with following code)


protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
gridbind();
}


//method to bind GridView control to database records

private void gridbind()
{
SqlDataAdapter adp = new SqlDataAdapter("Select * from tbemp", "server=tiger;database=database1;uid=sa");
DataSet ds = new DataSet();
adp.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}

//Event to select all rows in GridView at one click

protected void LinkButton1_Click(object sender, EventArgs e)
{
CheckBox cb;
for (int i = 0; i < GridView1.Rows.Count; i++) 


cb = ((CheckBox)(GridView1.Rows[i].FindControl("cb"))); 
if (cb.Checked == false) 
cb.Checked = true; 

}
//Event to De-select all rows in GridView at one click

protected void LinkButton2_Click(object sender, EventArgs e)
{
CheckBox cb;
for (int i = 0; i < GridView1.Rows.Count; i++) 


cb = ((CheckBox)(GridView1.Rows[i].FindControl("cb"))); 
if (cb.Checked) 
cb.Checked = false; 

}
//Button Clicked Event to delete selected rows form gridview and database

protected void LinkButton3_Click(object sender, EventArgs e)
{
CheckBox cb;
String d = "";
for (int i = 0; i < GridView1.Rows.Count; i++) 

 { 
cb = ((CheckBox)(GridView1.Rows[i].FindControl("cb")));
if (cb.Checked) 
d +=((Label)( GridView1.Rows[i].FindControl("lb1"))).Text + ","; 

d = d.Substring(0, d.Length - 1); 
SqlConnection con = new SqlConnection("Server=tiger;database=database1;uid=sa"); 
con.Open();
SqlCommand cmd=new SqlCommand("delete from tbemp where eno in("+d.ToString()+")",con); cmd.ExecuteNonQuery(); 
gridbind(); 
}

In LinkButton3_Click event first we loop through each row of gridview and and then tested if they are selected (CheckBox checked) or not, the ‘Emp ID’ of each selected row is concatinated in a string variable named ‘d’ and passed as parameter in delete query “delete from tbemp where eno in("+d.ToString()+")"

Download Source Code


click to Read More