How can I get an error message that happens when using ExecuteNonQuery()?

Question

I am executing a command in this way :

var Command = new SqlCommand(cmdText, Connection, tr);

Command.ExecuteNonQuery();

In the command there is an error, however .NET does not throw any error message. How could I know that the command did not executed properly, and how to get the exception?

Itchy

There is no NonQuery method - it really would be clearer if you used the actual names... editing... – Event

@Marc maybe his own custom method? – Subatomic

@Shadow then we can't possibly hope to debug it – Event

@Marc well let's hope that you are right, and your answer sounds correct. – Subatomic

Answer 1

You'll only get an exception in C# if your error's severity is 16 or above. If you are using a PRINT, you won't get an exception in .NET.

If you can edit the raise error code, this would cause a SqlException in C#:

RAISERROR('Some error message', 16, 1)

You can then get to each individual error in the SqlException.Errors collection.

Just a side-note - SQL Server will continue to run commands after the RAISERROR if you don't RETURN directly afterwards. If you don't return, you can get multiple errors back.

Cohesion

Answer 2

.NET does indeed raise an error message... if the severity is 16 or above (since it throws an exception) - the message will be in the exception .Message. If you are using RAISERROR with a lower severity (or using PRINT) then you will have to subscribe to the InfoMessage event on the connection.

Event

Wait, so SQL Server will actually consume the TDS with ExecuteNonQuery() (and thus see the error), but ExecuteScalar() won't consume it? I would expect ExecuteNonQuery() not to consume anything at all, though I actually prefer that it does. – Rondel

@NelsonRothermel I would expect both of those to result in an error, as long as the severity is high enough. There is an edge case when using ExecuteReader, though: if (for example) you have multiple select queries, and you raise an error between (say) the 3rd and 4th grids, but you dispose the reader part-way through the first grid: then the error can be missed. – Event

I was aware of the problems with ExecuteReader. However, my research (#1010883, https://mcmap.net/q/538750/-does-executescalar-have-any-advantages-over-executereader, sqlservercentral.com/Forums/Topic1070081-392-1.aspx#bm1070131) suggests ExecuteScalar wouldn't pick up the error either if it's after the first result set. I suppose the best way to be sure is to try it. – Rondel

Also, from my testing you don't need more than one result set to miss an error. A simple select ...; raiserror(...) requires that you do DataReader.NextResult() for it to pick up the error. – Rondel

@NelsonRothermel ah, right - in your ExecuteScalar discussion, you didn't say anything about multiple grids. I assumed (perhaps incorrectly) that if you are using ExecuteScalar, you were only doing one select, and that it was at the end of the query (because obviously, if you're only doing one thing, why would it throw after that?) – Event

Answer 3

Only high severity errors will be thrown back in ExecuteNonQuery. There is another scenario that I have observed with OdbcCommand.ExecuteNonQuery() method. May be this is true for SqlCommand.ExecuteNonQuery() as well. If the SQL contained in the CommandText property is a single statement (Example: INSERT INTO table (col1,col2) VALUES (2,'ABC'); ) and if there is a foreign key violation or primary key violation in the above statement ExecuteNonQuery will throw an exception. However, if your CommandText is a batch where you have more than one SQL Statements seperated by semi colon (Like Several INSERTS or UPDATES) and if one of them fails ExecuteNonQuery does not throw an exception back. You need to be explicitly checking for the number of records affected returned by the method. Simply putting the code in a try{}Catch{} wont help.

Caundra

Answer 4

Inspired by the work of M Hassan, Stefan Steiger, and Mark Gravell in this thread, here is a minimum proof-of-concept example of what is going on here:

private static void DoSql()
{
    // Errors of severity level of 10 or less 
    // will NOT bubble up to .Net as an Exception to be caught in the usual way
    const string sql = @"RAISERROR('A test error message of low severity', 10, 1)";

    using (SqlConnection conn = new SqlConnection(myConnString))
    {
        conn.Open();

        // Hook up my listener to the connection message generator
        conn.InfoMessage += new SqlInfoMessageEventHandler(MySqlMessageHandler);

        using (SqlCommand cmd = new SqlCommand(sql, conn))
        {
            cmd.ExecuteNonQuery();
            // code happily carries on to this point
            // despite the sql Level 10 error that happened above
        }
    }
}


