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

This question already has answers here: Closed 12 years ago.

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. "SELEC开发者_StackOverflowT * 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.

---

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.

---

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.

---

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.

---

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