best way to represent table like data in collections

I think my problem is a classic one, but I can't get the right way of implementing the solution. So I will ask it here at SOF.

I need to export some data to a CSV file which contains headers and values like a table. To get the data I need to iterate through a collection. Each item in the collection has a property which contains a collection of key/value pairs. The key contains the header. Not every collection of key/value pairs is of the same size.

When iterating through the key/value pair I collect all possible headers in a collection. This collection will be extended when you get to an other collection of key/values and an unknown key has been found. When this happens you need to make sure that the corresponding value will be written under the right header in the CSV file. I was trying to use the index of the header in the header collection, but I cant seem to get it to work. I have thought about multidimensional array's, jagged arrays and several combinations with dictionaries.

For my solution I don't want to do string comparisons between headers and keys when looking for the right column. This seems unnecessary. Two loops and indices should do it I think.

OK, here's my code I had so far. This only works for 1 item in the outer loop, because ArrayList expends only one at a time and not to any given index. When a new item to the header collection is added at index 12 in the outer loop, the values collection of the loop doesn't have an index 12 yet. So I get an index out of bounds. So I thought creating a values arraylist with the size of the header arraylist, but that doesn't work either.

Private Shared Function GetData(listItems As SPClient.ListItemCollection) As ArrayList
        'first store all values and make sure keys and values are matched
        Dim resultsToStore As ArrayList = New ArrayList()
        Dim headersToStore As ArrayList = New ArrayList()
        resultsToStore.Add(headersToStore)
        Dim totalListItems = listItems.Count
        Dim fieldNotToStore = ConfigurationService.FieldValuesNotToStore
        Dim displaynames = ConfigurationService.FieldValueDisplayNames

        For index As Integer = 0 To totalListItems - 1
            Dim valuesToStore As ArrayList = New ArrayList()
            Dim item As SPClient.ListItem = listItems(index)
            Dim fieldValues = item.FieldValues

            For Each fieldValue In fieldValues
                If (Not fieldNotToStore.Contains(fieldValue.Key)) Then 'If it is not in this collection is must be stored
                    Dim headerIndex = headersToStore.IndexOf(fieldValue.Key) 'does this key exist in the headersArray
                    If (开发者_如何转开发headerIndex = -1) Then 'If fieldValue.Key is already in the array it doesn't need to be stored again (-1 = no index found)
                        Dim displayname = String.Empty
                        If (displaynames.ContainsKey(fieldValue.Key)) Then
                            displayname = displaynames.Item(fieldValue.Key)
                        Else
                            displayname = fieldValue.Key.ToString
                        End If
                        headerIndex = headersToStore.Add(displayname) '' Add new header
                    End If
                    valuesToStore.Insert(headerIndex, fieldValue.Value.ToString) 'use headerindex to match key an value
                End If
            Next
            resultsToStore.Add(valuesToStore) 
        Next
        Return resultsToStore
    End Function

I think this problem has been solved like a thousand times, so please be kind.

Update: If you have an answer in any other (mainstream) language than vb.net, that is OK too, but I prefer vb.net and C# as they are both on the .net framework.

Thanks

First of all I think you should split up your function into smaller chunks. Second you should ask yourself why you are still using ArrayList while you have generics to play with and use.

You seem to be storing arraylists in arraylists which is already a sign you need a class somewhere and then a list of that class like

Public Class ValueStore
  Public Property Index as Integer
  Public Property FieldValue as String
End Class

You can then have a class ResutlStore like this

Public Class ResultStore
  Public ReadOnly Property ValueStores as Ilist(Of ValueStore)
  Public Sub New()
    ValueStores = new List(Of ValueStore)
  End Sub
  Public Sub AddValueStore(Byval Index as Integer, FieldValue as Integer)
    ValueStores.Add(new ValueStore() With {.Index = Index, .FieldValue = FieldValue})
  End Sub
End Class

After that you do some extract methods

For instance you can kick this whole thing out into it's own method

Dim headerIndex = headersToStore.IndexOf(fieldValue.Key) 'does this key exist in the headersArray
If (headerIndex = -1) Then 'If fieldValue.Key is already in the array it doesn't need to be stored again (-1 = no index found)
    Dim displayname = String.Empty
    If (displaynames.ContainsKey(fieldValue.Key)) Then
        displayname = displaynames.Item(fieldValue.Key)
    Else
        displayname = fieldValue.Key.ToString
    End If
    headerIndex = headersToStore.Add(displayname) '' Add new header
End If

Like this.

Private Shared Function HeaderIndex(Byval fieldvaluekey as object) as integer
    Dim displaynames = ConfigurationService.FieldValueDisplayNames
    Dim headerIndex = headersToStore.IndexOf(fieldValue.Key) 'does this key exist in the headersArray
      If (headerIndex = -1) Then 'If fieldValue.Key is already in the array it doesn't need to be stored again (-1 = no index found)
        Dim displayname = String.Empty
        If (displaynames.ContainsKey(fieldValue.Key)) Then
          displayname = displaynames.Item(fieldValue.Key)
        Else
          displayname = fieldValue.Key.ToString
        End If
        headerIndex = headersToStore.Add(displayname) '' Add new header
      End If
      return headerIndex
  End Function

Which will already make your function more readable