private static void MySqlMessageHandler(object sender, SqlInfoMessageEventArgs e)
{
    // This gets all the messages generated during the execution of the SQL, 
    // including low-severity error messages.
    foreach (SqlError err in e.Errors)
    {
        // TODO: Something smarter than this for handling the messages
        MessageBox.Show(err.Message);
    }
}

Itinerate

Answer 5

I found this to work well for me in a WCF service with Oracle ODP.Net -

            try
            {
                cmd.Connection = conn;
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (OracleException oex)
            {
                string errmsg = oex.Message;
                Logger.Instance.WriteLog(@"Some error --> " + errmsg);
                throw new Exception(errmsg);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cleanup...
            }

Deploy

Answer 6

Use this code and when the command is finished it will return the error:

SqlCommand.EndExecuteNonQuery(result) 

and this is my full class code:

Imports System.Data.SqlClient
Imports System.DirectoryServices.ActiveDirectory

Class clsExecuteAsync


    Public Event EnProceso(Identificador As Integer, Mensaje As String)
    Public Event Finalizado(IDentificador As Integer, Mensaje As String)
    Public Event CancelarProcesoEnEjecucion(Identificador As Integer, ByRef Cancel As Boolean)



    Dim Cancelar As Boolean

    Sub CancelarProceso()
        Cancelar = True
    End Sub


    Function test() As Boolean
        ' This is a simple example that demonstrates the usage of the
        ' BeginExecuteNonQuery functionality.
        ' The WAITFOR statement simply adds enough time to prove the
        ' asynchronous nature of the command.
        Dim commandText As String = "UPDATE Production.Product SET ReorderPoint = ReorderPoint + 1 " & "WHERE ReorderPoint Is Not Null;" & "WAITFOR DELAY '0:0:3';" & "UPDATE Production.Product SET ReorderPoint = ReorderPoint - 1 " & "WHERE ReorderPoint Is Not Null"
        Return (RunCommandAsynchronously(0, commandText, GetConnectionString()))
        Console.WriteLine("Press ENTER to continue.")
        Console.ReadLine()

    End Function

    Function ExecuteAsync(Identificador As Integer, Sql As String, Optional CadenaConexion As String = "") As String
        If CadenaConexion = "" Then
            CadenaConexion = clsIni.LeeIni("Provider")
        End If

        Return RunCommandAsynchronously(Identificador, Sql, CadenaConexion)

    End Function

    Function RunCommandAsynchronously(Identificador As Integer, commandText As String, connectionString As String) As String
        ' Given command text and connection string, asynchronously execute
        ' the specified command against the connection. For this example,
        ' the code displays an indicator as it is working, verifying the
        ' asynchronous behavior.
        Dim Resultado As String = ""
        Try

            Dim connection As SqlConnection
            Dim SqlCommand As SqlCommand

            connection = New SqlConnection(connectionString)
            Dim count As Integer = 0

            'testint to catch the error, but not run for me
            AddHandler connection.InfoMessage, AddressOf ErrorEnConexion


            SqlCommand = New SqlCommand(commandText, connection)
            connection.Open()
            Dim result As IAsyncResult = SqlCommand.BeginExecuteNonQuery()
            While Not result.IsCompleted
                Console.WriteLine("Waiting ({0})", count = count + 1)
                ' Wait for 1/10 second, so the counter
                ' does not consume all available resources
                ' on the main thread.
                System.Threading.Thread.Sleep(100)
                RaiseEvent EnProceso(Identificador, commandText)
                Application.DoEvents()

                If Cancelar Then
                    Cancelar = False

                    'cancelar 
                    Dim Cancel As Boolean = False
                    RaiseEvent CancelarProcesoEnEjecucion(Identificador, Cancel)

                    If Cancel = False Then
                        Resultado = "Cancelado"
                        GoTo SALIR
                    End If

                End If

            End While

            'Console.WriteLine("Command complete. Affected {0} rows.", Command.EndExecuteNonQuery(Result))

            '   MsgBox("El comando se ejecutó. " & SqlCommand.EndExecuteNonQuery(result), MsgBoxStyle.Information)

            'detect error: this code lunch and error: Cath with try cacth code
            SqlCommand.EndExecuteNonQuery(result)

            RaiseEvent Finalizado(Identificador, SqlCommand.EndExecuteNonQuery(result))

            Resultado = "OK"

        Catch ex As SqlException
            Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message)
            Resultado = ex.Message


        Catch ex As InvalidOperationException
            Console.WriteLine("Error: {0}", ex.Message)
            Resultado = ex.Message
        Catch ex As Exception
            ' You might want to pass these errors
            ' back out to the caller.
            Console.WriteLine("Error: {0}", ex.Message)
            Resultado = ex.Message
        End Try


