How to capture exception by SQL Server to console program?

Question

for(int i=0; i < fileDirectories.Count;i++){

string script = File.ReadAllText(@fileDirectories[i]);
string[] singleCommand = Regex.Split(script,"^GO", RegexOptions.Multiline);
StringCollection scl = new StringCollection();

foreach(string t in singleCommand){
    if(t.Trim().Length > 0) scl.Add(t.Trim());
}

try
{
    int[] result = Server
               .ConnectionContext
               .ExecuteNonQuery(scl, ExecutionTypes.ContinueOnError);
}catch(Exception e)
{
//whatever
}
}

The objective of this program is to run multiple scripts and if fail, capture the exceptions thrown by SQL SERVER and continue running the remaining scripts.

In order to try out, INSERT PERMISSION has been taken away from DB and hence first query (INSERT query) will fail when being executed on SQL SERVER manually because INSERT PERMISSION been taken away with below Error

The INSERT permission was denied on object 'xxx',database 'xxxxx', schema 'xx'

When running the program, variable result will have value 0 indicating the query failed but however it's not throwing error and being catch at catch? How should I pass the exception given by SQL Server to my console program?

Because the program is expecting to loop 100 queries. Even when in the middle there is something wrong, exceptions thrown by SQL server are to be captured and remaining queries are expected to continue to run — SuicideSheep
– SuicideSheep, Commented Jul 6, 2017 at 8:41

TriV · Accepted Answer · 2017-07-06 09:55:44Z

You could use

public int ExecuteNonQuery(
    string sqlCommand,
    ExecutionTypes executionType
)

instead of

public int[] ExecuteNonQuery(
    StringCollection sqlCommands,
    ExecutionTypes executionType
)

For example

foreach (string t in singleCommand)
        {
            if (t.Trim().Length > 0)
            {
                try
                {
                    // do not use ExecutionTypes.ContinueOnError, it will not
                    // throw any exceptions
                    int result = Server
                        .ConnectionContext
                        .ExecuteNonQuery(t, ExecutionTypes.Default); 
                }
                catch (ExecutionFailureException ex) 
                {
                    // do anything
                    // do not throw error here
                }
                catch (Exception e)
                {
                    // do anything
                    // do not throw error here
                }
            }
        }

Chandan Kumar · Accepted Answer · 2017-07-06 08:49:24Z

1

Remove ExecutionTypes.ContinueOnError.

No exception is thrown when you set execution type to ContinueOnError.

try
{
      int[] result = Server.ConnectionContext
                    .ExecuteNonQuery(stringColleciton);
}
catch(SqlException se)
{
      //write log, show message
}
catch(Exception e)
{
      //write log, show message
}

Read here about ServerConnection.ExecuteNonQuery Method (String, ExecutionTypes)

edited Jul 6, 2017 at 8:49

Chandan Kumar

4,6228 gold badges45 silver badges63 bronze badges

answered Jul 6, 2017 at 8:47

bluekushal

5196 silver badges14 bronze badges

1 Comment

SuicideSheep Over a year ago

Please find my question been updated. Thank you for suggestion if any.

Collectives™ on Stack Overflow

How to capture exception by SQL Server to console program?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related