How to validate a data value that should be unique?

How to validate a data value that should be unique?

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


superkuton posted on Tuesday, August 25, 2009

I have a value that should be unique, How should I check for it?

Are there any existing validation rule/class that can be used?

Or a commandbase should be created?

Any sample code for a newbie will help me a lot. Thanks.

rfcdejong replied on Wednesday, August 26, 2009

It depends how the value should be unique, based on what?

Anyway, in any case u should have several protections.

- the most important one in the database, an unique constraint on the column.
- another optional one in the data layer using a concurrency manager.
- a csla validation

We are using a "volgorde" which should be unique within a list, example:

Having a businesslist RentebladKenmerkItemList with child business objects called RentebladKenmerkItem. In the child business object there is a validation rule

#region
Validation Rules
protected override void AddBusinessRules()
{
      ValidationRules.AddRule<
RentebladKenmerkItem>(IsVolgnummerValid, volgnummerProperty);
}
private static bool IsVolgnummerValid<T>(T target, Csla.Validation.RuleArgs e)
where T : RentebladKenmerkItem
{
      
int? volgnummer = target.ReadProperty(volgnummerProperty);
      RentebladKenmerkItemList list = target.Parent as RentebladKenmerkItemList;
      
if (list != null)
      {
            
foreach (RentebladKenmerkItem item in list)
            {
                  
if (item.Volgnummer == target.ReadProperty(volgnummerProperty) && !(ReferenceEquals(item, target)))
                  {
                        e.Description =
"Volgnummer must be unique.";
                        
return false;
                  }
            }
      }
      return true;
}

rsbaker0 replied on Wednesday, August 26, 2009

All of our table-mapped BO's (e.g. class corresponds to database table) implement an Exists() static method -- implemented via CommandBase derived class -- that returns true if the passed key exists and false otherwise.

So, we use this to implement validation rules on all the properties that correspond to foreign keys in the database.

Of course, you can let the database do this also, but the user won't get notified until they try to save the object. The validation rule provides advance notification.

JoeFallon1 replied on Wednesday, August 26, 2009

I have a similar Exists rule. I set its priority to 1 and all the normal rules to 0 by default. That way the rules that hit the database do not even run until all the simple rules pass.

Joe

 

jamie.clayton replied on Wednesday, August 26, 2009

Here is an example of what we do.

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.SqlClient

Imports Csla
Imports Csla.Data

