For Asp.net web applications, is it best to:
What are the best practises here?
I use try/catch on all potentially error generating calls to libraries or other servers including database queries. I'm primarily a developer not a DBA so I handle the error in the language that I'm most proficient in, C# not SQL. I'm sure that'll vary for every programmer. Not handling the error is never fine in my book.
I prefer both -
in general, I always make db calls inside a try-catch
According this article, this is a type of "boneheaded" exception - i.e. if an error occurs in the SP, this means that there's something wrong with the SP or the data itself.
My advise would be to trap the error in aps.net, as there you have much more possibilities to log the error, as well as all the parameters passed to the SP in order to investigate the problem.
The most likely failure mode in the data layer is bad user input vales such as requesting a record that doesn't exist. Most other errors are caused by defective code and usually are caught right away when testing. I like to catch the db error and and always throw a custom error with more information about the data and context of the request. Then the error bubbles back up the call stack and if it was a user correctable error I can alert them to that fact. Otherwise the central error page handler for the application will be called when the error reaches the top of the stack. The worst thing to do is to catch an error and not let it bubble back up. Return codes are outdated constructs unless talking to com components or foreign systems.
Ideally, your web application isn't aware of the back-end or data access. So it shouldn't be handling sql-related errors - those should be handled by the data access layer. But the web application needs to handle failure gracefully, by providing the end-user with a friendly message.
A general rule that applies here is to catch the error as near the source as possible. SQL Server now has "try ... catch ..." error trapping syntax. So use it. The overhead of the little bit of extra code is insignificant, and if you have multiple statements in your SP, you can adapt the string in RAISERROR to help localize the problem.
In the interface, it shouldn't be difficult to trap the SP error event and handle it the same way you handle other error trapping in your procedural code.
This is one of the more neglected "best practices" in stored procedures, and it's even more important than in "regular" code because it's trickier to use a step=through debugger.
One useful pattern is to handle this in your SP the same way you it expect it to be handled in any other opaque SDK library.
I would say it depends on what you are doing in your stored proc and what you want to do with certain errors that occur. Sometimes I handle it in the sp but other times i let it raise up to the data layer code.
Bear in mind, sometimes you can miss errors when using sql server 2005 try/catch, see my post on this. Whereas, in code (C# in my case) you can access all the errors in the SqlErrorCollection object.
Just be absolutely certain that your error handling in the stored procedure is well thought out and any uncertainties are left to be passed up to the data layer code where you must log everything.
The answer is that you need to do both. Some errors are actually triggered downline from the Database engine. Their source varies, of course, according to the actual way you connect to the database (OLBC, OLEDB etc). You've got to find a way of dealing with these. Some errors such as Deadlock errors out to be handled at the application level too.
As well as errors, it is a good idea to receive and deal with messages from the SQL Server Database Engine. These are very similar to errors and can give the application a lot of useful diagnostic information. If you’re using System.Data.SQLClient, you’ll need to create a SqlInfoMessageEventHandler delegate, identifying the method that handles the event, to listen for the InfoMessage event on the SqlConnection class. You’ll find that message-context information such as severity and state are passed as arguments to the callback, because from the system perspective, these messages are just like errors. I hope this helps!