[ASP.net WebForm] GridView/ListView仿Excel大量更新
雖然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>
</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執行結果:
按下「插入」
也可以再對著剛剛那一筆做「全部更新」
資料庫裡的資料確實更新了
也可以從ListView 把剛剛那筆刪除
相關文章:
Inserting Multiple Rows Using GridView
Batch data update in an Excel-like GridView (CSASPNETExcelLikeGridView) - MSDN 示例库批量上传GridView中数据
2012.3.20 追加,手寫切換GridView多數資料列的呈現/編輯模式