'''
''' Protect this method from any calling applications as it is too complicated to expose to a higher level app.
''' See clsProductClientRules.ClientRuleExists() as that implements this method.
''' Provide a simple method to see if a property for a given BO already exists in the database.
'''
'''
''' Things to think of:
''' 1/. We get given the id of the record in question as it may already have this value (hence still be unique even though it exists),
''' so have to discount this id when checking.
''' 2/. Value may only be unique for a given foreign key, for example financial year for a Target record is unique for a given Consultant ID.
'''
Friend Class IsUniqueCommand
Inherits Csla.CommandBase

#Region " Business Properties and Methods "

Private mBusinessObject As String
Private mDbFieldToReview As String
Private mValue1ToCheck As Object
Private mExcludeID As Int32
Private mDbField2ToReview As String
Private mValue2ToCheck As Int32
Private mExists As Boolean

'''
''' The field to review is most likely to be a member of a unique index or composite unique index (multiple fields)
'''
'''
'''
'''
Public ReadOnly Property Field1() As String
Get
Return mDbFieldToReview
End Get
End Property

Public ReadOnly Property BusinessObject() As String
Get
Return mBusinessObject
End Get
End Property

Public ReadOnly Property Value() As Object
Get
Return mValue1ToCheck
End Get
End Property

Public ReadOnly Property ExcludeID() As Int32
Get
Return mExcludeID
End Get
End Property

Public ReadOnly Property Field2() As String
Get
Return mDbField2ToReview
End Get
End Property

Public ReadOnly Property Value2() As Int32
Get
Return mValue2ToCheck
End Get
End Property

Public ReadOnly Property Exists() As Boolean
Get
Return mExists
End Get
End Property
#End Region

'Private Enum UniqueObject
' Product
' Branch
' FundManager
' TargetRevenueAndCommission
' User
'End Enum

#Region " Factory Methods "

'''
''' We need to check unique indexes (single or composite [Max of 2 unique index fields catered for in this solution])
''' When using the function in code you should use code like.
'''
''' Dim test As IsUniqueCommand = DataPortal.Execute(Of IsUniqueCommand)(New IsUniqueCommand("Product", "ProductCode", "Example", 1, "", ""))
''' If test.Exists then
''' ' Logic here
''' End If
'''
'''
'''
'''
'''
''' The list is based on the actual CSLA Business objects named in your project. It makes an assumption that the code that calles this function is likely to use reflection to grab some of the details.
''' The name of the table/field that contains the information we need to check for unique values against.
''' The value that will be checked to see that it's unique.
''' When reviewing unique records it's important that you exclude the current record. The Exclude ID assumes that existing records might be changed and then restored back to its original value, which is acceptable. If the record happens to be new, the ExcludeID (jenasys design used negative incrementing numbers) is not going to affect the SQL where clauses filtering of records, so it doesn't matter.
''' Optional Composite field name.
''' Optional Composite field value (assumption of int32) as we assume the database has been normalised and uses Identity fields as primary keys.
''' A maximum of 2 fields in a composite index is covered by this solution at the moment.
''' In practice this should cover 80% of most database requirements. At some stage this needs to be extended
''' so we can have an unlimited amount of fields and values included in the composite index.
Public Sub New(ByVal aBusinessObject As String, ByVal dbField1ToReview As String, ByVal value1ToCheck As Object, ByVal excudeID As Int32, ByVal dbField2ToReview As String, ByVal value2ToCheck As Int32)
mBusinessObject = aBusinessObject
mDbFieldToReview = dbField1ToReview
mValue1ToCheck = value1ToCheck
mExcludeID = excudeID
mDbField2ToReview = dbField2ToReview
mValue2ToCheck = value2ToCheck
End Sub
#End Region

#Region "Authorisation Rules"

Public Shared Function CanExecuteCommand() As Boolean
Return True
End Function

#End Region

#Region "Data Access Command"
'''
''' The is some logic in this method to help prefill in some of the stored procedure details and to ensure that any code that calls the function is setup correctly.
'''
'''
''' The stored procedure has to return a integer value in the first column with the count of records.
''' It was very important to ensure than the connection was closed and returned to the connection pool
''' or Connection pool max/timeout errors occured.
''' PRE: Value is of the correct type for the expected stored proc we are going to call!
'''
Protected Overrides Sub DataPortal_Execute()
Dim StoredProcedureName As String = Nothing ' Set to keep compiler happy.
Dim ExcludeIDParamaterName As String = Nothing ' Set to keep compiler happy.
Dim DBFieldParameterName As String = Nothing ' Set to keep compiler happy.
Dim UnexpectedField1Name As Boolean = False ' Innocent until proven guilty.
Dim DbField2ParamName As String = Nothing ' Set to keep compiler happy.
Dim UnexpectedDbField2Name As Boolean = False ' Innocent until proven guilty.

' Make sure we have a stored proc for the given BO, field to check and foreign id field (if we even expect one).
Select Case mBusinessObject
Case "Product"
If mDbFieldToReview <> "ProductID" Then
UnexpectedField1Name = True
ElseIf Not String.IsNullOrEmpty(mDbField2ToReview) Then
UnexpectedDbField2Name = True
Else
StoredProcedureName = "spProductCodeExists"
ExcludeIDParamaterName = "@ProductNo"
DBFieldParameterName = "@ProductCode" ' SIC, stored proc uses a different name than what the underlying field is.
'DbField2ParamName = "Not used"
End If

Case "Branch"
If mDbFieldToReview <> "BranchName" Then
UnexpectedField1Name = True
ElseIf Not String.IsNullOrEmpty(mDbField2ToReview) Then
UnexpectedDbField2Name = True
Else
StoredProcedureName = "spIsBranchNameUnique"
ExcludeIDParamaterName = "@BranchID"
DBFieldParameterName = "@BranchName"
'DbField2ParamName = "Not used"
End If

Case "User"
If Not String.IsNullOrEmpty(mDbField2ToReview) Then
UnexpectedDbField2Name = True
Else
Select Case mDbFieldToReview
Case "UserFullName"
StoredProcedureName = "spIsUserFullNameUnique"
ExcludeIDParamaterName = "@UserPKID"
DBFieldParameterName = "@UserFullName"
'DbField2ParamName = "Not used"
Case "UserEmail"
StoredProcedureName = "spIsUserEmailUnique"
ExcludeIDParamaterName = "@UserPKID"
DBFieldParameterName = "@UserEmail"
'DbField2ParamName = "Not used"
Case Else
UnexpectedField1Name = True
End Select
End If
Case Else
Throw New Iims.BL.IimsException(mBusinessObject & " is not an expected Business Object to check for uniqueness.")
End Select

