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)




        ' 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)




        End While


        Return table

    End Function

No comments: