SQLDataReader returns columns in wrong order

Question

I have a function that builds a data table in VB.Net

 Public Function GetDataTableFromSqlDataReader(ByVal dr As SqlDataReader) As DataTable

    Dim dtSchema As DataTable = dr.GetSchemaTable()
    Dim dt As New DataTable
    Dim listCols As New ArrayList

    If Not dtSchema Is Nothing Then
        For Each drow As DataRow In dtSchema.Rows
            Dim columnName As String = System.Convert.ToString(drow("ColumnName"))
            Dim column As New DataColumn(columnName, DirectCast(drow("DataType"), Type))
            column.Unique = CBool(drow("IsUnique"))
            column.AllowDBNull = CBool(drow("AllowDBNull"))
            column.AutoIncrement = CBool(drow("IsAutoIncrement"))
            listCols.Add(column)
            dt.Columns.Add(column)
        Next
    End If

    While dr.Read()
        Dim dataRow As DataRow = dt.NewRow()
        For i As Integer = 0 To listCols.Count - 1
            dataRow(DirectCast(listCols(i), DataColumn)) = dr(i)
        Next
        dt.Rows.Add(dataRow)
    End While

    Return dt

End Function

I'm returning a set of data with columns (UserName, TeamID, 10/1/2014, 10/2/2014, ..... 10/29/2014) and about 15 rows of data.

When the data reader builds the data table and adds the columns, instead of them being in the order above, they go UserName, TeamID, 10/14/2014, 10/8/2014, 10/20/2014.... with no discernible pattern.

I've verified that the SQL dataset is correct, and that when the datatable is being built is where the issue occurs.

Any ideas as to why this is happening, or what other things I should be looking it? This code works fine in all other cases, but this one time it's not adding the columns in the correct order.

EDIT:

If it makes a difference, the SQL I'm using is taking username, teamid, a count of items, and the date and putting them into a temporary table. I'm them pivoting the data to get a list of names/teamid's and the counts for each day in a single row. #P is just a list of the dates, #M holds the data being returned.

 insert into #P(PV)
    select DISTINCT ReportDate 
    FROM #M;

    select @Pivot = coalesce(@Pivot+',','')+'['+PV+']'from #P;
    SET @SQL = 'select * from (select UserName,TeamID,ReportDate,PropertyValue from #M) StdP PIVOT (SUM(PropertyValue) FOR ReportDate IN ('+@Pivot+')) as PVT order by TeamID, UserName'
        EXEC(@SQL)

This is the order returned by my SQL query:

Result set from SSMS

This is the order that the VB.Net datareader gives me..

Order returned by VB.Net code

Why not just use [DataTable.Load()](http://msdn.microsoft.com/en-us/library/7x8ccbsb.aspx) ? — Joel Coehoorn, Oct 29 '14 at 14:08
I didn't write this, the developer I replaced did, and it's used in every application we have. Up until this incident, it's worked just fine. People aren't to keen on changing what's been established. Otherwise there's a myriad of changes I would like to make to make the code more efficient. — Bigdaddyduergar, Oct 29 '14 at 14:12
Not sure why the order is not the same, but could you try to change your foreach to this one? `for each drow in dtSchema.AsEnumerable().OrderBy(Function(x) x("ColumnOrdinal"))` — Steve, Oct 29 '14 at 14:36
I like @Steve's suggestion a lot, but you could also, before your `Return dt` statement, just re-order the columns of the datatable itself... Seems like a hack for sure, but probably the safest way if you ask me.... http://stackoverflow.com/a/12937748/1693085 — John Bustos, Oct 29 '14 at 14:51
@Steve: Tried your suggestion, the first date is still 10/14. At this point I'm looking at having to use a hack around for it. :/ — Bigdaddyduergar, Oct 29 '14 at 15:18

score 0 · Answer 1 · answered Oct 29 '14 at 14:09

0

Why not just use a DataTable.Load?

Public Function GetDataTableFromSqlDataReader(ByVal dr As SqlDataReader) As DataTable
  Dim dt As New DataTable
  dt.Load(dr)
  return dt
End Function

answered Oct 29 '14 at 14:09

basher

2,381
1
23
34

I just tried using this as a test, and it returns them in the same order my original code does. So this does not work either. – Bigdaddyduergar Oct 29 '14 at 14:19
You mention a sql dataset in your question, but the code shows datareader. Can you clarify where you are seeing the data in the order: UserName, TeamID, 10/1/2014, 10/2/2014? – basher Oct 29 '14 at 14:26
When I run the query in SSMS, it shows the result set (and I could be using the wrong terminology here, If so I apologize) in the proper order. – Bigdaddyduergar Oct 29 '14 at 14:28
I just added two screen shots showing what SSMS gives me, and the order that the datareader gives me. I just dumped the data straight into excel. – Bigdaddyduergar Oct 29 '14 at 14:34

SQLDataReader returns columns in wrong order

1 Answers1