If UnexpectedField1Name Then
Throw New Iims.BL.IimsException(mDbFieldToReview & " is not an expected field to check for uniqueness in " & mBusinessObject & " Business Object.")
End If

If UnexpectedDbField2Name Then
Throw New Iims.BL.IimsException(mDbField2ToReview & " is not an expected field to use as a foreign key in " & mBusinessObject & " Business Object.")
End If

' LL: The order of the Stored procedure where clause is very important. If you are checking a composite unique index (multiple fields) then you need to setup the stored procedures where clause to match the order of the fields used in the composite index to ensure SQL Server uses the index appropriately.
Dim cn As SqlConnection
cn = New SqlConnection(Database.PIS_IimsConnection)
Try
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
With cm
.CommandType = CommandType.StoredProcedure
.CommandText = StoredProcedureName
End With
With cm.Parameters
.AddWithValue(DBFieldParameterName, mValue1ToCheck)
.AddWithValue(ExcludeIDParamaterName, mExcludeID)
If Not String.IsNullOrEmpty(DbField2ParamName) Then
.AddWithValue(DbField2ParamName, mValue2ToCheck)
End If
End With

Dim count As Integer = CInt(cm.ExecuteScalar)
mExists = (count > 0)
End Using
Finally
cn.Close()
End Try
End Sub
#End Region

End Class


You then use the following Business rule to implement that code.

'''
''' Ensure given property does not have a value already in use.
'''
'''
'''
'''
'''
'''
''' Code this was based on also checked if property had a value - this is out of scope for this validation and should be performed by
''' either a Csla.Validation.CommonRules.StringRequired or NumericIDRequired.
''' Things to think of:
''' 1/. We get given the id of the record in question as it may already have this value (hence still be unique even though it exists),
''' so have to discount this id when checking.
''' 2/. Value may only be unique for a given foreign key, for example financial year for a Target record is unique for a given Consultant ID.
''' LL: If the property that calles this Business rule is the first property edited, then the dirty flag will not be correctly set until the BOName_PropertyChange event. YOU MUST add code into the BOName_PropertyChange event to ensure the business rule is correctly checked a second time with the dirty flag set.
'''
Public Shared Function IsUnique(Of T As Core.BusinessBase)(ByVal target As T, ByVal e As IsUniqueRuleArgs) As Boolean
Dim result As Boolean

' LL: Validation gets run when a BO gets read in; when we are reading in a list of them, this can cause a delay when the validator needs to perform
' SQL to determine validity. We originally turned off the validation check on Fetch but that had undesirable side-effects. We now only validate
' SQL-based validators if the BO is new or dirty, hence circumventing the issue more succinctly.
If target.IsDirty OrElse target.IsNew Then
' LL: GetProperty() is case-SENSITIVE, get the case wrong and you get a Nothing return!
Dim pi As PropertyInfo = target.GetType().GetProperty(e.PropertyName)
Dim value As Object = pi.GetValue(target, Nothing)

pi = target.GetType().GetProperty(e.PKFieldName)
Dim id As Int32 = DirectCast(pi.GetValue(target, Nothing), Int32)

Dim foreignKeyID As Int32
If Not String.IsNullOrEmpty(e.Field2ToReview) Then
pi = target.GetType().GetProperty(e.Field2ToReview)
foreignKeyID = DirectCast(pi.GetValue(target, Nothing), Int32)
End If

Dim test As IsUniqueCommand = DataPortal.Execute(Of IsUniqueCommand)(New IsUniqueCommand(target.GetType.Name, e.PropertyName, value, id, e.Field2ToReview, foreignKeyID))
If test.Exists Then
' LL: e.PropertyFriendlyName is not always set.
e.Description = e.PropertyFriendlyName & " value of '" & Stringify(value) & "' already exists and cannot be used."
result = False
Else
result = True
End If
Else
result = True ' Passed because we did not test it...
End If

Return result
End Function


At the SQL Server you would have stored procedures like the following.


CREATE PROCEDURE [dbo].[spIsBranchNameUnique]
@BranchName nvarchar(100),
@BranchID int
AS
BEGIN
SET NOCOUNT OFF

SELECT Count(*)
FROM dbo.tblBranches
WHERE BranchName = @BranchName AND BranchID <> @BranchID
END

Copyright (c) Marimer LLC