locked
using a temporary gridview RRS feed

  • Question

  • User-1246222823 posted

    Hello all,

    I would like to use a gridview to store temporary data. It will store the selected data and an insert query will perform to insert tempGrid content.

    How can i create it?

    Thank you for all help.

    Thursday, May 19, 2011 5:53 AM

Answers

  • User3866881 posted

    Hi:)

    If you want to do frequent inserting, deleting, updating things in a temporary table, and do a batch update. You can send email to maledong@qq.com, and I'll send your my sample codes within 3~4 days.

    Your email's title should be BatchInsert/Delete/Update Wanted.

    Thx

    Here's the codes:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CSExcelLikeGridView._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>CSExcelLikeGridView</title>
        <script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js" type="text/javascript">
        </script>
        <script type="text/javascript">

            //Function to read HidState (JSON) to keep the state color
            function ResetColors(color1, color2) {
                var contents = $(":hidden:last").val();

                // If not "[]", meaning something changed, reset colors.
                if (contents.toString() != "[]") {
                    // Convert to JSON object.
                    var objectc = eval(contents);

                    // To reset colors depending on whether it's changed
                    for (var i = 0; i < objectc.length; ++i) {
                        if (objectc[i].Color != '') {
                            $("td:eq(" + objectc[i].Index + ")").css("background-color", objectc[i].Color);
                        }

                        var checked = objectc[i].Deleted == "True";

                        if (parseInt(objectc[i].Index) % 2 == 0) {
                            $("td:eq(" + parseInt(objectc[i].Index - 2) + ")").find(":checkbox").attr("checked", checked);
                        }
                        else {
                            $("td:eq(" + parseInt(objectc[i].Index - 3) + ")").find(":checkbox").attr("checked", checked);
                        }

                        // If a checkbox checked, change the row color
                        if (checked) {
                            $("td:eq(" + objectc[i].Index + ")").parent().css("background-color", "red");
                        }
                    }
                }

            }

            //Validation for Save
            function SaveValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (Page_ClientValidate('Fill')) {
                    return confirm('Do you really want to save all these changes together?');
                }
                else {
                    alert("Attention! You cannot leave a name blank!");
                }
            }

            //Validation for Insert
            function InsertValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (!Page_ClientValidate('Insert')) {
                    alert("Attention! You cannot insert a blank name!");
                }
            }

            // Add dynamically events for all textboxes
            // except the footer one to turn the background color.
            function AddEvents() {

                var rowarray = $("tr");
                for (var i = 0; i < rowarray.length - 1; ++i) {
                    $(rowarray[i]).find(":text").change(function () {
                        $(this).parent().css("background", "blue");
                    });
                }

            }

            $(function () {

                //Keep the original color row for odd
                var color1 = $("tr:eq(1)").css("background-color").valueOf();
                var color2 = $("tr:eq(2)").css("background-color").valueOf();
                var headercolor = $("tr:first").css("background-color").valueOf();
                var footercolor = $("tr:last").css("background-color").valueOf();

                AddEvents();

                // Header checkbox's cascading effect:
                $("#chkAll").click(function () {

                    $(":checkbox").attr("checked", $(this).attr("checked"));

                    if ($(this).attr("checked")) {
                        $(":checkbox").parent().parent().css("background-color", "red");
                        //Reset the color of header
                        $("tr:first").css("background-color", headercolor);
                    }
                    else {
                        $("tr:odd").css("background-color", color1);
                        $("tr:even").css("background-color", color2);

                        //Reset the color of header and footer
                        $("tr:first").css("background-color", headercolor);
                        $("tr:last").css("background-color", footercolor);
                    }
                });

                //Single checkbox checked event
                $(":checkbox").click(function () {
                    if ($(this).attr("checked")) {
                        $(this).parent().parent().css("background-color", "red");
                    }
                    else {
                        if ($(this).parent().parent().index() % 2 == 0) {
                            $(this).parent().parent().css("background-color", color2);
                        }
                        else {
                            $(this).parent().parent().css("background-color", color1);
                        }
                    }

                    //Reset the header color
                    $("tr:first").css("background-color", headercolor);
                });

                ResetColors(color1, color2);
            })

        </script>
    </head>
    <body>
        <form id="form1" runat="server">
        <h1>
            Demo for Batching Actions</h1>
        <span style="color: Red">red row to be deleted</span>
        <br />
        <span style="color: green">green row to be added</span>
        <br />
        <span style="color: blue">blue cell to be modified</span>
        <br />
        <hr />
        <div>
            <asp:GridView ID="GridView1" runat="server" Width="70%" Height="50%" AutoGenerateColumns="False"
                CellPadding="4" ForeColor="#333333" GridLines="None" ShowFooter="True">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:TemplateField HeaderText="Delete State">
                        <HeaderTemplate>
                            <input id="chkAll" type="checkbox" />
                            Delete
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="chkDelete" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Id">
                        <ItemTemplate>
                            <%#Eval("Id") %>
                        </ItemTemplate>
                        <FooterTemplate>
                            Name:<asp:TextBox ID="tbNewName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="tbNewName"
                                ErrorMessage="You cannot insert a blank name!" ForeColor="#FFFF66" ValidationGroup="Insert"></asp:RequiredFieldValidator>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PersonName") %>'>
                            </asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="tbName"
                                ErrorMessage="You cannot leave a name blank!" ValidationGroup="Fill"></asp:RequiredFieldValidator>
                        </ItemTemplate>
                        <FooterTemplate>
                            Address:<asp:TextBox ID="tbNewAddress" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Address">
                        <ItemTemplate>
                            <asp:TextBox ID="tbAddress" runat="server" Text='<%#Eval("PersonAddress") %>'>
                            </asp:TextBox>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="btnAdd" runat="server" Text="Add a new row" OnClick="btnAdd_Click"
                                ValidationGroup="Insert" OnClientClick="InsertValidate()" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" HorizontalAlign="Center" VerticalAlign="Middle" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            </asp:GridView>
        </div>
        <asp:Button ID="btnSaveAll" runat="server" Height="30px" Text="Save All Changes"
            Width="149px" OnClick="btnSaveAll_Click" OnClientClick="SaveValidate()" ValidationGroup="Fill" />
        <asp:HiddenField ID="HidState" runat="server" Value="[]" />
        </form>
    </body>
    </html>

    /****************************** Module Header ******************************\
    * Module Name:    DBProcess.cs
    * Project:        CSExcelLikeGridView
    * Copyright (c) Microsoft Corporation
    *
    * This module is managing the Connection,adapter as well as datatable
    * instances.
    *
    * This source is subject to the Microsoft Public License.
    * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    * All other rights reserved.
    \*****************************************************************************/

    using System;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;
    using System.IO;
    using System.Runtime.Serialization.Formatters.Binary;

    namespace CSExcelLikeGridView
    {
        /// <summary>
        /// This is a class that manages the whole db's connection, create a
        /// memory-based datatable, maintaince the state and do a batch save
        /// things together in different functions.
        /// </summary>
        public sealed class DBProcess
        {
            private static SqlConnection conn = null;
            private static SqlDataAdapter adapter = null;
            private DataTable dt = null;

            /// <summary>
            /// This static constructor will read out the whole connect string
            /// from defined web.config. The connection and adapter are both
            /// pointing to the same db, so only create once.
            /// </summary>
            static DBProcess()
            {
                string constr = ConfigurationManager.ConnectionStrings["MyConn"]
                                                    .ConnectionString;
                conn = new SqlConnection(constr);
                string command = "select * from tb_personInfo";
                adapter = new SqlDataAdapter(command, conn);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                builder.GetDeleteCommand(true);
                builder.GetInsertCommand(true);
                builder.GetUpdateCommand(true);
            }

            /// <summary>
            /// This function will create a datatable from the real db to
            /// reload all data.
            /// </summary>
            public DataTable GetDataTable()
            {
                dt = new DataTable();
                adapter.Fill(dt);
                dt.Columns[0].AutoIncrement = true;
                dt.Columns[0].AutoIncrementStep = 1;
                dt.Columns[0].AutoIncrementSeed = dt.Rows.Count;
                return dt;
            }

            /// <summary>
            /// Update the DataTable and delete the serialized file.
            /// </summary>
            public void BatchSave(DataTable dt)
            {
                adapter.Update(dt);
            }
        }
    }

    /****************************** Module Header ******************************\
    * Module Name:    DBProcess.cs
    * Project:        CSExcelLikeGridView
    * Copyright (c) Microsoft Corporation
    *
    * This is a UI module which lets which lets you do a batch inserting, updating
    * as well as deleting.
    *
    * This source is subject to the Microsoft Public License.
    * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    * All other rights reserved.
    \*****************************************************************************/

    using System;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Text;

    namespace CSExcelLikeGridView
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DBProcess p = new DBProcess();
                    DataTable dt = p.GetDataTable();
                    ViewState["dt"] = dt;
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }

            /// <summary>
            /// This function will confirm the last modification
            /// and do a batch save.
            /// </summary>
            protected void btnSaveAll_Click(object sender, EventArgs e)
            {
                //Default the value is false, meaning that the db isn't saved
                bool flag = false;

                DBProcess p = new DBProcess();
                DataTable dt = ViewState["dt"] as DataTable;

                // Change states and do a batch update
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    if ((GridView1.Rows[i].FindControl("chkDelete") as CheckBox).Checked)
                    {
                        dt.Rows[i].Delete();
                        flag = true;
                    }
                    else
                    {
                        if (dt.Rows[i]["PersonName"].ToString()
                            != (GridView1.Rows[i].FindControl("tbName") as TextBox).Text)
                        {
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].BeginEdit();
                            }
                            dt.Rows[i]["PersonName"] =
                                (GridView1.Rows[i].FindControl("tbName") as TextBox).Text;
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].EndEdit();
                            }
                            flag = true;
                        }
                        if (dt.Rows[i]["PersonAddress"].ToString()
                            != (GridView1.Rows[i].FindControl("tbAddress") as TextBox).Text)
                        {
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].BeginEdit();
                            }
                            dt.Rows[i]["PersonAddress"] =
                                (GridView1.Rows[i].FindControl("tbAddress") as TextBox).Text;
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].EndEdit();
                            }
                            flag = true;
                        }
                    }
                }

                p.BatchSave(dt);

                // Save data into db, so no need to maintain the state colors
                HidState.Value = "[]";

                dt = p.GetDataTable();
                GridView1.DataSource = dt;
                GridView1.DataBind();

                if (flag)
                {
                    ClientScript.RegisterStartupScript
            (GetType(), "js", "alert('Save All Changes successfully!');", true);
                }

            }

            /// <summary>
            ///  This function processes with the following aspect:
            ///  Record every row and every cell's state into HidState.
            /// </summary>
            protected void btnAdd_Click(object sender, EventArgs e)
            {
                int i = 0;
                DBProcess p = new DBProcess();
                DataTable dt = ViewState["dt"] as DataTable;
                TextBox tb1 = null;
                TextBox tb2 = null;
                StringBuilder sbu = new StringBuilder();
                CheckBox chkbox = null;

                // To maintain the JSON state.
                sbu.Append("[");

                for (; i < dt.Rows.Count; ++i)
                {
                    // Loop the cells
                    for (int j = 2; j < GridView1.HeaderRow.Cells.Count; ++j)
                    {
                        tb1 = GridView1.Rows[i].FindControl("tbName") as TextBox;
                        tb2 = GridView1.Rows[i].FindControl("tbAddress") as TextBox;

                        sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count + j));

                        //Not added, meaning unchanged or added
                        if (dt.Rows[i].RowState !=DataRowState.Added)
                        {
                            // Decide whether the 1st cell in db changed or not
                            if (j == 2)
                            {
                                if (!tb1.Text.Equals(dt.Rows[i][j - 1, DataRowVersion.Original]))
                                {
                                    dt.Rows[i].BeginEdit();
                                    sbu.Append("','Color':'blue',");
                                    dt.Rows[i][j - 1] = tb1.Text;
                                }
                                else
                                {
                                    sbu.Append("','Color':'',");
                                }
                            }
                            else
                            {
                                // Decide whether the 2nd cell in db changed or not
                                if (!tb2.Text.Equals(dt.Rows[i][j - 1, DataRowVersion.Original]))
                                {
                                    dt.Rows[i].BeginEdit();
                                    sbu.Append("','Color':'blue',");
                                    dt.Rows[i][j - 1] = tb2.Text;
                                }
                                else
                                {
                                    sbu.Append("','Color':'',");
                                }
                            }
                            dt.Rows[i].EndEdit();
                        }

                        else
                        {
                            // Add row should be marked as green
                            if (dt.Rows[i].RowState == DataRowState.Added)
                            {
                                sbu.Append("','Color':'green',");
                            }
                            // Other rows should keep the original color
                            else
                            {
                                sbu.Append("','Color':'',");
                            }
                        }

                        // Keep the Delete Statement
                        chkbox = GridView1.Rows[i].FindControl("chkDelete") as CheckBox;
                        sbu.Append("'Deleted':'" + chkbox.Checked + "'},");
                    }
                }

                DataRow r = dt.NewRow();
                r["PersonName"] = (GridView1.FooterRow.FindControl("tbNewName") as TextBox).Text;
                r["PersonAddress"] = (GridView1.FooterRow.FindControl("tbNewAddress") as TextBox).Text;
                dt.Rows.Add(r);
                sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count
                                + 2) + "','Color':'green','Deleted':'false'},");
                sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count
                                + 3) + "','Color':'green','Deleted':'false'}");
                sbu.Append("]");
                HidState.Value = sbu.ToString();
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 21, 2011 4:37 AM

