c# - 两张表用SQL事务插入

Question

前端如何用SQL事务实现两表插入代码？

我有两个表，TblMaster 和 TblSub。如果 Tblsub 插入失败，则 TblMaster 记录应该回滚。

我在 SQL 帮助程序类中有一个常用函数 ExecuteNonQuery 用于插入记录。

请建议我解决此问题的方法。

Answer 1

C# 中的一个例子

SqlTransaction transaction = null;
SqlConnection con = null;

// they will be used to decide whether to commit or rollback the transaction
bool debitResult = false;
bool creditResult = false;

try
{
    con = new SqlConnection(CONNECTION_STRING);
    con.Open();

    // lets begin a transaction here
    transaction = con.BeginTransaction();

    // Let us do a debit first
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Insert into Table1";   // Query here

        // assosiate this command with transaction
        cmd.Transaction = transaction;

        debitResult = cmd.ExecuteNonQuery() == 1;
    }

    // A dummy throw just to check whether the transaction are working or not
    //throw new Exception("Let see..."); // uncomment this line to see the transaction in action

    // And now do a credit
    using (SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "Insert into Table2";   // Query here

        // assosiate this command with transaction
        cmd.Transaction = transaction;

        creditResult = cmd.ExecuteNonQuery() == 1;
    }

    if (debitResult && creditResult)
    {
        transaction.Commit();
    }
}
catch
{
    transaction.Rollback();            
}
finally
{
    con.Close();
}