How to catch controlled error from inside the Update Stored Procedure from SQL Server.

How to catch controlled error from inside the Update Stored Procedure from SQL Server.

Old forum URL: forums.lhotka.net/forums/t/10754.aspx


tarekahf posted on Monday, October 10, 2011

I am using the same logic of CSLA .NET for VS 2005 (from the book), to implement the Update Stored Procedure and to prevent simultaneous updates to the same record by different users.

I am really happy that this logic is working fine, and Yesterday, I received the first report of such case after I deployed the Staff Performance Management System Form (SPMS) a month ago. See a copy of the report below:

https://docs.google.com/viewer?a=v&pid=explorer&chrome=true&srcid=0B97clwYte2SHYjRiZTUzMWUtNzM2YS00MmZmLWI1MDEtNzViYTM3YWVhZDQy&hl=en_GB

However, the error message is very huge.

I have the following questions:

1. Why the message with Stack Trace is that very long ?

2. I am able to control other errors (Validation for example), and display only the Error Message without the full StackTrace. But for this error, I am not sure how to identify this specific error and diaply a friendly message instead of that very long one. How I can catch this very specific error ?

Tarek.

JonnyBee replied on Monday, October 10, 2011

1. The stacktrace show the number of levels where the execption has been handled and rehtrown.

2. A nicer way is to catch the SqlException in your DAL code and throw your own serializable exception with just a nicer message.

You can actually add this code in just on place, look at the sample Samples\NET\cs\CustomErrorHandling.

 

tarekahf replied on Monday, October 10, 2011

(Note: Why I am unable to add Rich Text Editing to this forum posts ?)

Thanks Jonny,

I was checking the samples, but seems that there are not such samples for VS 2005 and VB, right ?

I appreciate it if you could point me to more specific example of how to do that "add error control on just one place", in the context of VS 2005 and VB.

Or, jsut to go for the simple way, how I can catch that specific error whic I am throwing form SQL Server as follows:


USE [SPMSFormDB]
GO
/****** Object:  StoredProcedure [dbo].[uspSPMSFormUpdate]    Script Date: 10/10/2011 11:16:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspSPMSFormUpdate]
 (
  @StaffID varchar(10),
  @ReviewFrom varchar(7),
  @ReviewFromNew varchar(7),
  @ReviewTo varchar(7),
  @StaffNameEng nvarchar(150),
  @FormFileName varchar(25),
  @StsCde varchar(25) = null,
  @StsUpdBy varchar(50) = null,
  @StsUpdTime datetime = null,
  @lastChanged timestamp = null,
  @SubmitAction varchar(25) = null,
  @newLastChanged timestamp output
 )
AS
begin try

 declare @MSG varchar(200)
 update SPMSForm
 set
  ReviewFrom = @ReviewFromNew,
  ReviewTo = @ReviewTo,
  StaffNameEng = @StaffNameEng,
  FormFileName = @FormFileName,
  StsCde = @StsCde,
  StsUpdBy = @StsUpdBy,
  StsUpdTime = @StsUpdTime,
  SubmitAction = @SubmitAction
 where
  StaffID = @StaffID and
  ReviewFrom = @ReviewFrom and
  LastChanged = @lastChanged
 if @@ROWCOUNT = 0
 begin
  set @MSG = 'SPMS Form Update Failed for Staff ID ''' +  @StaffID + '''. Record does not exist or it has been changed by someone.'
  RAISERROR(@MSG, 16, 1)             
 end
 
 SELECT @newLastChanged = LastChanged
 FROM SPMSForm WHERE StaffID = @StaffID
 /*
  To Display the last timestamp used in the Database
  SELECT [master].[dbo].fn_sqlvarbasetostr(@@DBTS)
 */
end try
begin catch
 EXEC uspRethrowError;
end catch


Thanks,

Tarek.

vschaak replied on Monday, October 10, 2011

Hi Tarek,

without beeing an absolute CSLA-Pro like Jonny (or others) I'd suggest something like

TRY

[Data access code]

CATCH ex as DataError [this has to be of the kind your DB-Provider throws]

THROW new myError("Entity has been changed by someone else")

END TRY

put into you DP-methods. I'd even strongly recommend not to throw such messages to the user/UI, since it uncovers probably sensitive information about your DB.

