Execution flow of SQL Script in c#

Question

This is not a repeated question of rollback sql transactions if any failed in c#. But this question is based on the content of the previous question. Thanks to all who gave their opinion on my last question.I tried all those solutions and could finally found out that the scripts which I use in my application(DDL) are not allowed within multi-statement transaction.

My entire code is like this:

script1 = "CREATE USER " + username + " FROM LOGIN " + username;

script2 = "CREATE ROLE " + rolename;

script3 = @"CREATE ROLE [db_execute] AUTHORIZATION [dbo]
                        GRANT EXECUTE TO [db_execute]";

script4 = @"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";
script_result = string.Format(script4, rolename);

script6 = "EXEC sp_addrolemember '{0}', '{1}'";
            outp = string.Format(script6, rolename, username);

script7 = @"select m.name as Member, r.name as Role
                    from sys.database_role_members
                    inner join sys.database_principals m on sys.database_role_members.member_principal_id = m.principal_id
                    inner join sys.database_principals r on sys.database_role_members.role_principal_id = r.principal_id";

and executing all these in the same way as shown below:

SqlCommand SqlCmd = new SqlCommand();

//Create User Script Execution
SqlCmd.CommandText = script1;
SqlCmd.Connection = oConnection;        
lvinfo.Items.Add("Executing Create User script in " + dbname + " database");
var answer = SqlCmd.ExecuteNonQuery();

//Checking whether execution completed successfully
if (!answer.Equals(0))
{
lvinfo.Items.Add("Create User script executed successfully in " + dbname + " database");                                                                          
}
else
{
lvinfo.Items.Add("Create User script execution failed in " + db_select.SelectedItem.ToString() + " database");
}

Even though I only wrote this code, now I am having some doubts on the execution of these scripts. All the scripts shown above are executed at a time, on the click of a button. What I want to know is that will these scripts get executed at a time fully?Means, is there any chance for an execution failure while executing these scripts,at a time?Chance in the form of database disconnection, or any other errors.

Let me show you an example of what I am asking:If somehow the first 3 scripts got executed, and all the rest of the scripts failed, is there any chance for rollback automatically then?

Or do my code checks for any errors or failure chances before the entire code gets executed so that it need not have a rollback ever?

Can anyone please support me with their valuable suggestions and ideas..

Any help would be really appreciated..

Is there some reason why you are not encapsulating this sequence of SQL commands as a stored procedure? — podiluska, Sep 25 '13 at 07:07
@podiluska: To be open, I didnt try stored procedure here.Mainly because I am not aware of how to achieve so.. — vysakh, Sep 25 '13 at 08:12
@RameezAhmedSayad:tried sqlTransaction..but the error was like create command was not allowed within multi-statement transaction..while executing the very first statement itself.. — vysakh, Sep 25 '13 at 08:14
Can you paste the exact error msg because ideally CREATE statements should be transactional. — Rameez Ahmed Sayad, Sep 25 '13 at 08:42
@RameezAhmedSayad: CREATE LOGIN statement not allowed within multi-statement transaction — vysakh, Sep 25 '13 at 09:21
I think Sql server now treats CREATE statments as AUTO COMMIT , If you execute this statement SET IMPLICIT_TRANSACTIONS OFF; before CREATE LOGIN, you can make it work. — Rameez Ahmed Sayad, Sep 25 '13 at 09:26
@RameezAhmedSayad:will it enable the execution of script4 and script 6 also?as a transaction? — vysakh, Sep 25 '13 at 11:33
@RameezAhmedSayad:I tried as you said: SET IMPLICIT_TRANSACTIONS OFF before create script..but then also the same exception... — vysakh, Sep 25 '13 at 11:45
then I believe , you'd need to manually write everything. All the drop statements if there is any exception while executing the SQL — Rameez Ahmed Sayad, Sep 25 '13 at 12:03
@RameezAhmedSayad: So, based on my present code, if somthing happens while executing script4, then will the scripts1,2,3 get rolledback? Any option for doing this as a single transaction? — vysakh, Sep 25 '13 at 12:16

Rameez Ahmed Sayad · Answer 1 · 2013-09-26T09:20:42.423

I have given a skeleton for what it may look like. You can tell me what all you want to rollback on what and what not scenarios.

var scriptToExecute = new Dictionary<int, string>();
            var scriptToRollback = new Dictionary<int, string>();

        try
        {
            using (SqlCommand SqlCmd = new SqlCommand())
            {
                //Create User Script Execution
                SqlCmd.CommandText = script1;
                SqlCmd.Connection = oConnection;
                lvinfo.Items.Add("Executing Create User script in " + dbname + " database");
                var answer = SqlCmd.ExecuteNonQuery();

                //Checking whether execution completed successfully
                if (!answer.Equals(0))
                {
                    lvinfo.Items.Add("Create User script executed successfully in " + dbname + " database");
                }
                else
                {
                    lvinfo.Items.Add("Create User script execution failed in " + db_select.SelectedItem.ToString() + " database");
                }
            }
        }
        catch (SqlException)
        {
            string rollbackScript = string.Empty;
            for (int i = scriptExecuting; i > 0; i--)
            {
                scriptToRollback.TryGetValue(i, rollbackScript);
                if (!string.IsEmpty(rollbackScript))
                {
                    //Execute the scripts here.
                }
            }
        }



        finally
        {
            //If connection is open then close the active connection
        }

so, why can't I give the drop statement over here else { lvinfo.Items.Add("Create User script execution failed in " + db_select.SelectedItem.ToString() + " database"); //Drop Statement } instead of specifying it in catch statement..What is the difference between these two? I have much more scripts like this as I shown above in my question.So, are you trying to say that I should call the drop statement in all those scripts? — vysakh, Sep 26 '13 at 03:36
This place `if (!answer.Equals(0))` could be an expected scenario , it simply means that no of rows affected are zero (based on your update statements) but if you encounter any exception on the sql server end it will throw an exception and have to be coded there. — Rameez Ahmed Sayad, Sep 26 '13 at 09:00
Ok..but if it is somthing like:out of 6 scripts first 3 succedded and failed in 4th means, I should be able to rollback all the 3..is this possible with what you have said? — vysakh, Sep 26 '13 at 09:04
Yes , it's possible , what i can suggest is have 2 dictionaries , one dictionary will have the script to execute based on the sequence and other it's rollback scripts. Then you can create a generic for method for rolling back. — Rameez Ahmed Sayad, Sep 26 '13 at 09:08

Execution flow of SQL Script in c#

1 Answers1