Saving Images in Databases

[jmcilhinney]

C# version here.

This question gets asked all the time so I thought an example was in order.

Loading an image from a database field:

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("SELECT Picture FROM MyTable WHERE ID = 1", connection)
 
connection.Open()
 
Dim pictureData As Byte() = DirectCast(command.ExecuteScalar(), Byte())
 
connection.Close()
 
Dim picture As Image = Nothing
 
'Create a stream in memory containing the bytes that comprise the image.
Using stream As New IO.MemoryStream(pictureData)
    'Read the stream and create an Image object from the data.
    picture = Image.FromStream(stream)
End Using

Saving an image to a database field:

Dim connection As New SqlConnection("connection string here")
Dim command As New SqlCommand("UPDATE MyTable SET Picture = @Picture WHERE ID = 1", connection)
 
'Create an Image object.
Using picture As Image = Image.FromFile("file path here")
    'Create an empty stream in memory.
    Using stream As New IO.MemoryStream
        'Fill the stream with the binary data from the Image.
        picture.Save(stream, Imaging.ImageFormat.Jpeg)
 
        'Get an array of Bytes from the stream and assign to the parameter.
        command.Parameters.Add("@Picture", SqlDbType.VarBinary).Value = stream.ToArray()
    End Using
End Using
 
connection.Open()
command.ExecuteNonQuery()
connection.Close()

Please take note: this is example code only. Please don't paste it into your project and then ask why it doesn't work. Note also that while this code uses ExecuteScalar to get a single image's worth of data and ExecuteNonQuery to update a single existing record, the principles are the same no matter how your retrieving and saving your data. The important part is the MemoryStream. That is the link between the Byte array that gets stored in the database and the binary Image object in your VB app. For example, if you have 100 rows in a DataTable then you just use a MemoryStream for each row and assign the Byte array to the appropriate field of each row. You then just call the Update method of your DataAdapter as normal.