Monday 22 September 2014

Bulk Edit Update Multiple Rows in ASP.Net GridView using CheckBoxes

In this article I will explain how to edit and update multiple rows in ASP.Net GridView using CheckBoxes i.e. the Rows which are checked will become editable and user can update multiple rows on one single Update button click.


For this sample to work you will need to download the Microsoft Northwind database using the following link

Desingner Source Code :
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CS.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>
    <style type = "text/css">
    input[type=text], select{background-color:#FFDFD2; border:1px solid #ccc}
    </style>
</head>
<body style = "font-family:Arial;font-size:10pt">
    <form id="form1" runat="server">
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" OnRowDataBound = "OnRowDataBound" DataKeyNames = "CustomerId">
    <Columns>
        <asp:TemplateField>
            <HeaderTemplate>
                <asp:CheckBox ID = "chkAll" runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
            </HeaderTemplate>
            <ItemTemplate>
                <asp:CheckBox runat="server" AutoPostBack="true" OnCheckedChanged="OnCheckedChanged" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Contact Name" ItemStyle-Width = "150">
            <ItemTemplate>
                <asp:Label runat="server" Text='<%# Eval("ContactName") %>'></asp:Label>
                <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("ContactName") %>' Visible="false"></asp:TextBox>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField HeaderText="Country" ItemStyle-Width = "150">
            <ItemTemplate>
                <asp:Label ID = "lblCountry" runat="server" Text='<%# Eval("Country") %>'></asp:Label>
                <asp:DropDownList ID="ddlCountries" runat="server" Visible = "false">
                </asp:DropDownList>
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick = "Update" Visible = "false"/>
    </form>
</body>
</html>

 Code Behind :
using System;
using System.Web.UI.WebControls;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            this.BindGrid();
        }
    }
    private void BindGrid()
    {
        SqlCommand cmd = new SqlCommand("SELECT top 10 CustomerId, ContactName, Country FROM Customers");
        gvCustomers.DataSource = this.ExecuteQuery(cmd, "SELECT");
        gvCustomers.DataBind();
    }
    private DataTable ExecuteQuery(SqlCommand cmd, string action)
    {
        string conString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            cmd.Connection = con;
            switch (action)
            {
                case "SELECT":
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            return dt;
                        }
                    }
                case "UPDATE":
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                    break;
            }
            return null;
        }
    }

    protected void Update(object sender, EventArgs e)
    {
        foreach (GridViewRow row in gvCustomers.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
                if (isChecked)
                {
                    SqlCommand cmd = new SqlCommand("UPDATE Customers SET ContactName = @ContactName, Country = @Country WHERE CustomerId = @CustomerId");
                    cmd.Parameters.AddWithValue("@ContactName", row.Cells[1].Controls.OfType<TextBox>().FirstOrDefault().Text);
                    cmd.Parameters.AddWithValue("@Country", row.Cells[2].Controls.OfType<DropDownList>().FirstOrDefault().SelectedItem.Value);
                    cmd.Parameters.AddWithValue("@CustomerId", gvCustomers.DataKeys[row.RowIndex].Value);
                    this.ExecuteQuery(cmd, "UPDATE");
                }
            }
        }
        btnUpdate.Visible = false;
        this.BindGrid();
    }
    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            SqlCommand cmd = new SqlCommand("SELECT DISTINCT(Country) FROM Customers");
            DropDownList ddlCountries = (e.Row.FindControl("ddlCountries") as DropDownList);
            ddlCountries.DataSource = this.ExecuteQuery(cmd, "SELECT");
            ddlCountries.DataTextField = "Country";
            ddlCountries.DataValueField = "Country";
            ddlCountries.DataBind();
            string country = (e.Row.FindControl("lblCountry") as Label).Text;
            ddlCountries.Items.FindByValue(country).Selected = true;
        }
    }
    protected void OnCheckedChanged(object sender, EventArgs e)
    {
        bool isUpdateVisible = false;
        CheckBox chk = (sender as CheckBox);
        if (chk.ID == "chkAll")
        {
            foreach (GridViewRow row in gvCustomers.Rows)
            {
                if (row.RowType == DataControlRowType.DataRow)
                {
                    row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked = chk.Checked;
                }
            }
        }
        CheckBox chkAll = (gvCustomers.HeaderRow.FindControl("chkAll") as CheckBox);
        chkAll.Checked = true;
        foreach (GridViewRow row in gvCustomers.Rows)
        {
            if (row.RowType == DataControlRowType.DataRow)
            {
                bool isChecked = row.Cells[0].Controls.OfType<CheckBox>().FirstOrDefault().Checked;
                for (int i = 1; i < row.Cells.Count; i++)
                {
                    row.Cells[i].Controls.OfType<Label>().FirstOrDefault().Visible = !isChecked;
                    if (row.Cells[i].Controls.OfType<TextBox>().ToList().Count > 0)
                    {
                        row.Cells[i].Controls.OfType<TextBox>().FirstOrDefault().Visible = isChecked;
                    }
                    if (row.Cells[i].Controls.OfType<DropDownList>().ToList().Count > 0)
                    {
                        row.Cells[i].Controls.OfType<DropDownList>().FirstOrDefault().Visible = isChecked;
                    }
                    if (isChecked && !isUpdateVisible)
                    {
                        isUpdateVisible = true;
                    }
                    if (!isChecked )
                    {
                        chkAll.Checked = false;
                    }
                }
            }
        }
        btnUpdate.Visible = isUpdateVisible;
    }
}

Download Here


Ref-http://msdn.microsoft.com/en-us/library/aa992036.aspx

No comments:

Post a Comment