Let us know if it works for you.

HTH

Volker

tarekahf replied on Tuesday, October 11, 2011

Thanks Volker, I think I understand what you mean. But, let me clarify the difficulty I am facing to implement this. There are 2 types of errors expected from the Update SQL Stored Procedure: 1. The error coming form the THROW statement in case someone else updated the record since last time it was cached. 2. Any other error: Unexpected errors. My question, How I can throw a nice ERROR to the user only and only in case the error is thrown due to simultaneous update issue ? Also, in case there are other unexpected errors, I think it is good to thorw the Error Message with a complete Stack Trace, becuasem as per my experience, if I do not get a full stack trace, then it will be nearly impossible to know where the error occured exactly, as you knwo, this is the case especially I am using CSLA. You have indicated that you do not recommed to throw such messages to the UI, but, how I can debug the error, when reported by the user ? In this case, if there is unexpected error, I need to know where it occurred in the code ? What do you do in this case ? Tarek.

JonnyBee replied on Tuesday, October 11, 2011

RAISERROR can return either:

RAISERROR can also:

Is a user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

So you should be able to return a specific error number that you can test for in you exception handler to determin it is a user message (>49999) or a system error.

http://msdn.microsoft.com/en-us/library/ms177497.aspx

http://msdn.microsoft.com/en-us/library/ms178592.aspx

tarekahf replied on Tuesday, October 11, 2011

Thanks to all who helped.... all your feedabck was truely useful ! Check the attached image, where I highlighted all the areas I changed, and the result: https://docs.google.com/leaf?id=0B97clwYte2SHY2FlNjdlZmYtMWQ4Yi00YTQyLTllMTctM2VjNjk2OTA5NzBj&hl=en_GB (but still not sure why I am unable to get Ritch Text Editing in this forum ?!) Thank you again. Tarek.

StefanCop replied on Monday, October 17, 2011

> but still not sure why I am unable to get Ritch Text Editing in this forum

In your profile there's an option (site options / content editor). I have "enhanced" set.

tarekahf replied on Monday, October 17, 2011

Oh YES! .. it is working now Cool ...

Thanks

Tarek.

tiago replied on Monday, October 17, 2011

StefanCop

> but still not sure why I am unable to get Ritch Text Editing in this forum

In your profile there's an option (site options / content editor). I have "enhanced" set.

This is Off Topic but I also had the same issue a few weeks ago. Furthermore I couldn't edit my own messsges as the menu wasn't showing up (the popup menu under "More"). The rich text issue went away by itself (meanig I don't know how I did it) but the later issue persisted until I changed to a new computer. This problem did show up on Internet Explorer and Chrome. So it didn't seem like a browser configuration issue. On the new computer I have no issues under IE or Chrome. So the mystery remains...

tarekahf replied on Monday, October 17, 2011

Try the following with IE:

1. Reset Advanced Settings,

2. Reset IE Settings,

3. Delete Browser Cache,

4. Play around with Compatibility Mode Settings.

5. Remove and reinstall IE.

I am nearly 99.987% sure that one of the above will solve the problem.

Tarek.

vschaak replied on Tuesday, October 11, 2011

Hi Tarek,

nice to have been of some help.

Of course this is some sort of dilemna. On one hand, we want to get as much exception-info as possible and on the other hand, we want to hide as much of the inside of our apps. The second aspect surely becomes more and more relevant as your app is used by a greater number of users and if they are, at least to a certain degree, anonym users.

Since SQL-injection is a major entry door for c/s apps and can be for n-tier apps, if not properly handled, I tend to not publish any DB-details not server-/instance name (try to type a wrong PWD in your connection string and examine the exception details-> the username is published!) nor credentials or names of tables, views. sprocs etc.. (Sprocs can help adding another layer of abstraction, so no table-access is needed for the apps DB-user, thereby adding another barrier) For most DB-related exceptions, your DAL could probably be the best place to handle them... An exception may read "Error while connecting to the database" or "DB-Connection data incorrect" but not "Error connecting user myDBUser"
Also, I think handling CSLA as some sort of "black box", so you don't need to publish the way your data is routed through the framework, is a good thing.

And of course testing, testing, testing (a process I have to learn, too)

Best wishes from germany

Volker

Copyright (c) Marimer LLC