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!