Picurebox SQL issue...

[huntco@gmail.com]

Need to grab an image off the disk, preview in a picturebox, then save
in SQL database for later viewing, etc.

Got the "grab/preview" part working fine, and *something* is being
stored in the SQL database, but when I go to retrieve the image and
place it in the picture box, the picture is blank. SQL datatype is
image, and the tableadapter has an update query that accepts the image
field and primary key. Image field is sent as a byte array. Length
of the image in SQL is 2 bytes more than the file on disk.

So, in trying to debug....

I've found that if I do a picturebox.imag e.save("c:\save d.tif",TIFF)
it saves it just fine, and the resultant image file on disk is
readible. But, if I do:

picturebox.imag e.save(memorySt ream,TIFF)
memoryStream.re ad(byteArray,0, memoryStream.le ngth)
system.io.file. writeAllBytes(" c:\bytes.tif",b yteArray)

the resultant file on disk is not a readible TIFF image.

....which makes me wonder how I'm supposed to convert BLOB data for
storage and retrieval in SQL. I've read through many articles which
suggest that I should:

dim byteArr(imageCo ntent.length)
imageContent.re ad(byteArr,0,im ageContent.leng th)
tableAdapter.up dateQuery(byteA rr,primaryKey)

where imageContent is system.io.fileS tream and is initialized to
system.io.file. openRead(filePa thOnDisk).

And it *appears* to work - well, it stores *something* in the SQL
database, but whatever it's storing is illegible. And when I try the
same series of steps but save to disk, it's also not recognizable as a
TIFF file.

Which takes me back to the question - how do I get BLOB data (TIFF or
other image/picture data) into SQL so that I can extract it back from
SQL and view it in a picturebox (VB.Net 2.x)?

[Mark S. Milley, MCSD (BinarySwitch)]

Re: Picurebox SQL issue...

Hi Tracy -

Have you tried adding an additional column to the database to store
the original byte count? You can use that when you're converting the
data back.

In my experience, though, the best answer is to avoid database storage
of binary data alltogether. I've always run into strange quirks like
these.

I don't know anything about the problem domain/architecture you're
currently working with, but I've found that you're typically better
off saving the file outside of the database and accessing it via
either a web share, a shared folder, a webservice, or an RPC call.
Typically, on the server, I change the file names to match the primary
key of the row that it's referring to (typically a guid), and store
the original file name in the database for renaming on-the-fly later
(if desired). This avoids any filename collisions.

It will take a little more programming on your end to make sure that
the file system coincides with the database, but it will avoid these
kinds of bugs...

Good Luck,

-Mark

[huntco@gmail.com]

Re: Picurebox SQL issue...

Kept banging my head against the wall, and here's what I came up with.

Dim byteArr2(Me.ima geContent.Lengt h) As Byte
' convert stream to byte - the hard way, because the .read
and .write operations don't seem to work
Dim aByte As Byte
Dim x1 As Integer
For x1 = 0 To Me.imageContent .Length - 1
Me.imageContent .Seek(x1, IO.SeekOrigin.B egin)
aByte = Me.imageContent .ReadByte
byteArr2(x1) = aByte
Next
tableAdapter.up dateImage(byteA rr2, primaryKey)

That will get the data into SQL in a format that can be retrieved and
displayed. Here's how to get it out:

Dim aTable As myDataTable
Dim aTableRow As myDataTableRow
aTable=tableAda pter.getByPKEY( primaryKey)
For Each aTableRow In aTable
Dim aStream As New
System.IO.Memor yStream(aTableR ow.imageColumn) ' <-- this was the trick
Me.PictureBox1. Image = Image.FromStrea m(aStream)
Next

I originally had:

dim aStream as system.io.strea m
aStream=new system.io.memor yStream
aStream.write(a TableRow.imageC olumn,0,aTableR ow.imageColumn. length)

but that gave me "invalid parameter" or something on the
image.fromStrea m(aStream) line.

Anyone have any idea why it works one way and not the other? And, is
that a bug, or a feature? :)