Import Via Excel / Name Value Lists type scenarios

Just my 2 cents.

When I had to do this in the past, I would preload Name / value collection (of cost centers in your case) when I created list of items to import (ParticipantsBusinessList in your case) and hit Ctype(sender.Parent, ParticipantsBusinessList).CostCentersNameValueList.Exists(costCenterName) in the validation rule.  This way you do not have to bombard DB with checks for valid center.  A little more efficient this way.

 

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

A couple of weeks into implementing a new system with CSLA and it works a treat with data entry on winforms!

Busy writing an import where some fields on the file are plain text fields and need some help./advice on what is the best way to handle the following scenario,

Excel Sheet
FirstName
MiddleName
LastName
CostCentre * [Cost Centre Name]

Participant Table
FirstName
MiddleName
LastName
CostCentreID *

CostCentre Table
CostCentreID
Name

*The CostCentre captured on the excel file will be actual name of the cost centre as this has a true meaning to the user. The underlying value of CostCentre should actually be the CostCentreID as the import will obviously fail as database expects an integer.

The user should not have to know or worry about the Cost Centre ID but just be informed that the Cost Centre Name entered is either valid or not. I am tempted to start putting this in the Validation Rules section using perhaps a commandbase but somehow this does not seem to be the right way as I will have to set the CostCentreID to the underlying database foreign key to make the object valid.

Any advice or pointer much appreciated.

Thanks

I think I follow what you are asking…

 

You need to add a function to your CostCenterNameValueList (I did not actually compile it, so you may need to fix NameValuePair thing):

 

Friend Function IsValidCenter(costCenter as string) as Boolean

                Dim returnValue as Boolean = False

                For each onePair as NameValuePair In Me

                                If onePair.Value = costCenter Then

                                                returnValue = true

                                                Exit For

                                End If   

                Next     

                Return returnValue

End Function

 

 

Is this what you are looking for?

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

Thanks for the advice on checking in a more effecient way and preventing hitting the database too many times. How would i actually retrieve the CostCentreID from the namevalue list from the parent as per your suggestion?

I am trying the following with some difficulty;

The Parent Class [ParticipantImport] has the CostCentreNameValueList property defined which I populate when the parent is instantiated.

The child class[ParticipantImportItem] has the following validation rule wich is called whenever a child is created;

Private Shared Function CheckCostCentreExists(Of T As ParticipantImportItem)( _
ByVal target As T, _ByVal e As Validation.RuleArgs) As Boolean

If CType(target.Parent, ParticipantImport).CostCentreNameValueList.Contains(target.GetProperty(Of String)(CostCentreProperty)) = False Then

          e.Description = "Invalid Cost Centre"
          Return False

Else

          Return True

End If

End Function

Thanks

 

No, what you have looks perfect.  I just assumed that cost center is NV List not Read Only List.

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

mmm i have something along those lines in place already unless my CostCentreList class explicitly needs to inherit from NameValueListBase ?

I have the following functtion defined in my CostCentreList classs (Inherits ReadOnlyListBase(Of CostCentreList, CostCentreInfo))

Public Overloads Function Contains( _
ByVal name As String) As Boolean

For Each res As CostCentreInfo In Me

         If res.Name = name Then
              Return True
         End If

Next
        Return False

End Function

It looks OK to me.  Are you getting an exception?

 

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

mmm thanks for the help. glad to hear it looks good but it is failing on the validation rule on this line...

If CType(target.Parent, ParticipantImport).CostCentreNameValueList.Contains(target.GetProperty(Of String)(CostCentreProperty)) = False Then

the containts function on the CostCentreList is never hit so I am stumped at the moment.

Hmmm…  Not sure what exception is thrown…  Did you look at the inner exception?

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

Yes, I am getting an exception on the rule, let me check....

1. I Load the property like so....

LoadProperty(Of String)(CostCentreProperty, costCentre)

2. Then the Validation rule is called as a result and then the error...

{"ChildDataPortal.Create failed on the server"}

{"Validation rule rule://CheckCostCentreExists/CostCentre failed in property CostCentre"}

I think you are correct and read-only list is not loaded.  Make sure you call the load that in your factory method for Import.

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

{"Object reference not set to an instance of an object."}

I am wondering now if the namevalue list property on the parent is actually loaded at all...

I load the property on the parent class [ParticipantImport] like so;

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(CompanyID))

stumped..

Try to load read-only list before line items.

 

<RunLocal()> _
Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

ValidationRules.CheckRules()

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(CompanyID))

LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

End Sub

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

thanks for your help once again..

This is how I load it currently..

<RunLocal()> _
Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

ValidationRules.CheckRules()

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(CompanyID))

End Sub

Never mind.  I think the parent is probably not set for new line item.  Try to add check for Parent IsNot Nothing to validation rule.

 

 

 

Try to load read-only list before line items.

 

<RunLocal()> _
Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

ValidationRules.CheckRules()

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(CompanyID))

LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

End Sub

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

thanks for your help once again..

This is how I load it currently..

<RunLocal()> _
Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

ValidationRules.CheckRules()

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(CompanyID))

End Sub

 

Try changing the rule and add If Parent IsNot Nothing check to it…

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

ok initially the list did not load but now it is and funny enough i still get the same error..

{"Object reference not set to an instance of an object."}

i did change the code as suggested

<RunLocal()> _

Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

ValidationRules.CheckRules()

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(criteria.Value))

LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

End Sub

Parent is set when you add an item to the list.  As a workaround you can add an item to the list, then call validation rules.

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

yep you were right parent is nothing, what do i need to set then?

The parent is set by CSLA when an item is added to the list.  So, if you call validation rules in Create, by that time the parent is not set yet, so the rule is technically invalid.

There are a couple of ways you can address this:

