How to catch SQLException in C#?

Here i tried to catch the sql exception using innerexception in SMO Ojects.In Sqlserver i am getting this error message

Msg 121, Level 15, State 1, Procedure test_sp_syntaxtext, Line 124
The select list for the INSERT statement contains more it开发者_如何学JAVAems than the insert list.

But by using this code

catch (Exception ex)
          {
           ErrorFlag = true;
           //ex.Source.ToString();
           e2dInsertAndGenerateErrorLog(ex.InnerException.Message.ToString(), FileName, "CHECKINAPPLY", PathName, "ErrorLog.Err");
          }

i am getting this

The select list for the INSERT statement contains more items than the insert list.

I need to cache this line also

Msg 121, Level 15, State 1, Procedure test_sp_syntaxtext, Line 124

Any Suggestion?

EDIT:

  StreamReader str = new StreamReader(FiletextBox.Text.ToString());
  string script = str.ReadToEnd();
  str.Dispose();
  SqlConnection conn = new SqlConnection("Data Source=xx;database=xxx;User id=sx;Password=xxxx");
  Server server = new Server(new ServerConnection(conn));
  server.ConnectionContext.ExecuteNonQuery(script);

---

Use SQLException.

    catch (SqlException ex)
    {
        for (int i = 0; i < ex.Errors.Count; i++)
        {
            errorMessages.Append("Index #" + i + "\n" +
                "Message: " + ex.Errors[i].Message + "\n" +
                "LineNumber: " + ex.Errors[i].LineNumber + "\n" +
                "Source: " + ex.Errors[i].Source + "\n" +
                "Procedure: " + ex.Errors[i].Procedure + "\n");
        }
        Console.WriteLine(errorMessages.ToString());
    }

---

catch (SqlException ex) { ... }

---

Take a look at the properties you find at http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlexception.aspx

You will need to catch the SqlException instead of just any exception.

---

When logging an exception it's a good idea to log the entire thing, especially the stacktrace. The stacktrace is the most important part. If you just log ex.ToString() you won't be discarding that information.

---

Try

catch (SqlException ex) { } instead of catch (Exception ex) { }.

Have a look at MSDN information about SqlException.

---

Once again I am very late, but none of the answer looks right to me. OP context is SMO. With SMO, SqlException can be encapsulated as inner exceptions of ExecutionFailureException. That is why he is looking at InnerException.

Here is the solution I am using for logging SqlException details if present (with Logger being a log4net ILog).

Catch block:

catch (Exception ex)
{
    Logger.Error("Unhandled error", ex);
    LogSqlErrors(ex);
}

LogSqlErrors (note the last line, this is what takes care of inner exception):

private static void LogSqlErrors(Exception ex)
{
    if (ex == null)
        return;

    var sqlEx = ex as SqlException;
    if (sqlEx != null && sqlEx.Errors != null && sqlEx.Errors.Count > 0)
    {
        var sqlErrs = new StringBuilder();
        sqlErrs.AppendFormat("SqlException contains {0} SQL error(s):", sqlEx.Errors.Count)
            .AppendLine();
        foreach (SqlError err in sqlEx.Errors)
        {
            sqlErrs.AppendLine("--------------------------------")
                .AppendFormat("Msg {0}, Level {1}, State {2}, Procedure '{4}', Line {5}",
                    err.Number, err.Class, err.State, err.Procedure, err.LineNumber)
                .AppendLine()
                .AppendLine(err.Message);
        }
        Logger.Warn(sqlErrs.ToString());
    }

    LogSqlErrors(ex.InnerException);
}

Unfortunately, if your sql commands string contains many batches (using go separators, as supported by SMO), this will not tell you which sql batch yields the error. (The error line number is computed for the failing batch, without taking into account lines count of previous batches.)

I have checked the way ExecutionFailureException are built (using a decompiler on SMO version 12): it could have stored this info, but it does not. So you can only guess.

Moreover, if you use the ContinueOnError option, errors are swallowed (not stored anywhere). Well, it would be great if SMO team could improve exception handling in their library.