What Class for Serializable Multidimensional Arrays?

Question

EDIT: See Below

I have a web service which uses a class of functions in order to return data used in various business processes (via InfoPath).

One of the functions takes a given SQLCommand object and executes it into a SQLDataReader. Now depending on the SQL command text used this may return one or many rows of one or many columns. So what is the best class for this function to return bearing in mind it needs to be serialized by the web service.

My existing code is:

    Dim array As New ArrayList

    Try

        conCMS.Open()
        Dim rdr As SqlDataReader = cmdCurrent.ExecuteReader

        While rdr.Read
            If rdr.VisibleFieldCount > 1 Then
                Dim complexType(rdr.VisibleFieldCount - 1) As String
                For rowIndex As Integer = 0 To rdr.VisibleFieldCount - 1
                    complexType(rowIndex) = rdr(rowIndex)
                Next
                array.Add(complexType)
            Else
                array.Add(rdr(0))
            End If
        End While
        conCMS.Close()

        Return array

    Catch ex As Exception

        array.Add("ERROR " & ex.Message)

    End Try

    Return Nothing

Now I know this is not efficient code, but this a work in progress.

As you can probably see this is generating a string array to represent a row with more than one column, however this cannot be serialized by the web service.

So 2 things really;

• Some guidance on an effective type to use (without writing a serializable class of my own)
• Some advice on improving the code going forward.

Thanks in advance

EDIT: I have managed to get serialization to work by simply creating a nested arrayList as follows (doh!):

   If rdr.VisibleFieldCount > 1 Then
    Dim complexType As New ArrayList
    For rowIndex As Integer = 0 To rdr.VisibleFieldCount - 1
      complexType.Add(rdr(rowIndex))
    Next
    array.Add(complexType)
   Else
    array.Add(rdr(0))
   End If

However please let me know how this could be improved.

Answer 1

If your code knows the schema of the returned data before the call, then you should return data in the same shape. Have a struct or class with properties of the appropriate type for each column of the returned data. For each row, create an instance of such a struct, and fill in the properties from the returned columns. Then add each instance to a strongly-typed list of that struct, a List(Of T). Then return the list.

OBTW, ArrayList was created before we had generics in .NET. Today, it's better to use strongly-typed collections and not ArrayList, which is basically List(Of anything).