CODEWITHSUNDEEP
c#sql-serverexception
SuicideSheep
SuicideSheep

Reputation: 5550

How to capture exception by SQL Server to console program?

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?

Upvotes: 0

Views: 238

Answers (2)

TriV
TriV

Reputation: 5148

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
                }
            }
        }

Upvotes: 1

bluekushal
bluekushal

Reputation: 509

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)

Upvotes: 1

Related Questions