学会了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;
public partial class DemoBulkUpdate : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
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;
UpdateCommand.CommandText = "UPDATE EMPLOYEES SET Address=@Address,City=@City,Region=@Region,Country=@Country";
UpdateCommand.CommandType = CommandType.Text;
UpdateCommand.Connection = DBConUpdate;
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");
UpdateCommand.Parameters.Add(AddressParam);
UpdateCommand.Parameters.Add(CityParam);
UpdateCommand.Parameters.Add(RegionParam);
UpdateCommand.Parameters.Add(CountryParam);
//设置适配器，查询命令用于检索所有的雇员表中的记录，更新命令用于修改地址信息然后
//更新回数据库
EmpAdapter.SelectCommand = SelectCommand;
EmpAdapter.UpdateCommand = UpdateCommand;
EmpAdapter.Fill(EmpDT);
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";
}
EmpAdapter.RowUpdated+=new SqlRowUpdatedEventHandler(OnRowUpdated);
this.lblCounter.Text = "";
EmpAdapter.UpdateBatchSize = 100;
UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
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 />";
}
}
没想到这是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>
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;public partial class DemoBulkUpdate : System.Web.UI.Page{
protected void Page_Load(object sender, EventArgs e) {
} 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; UpdateCommand.CommandText = "UPDATE EMPLOYEES SET Address=@Address,City=@City,Region=@Region,Country=@Country"; UpdateCommand.CommandType = CommandType.Text; UpdateCommand.Connection = DBConUpdate; 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"); UpdateCommand.Parameters.Add(AddressParam); UpdateCommand.Parameters.Add(CityParam); UpdateCommand.Parameters.Add(RegionParam); UpdateCommand.Parameters.Add(CountryParam); //设置适配器，查询命令用于检索所有的雇员表中的记录，更新命令用于修改地址信息然后 //更新回数据库 EmpAdapter.SelectCommand = SelectCommand; EmpAdapter.UpdateCommand = UpdateCommand; EmpAdapter.Fill(EmpDT); 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"; } EmpAdapter.RowUpdated+=new SqlRowUpdatedEventHandler(OnRowUpdated); this.lblCounter.Text = ""; EmpAdapter.UpdateBatchSize = 100; UpdateCommand.UpdatedRowSource = UpdateRowSource.None; 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 />"; }}没想到这是2007年第一篇文章，希望给我带来好运气！
