在多个SQL查询上应用事务

本文关键字：应用 事务 查询 SQL | 更新日期: 2023-09-27 18:01:47

我如何在单个函数中使用多个插入和更新查询的事务?例如:

private void button1_Click(object sender, EventArgs e)
    {
        // transaction
        SqlConnection objConnection = new SqlConnection(annual_examination_system.Properties.Settings.Default.connString);
        SqlTransaction objTransaction = null;
        int count = 0;
        try 
        {
          objConnection.Open();
          objTransaction = objConnection.BeginTransaction();
          string q1 = "update query"
          SqlCommand objCommand1 = new SqlCommand(q1, objConnection, objTransaction);
          objCommand1.ExecuteNonQuery();
          objTransaction.Commit();
          string q2 = "insert query"
          SqlCommand objCommand1 = new SqlCommand(q2, objConnection, objTransaction);
          objCommand1.ExecuteNonQuery();
          objTransaction.Commit();
        }
        catch (Exception ex)
        {
           objTransaction.Rollback();
            MessageBox.Show(ex.Message);
            MessageBox.Show("Exception, Row :" + count);
            MessageBox.Show("Transaction Rollback.");
        }
        finally
        {
            // Close the connection.
            if (objConnection.State == ConnectionState.Open)
            {
                objConnection.Close();
            }
        }

现在有两个查询，一个用于更新，一个用于插入。那么我是需要分别申请交易还是可以同时申请交易?

在多个SQL查询上应用事务

可以对两个查询使用相同的事务。下面是一个例子:

SqlTransaction tran;

现在对两个查询

使用这个
using(SqlConnection connection=new SqlConnection(connection_string))
{
    connection.Open();
    tran = connection.BeginTransaction();
    cmd = new SqlCommand(query1, connection, tran);
    cmd1 = new SqlCommand(query2, connection, tran);
    count = cmd.ExecuteNonQuery();
    count = count + cmd1.ExecuteNonQuery();
    tran.Commit();
}

更新:连接未关闭，这是问题。我已经编辑了代码。

相关文章：