By Edhy Rijo - 11/14/2007
Hi,When trying to delete a parent record, the MS-SQL2005 throws an execption preventing the deletion of the record, which is just fine. Is there a way to trap this exception and display a more user friendly message? or is there something in SF tha will take care of this for us? Here is the Exception message: SqlException The DELETE statement conflicted with the REFERENCE constraint "Has_many". The conflict occurred in database "ATR", table "dbo.ServiceCalls", column 'FK_Customers'. The statement has been terminated. Source : .Net SqlClient Data Provider Stack Trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at MicroFour.StrataFrame.Data.DbDataSourceItem.InternalExecuteNonQuery(DbCommand Command, Boolean IsTransactional, String TransactionKey)
at MicroFour.StrataFrame.Data.DbDataSourceItem.ExecuteNonQuery(DbCommand Command, Boolean IsTransactional, String TransactionKey)
at MicroFour.StrataFrame.Data.DataLayer.DeleteByPrimaryKey(PrimaryKeyValue PKValue, DataRow Row)
at MicroFour.StrataFrame.Business.BusinessLayer.DeleteByPrimaryKey(PrimaryKeyValue PKValue)
at MicroFour.StrataFrame.Business.BusinessLayer.DeleteCurrentRow(Boolean CheckSecurity, Boolean OnlyMarkAsDeleted)
at ATR_System.frmCustomers.tsiDelete_Click(Object sender, EventArgs e) in E:\Visual Studio 2005\StrataFrame Projects\ATR Systems\ATR System with not security\ATR System\Forms\frmCustomers.vb:line 217
at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ToolStrip.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)
at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
|
By Paul Chase - 11/15/2007
Hi,You can set the AutoShowSavingErrors property to false and then handle the error saving event to display or suppress errors however you see fit.
|
By Edhy Rijo - 11/15/2007
Hi Paul,Thanks, but the ErrorSaving event is not fired when trying to delete a parent record. Any other idea?
|
By Greg McGuffey - 11/15/2007
One option is to sub-class business layer and override the OnBeforeDelete method, then have logic within it that would check for any problems (and possibly fix them). Then have your BOs inherit from this sub-classed BO. Just be sure to call the base method, so the delete actually happens, events get raised.
Another options is to handle the BeforeDelete method in the BO and again do the data checking/fixing.
The first is best if you have lots of BOs that need the fix, the second if only one does. 
|
By Edhy Rijo - 11/16/2007
Hi Greg,I am testing a different approach using the BeforeDelete event of the form and going through the BO ChildBusinessObject's collection and if any of them have some record, then cancel the delete action and display my custom message. Here the code I am testing: Private Sub frmCustomers_BeforeDelete(ByVal e As MicroFour.StrataFrame.Business.BeforeDeleteEventArgs) Handles MyBase.BeforeDelete Dim Counter As Integer For Counter = 0 To e.BusinessObject.ChildBusinessObjects.Count If e.BusinessObject.ChildBusinessObjects.Item(Counter).Count > 0 Then e.Cancel = True MessageBox.Show( "To delete this Customer you must delete all Building Address records first.", "Record Delete Warning...", MessageBoxButtons.OK, MessageBoxIcon.Information) Exit For End If NextEnd Sub The only problem I see with this approach is that the Ask for Delete messagebox will always show when entering the BeforeDelete event. I also tried using the BeforeDelete event of the Business Object, but then I could not use the MessageBox.Show(), I guess this is now allow from a BO code.
|
By Jc Martinez - 11/16/2007
You could trigger a cascade delete on all related records on the SQL side and tell the user that all related records will be deleted and ask if OK to continue.... That way the user does not have to stop what he is doing and start a deleting process for all related records and come back and delete the parent record… At least that’s what I do in my apps and some times I even tell them how many related records will be deleted. This gives the user a chance to make a decision to continue or cancel the delete.
|
By Greg McGuffey - 11/16/2007
Related to using BeforeDelete Event
The delete confirmation is handled by the SF form (BaseForm actually). This can be turned off at the form level (so no SF delete conformation is used). See http://forum.strataframe.net/FindPost11030.aspx for some more info.
I believe the events would be raised:
- OnBeforeDelete of BusinessLayer raises BeforeDelete event of the BO
- The BaseForm (which the SF StandardForm inherits from, which you form inherits from) handles this event, does the delete confirmation and raises form's BeforeDelete event.
- Any handlers you have added to the BeforeDelete event get fired (like the one you posted)...so confirmation has already happened at this point.
- Any handlers of the form's BeforeDelete event fires.
I think another option to have more control over this would be to handle it in a sub-class of BusinessLayer, then override the OnBeforeDelete method. The you have control over when the BusinessLayer raises the events in the first place. You could also add you own event that could fire when the child records aren't already deleted, then handle that event to provide the message.
MessageBox not working form within a BO
I know of no reason why you couldn't use MessageBox within a BO....it's just a class like any other (including Form classes). You might want to verify that the code is ever called by stepping through it.
Referential Integerity and Cascading Deletes
I also usually use referential integrity with cascading deletes. To setup this, you would setup the building address table to have a Foreign Key to the customers table with ON DELETE CASCADE set. This enforces that no building addresses can be added that don't have valid customers and that when a customer is deleted, any associated building addresses are deleted as well.
When the db is setup this way, then I usually turn off the auto confirmation of delete messages for the form and provide my own, so I can remind the user that any building addresses will be deleted as well (JC's idea of presenting how many would be deleted is a nice touch). This type of setup has a couple of advantages:
- it ensures that the db doesn't have any data in it...period
- it is simpler to implement (setup FK on table, override delete message)
- typically faster, as the db handles checking referential integrity and the cascading deletes very fast
Hope that helps!
|
By StrataFrame Team - 11/19/2007
Edhy,Back to Paul's suggestion: You'll need to change the ErrorSavingMode to ContinueOnError before the ErrorSaving event will get fired. On Greg's suggestion, it will work great, but you'll want to set the AutoShowDeleteConfirmation to False and show your own delete confirmation if the user is allowed to delete the message (because there are no children).
|
By StrataFrame Team - 11/19/2007
The other option would of course, be to wrap the Save() call in a Try/Catch and test the SqlErrors collection on the InnerException of the thrown BusinessLayerException. I believe the foreign key validation will have a specific error type you can test on.
|
By Edhy Rijo - 11/19/2007
Hi Ben,Thanks for clarifications on this one. I decided to handle this in the BO's BeforeDelete as follow, if you have any suggestion on how to improve this code, it will be greatly appreciated.  ''' <summary>''' Validate the Service Call before trying to delete it.''' </summary>''' <param name="e"></param>''' <remarks></remarks>Private Sub ServiceCallsBO1_BeforeDelete(ByVal e As MicroFour.StrataFrame.Business.BeforeDeleteEventArgs) Handles ServiceCallsBO1.BeforeDelete Dim LoopCounter As Integer = 0 For Counter As Integer = 1 To e.BusinessObject.ChildBusinessObjects.Count If e.BusinessObject.ChildBusinessObjects.Item(LoopCounter).Count > 0 Then e.Cancel = True Me.AutoShowDeleteConfirmation = False MessageBox.Show( "To delete this Service Call you must delete all related records first.", "Record Delete Warning...", MessageBoxButtons.OK, MessageBoxIcon.Information) Exit For End If LoopCounter += 1 NextEnd Sub |
By Greg McGuffey - 11/19/2007
Edhy,
When you BeforeDelete event handler is called, the user has already confirmed or canceled the delete, so the line
Me.AutoShowDeleteConfirmation = False
is not going to do much at this point.
What you need to do is to set this in the designer for the form, the in your BeforeDelete, handle the confirmation for the user. So, the logic in you BeforeDelete would be:
- Check that all child objects are deleted (current logic, minus call to turn off AutoShowDeleteConfirmation)
- If any children found, show message (as your doing), cancel delete (as your doing), exit for, then exit handler
- If no children found, get confirmation from user that they want to do delete.
- If they cancel, cancel delete (e.cancel=true).
Hope that makes sense.
|
By Edhy Rijo - 11/19/2007
Hi Greg,I tried your suggestion, but the problem is that when entering into the form's BeforeDelete() event, the delete confirmation message is already shown, wich leads me to believe that this message is only displayed in the BO. I ended up creating a form's public var: Public CheckAutoShowDeleteConfirmation As Boolean = False
Then in the BO's BeforeDelete: Dim LoopCounter As Integer = 0For Counter As Integer = 1 To e.BusinessObject.ChildBusinessObjects.Count If e.BusinessObject.ChildBusinessObjects.Item(LoopCounter).Count > 0 Then e.Cancel = True Me.CheckAutoShowDeleteConfirmation = True Me.AutoShowDeleteConfirmation = False MessageBox.Show("To delete this Service Call you must delete all related records first.", "Record Delete Warning...", MessageBoxButtons.OK, MessageBoxIcon.Information) Exit For End If LoopCounter += 1 Next
Then in the Form's BeforeDelete: If Me.CheckAutoShowDeleteConfirmation = True Then Me.CheckAutoShowDeleteConfirmation = False Me.AutoShowDeleteConfirmation = TrueEnd If This way, the AutoShowDeleteConfirmation will work if the end user wants to delete a record with no children and will get the confirmation first.
|
By Larry Caylor - 11/19/2007
The approach I took to solving this issue was to subclass BusinessLayer. In my custom business layer I shamelessly lifted someone else's business rules code to create my own ConstraintRules. Whenever a delete is called on my business objects a CheckConstraintsOnCurrentRow event is raised that is handeled by CheckConstraintRules in my BO. Within the BO I have private methods that are used to check for possible constraint violations. If one is found I have a AddConstraintViolation method (which is very similar to AddBorkenRule ) that adds violations to the ConstraintViolation collection. The custom business layer fires a ConstraintViolation event if violations exist that contains the collection of violations which in turn contains the error (violation) message/s added by AddConstraintViolation method. I can either handle the ConstraintViolation event or the ConstraintViolation collection and present the user with a friendy message before SQL barfs out a not so friendly one. While I try to use cascading deletes where ever possible there are a number of situations where that doesn't work. Adapting the business rules approach has worked out well for me. -Larry
|
By Edhy Rijo - 11/19/2007
Hi Larry,Thanks for that information, I knew there should be a way to handle this with more control on the developer. Unfortunately, I am pretty new to .NET and VB and at this time I am not sure I would be able to do a class like that wihout any sample code. So far for this small project I think I can handle this situation with my previous posted code, but I really would like to see SF taking your approach and enhance the framework to deal with this situation directly.
|
By Greg McGuffey - 11/19/2007
Well, I'm not sure that the framework could handle this situation correctly. As has been mentioned, there are a bunch of ways to handle this (ensuring that children records are deleted before deleting a parent record), including setting up the tables so you never have to do this sort of check. There is no way for the framework to know what particular approach you'd take, so any arbitrary solution would be limiting. So, instead the framework does allow for special cases. In this case there are at least three choices:
- sub class BusinessLayer, override OnBeforeDelete, which offers the best control
- handle the BeforeDelete event of the form
- Handle the BeforeDelete event of the BO
- ...more I'm sure
The trick is to understand how things work enough to get the right solution for your needs. Unfortunately, that just takes time and what you are doing right now...asking lots of questions (I should know, I've asked about a million ). When I was starting out, I often felt the same...the SF framework should do _____ (fill in the blank). Often it was more I just didn't understand how easy all this subclassing makes things (plus all the other .NET goodness). I'm refactoring some code I wrote about 6 months ago...boy did I do things the hard way then. I'm sure in another six months, it will be the same again (although by then, I'll likely be in VS2008, with linq, so god help me! ). Of course, many of your suggestions are excellent and given some more time, I'm sure we'll see the BO Mapper doing lots more work for us, etc.
So, don't get discouraged, it'll get better sooner than you think..you're working hard and asking the questions!
Good luck!
Greg
|
By Edhy Rijo - 11/19/2007
Hi Greg,Once again, thanks for the inside, you are in the right spot about what I am going thru, after 20+ years working with VFP one day a very good friend of mine (also a VFP developer) pushed me to start learning .NET and we start buying books and kind of compete each other, but at some point, the only way to really push yourself to learn a whole new language and environment is to start with a serious small project, and I took this project which I could have finished it already in VFP, to learn .NET and after looking for a framework I decided to go with SF. I have been working with a VFP framework for over 10 years (the same framework) and you bet there are a lot of stuff I miss  so in order for me to keep focus on the project, learning the new tools, and trying not to ask too much dummy questions, I simply post my comments/request for things I believe can be improved in SF in the future (of course I hope is the near future :hehe and thanks to you and the others I am getting there one step at a time.
|
|