Friday, September 12, 2008

Oracle Data Reader to Data Table VB.Net

One of the things that I don't like about Oracle's .Net Library is that sometimes you want more than the reader, but less than the DataAdapter.... So here is my little function to grab a reader and then return it as a data table. It returns nothing if there is nothing return


Function GetReaderAsDataTable(ByVal sql As String, ByVal Envrionment As String, Optional ByRef OtherOraConnection As OracleConnection = Nothing) As DataTable
 Try
   Dim OutputDataTable As New DataTable
   Dim x As Integer
  
   Dim cmdfunc As New OracleCommand
  
   If OtherOraConnection Is Nothing Then
    Me.ConnectToOracle(Envrionment)
    cmdfunc.Connection = mbiUser_Admin.OracleFunctionlib.oraConnection
   Else
    cmdfunc.Connection = OtherOraConnection
   End If
  
   cmdfunc.CommandText = sql
   cmdfunc.CommandType = CommandType.Text
  
   Dim drFunc As OracleDataReader
   drFunc = cmdfunc.ExecuteReader
  
  
   If Not drFunc.HasRows Then
    Return Nothing
   Else
    Dim DataRowString(drFunc.FieldCount - 1)
    For x = 0 To drFunc.FieldCount - 1
     OutputDataTable.Columns.Add(drFunc.GetName(x), drFunc.GetFieldType(x))
    Next
    While drFunc.Read
     For x = 0 To drFunc.FieldCount - 1
      DataRowString(x) = drFunc(x)
     Next
     OutputDataTable.LoadDataRow(DataRowString, True)
    End While
  
    Return OutputDataTable
   End If
  
  Catch ex As Exception
   Try
    HttpContext.Current.Session("msg") = (ex.Message & vbLf & vbCr & sql)
   Catch ex1 As Exception
    'Not in a HTTP Session; you can do something with the msg if you want... like return it to the user would be nice... but with most of my stuff just knowing that the sql didn't return anything is good enough. Robust, no, but usable.
   End Try
  
   Return Nothing
  End Try
  
  
End Function

No comments: