Sql Adapter, RAISERROR, TRY...CATCH and exceptions

Hi,
I am having a problem with the interaction between ADO.Net 2 and SQL Server 2005.

I have a typed dataset with a designer generated table adapter that wraps a SqlDataAdapter, configured to use custom stored procs for insert and update commands.

The stored procs are in SQL Server 2005 and I am using the new TRY...CATCH syntax. The stored proc sets NoCount On and in the catch block, I use RAISERROR with a severity of 16, immediately followed by a RETURN statement with no return value. If I run the stored proc in ISQL, I can see the error message from the RAISERROR.

Since there are multiple tables to update, the C# code begins a transaction on the connection and sets the transaction property on the SqlDataAdapter Insert and Update command objects.

The problem is that when an error is raised in the stored proc, no exception is thrown in the C# code that calls the adapter Update method.

I want to roll back the transaction because there has been an error but I don't get an exception.

The Row Updated event fires and I can't see a way of checking that an error has occurred. The event argument properties are:

Records Affected: -1
RowCount: 1
Status: Continue
Errros: null

Does anyone have any ideas why ADO.Net 2 does not throw an exception

I don't know if there is something special that I need to do in the stored proc to get ADO.Net to

Answer this question

Sql Adapter, RAISERROR, TRY...CATCH and exceptions

Zac Boyles
Hi Graham,

See http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=787232&SiteID=1 - it appears that any ADO.Net call that uses ExecuteScalar instead of ExecuteNonQuery will result in the behavior you describe.

I'm not sure about ExecuteReader, which is I'm sure what a SqlDataAdapter uses to populate a typed dataset. I think you might have to drain the reader of all its result sets to get to the error bits, and I'm not sure whether the SqlDataAdapter does this or not.

Hope that helps.
vlajko
Hi Jian,

I have just restarted investigating this issue and I have observed the following behaviour:

The SqlDataAdapter does not throw an exception if the stored proc executes a SELECT statement that returns a result set (including a result set with no rows) before it executes the RAISERROR.

If I modify the stored proc execution to be conditional on an error check, so that either the SELECT statement is executed or a RAISERROR is executed, then the SqlDataAdapter does throw an exception when the RAISERROR occurs.

The behaviour appears to be that if result set(s) are returned from the stored proc, the SqlDataAdapter does not subsequently check for RAISERROR conditions.

I have therefore resolved my specific problem by restructuring my stored procs to take this into account, but I am interested to know if others have observed the same behaviour and if it is by design

As this has a significant effect on the way I am now structuring my stored procs, any further information would be of great interest.

I hope this is helpful to others.

Regards

Graham
Rob26
Hi Graham,

Did you get your problem solved I tried the scenario that you described and I can get the exception of the Raiserror to throw in ADO.NET. Is it possible for you to post a code snippet that contains this problem

Thanks,
Jian
Sql Adapter, RAISERROR, TRY...CATCH and exceptions

Answer this question