Store Images In SQL Server Using VB.Net and OleDB

I've wasted most of my day trying to get my VB.Net program to store pictures in a SQL Server database and have finally got it working. Most of the information and examples on the Internet refer to ASP.net or C# or are not using OleDB. And as usual, I had to wade through all the infuriating spam pages to find the info I needed. So here it is.

1) Add Image Field

First, add your field to your table with the Management Studio, or use a statement something like this:

ALTER TABLE myTable ADD myPicture image;

I also had this working using VarBinary(Max) as the data type. I'm not sure what the difference is, whether both types are available in different versions of SQL Server, or why you'd use one over the other.

2) Load Image Into PictureBox

Load an image into a PictureBox on the screen, so we can see what we're working with. Obviously you don't need to do this as two stages but it might help you to get it working. I am also scaling and resizing at this stage to ensure the pictures are nice and small as we're storing the images directly in the database (not included here).

Private Sub btnSetPic_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles btnSetPic.Click

	Dim dlg As OpenFileDialog
	Dim img As Image

	Try
		dlg = New OpenFileDialog
		dlg.Filter = "All Pictures|*.bmp;*.gif;*.jpg;*.png|" & _
			"Bitmaps|*.bmp|GIFs|*.gif|JPEGs|*.jpg|PNGs|*.png"

		If dlg.ShowDialog = DialogResult.OK Then

			Me.Cursor = Cursors.WaitCursor
			PictureBox.Image = New Bitmap(dlg.FileName)

		End If

	Catch ex As Exception
		' My error handler
		m_errors.handle(ex)

	Finally
		img = Nothing
		dlg = Nothing

		Me.Cursor = Cursors.Default

	End Try

End Sub
		

3) Save the image from the PictureBox into the database

sKey is the primary key of the row that we want to contain the picture. myImage is the image we want to store. So call the function like: savePicture("FRED", PictureBox.Image)

    Private Sub savePicture(ByVal sKey As String, ByVal myImage As Image)

        Dim sql As String
        Dim da As OleDb.OleDbDataAdapter
        Dim cm As OleDb.OleDbCommand
        Dim cb As OleDb.OleDbCommandBuilder
        Dim dt As New DataTable
        Dim mstr As System.io.MemoryStream
        Dim arrImage() As Byte

        Try
            sql = _
                "SELECT rowId, myPicture " & _
                "FROM myTable " & _
                "WHERE rowId = '" & Replace(sKey, "'", "''") & "';"

            cm = New OleDb.OleDbCommand(sql, m_dbConnection)

            da = New OleDb.OleDbDataAdapter(cm)
            da.Fill(dt)

            If dt.Rows.Count = 0 Then
                Throw New CWarningException("Row " & sKey & " not found")
            End If

            If myImage Is Nothing Then
                dt.Rows(0)("myPicture") = DBNull.Value
            Else
                mstr = New System.io.MemoryStream
                myImage.Save(mstr, System.Drawing.Imaging.ImageFormat.Jpeg)
                arrImage = mstr.GetBuffer
                dt.Rows(0)("myPicture") = arrImage
            End If

            cb = New OleDb.OleDbCommandBuilder(da)

            da.Update(dt)

        Catch ex As Exception
            Throw

        Finally
            arrImage = Nothing
            mstr = Nothing
            dt = Nothing
            cb = Nothing
            cm = Nothing
            da = Nothing

        End Try

    End Sub
		

4) Retrieve the picture

Load the image data from the database and read into an Image data type.

First, get the DataTable containing the Row with the Image data we want to load into a DataTable called dt. Then use something like the following:

        Dim myImage As Image
        Dim barr() As Byte
        Dim imgstr As System.io.MemoryStream

        If IsDBNull(dr("myPicture")) Then
            ' No picture stored
            myImage = Nothing
        Else
            barr = CType(dr("myPicture"), Byte())

            If barr.GetUpperBound(0) > 0 Then
                imgstr = New System.io.MemoryStream(barr)

                myImage = Image.FromStream(imgstr)
            Else
                myImage = Nothing
            End If
        End If
		

You can then set myImage into your PictureBox or whatever.

Hopefully that should put you on the right track. I'm sure you'll have to fiddle about with this code a bit to get it working, but the method works. Wish I'd found a page like this this morning!