Best Conversion Method? - Oracle Raw(16) to Sql Guid

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am adding support to my application for Oracle 10g and using Enterprise
Library Data Access Application Blocks and trying to determine the best way
to convert the GUID's which are stored as RAW(16) in Oracle (Byte Arrays)
back to GUID's in my data layer. My best guess is a conversion method that
the DataSet goes through before leaving the data access layer. Here is my
code:

Private Sub Raw16ToGuid(ByRef dataSet As DataSet)
Dim columnIndices As New ArrayList
Dim dataColumn As DataColumn
For Each DataColumn In dataSet.Tables(0).Columns
If dataColumn.DataType Is System.Type.GetType("System.Byte[]")
Then
columnIndices.Add(dataColumn.Ordinal)
End If
Next
Dim dataRow As DataRow
For Each dataRow In dataSet.Tables(0).Rows
Dim index As Integer = 0
For Each index In columnIndices
dataRow(index) = New Guid(DirectCast(dataRow(index), Byte()))
Next
Next
dataSet.AcceptChanges()
End Sub

I know this will attempt to convert any byte array to a GUID but since I
don't use any other byte arrays currently, it seems better than checking each
dataItem for type and length before attempting the conversion. Any feedback
or suggestions would be appreciated.
 
I started using this and found a problem. The data in the datarows is
converted but the datacolumn datatype stays as a byte array. If the dataset
is serialized for any reason (remoting was mine), there is an
invalidcastexception. Any suggestions on fixing this issue? I tried changing
the datacolumn.datatype but got an error that it can't be changed after the
dataset is created. In the mean time, I rewrote the function as follows:

Private Function Raw16ToGuid(ByRef dataSet As DataSet) As DataSet
' Creates a new dataset with GUID column and converted datatype
in place of byte arrays
Dim table As DataTable
Dim newDataSet As New DataSet
For Each table In dataSet.Tables
Dim newDataTable As New DataTable(table.TableName)
Dim columnIndices As New ArrayList
Dim dataColumn As DataColumn
For Each dataColumn In table.Columns
If dataColumn.DataType Is
System.Type.GetType("System.Byte[]") Then
columnIndices.Add(dataColumn.Ordinal)
Dim newDataColumn As New
DataColumn(dataColumn.ColumnName, System.Type.GetType("System.Guid"))
newDataTable.Columns.Add(newDataColumn)
Else
Dim newDataColumn As New
DataColumn(dataColumn.ColumnName, dataColumn.DataType)
newDataTable.Columns.Add(newDataColumn)
End If
Next
Dim dataRow As DataRow
For Each dataRow In table.Rows
Dim index As Integer = 0
Dim newDataRow As DataRow = newDataTable.NewRow
For Each item As Object In dataRow.ItemArray
If columnIndices.Contains(index) Then
newDataRow(index) = New Guid(CType(item,
Byte())) ' convert guid
Else
newDataRow(index) = item
End If
index += 1
Next
newDataTable.Rows.Add(newDataRow)
Next
newDataSet.Tables.Add(newDataTable)
Next
Return newDataSet
End Function

This creates a new dataset with the row translated. I am sure this is less
efficient but I can't find a way to cleanly translate the datatype in place.
Thanks for any feedback.


Jim Shank said:
I am adding support to my application for Oracle 10g and using Enterprise
Library Data Access Application Blocks and trying to determine the best way
to convert the GUID's which are stored as RAW(16) in Oracle (Byte Arrays)
back to GUID's in my data layer. My best guess is a conversion method that
the DataSet goes through before leaving the data access layer. Here is my
code:

Private Sub Raw16ToGuid(ByRef dataSet As DataSet)
Dim columnIndices As New ArrayList
Dim dataColumn As DataColumn
For Each DataColumn In dataSet.Tables(0).Columns
If dataColumn.DataType Is System.Type.GetType("System.Byte[]")
Then
columnIndices.Add(dataColumn.Ordinal)
End If
Next
Dim dataRow As DataRow
For Each dataRow In dataSet.Tables(0).Rows
Dim index As Integer = 0
For Each index In columnIndices
dataRow(index) = New Guid(DirectCast(dataRow(index), Byte()))
Next
Next
dataSet.AcceptChanges()
End Sub

I know this will attempt to convert any byte array to a GUID but since I
don't use any other byte arrays currently, it seems better than checking each
dataItem for type and length before attempting the conversion. Any feedback
or suggestions would be appreciated.
Click to expand...
 
Back
Top