Hello All,
I need once last piece of plumbing in my Winforms app, before I can devote all my efforts to addressing the business at hand. My application will not use cascading deletes and instead will inform the user that they must first delete any child records which may exist prior to deleting a parent. Likewise a parent must exist before a child can be created.
Assuming it is possible and perhaps more efficient to enforce RI at the object level instead of the database, how does one best achieve this with CSLA?
At this point I should probably stick to the simpliest (I'm behind schedule) solution and if that means enforcing RI via the db I'll go that route.
Do I need to create some kind of variation of an command object for which has an exists method.?
Should I parse the SQL Server exceptions that occur instead?
What are other CSLA users doing for this issue?
Thanks in advance.
Hi warren,
For such issues i designed a utility class named ExistUtility using CommandBase class. This class is having a "Exists(ByVal arg As Argument, ByVal tableName As String, ByVal filterCriteia As String) As Boolean" method which takes two arguments. One argument is fully qualified Table name and another is where part(i.e. ususally ColumnName = value).
<Serializable()> Public Class ExistUtility
Inherits CommandBase
Private m_tableName As String
Private m_filterCriteria As String
Private m_Arg As Argument
Private m_exists As Boolean
Public ReadOnly Property IsExists() As Boolean
Get
Return m_exists
End Get
End Property
#Region "Shared Function"
''' <summary>
''' Checks the existence of a field value in a table
''' </summary>
''' <param name="arg"></param>
''' <param name="tableName"></param>
''' <param name="filterCriteia"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Function Exists(ByVal arg As Argument, ByVal tableName As String, ByVal filterCriteia As String) As Boolean
Dim util As ExistUtility = DataPortal.Execute(Of ExistUtility)(New ExistUtility(arg, tableName, filterCriteia))
Return (util.IsExists)
End Function
#End Region
#Region "Constructor"
Private Sub New(ByVal arg As Argument, ByVal tableName As String, ByVal filterCriteia As String)
m_tableName = tableName
m_Arg = arg
m_filterCriteria = filterCriteia
End Sub
#End Region
#Region "Data Access"
Protected Overrides Sub DataPortal_Execute()
Using cn As New SqlConnection(m_Arg.ConnString)
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
Dim sqlstr As New System.Text.StringBuilder
cm.CommandType = CommandType.Text
sqlstr.Append(" SELECT COUNT (*)")
sqlstr.Append(" FROM " + m_tableName)
If Not m_filterCriteria = "" Then
sqlstr.Append(" WHERE " + m_filterCriteria)
End If
cm.CommandText = sqlstr.ToString()
m_exists = (CInt(cm.ExecuteScalar) > 0)
End Using
End Using
End Sub
#End Region
End Class
To enforce RI while creating and deleting data, first call Exist method to check if data exists or not.
Hi Lalit,
Thanks for sharing the your code. I will be able to use it for purposes other than checking for RI. A bonus for me is that I don't have to convert C# to VB :-).
I read other posts which suggest that an exists method could be used in conjunction with DB RI exception handling to provide a better user experience. I am wary of doing this since it means that the UI must know more about the DB design.
Since I made this post yesterday a decison has been made here too enforce RI at the DB level. After a DB update I need to interogate any exception that occurred for a particular SQL RI error. I need to do this in a standard way such that the error codes reside in a table along with a standard message.
I am researching the best way to achieve this functionality.
Hi Jeff,
Thanks for your valuable insight. I like the idea of catching the DB exceptions via the CLSA.DataPortalException object and then rethrowing to a class that can intepret the error and present it to the user in a meaningful way. This exception interpreter class could log the error if required and/or present a meaning UI message to the user. Perhaps this interpreter class would use another class that encapsulates the SQL Server error message codes and associated messages?
Like many things in the still new to me .NET world, I can figure out what to do after discussing with helpful individuals here like yourself but the how to do it is often my biggest challenge...
For the exists code above, what namespace is required for "Argument type'. I have tried a few now and cannot get it compiled.
Private
m_Arg As ArgumentThe error is type "Argument is not defined".
Thanks in advance.
Hi warren,
This Argument is not a system type, infact it is something from my application used to fetch connection string and other global parameters of my application. As i extracted the code from my existing application where we are using this class, i forgot to remove that argument. You may remove this argument from code safely.
I would avoid using Exceptions for 'normal' processing.
Look at PTracker. The BO's handle RI themselves. If you use Child Collections, you won't have a problem with RI or orphans because they will be associated with a Parent.
If I am going to have a static Delete method for a BO, I handle the child deletions in the sproc.
SomeGuy:
I would avoid using Exceptions for 'normal' processing....
I think I am talking about RI issues outside the BOs in my use case. For example, in my particular use case I have a bank parent object and a collection of
accounts. There are other children of bank that are not participating in the use case. I was concerned that a user could attempt to delete a bank for
which no account records exists but other children outside of this use case do exists in the DB which would cause an RI error. I based my app on the
PTTracker design which contains the follow exception handling on the delete:
Bank.DeleteBank(mBank.BankId)
Catch ex As Csla.DataPortalException
MessageBox.Show(ex.BusinessException.ToString, _
"Error deleting", MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
Catch ex As Exception
MessageBox.Show(ex.ToString, _
"Error deleting", MessageBoxButtons.OK, _
MessageBoxIcon.Exclamation)
End Try
I would like to notify the user that child records of bank still exist and must be deleted first. I am reluctant to code SQL error code specific handling in each Catch block associated with a database update.
On a related note, does anyone have an opinion on whether it would be overkill to introduce the Enterprise Library Exception Handling Block into a CSLA winforms app?
I see what you are saying.
I would look at either adding those children to your Bank object, or maybe look at the CanDelete method and add checks to Exists commands on the child objects. To me it makes more sense to prevent the user from deleting the Bank if the children exist, rather than letting them try to delete it and catch and error/exception.
Over the weekend I added the Enterprise Library 4.0 Exception Handling and Logging Blocks to my app. I can log now the exceptions to either a SQL Server database, the event log or a flat file just by modifying settings in my app.config file.
My plan now is to implement an exists method but also trap any database exceptions including RI issues arising in my DB which are not handled by the business objects on my use cases.
Copyright (c) Marimer LLC