How to retrieve VARBINARY values from SQL Server 2008 using VB.Net

Question

I'm trying to populate a listview with varBinary(max) values. Well, I actually need to write each varBinary into a csv file and the table consists of 100 000 odd rows.

I just don't know how to retrieve the datatype!

    Sub getInformation()
    Try
        If Not String.IsNullOrWhiteSpace(connString) Then
            Using cn As SqlConnection = New SqlConnection(connString)
                cn.Open()
                Using cmd As SqlCommand = New SqlCommand()
                    cmd.Connection = cn
                    Dim qry As String
                    qry = String.Format("SELECT [varBinaryField] FROM [dbX].[dbo].[tableX]")
                    cmd.CommandText = qry
                    cmd.CommandTimeout = 0

                    Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                        While (myReader.Read())
                            ListView1.Items.Add(myReader.get?WHAT?(0)) 'Help needed here.
                        End While
                    End Using
                End Using
            End Using
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Heres an example of one of the varBinary codes :

0x00100800C40400000210080004000000000400020810080004000000020306030B10080004000000020306030910080004000000000604040510080004000000A00100000610080004000000A001000007100800040000000000FA430C10080004000000000000000A100800040000000000000000110800500400000111080004000000010000000211080004000000000000000311080004000000010200000411080004000000040000001011080004000000010000002011080004000000010000001311080004000000A00100001411080004000000A00100001111080004000000D00000001211080004000000D00000001511080004000000000000001611080004000000010000000611080004000000FF0000002311080004000000A00100002411080004000000A00100002111080004000000D00000002211080004000000D0000000001208007C030000101208000400000002000000111208000400000000000000261208000400000001000000201208000400000043000000211208002403000042012D00DD7E3C400D390000C5003B00000000000D39000007013B00DD7E3C400D390000470146001CA3BF400D39000021014B00DD7E3C400D3900006E014B0060A529400D390000FE0059001CA3BF400D390000590173005E3636400D390000F2009400D8A055400D3900008F0194009E5AB9400D3900006501A200DFED2F400D3900001801AB005CC742400D3900005501AE00DD7EBC400D390000DF00B700E4CB963E0D3900000701BA0059584F400D3900004B01BE00DD7E3C400D3900006C01CC00DFED2F400D390000BA00CF00DB0F493F0D3900002101D10059584F400D3900000901D30057E95B400D3900006E01E2009E5AB9400D390000E400F700D4C26E400D390000F2000001520B75400D39000023010001D4C26E400D39000055010001DD7EBC400D390000FE000201D1537B400D390000B3010501E4CB16400D390000A2000701D1537B3F0D3900008601070163141D400D3900001A011A0167F283400D3900002D012101A71687400D39000094012A01658310400D390000CA002D01E63A8A400D390000CC013401D4C2EE3F0E39000031013801A238A0400E390000B5013801E63A0A400E3900004E013F0160A5A9400E3900001501490163149D400E390000A2014B01658310400E3900006C015001A0C9AC400E390000A2005201E25CA33F0E390000C1015C01E4CB16400E390000D1005E01E4CB96400E390000F000670163149D400E39000078016E01658310400E39000078017D015E36B6400E390000A701880160A529400E390000AB00A401A238A0400E3900006701A9019E5AB9400E3936000C01AE01A0C9AC400E393B00B501AE015CC742400E394200BE01B701DB0F49400E3949009401BA015E3636400E3973006501C3011CA3BF400E3975007101C30160A529400E397A003F01C801E4CB16400E397A002801D80163141D400E397A007801D801DFED2F400E397F000701DB01E4CB16400E398D00A701DB0159584F400E399D004201E40160A529400E39A2006301E401DFED2F400E39AC000701E901E25C23400E39AC00EB00EB01E25C23400E39B5005901F401DD7E3C400E39BA00D8000202DD7E3C400E39BC0063010C02DB0F49400E39CA000013080018000000311308000400000090000000351308000400000066660641

And what does this pile of binary stand for? A picture? A document? — marc_s
– marc_s, Commented Jul 12, 2013 at 10:52
A VARBINARY is just a pile of bytes as far as the db server is concerned, it can be literally anything and the db server doesn't care. You could use @sqladmin 's suggestion to dump some test data in a file and have a look at that file to see what's actually stored in there. — fvu
– fvu, Commented Jul 12, 2013 at 11:14

sqladmin · Accepted Answer · 2013-07-12 14:36:48Z

2

    Using cn As SqlConnection = New SqlConnection("Server=.;Database=test;Trusted_Connection=True;")
        cn.Open()
        Using cmd As SqlCommand = New SqlCommand()
            cmd.Connection = cn
            Dim qry As String
            qry = String.Format("SELECT field FROM test.dbo.test")
            cmd.CommandText = qry
            cmd.CommandTimeout = 0
            Dim oFileStream As System.IO.FileStream
            oFileStream = New System.IO.FileStream("c:\bytes.txt", System.IO.FileMode.Append)
            Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
                While (myReader.Read())
                    Dim data As Byte() = myReader(0)
                    oFileStream.Write(data, 0, data.Length)
                End While
                oFileStream.Close()
            End Using
        End Using
    End Using

UPDATE: here is: another example on VB.NET

edited Jul 12, 2013 at 14:36

answered Jul 12, 2013 at 11:08

sqladmin

2,2092 gold badges17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dean Hart Over a year ago

I want to save the actual binary code itself in string format.

sqladmin Over a year ago

@DeanHart - well just write it to file using 'StreamWriter' object or 'FileStream' object

sqladmin Over a year ago

@DeanHart - try code in answer on this post stackoverflow.com/questions/2549888/…

sqladmin Over a year ago

@DeanHart i've updated my answer - this code is working - but i don't understand why you need to write BLOBs into text file with csv format

Chevelle · Accepted Answer · 2020-04-26 05:24:32Z

0

this is other option and work fine

dim path as string = "c:\myFile.pdf"
Dim data As Byte() = TB.Rows(0)("documento")
Dim f As System.IO.File
f.WriteAllBytes(path, data)

answered Apr 26, 2020 at 5:24

Chevelle

2585 silver badges14 bronze badges

Collectives™ on Stack Overflow

How to retrieve VARBINARY values from SQL Server 2008 using VB.Net

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related