You can create Friend Sub Validate on child object that calls validation rules.  In this case, your import items list code would be:

 

Dim newItem as ImportItem = ImportItem.NewImportItem()\

Add(importItem)

importItem.Validate()

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

i am calling the validation rules after all the fields for the child is specifiied and the Private Overloads Sub Child_Create(...) method is called where properties are loaded like so..

Private Overloads Sub Child_Create......

.......

LoadProperty(Of String)(JobTitleProperty, jobTitle)

LoadProperty(Of String)(JobGradeProperty, jobGrade)

LoadProperty(Of String)(CostCentreProperty, costCentre)

LoadProperty(Of String)(StatusProperty, status)

LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)

ValidationRules.CheckRules()

mmm Parent is still nothing...I think there might be something wrong with the way I have created the objects. I will try and walk through the steps i have teken quickly. I have the parent object [ParticipantImport] which I create like so

<RunLocal()> _

Protected Overloads Sub DataPortal_Create(ByVal criteria As SingleCriteria(Of ParticipantImport, Integer))

LoadProperty(Of Integer)(CompanyIDProperty, criteria.Value)
LoadProperty(Of String)(ModifiedByProperty, user.Identity.Name.ToString)
LoadProperty(Of CostCentreList)(CostCentreNameValueListProperty, CostCentreList.GetCostCentreList(criteria.Value))
LoadProperty(Of ParticipantImportItems)(ItemsProperty, ParticipantImportItems.NewParticipantImportItems)

End Sub

Line Items are not added immediately, but only once the the excel file is selected and then an Assign method on the ParticipantImportITems class is called which inturn creates a ParticipantImportItem.

Public Sub Assign(ByVal companyID As Integer, ByVal referenceNumber As String, ByVal nationalIdentifier As String, _
ByVal payrollNumber As String, ByVal enterpriseID As String, ByVal socialSecurityNumber As String, ByVal title As String, _
ByVal firstName As String, ByVal middleName As String, ByVal lastName As String, ByVal Suffix As String, ByVal preferredName As String, ByVal gender As String, _
ByVal maritalStatus As String, ByVal email As String, ByVal salary As Double, ByVal taxRate As Double, ByVal startDate As String, ByVal endDate As String, _
ByVal dateOfBirth As String, ByVal street As String, ByVal city As String, ByVal province As String, ByVal postalCode As String, ByVal country As String, _
ByVal telephone As String, ByVal mobile As String, ByVal nationalityID As String, ByVal greenCard As Boolean, ByVal director As Boolean, ByVal insider As Boolean, _ByVal division As String, ByVal department As String, ByVal payroll As String, ByVal jobTitle As String, ByVal jobGrade As String, _
ByVal costCentre As String, ByVal status As Integer)

If Not Contains(referenceNumber) Then

Dim resource As ParticipantImportItem = _

ParticipantImportItem.NewParticipantImportItem(companyID, referenceNumber, nationalIdentifier, payrollNumber, enterpriseID, socialSecurityNumber, title, firstName, middleName, lastName, _

Suffix, preferredName, gender, maritalStatus, email, salary, taxRate, startDate, endDate, dateOfBirth, street, city, province, postalCode, country, _
telephone, mobile, nationalityID, greenCard, director, insider, division, department, payroll, jobTitle, jobGrade, costCentre, status)

Me.Add(resource)

Else

Throw _

New InvalidOperationException("Duplicate Participant specified in list: " & referenceNumber)

End If

End Sub

I am not sure where the Parent exactly is set.

Should be (in ParticipantImportItem)

Friend Sub Validate()

Me.ValidationRules.CheckRules()

End Sub

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

Thanks again for your input..

I implemented the following within the Validation Section within the child object [ParticipantImportItem]

Friend Sub Validate()

Me.Validate()

End Sub

 

Withing my collection class [ParticipantImportItems] i now call the validate only after adding the Item.

Dim resource As ParticipantImportItem = _ParticipantImportItem.NewParticipantImportItem(companyID, referenceNumber, nationalIdentifier, payrollNumber, enterpriseID, SocialSecurityNumber, title, firstName, middleName, lastName, _
Suffix, preferredName, gender, maritalStatus, email, salary, taxRate, startDate, endDate, dateOfBirth, street, city, province, postalCode, country, _
telephone, mobile, nationalityID, greenCard, director, insider, division, department, payroll, jobTitle, jobGrade, costCentre, status)

Me.Add(resource)

resource.Validate()

but now I am getting a System.StackOverflow exception {Property evaluation failed}

 

Yes, it should be

If CType(target.Parent, ParticipantImportItems).CostCentreNameValueList.Contains(target.GetProperty(Of String)(CostCentreProperty)) = False Then

And your cost center name collection should be attached to that list.  Or you can keep it attached to the Import class, but then you have to have it accessible from import line items list by linking them together.

 

 

Sergey Barskiy

Senior Consultant

office: 678.405.0687 | mobile: 404.388.1899

Magenic ®

Microsoft Worldwide Partner of the Year | Custom Development Solutions, Technical Innovation

 

 

thanks again, by chaning it to

Friend Sub Validate()

Me.ValidationRules.CheckRules()

End Sub

did work but it now my CheckCostCentreExists validation rule is wrong. An error occurs.

{"Unable to cast object of type 'Centive.Library.ParticipantImportItems' to type 'Centive.Library.ParticipantImport'."}

If i am correct the error states that the parent object is the collection object [ParticipantImportItems] and not the ParticipantImport as expected on which i want to access the namevaluelist property to check against.

Validation rule snippet.

If CType(target.Parent, ParticipantImport).CostCentreNameValueList.Contains(target.GetProperty(Of String)(CostCentreProperty)) = False Then