C# SqlDataAdapter.update结合事务(SqlTransaction)批量更新数据

 //此处的程序功能主要是将一个库中的表数据(源表)更新到另外一个库中的表数据(目标表)

//实现目标表的数据和源表数据一致,已包括增加,更新和删除数据记录功能

//主调用代码块

 

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;
            }
          

        }