Retrieving an image from SQL Server using dataset

[Dean Slindee]

Does anybody have an actual example of retrieving an Image data type column
from a SQL Server table using a dataset (not a datareader)? I would like
to see the statements that would move the Image returned in the dataset to a
picture box, like:

For Each dr in ds.tables(0).rows
Pic.Image = dr("ImageColumnName") 'not this simple!
Next

Thanks,
Dean Slindee

 

[Herfried K. Wagner [MVP]]

Does anybody have an actual example of retrieving an Image data type column
from a SQL Server table using a dataset (not a datareader)? I would like
to see the statements that would move the Image returned in the dataset to a
picture box, like:

For Each dr in ds.tables(0).rows
Pic.Image = dr("ImageColumnName") 'not this simple!
Next

The solution depends on how the image is stored in the database. If the
database contains the whole image files and provides them as a byte
array, you can cast 'dr(...)' to 'Byte()' using 'DirectCast', then write
the data to a 'MemoryStream' and instantiate the image from the data in
the memory stream using 'Image.FromStream'. Notice that the stream must
be kept open as long as the image object is used.

 

[Samuel L Matzen]

Dean,

Cast the Image field to a ByteArray, create a MemoryStream from the
ByteArray, convert the MemoryStream to a Bitmap and assign the BitMap to the
Image property of the PictureBox.

This will only work if the image is a "raw" picture. If it has an OLE
wrapper around it won't work, so if you are trying to display images from
Access or the Pictures in the Northwind database, this won't work unless you
can figure out how to remove the OLE weapper.

It works fine if the image in the field is a raw image only.

Something like:

<code (VB.NET) >
Me.PictureBox1.Image = New Bitmap(New
System.IO.MemoryStream(DirectCast(Me.DataSet11.Tables("PersonPicture").Rows(
0)("Picture"), Byte())))
</code>


-Sam Matzen

 

[Dean Slindee]

Thanks for the code Sam. I have used it below; syntax is correct. Still
get an "Invalid parameter used" message on the "picImage.Image = ..."
statement. There is one row in the dataset, it gets selected and returned.
The image column can be either "binary" or "image" in SQL Server, same error
message. Loaded a .jpg and/or .gif file from C:/Program Files\Microsoft
Office\Clipart\Pub60Cor\anyfile.JPG. Also loaded an image that I personally
scanned myself, same error message. Care to hazard any guesses? Thanks,
Dean
ds = GetImageRowWithImageID(ImageID)

For Each dr In ds.Tables(0).Rows

picImage.Image = New Bitmap(New
System.IO.MemoryStream(DirectCast(ds.Tables(0).Rows(0)("Image"), Byte())))

Next

 

[Samuel L Matzen]

Dean,

Best guess is that whatever you have in your database is not a raw picture.
I suspect it is either corrupted or has some sort of wrapper around it that
the picturebox doesn't understand.

-Sam Matzen

 

[Ken Tucker [MVP]]

Hi,

This will work with the northwind database. Note the northwind
database has an offset of 78 (see the ms.write line) you might be able to
replace the 78 with 0 depending on how it is saved in the database. Included
a link to sample program.

Dim dr As DataRow = ds.Tables("Categories").Rows(ListBox1.SelectedIndex)

Dim ms As New System.IO.MemoryStream

Dim bm As Bitmap

Dim arData() As Byte = dr.Item("Picture")

ms.Write(arData, 78, arData.Length - 78)

bm = New Bitmap(ms)

PictureBox1.Image = bm



http://www.onteorasoftware.com/downloads/northwindimages.zip



Ken

-----------------------

Dean,

Cast the Image field to a ByteArray, create a MemoryStream from the
ByteArray, convert the MemoryStream to a Bitmap and assign the BitMap to the
Image property of the PictureBox.

This will only work if the image is a "raw" picture. If it has an OLE
wrapper around it won't work, so if you are trying to display images from
Access or the Pictures in the Northwind database, this won't work unless you
can figure out how to remove the OLE weapper.

It works fine if the image in the field is a raw image only.

Something like:

<code (VB.NET) >
Me.PictureBox1.Image = New Bitmap(New
System.IO.MemoryStream(DirectCast(Me.DataSet11.Tables("PersonPicture").Rows(
0)("Picture"), Byte())))
</code>


-Sam Matzen

 

[Cor Ligthert]

Hi Dean,

When you still strugling have than a look at this sample I once made.

It uses not the database itself however the dataset, but that is the same as
a database, it needs only an update or a select for whatever database

I hope this helps, the sample goes completly, it needs only a formproject, a
picturebox on it and 4 buttons. (When you use something else than a pic you
have to delete the picturebox part, that is by the way only for showing).

I hope this helps

Cor

Private abyt() As Byte
Private fo As New OpenFileDialog
Private sf As New SaveFileDialog
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
'Reading a pictur from file and put it in a bytearray
If fo.ShowDialog = DialogResult.OK Then
Dim fs As New IO.FileStream(fo.FileName, _
IO.FileMode.Open)
Dim br As New IO.BinaryReader(fs)
abyt = br.ReadBytes(CInt(fs.Length))
br.Close()
'just to show the sample without a fileread error
Dim ms As New IO.MemoryStream(abyt)
Me.PictureBox1.Image = Image.FromStream(ms)
End If
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles Button2.Click
'writing a picture from a bytearray to a file
If sf.ShowDialog = DialogResult.OK Then
Dim fs As New IO.FileStream(sf.FileName, _
IO.FileMode.CreateNew)
Dim bw As New IO.BinaryWriter(fs)
bw.Write(abyt)
bw.Close()
End If
End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles Button3.Click
'writing a bytearray to a dataset and than to disk
Dim ds As New DataSet
ds.Tables.Add(New DataTable("Photo"))
ds.Tables(0).Columns.Add(New DataColumn("Sample"))
ds.Tables(0).Columns(0).DataType =
System.Type.GetType("System.Byte[]")
ds.Tables(0).Rows.Add(ds.Tables(0).NewRow)
ds.Tables(0).Rows(0)(0) = abyt
Dim sf As New SaveFileDialog
If sf.ShowDialog = DialogResult.OK Then
ds.WriteXml(sf.FileName, XmlWriteMode.WriteSchema)
End If
End Sub

Private Sub Button4_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button4.Click
'reading a picture from a dataset from disk and set it in the pic
box
Dim ds As New DataSet
If fo.ShowDialog = DialogResult.OK Then
ds.ReadXml(fo.FileName)
End If
abyt = CType(ds.Tables(0).Rows(0)(0), Byte())
Dim ms As New IO.MemoryStream(abyt)
Me.PictureBox1.Image = Image.FromStream(ms)
End Sub