How can I get the actual SQL that caused an SqlException in C#? [duplicate]

Question

Possible Duplicate:
Obtain the Query/CommandText that caused a SQLException

I am working on some error handling code (using elmah) and the default setup only sends the error message. I would like to know the actual SQL that throws an error (i.e. "SELECT * FROM thisTableDoesNotExist")

This is what I have so far:

if (e.Error.Exception is SqlException)
{
    //if SQL exception try to give some extra information
    SqlException sqlEx = e.Error.Exception as SqlException;
    e.Mail.Body = e.Mail.Body + "<div>" +
                                "<h1>SQL EXCEPTION</h1>" +
                                "<b>Message</b>: " + sqlEx.Message +
                                "<br/><b>LineNumber:</b> " + sqlEx.LineNumber + 
                                "<br/><b>Source:</b> " + sqlEx.Source +
                                "<br/><b>Procedure:</b> " + sqlEx.Procedure +
                                "</div>";
}

And I would like to be able to also show the actual SQL. The database is SQL Server 2008 and SqlException is of type System.Data.SqlClient.SqlException.

Charentemaritime

Answer 1

Not possible. You'll need to catch the exception where the SQL command was executed, and then include your command text in your own custom exception. See Obtain the Query/CommandText that caused a SQLException.

Warlord

you might as well include the Code in the referenced answer – Unpleasant

Doesn't this make it a duplicate of that question, then? – Decanter

Answer 2

The best way to examine the exception is to put a breakpoint in your code where the exception happens and examine the values of the exception object graph.

Try the Message member of the InnerException like this:

sqlEx.InnerException.Message

It may not provide the exact SQL that failed but may give you more specific information such as the operation and the table name. The StackTrace member may also have some information.

Stoll

Answer 3

You could have error handling code in your SQL, and when it encounters an error, you can send back the SQL that it attempted to run with a print or returns statement or however you want to return it to your application.

Circular

Best solution to the problem. The other answers are true, but this is a legitimate (not hacky) workaround. – Truck

Answer 4

If you can't get enough information on C# side you can use "SQL profiler" (part of complete MS SQL) to see what commands where executed.

Information on SQL Profiler http://msdn.microsoft.com/en-us/library/ms181091.aspx . You should also be able to use underlying Tracing API if you don't have profiler - http://msdn.microsoft.com/en-us/library/ms191006.aspx

Mahogany

SQLExceptions can be thrown by other data providers besides MS-SQL. – Warlord