如果在 C# 中有任何失败，则回滚 sql 事务 [英] rollback sql transactions if any failed in c#

问题描述

我有一个 windows 窗体应用程序，单击按钮即可在数据库中执行一些 sql 脚本.这些 sql 脚本执行用户创建，并提供一些权限，如数据执行、数据读/写等.这是我执行此操作的示例 c# 代码:

I have a windows forms application that executes some sql scripts in a database at the click of a button.These sql scripts do user creation, and giving some permissions like data execute, data read/write etc.. Here is my sample c# code for doing this:

script1 = "CREATE USER " + username + " FROM LOGIN " + username;
script2 = @"CREATE ROLE [db_execute] AUTHORIZATION [dbo]
                        GRANT EXECUTE TO [db_execute]";

script3 = @"DECLARE @rolename varchar(max)
                    SET @rolename ='{0}'
                    EXEC sp_addrolemember N'db_execute',@rolename
                    EXEC sp_addrolemember N'db_datareader', @rolename
                    EXEC sp_addrolemember N'db_datawriter', @rolename";

并在数据库中执行此操作:

And executing this in the database like:

SqlCmd.CommandText = script1;
SqlCmd.Connection = oConnection;
var ans = SqlCmd.ExecuteNonQuery();

if (!ans.Equals(0))
{
    //some task
} else
{
    //messagebox
}

SqlCmd.CommandText = script2;
SqlCmd.Connection = oConnection;
var answer = SqlCmd.ExecuteNonQuery();

if (!answer.Equals(0))
{
    //some task
} else
{
    //messagebox
}

SqlCmd.CommandText = script3;
SqlCmd.Connection = oConnection;
var answ = SqlCmd.ExecuteNonQuery();

if (!answ.Equals(0))
{
    //some task
} else
{
    //messagebox
}

我通过按一个按钮一次执行所有这些脚本，因此据我所知，我将其作为单个事务执行.我想要做的是，如果这些脚本中的任何一个失败，我应该能够完全回滚而不做任何更改.

I am executing all these scripts at a time by the press of a button,so in my understanding I am doing this as a single transaction. What I want to do is that, if in case any of these scripts fail in between I should be able to rollback fully without doing any change.

为了给你一个更好的画面，如果脚本3的执行由于某种原因失败，它应该自动回滚数据库中的脚本1和脚本2.

For giving you a better picture,if in case the execution of script3 got failed for some reason, it should automatically rollback whatever the script1 and script2 done in the database.

我们怎样才能做到这一点?我用谷歌搜索了一下，发现了一些与此无关的帖子.

How can we do this? I googled it and found some irrelevant posts for this.

任何帮助或想法将不胜感激..

Any help or ideas would be really appreciated..

推荐答案

使用 SqlConnection 和 SqlTransaction 对象.试试这个;

Use the SqlConnection and SqlTransaction objects. Try this;

   {       
     // declarations

     try
     {
        // open connection
        trans = conn.BeginTransaction();
        cmd.Transaction = trans;   // Includes this cmd as part of the trans

        SqlCmd.CommandText = script1;
        SqlCmd.Connection = oConnection;
        var ans = SqlCmd.ExecuteNonQuery();


        if (!answ.Equals(0))
        {
           //some task
        } else
        {
           //messagebox
        }

       // Your other queries

       trans.Commit(); // commit transaction

     }
     catch (Exception e){
        trans.Rollback();
        throw e;
     }
     finally{
        conn.Close();
   }

这篇关于如果在 C# 中有任何失败，则回滚 sql 事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！