SALIR:
        Return Resultado

    End Function

    Private Sub ErrorEnConexion(sender As Object, e As SqlInfoMessageEventArgs)
        MsgBox(e.Message)

    End Sub


    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,
        ' you can retrieve it from a configuration file.
        ' If you have not included "Asynchronous Processing=true" in the
        ' connection string, the command is not able
        ' to execute asynchronously.
        Return "Data Source=(local);Integrated Security=SSPI;" & "Initial Catalog=AdventureWorks; Asynchronous Processing=true"

    End Function


End Class

Crowl

Answer 7

Try the below.

PS: Just because you use a transaction, doesn't mean you can neglect handling exceptions and rollbacks.

 public static void MessageEventHandler( object sender, SqlInfoMessageEventArgs e ) {
         foreach( SqlError error in e.Errors ) {
            Console.WriteLine("problem with sql: "+error);
            throw new Exception("problem with sql: "+error);
         }
      }
      public static int executeSQLUpdate(string database, string command) {
         SqlConnection connection = null;
         SqlCommand sqlcommand = null;
         int rows = -1;
         try {
            connection = getConnection(database);
            connection.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );
            sqlcommand = connection.CreateCommand();
            sqlcommand.CommandText = command;
            connection.Open();
            rows = sqlcommand.ExecuteNonQuery();
          } catch(Exception e) {
            Console.Write("executeSQLUpdate: problem with command:"+command+"e="+e);
            Console.Out.Flush();
            throw new Exception("executeSQLUpdate: problem with command:"+command,e);
         } finally {
            if(connection != null) { connection.Close(); }
         } 
         return rows;
      }

And this is proper transaction handling:

//public static void ExecuteInTransaction(Subtext.Scripting.SqlScriptRunner srScriptRunner)
        public override void ExecuteInTransaction(string strSQL)
        {

            System.Data.Odbc.OdbcTransaction trnTransaction = null;

            try
            {


                System.Threading.Monitor.Enter(m_SqlConnection);
                if (isDataBaseConnectionOpen() == false)
                    OpenSQLConnection();

                trnTransaction = m_SqlConnection.BeginTransaction();

                try
                {
                    /*
                    foreach (Subtext.Scripting.Script scThisScript in srScriptRunner.ScriptCollection)
                    {
                        System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(scThisScript.ScriptText, m_sqlConnection, trnTransaction);
                        cmd.ExecuteNonQuery();
                    }
                    */

                    // pfff, mono C# compiler problem...
                    // System.Data.Odbc.OdbcCommand cmd = new System.Data.Odbc.OdbcCommand(strSQL, m_SqlConnection, trnTransaction);
                    System.Data.Odbc.OdbcCommand cmd = this.m_SqlConnection.CreateCommand();
                    cmd.CommandText = strSQL;

                    cmd.ExecuteNonQuery();

                    trnTransaction.Commit();
                } // End Try
                catch (System.Data.Odbc.OdbcException exSQLerror)
                {
                    Log(strSQL);
                    Log(exSQLerror.Message);
                    Log(exSQLerror.StackTrace);
                    trnTransaction.Rollback();
                } // End Catch
            } // End Try
            catch (Exception ex)
            {
                Log(strSQL);
                Log(ex.Message);
                Log(ex.StackTrace);
            } // End Catch
            finally
            {
                strSQL = null;
                if(m_SqlConnection.State != System.Data.ConnectionState.Closed)
                    m_SqlConnection.Close();
                System.Threading.Monitor.Exit(m_SqlConnection);
            } // End Finally


        } // End Sub ExecuteInTransaction

Peasant

