Ado.net Fill method not throwing error on running a Stored Procedure that does not exist.

Question

I am using a combination of the Enterprise library and the original Fill method of ADO. This is because I need to open and close the command connection myself as I am capture the event Info Message

Here is my code so far

        // Set Up Command 
        SqlDatabase db = new SqlDatabase(ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString);
        SqlCommand command = db.GetStoredProcCommand(StoredProcName) as SqlCommand;
        command.Connection = db.CreateConnection() as SqlConnection;

        // Set Up Events for Logging
        command.StatementCompleted += new StatementCompletedEventHandler(command_StatementCompleted);
        command.Connection.FireInfoMessageEventOnUserErrors = true;
        command.Connection.InfoMessage += new SqlInfoMessageEventHandler(Connection_InfoMessage);

        // Add Parameters
        foreach (Parameter parameter in Parameters)
        {
            db.AddInParameter(command, 
                parameter.Name, 
                (System.Data.DbType)Enum.Parse(typeof(System.Data.DbType), parameter.Type), 
                parameter.Value);
        }

            // Use the Old Style fill to keep the connection Open througout the population
            // and manage the Statement Complete and InfoMessage events
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();

            // Open Connection
            command.Connection.Open();

            // Populate
            da.Fill(ds);

            // Dispose of the adapter
            if (da != null)
            {
                da.Dispose();
            }

            // If you do not explicitly close the connection here, it will leak!  
            if (command.Connection.State == ConnectionState.Open)
            {
                command.Connection.Close();
            }

...

Now if I pass into the variable StoredProcName = "ThisProcDoesNotExists"

And run this peice of code. The CreateCommand nor da.Fill through an error message. Why is this. The only way I can tell it did not run was that it returns a dataset with 0 tables in it. But when investigating the error it is not appearant that the procedure does not exist.

EDIT Upon further investigation command.Connection.FireInfoMessageEventOnUserErrors = true; is causeing the error to be surpressed into the InfoMessage Event

From BOL

When you set FireInfoMessageEventOnUserErrors to true, errors that were previously treated as exceptions are now handled as InfoMessage events. All events fire immediately and are handled by the event handler. If is FireInfoMessageEventOnUserErrors is set to false, then InfoMessage events are handled at the end of the procedure.

What I want is each print statement from Sql to create a new log record. Setting this property to false combines it as one big string. So if I leave the property set to true, now the question is can I discern a print message from an Error

ANOTHER EDIT

So now I have the code so that the flag is set to true and checking the error number in the method

    void Connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
    {
        // These are not really errors unless the Number >0
        // if Number = 0 that is a print message
        foreach (SqlError sql in e.Errors)
        {
            if (sql.Number == 0)
            {
                Logger.WriteInfo("Sql Message",sql.Message);
            }
            else
            {

                // Whatever this was it was an error 
                throw new DataException(String.Format("Message={0},Line={1},Number={2},State{3}", sql.Message, sql.LineNumber, sql.Number, sql.State));
            }
        }
    }

The issue now that when I throw the error it does not bubble up to the statement that made the call or even the error handler that is above that. It just bombs out on that line

The populate looks like

            // Populate
            try
            {
                da.Fill(ds);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }

Now even though I see the calling codes and methods still in the Call Stack, this exception does not seem to bubble up?

Answer 1

I spent some time on this and came to the conclusion that the InfoMessageHandler is not raised within the scope of the executing command object. Therefore, exceptions that you throw within the event will not bubble up to command object's method. It must be executing in a different thread.

I assume you are using Visual Studio 2008, because I was able to reproduce your issue exactly in that environment. When I migrated the code to Visual Studio 2010, still using framework 3.5, the new IDE catches the custom exceptions, but I wasn't able to figure out an easy way to catch the exceptions in code. The Visual Studio 2010 debugger is much better at debugging multiple threads.

If you want to catch exceptions from this event, you will have to write code that can track thread exceptions.