Wednesday, November 28, 2007

Dynamically create a DataTable for Oracle Stored Procedures results

I recently had to write a report that flattened 20 joined tables to “dump” the contents for ad-hoc-i-ness; probably 100+ columns.  Well, I did not want to have to build a fixed DataTable (Table.Column.Add(“Col1”)…), as was the standard in our shop, so I wrote a routine to do it instead:

 

    Public Shared Function BuildDataTable(ByRef dr As OracleDataReader) As DataTable

        Dim table As New DataTable()

 

        Dim colName As String

 

        ' Create the table from the DataReader columns

        For i As Integer = 0 To dr.FieldCount - 2

            colName = dr.GetName(i)

            table.Columns.Add(colName)

        Next

 

        ' Populate the table

        While dr.Read

            Dim row As DataRow = table.NewRow()

 

            For i As Integer = 0 To dr.FieldCount - 2

                row.Item(i) = dr(i)

            Next

 

            table.Rows.Add(row)

        End While

 

        Return table

    End Function

No comments: