Comparing arrays in VB.NET

Question

Let me provide a little detail to explain what I'm trying to accomplish before I get into the nuts and bolts of the question.

I've got two data sources - one is a SQL Server containing part numbers, descriptions, etc. The other is a CAD system that does not have a database in a traditional sense. What I'm trying to do is read out the bill of materials from the SQL Server and compare it to the CAD assembly drawing to ensure that the CAD system contains the same information as the SQL Server.

Getting the data from the SQL Server is fairly straight forward. I query the database and populate a datagrid. Done. Quick. Easy.

Getting the data from the CAD system is a little more involved. I have to load the assembly drawing to get a listing of all the component parts and then load those individual drawings to pull the "Part Number" property from the drawing. This is a somewhat time consuming and slow process (unfortunately) since each of the files must actually be accessed. I load those properties into an array (I guess a list might be more efficient).

So now I have a datagrid and array with part numbers. I need to compare them and colorize the grid accordingly. The grid should remain transparent if the part exists in both, color the row yellow if it only exists in the grid, and add a row colored red if only in the array.

As best I can tell, this means looping through the array on each line of the grid. The thought process is this:

1. Default the grid to yellow rows.
2. Loop through the grid and loop through the array to compare. If a match is found, make the row transparent and delete the element from the array.
3. After step 2 is completed, the array should only contain elements that are not found in the grid. Resize the array to remove the empty elements.
4. Add the elements of the array to the grid and color those new rows red.

The problems with this logic is that it seems expensive from a performance standpoint. Surely there is a better method? Also, if I modify the grid in some manner (like a resort) I have to go through the process again. I'd really appreciate some advice on this.

Thanks!

Note: written in Visual Studio 2005.

Answer 1

You could load the data from the CAD system in a dictionary (indexed by part number). Then you could go through the grid and check if it exists in the dictionary, which is a fast operation ( O(1) ). You could do exactly as you say, remove the found elements in the dictionary and add the remaining elements in the datagrid.

Here's some code for creating and using a dictionary (used C# style comments to preserve formatting):

//First argument is your key type, second is your item type
Dim cadParts As New Dictionary(Of Integer, Part)

//Add items to the parts dictionary
For Each part As Part In cadPartsArray
  cadParts.Add(part.PartNumber,part)
Next

//Check if a part exists
Dim partNumber As Integer = 12345
If cadParts.ContainsKey(partNumber) ...

//Remove a part
cadParts.Remove(partNumber)

//Go through the remaining values
For Each part As Part In cadParts.Values ...

Edit:

1) Yes, if your key (here, part number) is a string, then a Dictionary(Of String,...) would be used.

2) I assumed you had a class named Part, which contained some information about a part. If you just have a part number, and no other info, then you could create a Hashset instead. It is basically the same as a dictionary, but with this structure the value is also your key. You would create a hashset like this:

Dim cadParts As New Hashset(Of String)

I won't go through code examples because it is very close to the Dictionary. ContainsKey becomes Contains, and Add accepts only one argument (which would be your part number here).

3) Yes, loop through them and add them to the hashset.

Answer 2

If part number is unique (it is not repeated in the values you want to search for) then you can use sorted dictionary. Then remove the duplicates and use remaining items.

To compare you can use part number as follows:

if(dictionary.ContainsKey(partNumber))
    dictionary.Remove(partNumber)
end if

Answer 3

Given that there will only be a certain number of rows of the data grid visible at one time, it might be quicker to implement some code in the OnPaint method (I'm a little rusty here so apologies if that isn't exactly right) for each of the rows which checks against the array of part information and sets the background color of each row as it becomes visible, perhaps even marking each row as having been processed so the operation doesn't need to be repeated. There might be an initial performance gain here over processing all of the rows at once.

Of no real relevance; but from previous experience this sounds like you are interfacing with AutoDESK Inventor files?

Answer 4

Given that there will only be a certain number of rows of the data grid visible at one time, it might be quicker to implement some code in the OnPaint method (I'm a little rusty here so apologies if that isn't exactly right) for each of the rows which checks against the array of part information and sets the background color of each row as it becomes visible, perhaps even marking each row as having been processed so the operation doesn't need to be repeated. There might be an initial performance gain here over processing all of the rows at once.

Of no real relevance; but from previous experience this sounds like you are interfacing with AutoDESK Inventor files?

Answer 5

Another solution could be to implement the IComparable(Of T) interface

That would require for you to build a class that you would use for both situation.

Public Class Item
    Implements IComparable(Of Item)

    Private _Description As String
    Public Property Description() As String
        Get
            Return _Description
        End Get
        Set(ByVal value As String)
            _Description = value
        End Set
    End Property

    Private _PartNo As Integer
    Public Property PartNo() As Integer
        Get
            Return _PartNo
        End Get
        Set(ByVal value As Integer)
            _PartNo = value
        End Set
    End Property

    Public Function CompareTo(ByVal other As Item) As Integer Implements System.IComparable(Of Item).CompareTo
        ' Your rules for comparing content for objects
        If other.PartNo <> Me.PartNo Then Return 1
        If other.Description <> Me.Description Then Return 1

        ' Return 0 if the object are the same
        Return 0
    End Function
End Class

Here is a small test that works with upper implementation.

    Dim item As New Item
    item.Description = "Desc"
    item.PartNo = 34

    Dim item2 As New Item
    item2.Description = "Desc"
    item2.PartNo = 35

    Dim item3 As New Item
    item3.Description = "Desc"
    item3.PartNo = 36

    Dim listFromDatabase As New Generic.List(Of Item)
    listFromDatabase.Add(item)
    listFromDatabase.Add(item2)

    If listFromDatabase.Contains(item2) Then
        MessageBox.Show("item2 was found in list")
    End If

    If Not listFromDatabase.Contains(item3) Then
        MessageBox.Show("item3 was NOT found in list")
    End If

Hope it helps a bit, - Dan