Fill(DataTable) does not throw SqlClient exceptions

[Russell Smith]

I have a terrible problem trapping sqlclient exceptions when filling
datatables using stored procedures. I wrote a simple test case that shows
filling a dataset throws exceptions fine, but filling a datatable will
ignore exceptions. I use the "Ten Most Expensive Products" stored procedure
in the Northwind database, with a minor change to raise an error at the end.
When useDataSet is true, an exception is thrown. When false, no exception.
Test case code and exception shown below. I am using .NET Framework 1.1 SP1
and SQL Server 2000 SP3. Obviously it is important to figure out how to make
sure exceptions are not ignored. Please help.

raiserror('Injected error to throw a SqlClient exception', 16, 1)

private void TestFill(bool useDataSet)
{
const string connectionString = @"Server=swift;Initial
Catalog=Northwind;Integrated Security=true";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
if (useDataSet)
{
DataSet dataSet = new DataSet();
da.Fill(dataSet); // this throws the exception correctly
}
else
{
DataTable dataTable = new DataTable();
da.Fill(dataTable); // this ignores the exception
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString(), Application.ProductName,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}

System.Data.SqlClient.SqlException: Injected error to throw a SqlClient
exception
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.SqlClient.SqlDataReader.NextResult()
at System.Data.Common.DbDataAdapter.FillNextResult(IDataReader
dataReader)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at TestConnect.MainForm.TestFill(Boolean useDataSet) in c:\documents and
settings\batman\my documents\visual studio
projects\test\testconnect\mainform.cs:line 192
The program '[2668] TestConnect.exe' has exited with code 0 (0x0).

 

[Russell Smith]

Here is what I think is going on. Just a guess.

1. SQL Server SP's return multiple result sets.
2. When the SP fills a dataset, a datatable within the dataset is created
for each result set.
3. When the SP fills a datatable, the datatable is filled using the first
result set.
4. When the SP fills a datatable, processing of SP results stop after the
first result set is consumed.
5. So the error is never detected , and no exception is raised.

If this is accurate, SQL Server error handling is not very sophisticated.

I have a terrible problem trapping sqlclient exceptions when filling
datatables using stored procedures. I wrote a simple test case that shows
filling a dataset throws exceptions fine, but filling a datatable will
ignore exceptions. I use the "Ten Most Expensive Products" stored procedure
in the Northwind database, with a minor change to raise an error at the
end. When useDataSet is true, an exception is thrown. When false, no
exception. Test case code and exception shown below. I am using .NET
Framework 1.1 SP1 and SQL Server 2000 SP3. Obviously it is important to
figure out how to make sure exceptions are not ignored. Please help.

raiserror('Injected error to throw a SqlClient exception', 16, 1)

private void TestFill(bool useDataSet)
{
const string connectionString = @"Server=swift;Initial
Catalog=Northwind;Integrated Security=true";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("Ten Most Expensive Products", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
try
{
if (useDataSet)
{
DataSet dataSet = new DataSet();
da.Fill(dataSet); // this throws the exception correctly
}
else
{
DataTable dataTable = new DataTable();
da.Fill(dataTable); // this ignores the exception
}
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString(), Application.ProductName,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}

System.Data.SqlClient.SqlException: Injected error to throw a SqlClient
exception
at System.Data.SqlClient.SqlDataReader.Read()
at System.Data.SqlClient.SqlDataReader.NextResult()
at System.Data.Common.DbDataAdapter.FillNextResult(IDataReader
dataReader)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at TestConnect.MainForm.TestFill(Boolean useDataSet) in c:\documents and
settings\batman\my documents\visual studio
projects\test\testconnect\mainform.cs:line 192
The program '[2668] TestConnect.exe' has exited with code 0 (0x0).