A developer at work queried me to why RaiseError generates an exception when working with a DataSet but not when working with an IDataReader. Here is a short code fragement that demonstrates the problem.
using System;
using System.Data;
using System.Data.SqlClient;
class TestRaiseError
{
static void Main(string[] args)
{
SqlConnection connection = new SqlConnection("server=myServer;database=northwind;uid=sa;pwd=password");
SqlCommand command = new SqlCommand("select * from Employees RAISERROR ('raise an error', 16, 1)", connection);
IDataReader reader = null;
try
{
connection.Open();
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetString(1));
}
}
finally
{
if (reader != null)
{
reader.Close();
}
if (connection != null)
{
connection.Close();
}
}
}
}
If you compile and run the code fragment you’ll see no exception is thrown yet the sql statement being executed contains a raise error. If the program was rewritten to use a DataSet and a SqlDataAdpater then an exception would be thrown.
This behaviour may seem a little strange but it is by design. It is very important to remember that the an IDataReader represents an active connection to the database and records from the database will be retrieved in turn where as a dataset grabs all results from a query and places them in the clients memory.
It is possible to return a number of different results sets from a stored procedure, when using an IDataReader these are accessed using .NextResult(). Interestingly the ADO.NET client treats RaiseError as separate results set and it is this that can lead to slightly surprising results. This means that if you have a stored procedure something like:
SELECT * FROM myTable
RAISERROR ('raise an error', 16, 1)
Then it is possible to read all the contents of myTable with out ever seeing the exception because you need to call .NextResult() to see the exception that is raised.
As the DbDataAdapter that fills the DataSet is written to enumerate over all results sets from the query so the exception from the RaiseError is picked up.
I’ve create a small winfoms application which demonstrates all the situations described in here. It is available here.