学会了ASP.NET 2.0中的数据批量更新
今天看《ASP.NET 2.0高级编程》,学会了ADO.NET 2.0中的数据批量更新,把代码发到这里,以供日后之需。
DemoBulkUpdate.aspx
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
public partial class DemoBulkUpdate : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
protected void btnUpdateAddress_Click(object sender, EventArgs e)
{
SqlDataAdapter EmpAdapter = new SqlDataAdapter();
DataTable EmpDT = new DataTable();
SqlConnection DBConSelect = new SqlConnection();
SqlConnection DBConUpdate = new SqlConnection();
SqlCommand SelectCommand = new SqlCommand();
SqlCommand UpdateCommand = new SqlCommand();
//采用两个不同的数据源供查询和更新使用
DBConSelect.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
DBConUpdate.ConnectionString = ConfigurationManager.ConnectionStrings["DSN_Northwind"].ConnectionString;
//获取所有的雇员表中的记录
SelectCommand.CommandText = "SELECT TOP 500 * FROM EMPLOYEES";
SelectCommand.CommandType = CommandType.Text;
SelectCommand.Connection = DBConSelect;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
UpdateCommand.CommandText = "UPDATE EMPLOYEES SET Address=@Address,City=@City,Region=@Region,Country=@Country";
UpdateCommand.CommandType = CommandType.Text;
UpdateCommand.Connection = DBConUpdate;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SqlParameter AddressParam = new SqlParameter("@Address", SqlDbType.VarChar, 15, "Address");
SqlParameter CityParam = new SqlParameter("@City", SqlDbType.VarChar, 15, "City");
SqlParameter RegionParam = new SqlParameter("@Region", SqlDbType.VarChar, 15, "Region");
SqlParameter CountryParam = new SqlParameter("@Country", SqlDbType.VarChar, 15, "Region");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
UpdateCommand.Parameters.Add(AddressParam);
UpdateCommand.Parameters.Add(CityParam);
UpdateCommand.Parameters.Add(RegionParam);
UpdateCommand.Parameters.Add(CountryParam);
//设置适配器,查询命令用于检索所有的雇员表中的记录,更新命令用于修改地址信息然后
//更新回数据库
EmpAdapter.SelectCommand = SelectCommand;
EmpAdapter.UpdateCommand = UpdateCommand;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
EmpAdapter.Fill(EmpDT);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
DBConSelect.Close();
//遍历所有的雇员表记录然后更新地址信息
foreach (DataRow DR in EmpDT.Rows)
{
DR["Address"] = "4445 W 77th Street, Suite 140";
DR["City"] = "Edina";
DR["Region"] = "Minnesota";
DR["Country"] = "USA";
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
EmpAdapter.RowUpdated+=new SqlRowUpdatedEventHandler(OnRowUpdated);
this.lblCounter.Text = "";
EmpAdapter.UpdateBatchSize = 100;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
try
{
DBConUpdate.Open();
EmpAdapter.Update(EmpDT);
}
catch (Exception ex)
{
this.lblCounter.Text += ex.Message + "<br />";
}
finally
{
if (DBConUpdate.State == ConnectionState.Open)
{
DBConUpdate.Close();
}
}
}
private void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)
{
this.lblCounter.Text += "Batch is processed till row number= " + args.RowCount.ToString() + "<br />";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
}
没想到这是2007年第一篇文章,希望给我带来好运气!
DemoBulkUpdate.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DemoBulkUpdate.aspx.cs" Inherits="DemoBulkUpdate" %>
<!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">
<div>
<asp:Button ID="btnUpdateAddress" runat="server" Text="批量更新地址" OnClick="btnUpdateAddress_Click" />
<br /><br />
<asp:Label ID="lblCounter" runat="server"></asp:Label> <br />
</div>
</form>
</body>
</html>
DemoBulkUpdate.aspx.cs<!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">
<div>
<asp:Button ID="btnUpdateAddress" runat="server" Text="批量更新地址" OnClick="btnUpdateAddress_Click" />
<br /><br />
<asp:Label ID="lblCounter" runat="server"></asp:Label> <br />
</div>
</form>
</body>
</html>
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
没想到这是2007年第一篇文章,希望给我带来好运气!