I really disagree with your choice of error-handling; using and lock would be far more appropriate here; also, you aren't re-throwing, and logging at every level is overkill (that is what .StackTrace is for). That silently fails - very bad, IMO. And why lock on a connection? If you are in a position where two thread can even see the same connection, you're doing it wrong, IMO. If anything, that looks to me like a perfect example of how not to do error handling. – Event

thnx for reply it really helped me – Itchy

@Quandary - for DAL, that is why connection pools exist. By locking, you actually limit your entire app to a single useful worker - that is really, really bad IMO. On a busy site (like, say, this one) we have significant amounts of parallel data access. Jeff would shoot me if I used a single connection... – Event

@Marc Gravell: You can never log enough. If you're using asp.net and want to instantiate the DAL class on every page, it's going to be horrible. Not to mention the speed loss by accessing the web.config for reading out the connectionstring info every time. And as you see in the commented out part, the methods are static. Since technologies such as Flash and AJAX are asynchronous, you need to lock... Besides, VB.NET does not have lock, so one needs monitor, and this is an automagic code conversion. But yes, lock would be better. ;) PS: Rethrowing was removed, to not present the users YSODs. – Peasant

@Marc Gravell: For everything else, I could simply use elmah, and not catch exceptions at all. PS2: Isn't lock shorthand for system.threading monitor ? So it wouldn't actually make any difference at all, except readability ? – Peasant

@Quandary (see above also) - re VB: see the SyncLock keyword; re YSOD - that is what a custom error handler page is for; re Flash/AJAX - sync/async is completely unrelated to how you handle errors and synchronize access; re web.config - without evidence that is hollow, but you can (and should) hoist that info out for quick access during app-startup - but again, that has little to do with error-handling. – Event

@Marc Gravell: True, but customers do not like error pages in general, be that YSOD or custom. Since code is never going to be bug free - I just log the bugs and pretend nothing happened :)) . I personally like YSODs more than a custom page with error info degradation. – Peasant

@Quandary a: readability is important, and b: it is more complex than that - there are better (more robust) overloads that the compiler will use automatically; for example, the call to Enter() can fail - which means you haven't taken the lock - which you then try to Exit() (even though you didn't get the lock). But more importantly, why write the extra code? – Event

@Marc Gravell: Interesting. It's true that changing things to TryEnter and handling failures was on my todo list. I guess instead I'm going to use SyncLock in the future. – Peasant

@Marc Gravell: Am I dreaming or did you miss to open the connection (if not already open) ? Also, is your GetConnection() doing anything more than creating a new connection ? – Peasant

@Quandry - conn.Open(); is there - actually, internally we use an EnsureOpen() entension-method, which uses IDisposable and opens it if not open and closes it at dispose (if it opened it) - handy little wrapper utility method. The whole "how much do we re-use the connection" is unrelated to the main point I was trying to illustrate, though - so I didn't focus on that – Event

Answer 8

You catch the SqlException using try/catch

 try
  {
       //.......
    Command.ExecuteNonQuery();      
   }
    catch (SqlException ex)
     {   
       log (SqlExceptionMessage(ex).ToString());
     }

The following Method Catch details of SqlException which can be logged or displayed to user

  public StringBuilder SqlExceptionMessage(SqlException ex)
    {
        StringBuilder sqlErrorMessages = new StringBuilder("Sql Exception:\n");

        foreach (SqlError error in ex.Errors)
        {
            sqlErrorMessages.AppendFormat("Mesage: {0}\n", error.Message)
                .AppendFormat("Severity level: {0}\n", error.Class)
                .AppendFormat("State: {0}\n", error.State)
                .AppendFormat("Number: {0}\n", error.Number)
                .AppendFormat("Procedure: {0}\n", error.Procedure)
                .AppendFormat("Source: {0}\n", error.Source)
                .AppendFormat("LineNumber: {0}\n", error.LineNumber)
                .AppendFormat("Server: {0}\n", error.Server)
                .AppendLine(new string('-',error.Message.Length+7));

        }
        return sqlErrorMessages;
    }

The Generated message look like:

 Sql Exception:
 Mesage: Error converting data type nvarchar to datetime.
 Severity level: 16
 State: 5
 Number: 8114
 Procedure: Sales by Year
 Source: .Net SqlClient Data Provider
 LineNumber: 0
 Server: myserver
 -------------------------------------------------------

Standee