All replies

  • User860067021 posted

    Create a normal gridview and use a DataSet (or other) as datasource.

    Then, you only need to manipulate the dataset data and save it to database when you want.

    Check this article too http://blogs.sitepoint.com/net-shopping-cart-datatables/

    Thursday, May 19, 2011 6:06 AM
  • User-1696077569 posted

    Why don't you use SqlDataAdapter for insert/update/delete

     

    http://support.microsoft.com/kb/308055
    Thursday, May 19, 2011 6:09 AM
  • User3866881 posted

    Hi:)

    If you want to do frequent inserting, deleting, updating things in a temporary table, and do a batch update. You can send email to maledong@qq.com, and I'll send your my sample codes within 3~4 days.

    Your email's title should be BatchInsert/Delete/Update Wanted.

    Thx

    Here's the codes:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="CSExcelLikeGridView._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>CSExcelLikeGridView</title>
        <script src="http://ajax.microsoft.com/ajax/jquery/jquery-1.4.4.min.js" type="text/javascript">
        </script>
        <script type="text/javascript">

            //Function to read HidState (JSON) to keep the state color
            function ResetColors(color1, color2) {
                var contents = $(":hidden:last").val();

                // If not "[]", meaning something changed, reset colors.
                if (contents.toString() != "[]") {
                    // Convert to JSON object.
                    var objectc = eval(contents);

                    // To reset colors depending on whether it's changed
                    for (var i = 0; i < objectc.length; ++i) {
                        if (objectc[i].Color != '') {
                            $("td:eq(" + objectc[i].Index + ")").css("background-color", objectc[i].Color);
                        }

                        var checked = objectc[i].Deleted == "True";

                        if (parseInt(objectc[i].Index) % 2 == 0) {
                            $("td:eq(" + parseInt(objectc[i].Index - 2) + ")").find(":checkbox").attr("checked", checked);
                        }
                        else {
                            $("td:eq(" + parseInt(objectc[i].Index - 3) + ")").find(":checkbox").attr("checked", checked);
                        }

                        // If a checkbox checked, change the row color
                        if (checked) {
                            $("td:eq(" + objectc[i].Index + ")").parent().css("background-color", "red");
                        }
                    }
                }

            }

            //Validation for Save
            function SaveValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (Page_ClientValidate('Fill')) {
                    return confirm('Do you really want to save all these changes together?');
                }
                else {
                    alert("Attention! You cannot leave a name blank!");
                }
            }

            //Validation for Insert
            function InsertValidate() {
                //first tell whether you've missed "Name" to be ful-filled...

                if (!Page_ClientValidate('Insert')) {
                    alert("Attention! You cannot insert a blank name!");
                }
            }

            // Add dynamically events for all textboxes
            // except the footer one to turn the background color.
            function AddEvents() {

                var rowarray = $("tr");
                for (var i = 0; i < rowarray.length - 1; ++i) {
                    $(rowarray[i]).find(":text").change(function () {
                        $(this).parent().css("background", "blue");
                    });
                }

            }

            $(function () {

                //Keep the original color row for odd
                var color1 = $("tr:eq(1)").css("background-color").valueOf();
                var color2 = $("tr:eq(2)").css("background-color").valueOf();
                var headercolor = $("tr:first").css("background-color").valueOf();
                var footercolor = $("tr:last").css("background-color").valueOf();

                AddEvents();

                // Header checkbox's cascading effect:
                $("#chkAll").click(function () {

                    $(":checkbox").attr("checked", $(this).attr("checked"));

                    if ($(this).attr("checked")) {
                        $(":checkbox").parent().parent().css("background-color", "red");
                        //Reset the color of header
                        $("tr:first").css("background-color", headercolor);
                    }
                    else {
                        $("tr:odd").css("background-color", color1);
                        $("tr:even").css("background-color", color2);

                        //Reset the color of header and footer
                        $("tr:first").css("background-color", headercolor);
                        $("tr:last").css("background-color", footercolor);
                    }
                });

                //Single checkbox checked event
                $(":checkbox").click(function () {
                    if ($(this).attr("checked")) {
                        $(this).parent().parent().css("background-color", "red");
                    }
                    else {
                        if ($(this).parent().parent().index() % 2 == 0) {
                            $(this).parent().parent().css("background-color", color2);
                        }
                        else {
                            $(this).parent().parent().css("background-color", color1);
                        }
                    }

                    //Reset the header color
                    $("tr:first").css("background-color", headercolor);
                });

                ResetColors(color1, color2);
            })

        </script>
    </head>
    <body>
        <form id="form1" runat="server">
        <h1>
            Demo for Batching Actions</h1>
        <span style="color: Red">red row to be deleted</span>
        <br />
        <span style="color: green">green row to be added</span>
        <br />
        <span style="color: blue">blue cell to be modified</span>
        <br />
        <hr />
        <div>
            <asp:GridView ID="GridView1" runat="server" Width="70%" Height="50%" AutoGenerateColumns="False"
                CellPadding="4" ForeColor="#333333" GridLines="None" ShowFooter="True">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:TemplateField HeaderText="Delete State">
                        <HeaderTemplate>
                            <input id="chkAll" type="checkbox" />
                            Delete
                        </HeaderTemplate>
                        <ItemTemplate>
                            <asp:CheckBox ID="chkDelete" runat="server" />
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Id">
                        <ItemTemplate>
                            <%#Eval("Id") %>
                        </ItemTemplate>
                        <FooterTemplate>
                            Name:<asp:TextBox ID="tbNewName" runat="server"></asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="tbNewName"
                                ErrorMessage="You cannot insert a blank name!" ForeColor="#FFFF66" ValidationGroup="Insert"></asp:RequiredFieldValidator>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Name">
                        <ItemTemplate>
                            <asp:TextBox ID="tbName" runat="server" Text='<%#Eval("PersonName") %>'>
                            </asp:TextBox>
                            <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="tbName"
                                ErrorMessage="You cannot leave a name blank!" ValidationGroup="Fill"></asp:RequiredFieldValidator>
                        </ItemTemplate>
                        <FooterTemplate>
                            Address:<asp:TextBox ID="tbNewAddress" runat="server"></asp:TextBox>
                        </FooterTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Address">
                        <ItemTemplate>
                            <asp:TextBox ID="tbAddress" runat="server" Text='<%#Eval("PersonAddress") %>'>
                            </asp:TextBox>
                        </ItemTemplate>
                        <FooterTemplate>
                            <asp:Button ID="btnAdd" runat="server" Text="Add a new row" OnClick="btnAdd_Click"
                                ValidationGroup="Insert" OnClientClick="InsertValidate()" />
                        </FooterTemplate>
                    </asp:TemplateField>
                </Columns>
                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"
                    VerticalAlign="Middle" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" HorizontalAlign="Center" VerticalAlign="Middle" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
            </asp:GridView>
        </div>
        <asp:Button ID="btnSaveAll" runat="server" Height="30px" Text="Save All Changes"
            Width="149px" OnClick="btnSaveAll_Click" OnClientClick="SaveValidate()" ValidationGroup="Fill" />
        <asp:HiddenField ID="HidState" runat="server" Value="[]" />
        </form>
    </body>
    </html>

    /****************************** Module Header ******************************\
    * Module Name:    DBProcess.cs
    * Project:        CSExcelLikeGridView
    * Copyright (c) Microsoft Corporation
    *
    * This module is managing the Connection,adapter as well as datatable
    * instances.
    *
    * This source is subject to the Microsoft Public License.
    * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    * All other rights reserved.
    \*****************************************************************************/

    using System;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Data;
    using System.IO;
    using System.Runtime.Serialization.Formatters.Binary;

    namespace CSExcelLikeGridView
    {
        /// <summary>
        /// This is a class that manages the whole db's connection, create a
        /// memory-based datatable, maintaince the state and do a batch save
        /// things together in different functions.
        /// </summary>
        public sealed class DBProcess
        {
            private static SqlConnection conn = null;
            private static SqlDataAdapter adapter = null;
            private DataTable dt = null;

            /// <summary>
            /// This static constructor will read out the whole connect string
            /// from defined web.config. The connection and adapter are both
            /// pointing to the same db, so only create once.
            /// </summary>
            static DBProcess()
            {
                string constr = ConfigurationManager.ConnectionStrings["MyConn"]
                                                    .ConnectionString;
                conn = new SqlConnection(constr);
                string command = "select * from tb_personInfo";
                adapter = new SqlDataAdapter(command, conn);
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                builder.GetDeleteCommand(true);
                builder.GetInsertCommand(true);
                builder.GetUpdateCommand(true);
            }

            /// <summary>
            /// This function will create a datatable from the real db to
            /// reload all data.
            /// </summary>
            public DataTable GetDataTable()
            {
                dt = new DataTable();
                adapter.Fill(dt);
                dt.Columns[0].AutoIncrement = true;
                dt.Columns[0].AutoIncrementStep = 1;
                dt.Columns[0].AutoIncrementSeed = dt.Rows.Count;
                return dt;
            }

            /// <summary>
            /// Update the DataTable and delete the serialized file.
            /// </summary>
            public void BatchSave(DataTable dt)
            {
                adapter.Update(dt);
            }
        }
    }

    /****************************** Module Header ******************************\
    * Module Name:    DBProcess.cs
    * Project:        CSExcelLikeGridView
    * Copyright (c) Microsoft Corporation
    *
    * This is a UI module which lets which lets you do a batch inserting, updating
    * as well as deleting.
    *
    * This source is subject to the Microsoft Public License.
    * See http://www.microsoft.com/opensource/licenses.mspx#Ms-PL.
    * All other rights reserved.
    \*****************************************************************************/

    using System;
    using System.Web.UI.WebControls;
    using System.Data;
    using System.Text;

    namespace CSExcelLikeGridView
    {
        public partial class _Default : System.Web.UI.Page
        {
            protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    DBProcess p = new DBProcess();
                    DataTable dt = p.GetDataTable();
                    ViewState["dt"] = dt;
                    GridView1.DataSource = dt;
                    GridView1.DataBind();
                }
            }

            /// <summary>
            /// This function will confirm the last modification
            /// and do a batch save.
            /// </summary>
            protected void btnSaveAll_Click(object sender, EventArgs e)
            {
                //Default the value is false, meaning that the db isn't saved
                bool flag = false;

                DBProcess p = new DBProcess();
                DataTable dt = ViewState["dt"] as DataTable;

                // Change states and do a batch update
                for (int i = 0; i < GridView1.Rows.Count; i++)
                {
                    if ((GridView1.Rows[i].FindControl("chkDelete") as CheckBox).Checked)
                    {
                        dt.Rows[i].Delete();
                        flag = true;
                    }
                    else
                    {
                        if (dt.Rows[i]["PersonName"].ToString()
                            != (GridView1.Rows[i].FindControl("tbName") as TextBox).Text)
                        {
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].BeginEdit();
                            }
                            dt.Rows[i]["PersonName"] =
                                (GridView1.Rows[i].FindControl("tbName") as TextBox).Text;
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].EndEdit();
                            }
                            flag = true;
                        }
                        if (dt.Rows[i]["PersonAddress"].ToString()
                            != (GridView1.Rows[i].FindControl("tbAddress") as TextBox).Text)
                        {
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].BeginEdit();
                            }
                            dt.Rows[i]["PersonAddress"] =
                                (GridView1.Rows[i].FindControl("tbAddress") as TextBox).Text;
                            if (dt.Rows[i].RowState == DataRowState.Unchanged)
                            {
                                dt.Rows[i].EndEdit();
                            }
                            flag = true;
                        }
                    }
                }

                p.BatchSave(dt);

                // Save data into db, so no need to maintain the state colors
                HidState.Value = "[]";

                dt = p.GetDataTable();
                GridView1.DataSource = dt;
                GridView1.DataBind();

                if (flag)
                {
                    ClientScript.RegisterStartupScript
            (GetType(), "js", "alert('Save All Changes successfully!');", true);
                }

            }

            /// <summary>
            ///  This function processes with the following aspect:
            ///  Record every row and every cell's state into HidState.
            /// </summary>
            protected void btnAdd_Click(object sender, EventArgs e)
            {
                int i = 0;
                DBProcess p = new DBProcess();
                DataTable dt = ViewState["dt"] as DataTable;
                TextBox tb1 = null;
                TextBox tb2 = null;
                StringBuilder sbu = new StringBuilder();
                CheckBox chkbox = null;

                // To maintain the JSON state.
                sbu.Append("[");

                for (; i < dt.Rows.Count; ++i)
                {
                    // Loop the cells
                    for (int j = 2; j < GridView1.HeaderRow.Cells.Count; ++j)
                    {
                        tb1 = GridView1.Rows[i].FindControl("tbName") as TextBox;
                        tb2 = GridView1.Rows[i].FindControl("tbAddress") as TextBox;

                        sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count + j));

                        //Not added, meaning unchanged or added
                        if (dt.Rows[i].RowState !=DataRowState.Added)
                        {
                            // Decide whether the 1st cell in db changed or not
                            if (j == 2)
                            {
                                if (!tb1.Text.Equals(dt.Rows[i][j - 1, DataRowVersion.Original]))
                                {
                                    dt.Rows[i].BeginEdit();
                                    sbu.Append("','Color':'blue',");
                                    dt.Rows[i][j - 1] = tb1.Text;
                                }
                                else
                                {
                                    sbu.Append("','Color':'',");
                                }
                            }
                            else
                            {
                                // Decide whether the 2nd cell in db changed or not
                                if (!tb2.Text.Equals(dt.Rows[i][j - 1, DataRowVersion.Original]))
                                {
                                    dt.Rows[i].BeginEdit();
                                    sbu.Append("','Color':'blue',");
                                    dt.Rows[i][j - 1] = tb2.Text;
                                }
                                else
                                {
                                    sbu.Append("','Color':'',");
                                }
                            }
                            dt.Rows[i].EndEdit();
                        }

                        else
                        {
                            // Add row should be marked as green
                            if (dt.Rows[i].RowState == DataRowState.Added)
                            {
                                sbu.Append("','Color':'green',");
                            }
                            // Other rows should keep the original color
                            else
                            {
                                sbu.Append("','Color':'',");
                            }
                        }

                        // Keep the Delete Statement
                        chkbox = GridView1.Rows[i].FindControl("chkDelete") as CheckBox;
                        sbu.Append("'Deleted':'" + chkbox.Checked + "'},");
                    }
                }

                DataRow r = dt.NewRow();
                r["PersonName"] = (GridView1.FooterRow.FindControl("tbNewName") as TextBox).Text;
                r["PersonAddress"] = (GridView1.FooterRow.FindControl("tbNewAddress") as TextBox).Text;
                dt.Rows.Add(r);
                sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count
                                + 2) + "','Color':'green','Deleted':'false'},");
                sbu.Append("{'Index':'" + (i * GridView1.HeaderRow.Cells.Count
                                + 3) + "','Color':'green','Deleted':'false'}");
                sbu.Append("]");
                HidState.Value = sbu.ToString();
                GridView1.DataSource = dt;
                GridView1.DataBind();
            }
        }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 21, 2011 4:37 AM