Ado.net Fill method not throwing error on running a Stored Procedure that does not exist.
- by Mike
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?
