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
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
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment