[ASP.net WebForm] GridView/ListView仿Excel大量更新

2012-03-15

8390
0
ASP.net WebForm
2012-03-20

雖然MSDN已有範例：逐步解說：對繫結至 GridView Web 伺服器控制項的資料列執行大量更新

mis2000Lab老師也有對MSDN範例的說明：[MSDN][轉貼] GridView "批次"執行更新與刪除、執行 "大量"更新更新與刪除

不過實務上我不會這樣做XD

太麻煩了，而且把DataTable存進ViewState或Session的做法可能導致網頁回應慢

這邊提供另一個演算法：

Step 1.

把GridView放到畫面上並做資料繫結，把想要更新的資料行轉成TemplateField，再把ItemTempate裡的控制項換成使用者可輸入的TextBox

Step 2.

在GridView外面放一個Button，Button Click事件裡去走訪GridView的每個GridViewRow

Step 3.

從GridViewRow中去抓出畫面上該列的控制項，如果抓得到的話就直接更新此筆資料

Sample Code:

先塞DB資料

準備好GridView畫面

(可先藉助SqlDataSource幫助GridView資料繫結好畫面後，再把SqlDataSource控制項刪除還有GridView的DataSourceID屬性也刪除

待會用手寫ADO.net語法做Update更新比較有彈性)

GridView編輯資料行

把主鍵資料行以外都轉換成TemplateField

再把GridView裡的ItemTemplate(Select語法對應的樣版)裡要修改的欄位從Label控制項換成TextBox，這樣使用者才能輸入值

並把原本的Bind改成Eval(單向繫結)即可

EditTemplate本範例不會用到所以可以刪除，畫面看起來會比較乾淨

完成的設計畫面如下：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MSDN.aspx.cs" Inherits="MSDN"  Debug="true"%>

<!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>
</head>
<body>
    <form id="form1" runat="server">
    
   
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        DataKeyNames="id" 
        >
        <Columns>
            <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" 
                ReadOnly="True" SortExpression="id" />
            <asp:TemplateField HeaderText="name" SortExpression="name">
                <ItemTemplate>
                    <asp:TextBox ID="TextBoxName" runat="server" Text='<%# Eval("name") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="title" SortExpression="title">
              
                <ItemTemplate>
                    <asp:TextBox ID="TextBoxTitle" runat="server" Text='<%# Eval("title") %>' />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="company" SortExpression="company">
                <ItemTemplate>
                    <asp:TextBox ID="TextBoxCompany" runat="server" Text='<%# Eval("company") %>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="Button1" runat="server" Text="全部更新" onclick="Button1_Click" />
    </form>
</body>
</html>

接著撰寫Button1_Click的Update更新：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Data.SqlClient;
using System.Data;

public partial class MSDN : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)//Get Method
        {
            GridView1.DataSource = this.selectTable();
            GridView1.DataBind();
           
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        foreach (GridViewRow row in GridView1.Rows)//一列一列地走訪GridView
        {
             
            if (row.RowType==DataControlRowType.DataRow)//是DataRow(資料繫結的列)才要往下做
            {
                //抓出各欄位的控制項
                TextBox TextBoxName = (TextBox)row.FindControl("TextBoxName");
                TextBox TextBoxTitle = (TextBox)row.FindControl("TextBoxTitle");
                TextBox TextBoxCompany = (TextBox)row.FindControl("TextBoxCompany");
                //抓出主鍵值
                string id = row.Cells[0].Text;
                //Update更新此列
                this.updateTable(id, TextBoxName.Text, TextBoxTitle.Text, TextBoxCompany.Text);

                //重新資料繫結，畫面上才會是最新值
                GridView1.DataSource = this.selectTable();
                GridView1.DataBind();
            }
        }

    }

    //連線字串
    protected string Conn_Str = WebConfigurationManager.ConnectionStrings["NorthwindChineseConnectionString"].ConnectionString;
    private void updateTable(string id,string name,string title,string company)
    {
      SqlConnection conn=new SqlConnection(Conn_Str);
        string sql  =@"Update tb_test 
                       Set name=@name,
                       title=@title,
                       company=@company
                       Where id=@id";
        SqlCommand cmd=new SqlCommand(sql,conn);
        cmd.Parameters.AddWithValue("@name",name);
        cmd.Parameters.AddWithValue("@title",title);
        cmd.Parameters.AddWithValue("@company",company);
        cmd.Parameters.AddWithValue("@id",id);
        conn.Open();
        //執行
        cmd.ExecuteNonQuery();
        conn.Close();
    
    }


    private DataTable selectTable()
    {
        
        SqlConnection conn = new SqlConnection(Conn_Str);
        string sql = @"Select id,name,title,company From tb_test Order by id ASC ";
        SqlCommand cmd = new SqlCommand(sql, conn);
        DataTable dt = new DataTable();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

        return dt;
    }


  
}

這樣就完成了

執行結果：

一開始的畫面

把第二筆資料改成

按「全部更新」

用SSMS打開Table看，值確實更新了

ListView也是相同邏輯，只不過把Row改成Item

以下是ListView範例(增加”新增Insert””刪除Delete”功能，為了方便撈資料，所以和SqlDataSource做資料繫結)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListViewDemo.aspx.cs" Inherits="ListViewDemo" Debug="true" %>

<!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>
</head>
<body>
    <form id="form1" runat="server">
   
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:NorthwindChineseConnectionString %>" 
        DeleteCommand="DELETE FROM [tb_test] WHERE [id] = @id" 
        InsertCommand="INSERT INTO [tb_test] ([name], [title], [company]) VALUES (@name, @title, @company)" 
        SelectCommand="SELECT [id], [name], [title], [company] FROM [tb_test]" 
        UpdateCommand="UPDATE [tb_test] SET [name] = @name, [title] = @title, [company] = @company WHERE [id] = @id">
        <DeleteParameters>
            <asp:Parameter Name="id" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="title" Type="String" />
            <asp:Parameter Name="company" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="name" Type="String" />
            <asp:Parameter Name="title" Type="String" />
            <asp:Parameter Name="company" Type="String" />
            <asp:Parameter Name="id" Type="Int32" />
        </UpdateParameters>
    </asp:SqlDataSource>
    <br />
    <asp:ListView ID="ListView1" runat="server" DataKeyNames="id" 
        DataSourceID="SqlDataSource1" InsertItemPosition="LastItem">
     
        
        <EmptyDataTemplate>
            <table runat="server" style="">
                <tr>
                    <td>
                        無資料。</td>
                </tr>
            </table>
        </EmptyDataTemplate>
        <InsertItemTemplate>
            <tr style="">
                <td>
                    <asp:Button ID="InsertButton" runat="server" CommandName="Insert" Text="插入" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel" Text="清除" />
                </td>
                <td>
                    &nbsp;</td>
                <td>
                    <asp:TextBox ID="nameTextBox" runat="server" Text='<%# Bind("name") %>' />
                </td>
                <td>
                    <asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>' />
                </td>
                <td>
                    <asp:TextBox ID="companyTextBox" runat="server" Text='<%# Bind("company") %>' />
                </td>
            </tr>
        </InsertItemTemplate>
        <ItemTemplate>
            <tr style="">
                <td>
                    <asp:Button ID="DeleteButton" runat="server" CommandName="Delete" Text="刪除" />
                </td>
                <td>
                    <asp:Label ID="idLabel" runat="server" Text='<%# Eval("id") %>' />
                </td>
                <td>
                    <asp:TextBox ID="txt_name" runat="server" Text='<%# Eval("name") %>' />
                </td>
                <td>
                    <asp:TextBox ID="txt_title" runat="server" Text='<%# Eval("title") %>' />
                </td>
                <td>
                    <asp:TextBox ID="txt_company" runat="server" Text='<%# Eval("company") %>' />
                </td>
            </tr>
        </ItemTemplate>
        <LayoutTemplate>
         
                        <table ID="itemPlaceholderContainer" runat="server" border="1" style="">
                            <tr runat="server" style="">
                                <th runat="server">
                                </th>
                                <th runat="server">
                                    id</th>
                                <th runat="server">
                                    name</th>
                                <th runat="server">
                                    title</th>
                                <th runat="server">
                                    company</th>
                            </tr>
                            <tr ID="itemPlaceholder" runat="server">
                            </tr>
                </table>
        </LayoutTemplate>
      
    </asp:ListView>
    <br />
    <asp:Button Text="全部更新" ID="Button1" runat="server" onclick="Button1_Click" />
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class ListViewDemo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)//一列一列地走訪ListView
    {
        foreach (ListViewDataItem item in ListView1.Items)
        {
            if (item.ItemType==ListViewItemType.DataItem)//是資料列才往下處理
            {
                //抓出各欄位的控制項
                TextBox txt_name = (TextBox)item.FindControl("txt_name");
                TextBox txt_title = (TextBox)item.FindControl("txt_title");
                TextBox txt_company = (TextBox)item.FindControl("txt_company");
                //抓出主鍵值
                string id = ((Label)item.FindControl("idLabel")).Text;
                //Update更新此列
                this.updateTable(id, txt_name.Text, txt_title.Text, txt_company.Text);

                //重新資料繫結，畫面上才會是最新值
                ListView1.DataBind();
            }
        }
    }

    //連線字串
    protected string Conn_Str = WebConfigurationManager.ConnectionStrings["NorthwindChineseConnectionString"].ConnectionString;
    private void updateTable(string id, string name, string title, string company)
    {
        SqlConnection conn = new SqlConnection(Conn_Str);
        string sql = @"Update tb_test 
                       Set name=@name,
                       title=@title,
                       company=@company
                       Where id=@id";
        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("@name", name);
        cmd.Parameters.AddWithValue("@title", title);
        cmd.Parameters.AddWithValue("@company", company);
        cmd.Parameters.AddWithValue("@id", id);
        conn.Open();
        //執行
        cmd.ExecuteNonQuery();
        conn.Close();

    }
}

ListView執行結果：