views:

4786

answers:

5

Hi, I have this situation. I have a real stored in a varbinary field in a sql 2005 database. As I can't convert a varbinary to a real in sql 2005, I'm trying to do that in vb.net.

That field gets stored as a byte() array in a DataTable.

Now I would like to read that byte() into a double, or decimal variable. But I don't have much of a clue on how to do that...

A: 

How is it stored in the varbinary field? String representation or actual binary representation?

hova
+2  A: 

It really depends on how it's stored, but BitConverter.ToDouble may be your friend. That's assuming it's in IEE754 format. Where are you getting the data from in the first place?

Jon Skeet
A: 

I don't know VB.net well, but know the .NET libraries.

Wrap the byte[] in a MemoryStream and wrap that in a BinaryReader. Then use the BinaryReader.ReadDouble() method. See here and here for MSDN pages.

Edit in response to this

You are looking for a piece of code looking like this:

//declare a test array
Dim testArray As Byte() = {0, 0, 0, 0}
//wrap it into a memory stream
Dim memStream As MemoryStream = new MemoryStream(testArray)
//wrap the stream in a binary reader
Dim bReader As BinaryReader = new BinaryReader(memStream)
//read a 32bit integer from the stream using the reader
Dim count As Integer = bReader.ReadInt32();
biozinc
A: 

Jon Skeet, it doesn't work...

biozinc, I'm trying to do as you say but I'm getting a "Unable to read beyond the end of the stream" exception.

This is my code:

Dim con As SqlConnection = New SqlConnection("Server=OMNILOGADMIN-PC;Database=master;Trusted_Connection=True;")
Dim cmd As SqlCommand = New SqlCommand()
Dim da As SqlDataAdapter = New SqlDataAdapter()
Dim dt As DataTable = New DataTable()
Dim ba As Byte()
Dim val As Decimal
Dim memStream As MemoryStream = New MemoryStream()
Dim bReader As BinaryReader

cmd.CommandText = "select convert(varbinary(max), cast(500 as real)) val"
cmd.Connection = con

da.SelectCommand = cmd

Try
    con.Open()
    da.Fill(dt)
    ba = dt.Rows(0).Item(0)
    'val = BitConverter.ToDouble(ba, 0)
    memStream.Write(ba, 0, ba.Length)

    MessageBox.Show( _
     memStream.Capacity.ToString() & vbCrLf & _
     memStream.Length.ToString() & vbCrLf & _
     memStream.Position.ToString())

    bReader = New BinaryReader(memStream)
    memStream.Seek(0, SeekOrigin.Begin)
    Dim testArray As Byte() = {0, 0, 0, 0}
    Dim count As Integer = bReader.Read(testArray, 0, 3)

    MessageBox.Show(count & " - " & bReader.ReadDecimal())

Catch ex As Exception
    MessageBox.Show("Erro: " & vbCrLf & ex.Message)
Finally
    con.Close()
End Try

Any clues/sugestions? thanks a lot!

What's the size of ba? What happens when you try using ToDouble?
Jon Skeet
A: 
Public Function GetDateFromBytes(ByRef value() As Byte, _
                                    ByRef startindex As Int32) As Date
    'create a aray of Ints
    Dim IntValues() As Int32 = {BitConverter.ToInt32(value, startindex), _
                                    BitConverter.ToInt32(value, (startindex + 7)), _
                                    BitConverter.ToInt32(value, startindex + 15), _
                                     BitConverter.ToInt32(value, startindex + 31)}

    Return Date.FromBinary(New Decimal(IntValues))

End Function
Sorry, my prev. code was dead wrong. here, below, is the correct one