Is there a way to stop running SQL scripts if we get an error?

Question

I am developing a program for my final project of college that consists of taking several SQL scripts from a fixed folder and executing them. In a meeting with my project supervisor he said that it would be a good idea to make sure that if an error is detected while running one of the scripts the program should stop running the script and stop. And i'm having problems with the part of stopping the program after it detects the error. Help would be very appreciated. Btw this is all done in C# (WindowsFormsApp(.NET Framework)). The script that i'm showing works perfectly.

        public string ExecuteFiles(string connectionString, string fileName)
        {
            var result = string.Empty;
            //var error = ">> [" + MethodBase.GetCurrentMethod().Name + "]\n";

            try
            {
                string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Updates", fileName);

                string script = File.ReadAllText(path);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    Logger.Debug("Script a ser executado: " + fileName);

                    connection.Open();

                    Server server = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(connection));

                    server.ConnectionContext.ExecuteNonQuery(script);

                    Logger.Debug("Script executado: " + fileName);
                }
            }
            catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException cfe)
            {
                Logger.Error("Message: " + cfe);
                lblmsg.Text = ">> [Message] => " + cfe.Message + "\n";

            }
            catch (SmoException se)
            {
                Logger.Error("Message: " + se);
                lblmsg.Text = ">> [Message] => " + se.Message + "\n";

            }
            catch (Exception ex)
            {
                Logger.Error("Message: " + ex);
                lblmsg.Text = ">> [Message] => " + ex.Message + "\n";

                if (ex.InnerException is SqlException)
                {              
                    SqlException sqlex = (SqlException)ex.InnerException;
                    lblmsg.Text = ">> [Message] => " + sqlex.Message + "\n";
                    Logger.Error("Message: " + sqlex);
                }
            }
            return result;
        }

The way you are describing the problem i assume you are calling this method in a loop where you have a bunch of files. If you want to cancel all that is left in that loop do as @Mikael said. If you want to cancel everything that has been done so far you need to rework everything in a single transaction with a rollback on a single error — Franck, Jul 24 '20 at 15:37
You need to show how this function is called in order to advise you how to stop it when one script fails. — AhmadWabbi, Jul 24 '20 at 15:50
I'm in a meeting so I'm typing on my mobile. Please excuse the format. In your catch(exception ex) block, do all of your logging then at the end type Throw.ex; this will throw a fatal error and halt the program from continuing. You may need to write your log before you throw the error though — Mikael, Jul 24 '20 at 15:52
Np. Let me know if you have further difficulties and I'll type up a better example after this call. — Mikael, Jul 24 '20 at 15:56

score -1 · Answer 1 · answered Jul 27 '20 at 08:03

You can add code System.Environment.Exit(0); in your catch sentence when an error is thrown in the try block.

The window will prompt an error message first, and then exit the program.

You could try the following code to replace it:

public string ExecuteFiles(string connectionString, string fileName)
        {
            var result = string.Empty;
            //var error = ">> [" + MethodBase.GetCurrentMethod().Name + "]\n";

            try
            {
                string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Updates", fileName);

                string script = File.ReadAllText(path);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {

                    Logger.Debug("Script a ser executado: " + fileName);
                    connection.Open();

                    Server server = new Server(new Microsoft.SqlServer.Management.Common.ServerConnection(connection));

                    server.ConnectionContext.ExecuteNonQuery(script);

                    Logger.Debug("Script executado: " + fileName);
                }
            }
            catch (Microsoft.SqlServer.Management.Common.ConnectionFailureException cfe)
            {
                Logger.Error("Message: " + cfe.Message);
                lblmsg.Text = ">> [Message] => " + cfe.Message + "\n";
                System.Environment.Exit(0);
                

            }
            catch (SmoException se)
            {
                Logger.Error("Message: " + se);
                lblmsg.Text = ">> [Message] => " + se.Message + "\n";
                System.Environment.Exit(0);

            }
            catch (Exception ex)
            {
                Logger.Error("Message: " + ex);
                lblmsg.Text = ">> [Message] => " + ex.Message + "\n";
                
                if (ex.InnerException is SqlException)
                {
                    SqlException sqlex = (SqlException)ex.InnerException;
                    lblmsg.Text = ">> [Message] => " + sqlex.Message + "\n";
                    Logger.Error("Message: " + sqlex);
                    System.Environment.Exit(0);
                }
            }
            return result;
        }

Result：

This is obtuse at best. [Re-throwing the exception](https://stackoverflow.com/questions/63076620/is-there-a-way-to-stop-running-sql-scripts-if-we-get-an-error#comment111542752_63076620) is a much better idea — Liam, Jul 27 '20 at 08:08

Is there a way to stop running SQL scripts if we get an error?

1 Answers1