SQL Server to .Net error handling
-
Hi. A general question I wanted to ask someone. Thought here would be a good place. For Stored Procedures (SP's) in a SQL Server Database would you consider it acceptable to leave any error handling to the calling code (i.e. caught in an exception handler in the .Net code), and just check the number of records affected in the .Net code too, for extra validation. Rather than, handling errors in the SP's and returning a value indicating things like no. of records affected and whether an error occurred whilst trying to execute the procedure. I'd like to keep the SP's as simple as possible, and handle problems at the code end as it seems easier to do that. But not sure if we're missing a trick with any error handling by the SP's? Hope I've made myself clear. Thoughts? Thanks J
-
Hi. A general question I wanted to ask someone. Thought here would be a good place. For Stored Procedures (SP's) in a SQL Server Database would you consider it acceptable to leave any error handling to the calling code (i.e. caught in an exception handler in the .Net code), and just check the number of records affected in the .Net code too, for extra validation. Rather than, handling errors in the SP's and returning a value indicating things like no. of records affected and whether an error occurred whilst trying to execute the procedure. I'd like to keep the SP's as simple as possible, and handle problems at the code end as it seems easier to do that. But not sure if we're missing a trick with any error handling by the SP's? Hope I've made myself clear. Thoughts? Thanks J
-
Yes, that did cross my mind. Fortunately, I don't think that's ever going to happen.
-
Hi. A general question I wanted to ask someone. Thought here would be a good place. For Stored Procedures (SP's) in a SQL Server Database would you consider it acceptable to leave any error handling to the calling code (i.e. caught in an exception handler in the .Net code), and just check the number of records affected in the .Net code too, for extra validation. Rather than, handling errors in the SP's and returning a value indicating things like no. of records affected and whether an error occurred whilst trying to execute the procedure. I'd like to keep the SP's as simple as possible, and handle problems at the code end as it seems easier to do that. But not sure if we're missing a trick with any error handling by the SP's? Hope I've made myself clear. Thoughts? Thanks J
An error handler in the calling code is better IMHO. That way, nobody can forget to check the return value to see whether or not the call succeeded. However, if your SQL code is using a resource that needs to be cleaned up (a cursor, an ActiveX object, a prepared XML document, an app-lock, etc.), then it should include code to clean up after itself before it exits. Unfortunately, SQL's
TRY...CATCHblock doesn't include aFINALLYclause, so you'll need to duplicate the cleanup code in both blocks.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
An error handler in the calling code is better IMHO. That way, nobody can forget to check the return value to see whether or not the call succeeded. However, if your SQL code is using a resource that needs to be cleaned up (a cursor, an ActiveX object, a prepared XML document, an app-lock, etc.), then it should include code to clean up after itself before it exits. Unfortunately, SQL's
TRY...CATCHblock doesn't include aFINALLYclause, so you'll need to duplicate the cleanup code in both blocks.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thanks Richard. Glad you agree. We have kept the Stored Procedures fairly straightfoward, so no worries on the tidy up front. Wil certainly take that on board for future development though. Thanks. Julian
-
Yes, that did cross my mind. Fortunately, I don't think that's ever going to happen.
No webservices planned, no API, no addins? Then it still depends on how the database is going to be used. Most of the problems can be blocked using simple constructions, and most of it will require some handling at a higher level. Prohibiting the insertion of an order-line without an existing order is easily done by defining keys and a relation. Cascading deletes are dandy if you want to delete all order-lines if an order is deleted. That is assuming you allow access to the tables. It can be beneficial to remove that access and only allow stored procedures. In that case, you probably want to have them "succeed" or "fail" as a single atomic operation, inside a transaction that is either commited or rolled back. Have a whiteboard? Draw a large T, title the left column "advantages" and the right one "disadvantages" :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
No webservices planned, no API, no addins? Then it still depends on how the database is going to be used. Most of the problems can be blocked using simple constructions, and most of it will require some handling at a higher level. Prohibiting the insertion of an order-line without an existing order is easily done by defining keys and a relation. Cascading deletes are dandy if you want to delete all order-lines if an order is deleted. That is assuming you allow access to the tables. It can be beneficial to remove that access and only allow stored procedures. In that case, you probably want to have them "succeed" or "fail" as a single atomic operation, inside a transaction that is either commited or rolled back. Have a whiteboard? Draw a large T, title the left column "advantages" and the right one "disadvantages" :)
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Well, yes we do already use the tables from a web service as well as the website itself, using the same DAL DLLs that throw up any exception to the calling service, which handles the failure accordingly. We are in control of all this and any furture development. Thinking about, I think I need to double check the SQL transactions we are using, which aren't many, for their return status and how we're handling those, and I'm just writing a function to check the record count from an update/insert or delete, so that that can be handled at code level. Thanks Eddy for sparking some thoughts in my head...
-
Hi. A general question I wanted to ask someone. Thought here would be a good place. For Stored Procedures (SP's) in a SQL Server Database would you consider it acceptable to leave any error handling to the calling code (i.e. caught in an exception handler in the .Net code), and just check the number of records affected in the .Net code too, for extra validation. Rather than, handling errors in the SP's and returning a value indicating things like no. of records affected and whether an error occurred whilst trying to execute the procedure. I'd like to keep the SP's as simple as possible, and handle problems at the code end as it seems easier to do that. But not sure if we're missing a trick with any error handling by the SP's? Hope I've made myself clear. Thoughts? Thanks J
julian@giant wrote:
acceptable to leave any error handling to the calling code
No. Not in anything non-trivial. I always write database layers. That layer should handle 'sql' errors. That layer should also be constructed in such a way that it is mindful of potential system errors. For example a user client application might want to tell the user that the server 'was down' by catching appropriate SQL Exceptions and determining that. For certain errors it should log information and then tell the user to 'contact an administrator'. This layer would also be unit tested independent from the rest of the application (regardless what sort of app it is.)
-
julian@giant wrote:
acceptable to leave any error handling to the calling code
No. Not in anything non-trivial. I always write database layers. That layer should handle 'sql' errors. That layer should also be constructed in such a way that it is mindful of potential system errors. For example a user client application might want to tell the user that the server 'was down' by catching appropriate SQL Exceptions and determining that. For certain errors it should log information and then tell the user to 'contact an administrator'. This layer would also be unit tested independent from the rest of the application (regardless what sort of app it is.)
Thanks J. The more people comment here, the more confortable I feel about our current approach. Albeit not perfect, I'm pretty sure we're in the right direction. And yes, we're upping the Unit Tests lately, which I've banged on about loads, and 'time' is finally being properly allocated to it from the 'time' people.... Thanks J