Private Shared Function GetData(listItems As SPClient.ListItemCollection) As ArrayList
        'first store all values and make sure keys and values are matched
        Dim resultsToStore As ArrayList = New ArrayList()
        Dim headersToStore As ArrayList = New ArrayList()
        resultsToStore.Add(headersToStore)
        Dim totalListItems = listItems.Count
        Dim fieldNotToStore = ConfigurationService.FieldValuesNotToStore

        For index As Integer = 0 To totalListItems - 1
            Dim valuesToStore As ArrayList = New ArrayList()
            Dim item As SPClient.ListItem = listItems(index)
            Dim fieldValues = item.FieldValues

            For Each fieldValue In fieldValues
                If (Not fieldNotToStore.Contains(fieldValue.Key)) Then 'If it is not in this collection is must be stored
                    valuesToStore.Insert(headerIndex(fieldValue.Key), fieldValue.Value.ToString) 'use headerindex to match key an value
                End If
            Next
            resultsToStore.Add(valuesToStore) 
        Next
        Return resultsToStore
    End Function

First of All I want to thank Chrissie1 for inspiring me to get the answer. For everyone else, here's my solution.

First I created a class to represent each line in the result csv file.

Public Class MetaDataValue
     Inherits SortedDictionary(Of Integer, String)

     ''' <summary>
     ''' creates a comma seperated string of all data
     ''' </summary>
     ''' <returns></returns>
     ''' <remarks></remarks>
     Public Overrides Function ToString() As String
           ' some code to create a comma separated string of all data contained in the sorted    dictionary
     End Function
End Class

I used a sorted dictionary because that sorts all data on the key which is an integer. This way you are sure all data is sorted on the index. Furthermore using a dictionary provides a way of adding data to a collection without getting index out of bounds errors.

After that you need a way to collect all metadataValues. So I created a class:

''' <summary>
''' This class respresents a listItemCollection as provided in the constructor as table like data with headers and rows
''' </summary>
''' <remarks></remarks>
Public Class MetadataValuesFactory
    Private _fieldsNotToStore As List(Of String) = ConfigurationService.FieldValuesNotToStore
    Private _headers As MetaDataValue = New MetaDataValue()
    Private _rows As IList(Of MetaDataValue) = New List(Of MetaDataValue)

    ''' <summary>
    ''' returns a metaDAtvaleu object that contains all header values
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>

    Public Function GetHeaders() As MetaDataValue
        Dim result As MetaDataValue = New MetaDataValue()
        Dim displaynames = ConfigurationService.FieldValueDisplayNames
        For Each item In Me._headers
            Dim displayname = String.Empty
            If (displaynames.ContainsKey(item.Value)) Then
                result.Add(item.Key, displaynames.Item(item.Value))
            Else
                result.Add(item.Key, item.Value)
            End If
        Next
        Return result
    End Function

    ''' <summary>
    ''' Returns all rows that represent the values
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRows() As IList(Of MetaDataValue)
        Return _rows
    End Function

    ''' <summary>
    ''' Creates a new metadatavaluesstore with the specified values
    ''' </summary>
    ''' <param name="listItems"></param>
    ''' <remarks></remarks>

    Sub New(listItems As ListItemCollection)
        'first store all values and make sure keys and values are matched
        Dim totalListItems = listItems.Count

        For index As Integer = 0 To totalListItems - 1
            Dim valuesToStore As MetaDataValue = New MetaDataValue()
            Dim item As SPClient.ListItem = listItems(index)
            Dim fieldValues = item.FieldValues

            For Each fieldValue In fieldValues
                If (Not _fieldsNotToStore.Contains(fieldValue.Key)) Then 'If it is not in this collection is must be stored
                    'Get index of field in _headers
                    Dim headerindex As Integer = _headers.Values.ToList().IndexOf(fieldValue.Key.ToString)
                    If (headerindex = -1) Then 'If fieldValue.Key is already in the array it doesn't need to be stored again (-1 = no index found)
                        'If Not exists then store header/key in _headers ánd set previous index to index of new headers value
                        headerindex = _headers.Count '' Add new header
                        _headers.Add(headerindex, fieldValue.Key.ToString)
                    End If
                    'add value to valuesstore
                    Dim valueToStore As String
                    If (fieldValue.Value Is Nothing) Then
                        valueToStore = String.Empty
                    Else
                        valueToStore = fieldValue.Value.ToString
                    End If
                    valuesToStore.Add(headerindex, valueToStore)
                End If
            Next
            _rows.Add(valuesToStore)
        Next
 End Sub
 End Class

N.B. I called this a factory I'm not quite sure this is the right way to use this pattern. But it can't be a (ordinary) model because this code uses the configurationservice. You can't really call it a service either, so I settled for factory.

Using these two classes you can much more easily use the data contained in the factory. For example, in my original solution I changed some values with displaynames. Like Chrissie1 suggested this can be refactored. As you can see I now do this in a method that gets all headers. This way it is sort of late bound; internally the original values are used while on the outside only values are visible that the factory will allow. This way this logic is contained within the factory. If, at some point in the future some new functionality is needed, it is easy to access headers and values separately.

The code writing the CSV file is now much more understandable:

metaDataStreamWriter.WriteLine(metadataFactory.GetHeaders.ToString)
For Each storeValue In metadataFactory.GetRows
     metaDataStreamWriter.WriteLine(storeValue.ToString)
Next

Anyway this solved my problem. Many thanks again for providing feedback and for a lesson learned. If you have some comments please provide.