//此处的程序功能主要是将一个库中的表数据(源表)更新到另外一个库中的表数据(目标表)//实现目标表的数据和源表数据一致,已包括增加,更新和删除数据记录功能//主调用代码块SqlConnection conn = new SqlConnection("data source=.;user id=sa;password=sa;initial catalog=test");SqlTransaction trans;conn.Open();trans = conn.BeginTransaction();try{ TransUpdateTable(ds1.Tables[0], dtto, conn, sql1, trans, "key1"); TransUpdateTable(ds2.Tables[0], dtto, conn, sql2, trans, "key2"); TransUpdateTable(ds3.Tables[0], dtto, conn, sql3, trans, "key3"); trans.Commit();}catch (Exception ex){ trans.Rollback(); Response.Write(ex.Message);}finally{ conn.Close();}/// <summary>/// 处理数据/// </summary>/// <param name="dtfrom">源数据表</param>/// <param name="conn">连接串</param>/// <param name="sql">要更新表的查询语句</param>/// <param name="trans">事务</param>public void TransUpdateTable(DataTable dtfrom, DataTable dtto, SqlConnection conn, string sql, SqlTransaction trans, string key){ // try { SqlDataAdapter da1 = new SqlDataAdapter(sql, conn); da1.UpdateCommand = new SqlCommand("", conn, trans); da1.InsertCommand = new SqlCommand("", conn, trans); da1.DeleteCommand = new SqlCommand("", conn, trans); da1.SelectCommand = new SqlCommand(sql, conn, trans); //操作要更新的TO对象表 UpdateDataTableByTableKey(key, dtfrom, dtto); SqlCommandBuilder sqlcmd = new SqlCommandBuilder(da1); da1.UpdateCommand = sqlcmd.GetUpdateCommand(); da1.InsertCommand = sqlcmd.GetInsertCommand(); da1.DeleteCommand = sqlcmd.GetDeleteCommand(); da1.Update(dtto); dtto.AcceptChanges(); } catch { throw; }}/// <summary>/// 更新datatable/// </summary>/// <param name="key">表关键字</param>/// <param name="dtfrom">源表</param>/// <param name="dtto">目标表(要更新的表)</param>public void UpdateDataTableByTableKey(string key, DataTable dtfrom, DataTable dtto){ bool flag = false; int row = -1; for (int i = 0; i < dtfrom.Rows.Count; i++) { //判断是否存在记录 for (int j = 0; j < dtto.Rows.Count; j++) { if (dtfrom.Rows[i][key].ToString() == dtto.Rows[j][key].ToString()) { flag = true;//标志存在记录 row = j;//记录存在行号 break; } } if (flag && row >= 0) { //循环列 for (int m = 0; m < dtto.Columns.Count; m++) { //存在列则更新 string columnname = dtto.Columns[m].ColumnName; if (dtfrom.Columns.Contains(columnname)) { dtto.Rows[row][columnname] = dtfrom.Rows[i][columnname]; } else { dtto.Rows[i][columnname] = null; } } } else { //不存在记录则新增 DataRow dr = dtto.NewRow(); //循环列 for (int m = 0; m < dtto.Columns.Count; m++) { //存在列则更新 string columnname = dtto.Columns[m].ColumnName; if (dtfrom.Columns.Contains(columnname)) { dr[columnname] = dtfrom.Rows[i][columnname]; } else { dr[columnname] = null; } } dtto.Rows.Add(dr); } flag = false; row = -1; } //判断删除记录 for (int m = 0; m < dtto.Rows.Count; m++) { for (int n = 0; n < dtfrom.Rows.Count; n++) { if (dtfrom.Rows[n][key].ToString() == dtto.Rows[m][key].ToString()) { flag = true;//标志存在记录 row = m;//记录存在行号 break; } } if (!flag && row < 0) { dtto.Rows[m].Delete(); } flag = false; row = -1; }}