SQL Server 2005 Error Handling vs C# Error handling

[teixeira]

Hello,

 

I've some doubts about error handling in sql server 2005.

I'm confused about what to do, for example: should i use BEGIN TRANSACTION ... BEGIN TRY...END TRY BEGIN CATCH... END CATCH.....COMIT ...ROLLBACK... END inside the procedure and i get my sql execution prevented from any exception, but the SqlClient Connector doesn't know what happen, or should i have no error handling inside the procedure, and make the transaction inside my C# code, using SQlTransaction Class??

 

what is the best practice/advice about this, to catch all the errors, commit or roolback actions and have that information available at SqlClient Connector level in my client application ?

 

TIA,

Tiago Teixeira

[PlausiblyDamp]

Rather than any absolute rules I find this tends to be a case of use what works best in a given situation...

 

I am (currently anyway - the new Linq-SQL stuff could very well change my mind) a big fan of doing as much of the data access as possible within stored procedures on the database; this pretty much means I would be using the BEGIN TRY ... END TRY stuff on the SQL end for as much error handling as possible.

 

Similarly I try to keep as much of the transactional stuff within the stored procedures as well, very rarely do I find myself using the .BeginTransaction method within .Net.

 

In terms of how I handle errors I take pretty much the same approach as I would with .Net code; if the stored proc can catch and handle the error then there is no need to pass the error back and SQL can swallow the error. If the SQL error isn't something that can be safely handled / can be handled but the error needs to be passed up then I will get the stored proc to do as much of the error handling as makes sense but will then either re-throw the original error or raise a new application specific error that I an then trap within the .Net side of things.

 

Note that I use these as a guideline rather than fixed rules - some situations may require things to be done differently (performance, security, required behaviour can all influence this) but I try to stick to these ideas as much as possible.

[teixeira]

PlausiblyDamp thanks,

 

"Unconscientiously", i follow the same guidelines as you, i treat as much as i can the exception in my .net code, because its more quick, and i prefer control all in one place.

 

But for instance, imagine that i create a stored procedure and an error occurs inside, how can i pass that info to my C# code?

 

Normally it raises the error but my .NET code isn't notified.

 

Any idea how to pass back the error indication to my code in the client application?

 

if i use a simple sql command from my net code it raises the error if any, but not with Store Procedures.

 

Tiago Teixeira

[PlausiblyDamp]

Within your stored proc you can just use the RAISERROR command - the error will be passed up to .Net as long as you aren't clearing the @@error variable anywhere in your SQL code.

 

Once the error hits .Net it will be a SqlException you can handle with a normal try ... catch block.

[teixeira]

Great!

 

Thanks, it was that explanation i needed.

 

Best regards,

Tiago Teixeira