雖然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"%> |
< form id = "form1" runat = "server" > |
< asp:GridView ID = "GridView1" runat = "server" AutoGenerateColumns = "False" |
< asp:BoundField DataField = "id" HeaderText = "id" InsertVisible = "False" |
ReadOnly = "True" SortExpression = "id" /> |
< asp:TemplateField HeaderText = "name" SortExpression = "name" > |
< asp:TextBox ID = "TextBoxName" runat = "server" Text='<%# Eval("name") %>' /> |
< asp:TemplateField HeaderText = "title" SortExpression = "title" > |
< asp:TextBox ID = "TextBoxTitle" runat = "server" Text='<%# Eval("title") %>' /> |
< asp:TemplateField HeaderText = "company" SortExpression = "company" > |
< asp:TextBox ID = "TextBoxCompany" runat = "server" Text='<%# Eval("company") %>' /> |
< asp:Button ID = "Button1" runat = "server" Text = "全部更新" onclick = "Button1_Click" /> |
接著撰寫Button1_Click的Update更新:
using System.Collections.Generic; |
using System.Web.UI.WebControls; |
using System.Web.Configuration; |
using System.Data.SqlClient; |
public partial class MSDN : System.Web.UI.Page |
protected void Page_Load( object sender, EventArgs e) |
GridView1.DataSource = this .selectTable(); |
protected void Button1_Click( object sender, EventArgs e) |
foreach (GridViewRow row in GridView1.Rows) |
if (row.RowType==DataControlRowType.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; |
this .updateTable(id, TextBoxName.Text, TextBoxTitle.Text, TextBoxCompany.Text); |
GridView1.DataSource = this .selectTable(); |
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 |
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); |
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); |
這樣就完成了
執行結果:
一開始的畫面
把第二筆資料改成
按「全部更新」
用SSMS打開Table看,值確實更新了
ListView也是相同邏輯,只不過把Row改成Item
以下是ListView範例(增加”新增Insert””刪除Delete”功能,為了方便撈資料,所以和SqlDataSource做資料繫結)
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ListViewDemo.aspx.cs" Inherits="ListViewDemo" Debug="true" %> |
< 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"> |
< asp:Parameter Name = "id" Type = "Int32" /> |
< asp:Parameter Name = "name" Type = "String" /> |
< asp:Parameter Name = "title" Type = "String" /> |
< asp:Parameter Name = "company" Type = "String" /> |
< asp:Parameter Name = "name" Type = "String" /> |
< asp:Parameter Name = "title" Type = "String" /> |
< asp:Parameter Name = "company" Type = "String" /> |
< asp:Parameter Name = "id" Type = "Int32" /> |
< asp:ListView ID = "ListView1" runat = "server" DataKeyNames = "id" |
DataSourceID = "SqlDataSource1" InsertItemPosition = "LastItem" > |
< table runat = "server" style = "" > |
< asp:Button ID = "InsertButton" runat = "server" CommandName = "Insert" Text = "插入" /> |
< asp:Button ID = "CancelButton" runat = "server" CommandName = "Cancel" Text = "清除" /> |
< asp:TextBox ID = "nameTextBox" runat = "server" Text='<%# Bind("name") %>' /> |
< asp:TextBox ID = "titleTextBox" runat = "server" Text='<%# Bind("title") %>' /> |
< asp:TextBox ID = "companyTextBox" runat = "server" Text='<%# Bind("company") %>' /> |
< asp:Button ID = "DeleteButton" runat = "server" CommandName = "Delete" Text = "刪除" /> |
< asp:Label ID = "idLabel" runat = "server" Text='<%# Eval("id") %>' /> |
< asp:TextBox ID = "txt_name" runat = "server" Text='<%# Eval("name") %>' /> |
< asp:TextBox ID = "txt_title" runat = "server" Text='<%# Eval("title") %>' /> |
< asp:TextBox ID = "txt_company" runat = "server" Text='<%# Eval("company") %>' /> |
< table ID = "itemPlaceholderContainer" runat = "server" border = "1" style = "" > |
< tr runat = "server" style = "" > |
< tr ID = "itemPlaceholder" runat = "server" > |
< asp:Button Text = "全部更新" ID = "Button1" runat = "server" onclick = "Button1_Click" /> |
using System.Collections.Generic; |
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) |
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; |
this .updateTable(id, txt_name.Text, txt_title.Text, txt_company.Text); |
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 |
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); |
ListView執行結果:
按下「插入」
也可以再對著剛剛那一筆做「全部更新」
資料庫裡的資料確實更新了
也可以從ListView 把剛剛那筆刪除
Sample包(不含資料庫)
相關文章:
Inserting Multiple Rows Using GridView
Batch data update in an Excel-like GridView (CSASPNETExcelLikeGridView) - MSDN 示例库批量上传GridView中数据
2012.3.20 追加,手寫切換GridView多數資料列的呈現/編輯模式