Enforcing Referential Integrity

Enforcing Referential Integrity

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


Warren posted on Thursday, June 05, 2008

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.

 

Lalit replied on Friday, June 06, 2008

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.

Warren replied on Friday, June 06, 2008

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.

 

 

jeff replied on Friday, June 06, 2008

You could catch the sql exception in the dataportal then rethrow an exception that has a more business specific meaning such as OrderCannotBeDeletedException. This lets you interpret the specific RI error and throw something that will make more sense to the UI.

I'm not sure if the "exists method" would provide any better user experience than just catching exceptions in the UI and responding that way. Plus, even though it is usually good enough it is not guaranteed to work.











Warren replied on Friday, June 06, 2008

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...

 

 

jeff replied on Friday, June 06, 2008

Well I was thinking of just interpreting in the dataportal itself. Such as:

            try
            {
                cm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                if (ex.Number == 2601)
                    throw new OrderNumberExistsException();
            }

In this case I'm checking for a constraint error so you would have to see which error code is thrown for your situation. There's no need to log this since no actual error is occurring, you're just using exceptions to determine something at the DB level. Then just catch your custom error in the UI and do what you need.

Yes, you could get rid of the "magic number" with an enum for readability.

Keep in mind if you're using a remote portal your custom exception would need to be serializable and also implement a protected constructor (I think):

    [Serializable]
    public class MyException: Exception
    {
        public MyException()
        {}

        protected MyException(SerializationInfo info, StreamingContext context)
            : base(info, context)
        {
        }
    }





Warren replied on Friday, June 06, 2008

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 Argument

The error is type "Argument is not defined".

 

Thanks in advance.

Lalit replied on Tuesday, June 10, 2008

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.

SomeGuy replied on Friday, June 06, 2008

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.

 

jeff replied on Friday, June 06, 2008

SomeGuy:
I would avoid using Exceptions for 'normal' processing....


This is true. I think it may be ok in this case though. We are trying to determine an RI violation outside the scope of a parent/child object graph, such as trying to delete a customer that has associated orders in the database. I don't see where this is addressed in project tracker.

I see an "Exists" method (seems to be unused) in project tracker but wouldn't this have to be in the same transaction as doing the actual delete?




jeff replied on Friday, June 06, 2008

Warren,

Re-reading your post I realized you may be talking about parent/child CSLA objects. If this is the case then SomeGuy is right in that CSLA handles the RI for you.

I thought you meant you wanted to perform some delete where some RI may be violated outside of your CSLA parent/child objects.

Warren replied on Friday, June 06, 2008

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? 

 

SomeGuy replied on Monday, June 09, 2008

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.

 

Warren replied on Monday, June 09, 2008

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