SqlDataAdapter批量更新数据---实例

想用SqlDataAdapter进行批量更新数据，在网上找了资料，自己写个小例子：

一：数据表--TestUpdate

 

二：页面前段---添加文本框的数据到缓存表里然后点击保存进行批量更新或插入

<form id="form1" runat="server"> <div> 用户名：<asp:TextBox ID="txtUserName" runat="server" ></asp:TextBox> <asp:Button ID="btnAdd" Text="添加" runat="server" οnclick="btnAdd_Click" /> <asp:DataGrid ID="DataGrid" runat="server" AutoGenerateColumns="false"> <Columns> <asp:BoundColumn DataField="UserName" HeaderText="用户名"></asp:BoundColumn> </Columns> </asp:DataGrid> <asp:Button ID="btnSave" runat="server" Text="保存" οnclick="btnSave_Click" /> </div> <div><asp:Label ID="lblCounter" runat="server" ></asp:Label></div> </form>

 

三：后台代码

public partial class _Default : System.Web.UI.Page { /// <summary> /// 缓存表 /// </summary> private DataTable EmpDT { get { return (DataTable)ViewState["EmpDT"]; } set { ViewState["EmpDT"] = value; } } protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { EmpDT = new DataTable(); EmpDT.Columns.Add("UserName"); EmpDT.AcceptChanges(); } } protected void btnAdd_Click(object sender, EventArgs e) { DataRow dr = EmpDT.NewRow(); dr[0] = txtUserName.Text; EmpDT.Rows.Add(dr); EmpDT.AcceptChanges(); DataGrid.DataSource = EmpDT; DataGrid.DataBind(); } protected void btnSave_Click(object sender, EventArgs e) { string sql = "SELECT * FROM TestUpdate"; string conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; SqlConnection cn = new SqlConnection(conn); SqlDataAdapter da = new SqlDataAdapter(sql, cn); DataSet ds = new DataSet(); try { cn.Open(); da.Fill(ds, "dsTable1"); } finally { cn.Close(); } 批量添加数据 //DataRow dr; //for (int i = 0; i < EmpDT.Rows.Count; i++) //{ // dr = ds.Tables["dsTable1"].NewRow(); // dr["UserName"] = EmpDT.Rows[i]["UserName"].ToString(); // ds.Tables["dsTable1"].Rows.Add(dr); //} //sql = "INSERT INTO TestUpdate (UserName) VALUES (@UserName)"; //da.InsertCommand = new SqlCommand(sql, cn); //SqlParameter param = new SqlParameter(); //param = da.InsertCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.Char, 50)); //param.SourceVersion = DataRowVersion.Current; //param.SourceColumn = "UserName"; //批量更新 for (int i = 0; i < ds.Tables["dsTable1"].Rows.Count;i++ ) { ds.Tables["dsTable1"].Rows[i][1] = "Update"; } sql = "Update TestUpdate set UserName=@UserName"; da.UpdateCommand = new SqlCommand(sql, cn); SqlParameter param = new SqlParameter(); param = da.UpdateCommand.Parameters.Add(new SqlParameter("@UserName", SqlDbType.Char, 50)); param.SourceVersion = DataRowVersion.Current; param.SourceColumn = "UserName"; //批量更新数据 try { cn.Open(); da.Update(ds, "dsTable1"); } catch (Exception ex) { lblCounter.Text = "数据库错误：" + ex.Message.ToString(); } finally { cn.Close(); } } }

四：效果

数据库结果

 

其它： 批量导入数据sqlbulkcopy最快，sqldataadapter.update()次之，sqlcommand.ExecuteNonQuery()最慢。