Storing multiple images in SQL Database

Question

I'm trying to store three images from a form (PictureBox's) into a SQL Database (MSSQL2008) I'm new to programming and having a little difficulty simplifying my code. The below does what I want but is clearly inefficient. Any pointers on what to change to improve this?

    Private Sub SaveImages()

    Dim ConStr As String = "Data Source=SERVER\SQL2008;Initial Catalog=NorthPole;Integrated Security=True"
    Dim con As New SqlConnection(ConStr)
    Dim sqlCommand As New SqlCommand()
    sqlCommand.Connection = con
    sqlCommand.CommandText = "INSERT INTO Items (ItemID, ItemNumber, ItemImage1, ItemImage2, ItemImage3) SELECT NEWID(), @ItemNumber, @ItemImage1, @ItemImage2, @ItemImage3"

    sqlCommand.Parameters.Add(New SqlParameter("@ItemNumber", SqlDbType.VarChar, 20)).Value = "MS1006"

    Dim ms1 As MemoryStream = New MemoryStream()
    PictureEdit1.Image.Save(ms1, System.Drawing.Imaging.ImageFormat.Jpeg)
    sqlCommand.Parameters.Add(New SqlParameter("@ItemImage1", SqlDbType.VarBinary)).Value = ms1.GetBuffer

    Dim ms2 As MemoryStream = New MemoryStream()
    PictureEdit2.Image.Save(ms2, System.Drawing.Imaging.ImageFormat.Jpeg)
    sqlCommand.Parameters.Add(New SqlParameter("@ItemImage2", SqlDbType.VarBinary)).Value = ms2.GetBuffer

    Dim ms3 As MemoryStream = New MemoryStream()
    PictureEdit3.Image.Save(ms3, System.Drawing.Imaging.ImageFormat.Jpeg)
    sqlCommand.Parameters.Add(New SqlParameter("@ItemImage3", SqlDbType.VarBinary)).Value = ms3.GetBuffer

    con.Open()
    sqlCommand.ExecuteNonQuery()
    con.Close()

End Sub



Private Sub LoadImages()

    Dim ConStr As String = "Data Source=SERVER\SQL2008;Initial Catalog=NorthPole;Integrated Security=True"
    Dim con As New SqlConnection(ConStr)
    con.Open()
    Dim sqlCommand As New SqlCommand()
    sqlCommand.Connection = con

    sqlCommand.CommandText = "SELECT ItemImage1 FROM items WHERE ItemNumber = 'MS1006'"
    Dim buffer1 As Byte() = sqlCommand.ExecuteScalar()
    Dim ms1 As MemoryStream = New MemoryStream(buffer1)
    PictureEdit1.Image = Image.FromStream(ms1)

    sqlCommand.CommandText = "SELECT ItemImage2 FROM items WHERE ItemNumber = 'MS1006'"
    Dim buffer2 As Byte() = sqlCommand.ExecuteScalar()
    Dim ms2 As MemoryStream = New MemoryStream(buffer2)
    PictureEdit2.Image = Image.FromStream(ms2)

    sqlCommand.CommandText = "SELECT ItemImage3 FROM items WHERE ItemNumber = 'MS1006'"
    Dim buffer3 As Byte() = sqlCommand.ExecuteScalar()
    Dim ms3 As MemoryStream = New MemoryStream(buffer3)
    PictureEdit3.Image = Image.FromStream(ms3)

    con.Close()

End Sub

Is it only ever going to 3 images or will you want to make it possible to add more. — Ash Burlaczenko
– Ash Burlaczenko, Commented Aug 22, 2010 at 13:44
Theres a chance of adding more picturebox's in the future, although it would just be another PictureBox or two. — madlan
– madlan, Commented Aug 22, 2010 at 13:47

Will A · Accepted Answer · 2010-08-22 13:48:11Z

2

One simple step to cut down the duplication - have a function to get you the bytes for your jpgs...

Private Function GetJpegBytesForImage(theImage As Image) As Byte()
    Using ms As MemoryStream = New MemoryStream()
      theImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
      Return ms.GetBuffer()
    End Using
End Function

...similarly for the reverse...

Private Function GetImageForJpegBytes(theBytes As Byte()) As Image
    Using ms As MemoryStream = New MemoryStream(theBytes)
      return Image.FromStream(ms)
    End Using
End Function

Note that when loading the images, you don't need three separate queries - you can perform one query that brings back the three columns and then extract each of the images from the relevant column.

answered Aug 22, 2010 at 13:48

Will A

25k5 gold badges54 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

Storing multiple images in SQL Database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related