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

51 篇文章 0 订阅
//此处的程序功能主要是将一个库中的表数据(源表)更新到另外一个库中的表数据(目标表)
//实现目标表的数据和源表数据一致,已包括增加,更新和删除数据记录功能
//主调用代码块
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;
    }
}

 

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:技术工厂 设计师:CSDN官方博客 返回首页
评论
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值