ADO.NET Reading and Writing BLOB in VB.NET

Often you may need to save user images in a database and then read back from a database when needed. For an example, we’ll save an author’s photo in a database so it can be read later to display in the author’s article. In this article, you will learn how to read and write BLOB data using ADO.NET.
  • 12276

Often you may need to save user images in a database and then read back from a database when needed. For an example, we'll save an author's photo in a database so it can be read later to display in the author's article.

The Northwind database's Employees table has a Photo field that stores images of employees. You can use this table for testing your code if you want. For this example, though, we'll create our own database.

To make it simple, we created a new AppliedAdoNet.mdb Access database and added a Users table to it. The database table schema looks like Figure C-3. Access stores BLOB objects as OLE Object data types.

ReadingBLOBImg1.jpg

Figure C-3. Users table schema

To make the application a little more interactive and user friendly, we created a Windows application, added a TextBox control, three Button controls, and a PictureBox control. The final form looks like Figure C-4. As you can pretty much guess from this figure, the Browse Image button allows users to browse for bitmap files. The Save Image button saves opened file in the database, and the Read Image button reads the first row of the database table, saves binary data as a bitmap, and displays the image in a PictureBox control.

ReadingBLOBImg2.jpg

Figure C-4. Reading and writing images in a database final form

Before writing code on the button clicks, define following variables:

' User defined variables
Private curImage As Image = Nothing
Private
curFileName As String = Nothing
Private
connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; " & "Data Source=C:\\AppliedAdoNet.mdb"
Private savedImageName As String = "C:\\ImageFromDb.BMP"

Also, don't forget to add references to the System.IO and System.Data.OleDb namespaces:

Imports System.Data.OleDb
Imports System.IO

Listing C-12 shows the Browse button click code, which simply browses bitmap files and saves the filename in the curFileName variable.

Private Sub BrowseBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BrowseBtn.Click
Dim openDlg As OpenFileDialog = New OpenFileDialog
openDlg.Filter = "All Bitmap files|*.bmp"
Dim filter As String = openDlg.Filter
openDlg.Title = "Open a Bitmap File"
If (openDlg.ShowDialog() = DialogResult.OK)
Then
curFileName = openDlg.FileName
TextBox1.Text = curFileName
End
If
End
Sub

Listing C-12. Browse Button Click Event Handler

The Save Image button code shown in Listing C-13 first creates a FileStream object from the bitmap file, opens a connection with the database, adds a new DataRow, set its values, and saves the row back to database.

Private Sub SaveImageBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveImageBtn.Click
If TextBox1.Text Is String.Empty Then
MessageBox.Show("Browse a bitmap")
Return
End
If
' Read a bitmap contents in a stream
Dim fs As FileStream = New FileStream(curFileName, FileMode.OpenOrCreate, FileAccess.Read)
Dim rawData() As Byte = New Byte(fs.Length) {}
fs.Read(rawData, 0, System.Convert.ToInt32(fs.Length))
fs.Close()
' Construct a SQL string and a connection object
Dim sql As String = "SELECT * FROM Users"
Dim conn As OleDbConnection = New OleDbConnection
conn.ConnectionString = connectionString
' Open connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
' Create a data adapter and data set
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(sql, conn)
Dim cmdBuilder As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
Dim ds As DataSet = New DataSet("Users")
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
' Fill data adapter
adapter.Fill(ds, "Users")
Dim userDes As String = "Mahesh Chand is a founder of C# Corner "
userDes += "Author: 1. A Programmer's Guide to ADO.NET;"
userDes += ", 2. Applied ADO.NET. "
' Create a new row
Dim row As DataRow = ds.Tables("Users").NewRow()
row("UserName") = "Mahesh Chand"
row("UserEmail") = [email protected]
row("UserDescription") = userDes
row("UserPhoto") = rawData
' Add row to the collection
ds.Tables("Users").Rows.Add(row)
' Save changes to the database
adapter.Update(ds, "Users")
' Clean up connection
If conn Is Nothing Then
If
conn.State = ConnectionState.Open Then
conn.Close()
End If
' Dispose connection
conn.Dispose()
End If
MessageBox.Show("Image Saved")
End Sub

Listing C-13. Save Image Button Click Event Handler

Once data is saved, the next step is to read data from the database table, save it as a bitmap again, and view the bitmap on the form. You can directly view an image using the Graphics.DrawImage method or by using a PictureBox control. In this case, we'll use a PictureBox. Listing C-14 shows the code for reading binary data. As you can see, the code simply opens a connection, creates a DataAdapter, fills a DataSet, and gets the first row of the Users table. Now if you want to read all images, you may want to modify your application or make a loop through all rows.

Once a row is read, you get the data stored in the UserPhoto column (Image column) in a stream and save it as a bitmap file. Later you can view that bitmap file in the PictureBox control by setting its Image property to the filename.

Private Sub UseReaderBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UseReaderBtn.Click
' Construct a SQL string and a connection object
Dim sql As String = "SELECT UserPhoto FROM Users"
Dim conn As OleDbConnection = New OleDbConnection
conn.ConnectionString = connectionString
' Open connection
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
Dim
cmd As OleDbCommand = New OleDbCommand(sql, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 300000
Dim outbyte(300000 - 1) As Byte
Dim
retval As Long
Dim
startIndex As Long = 0
Dim pub_id As String = ""
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
' Read first record
reader.Read()
fs = New FileStream(savedImageName, FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)
startIndex = 0
retval = reader.GetBytes(0, 0, outbyte, 0, bufferSize)
bw.Write(outbyte)
bw.Flush()
' Close the output file.
bw.Close()
fs.Close()
reader.Close()
' Display image
curImage = Image.FromFile(savedImageName)
PictureBox1.Image = curImage
PictureBox1.Invalidate()
' Clean up connection
If conn.State = ConnectionState.Open Then
conn.Close()
' Dispose connection
conn.Dispose()
End If
End
Sub

Listing C-14. Reading Binary Data

Now, you probably want to see this program in action. You can select any image by clicking the Browse Image button, which lets you browse images. Once you've selected a file, you need to save it by clicking the Save Image button. To read the image, simply click the Read Image button. This creates a temporary bitmap file named ImageFromDb.BMP file in c:// folder. You may want to change your path to C:\\. The final output looks like Figure C-5.

ReadingBLOBImg3.jpg

Figure C-5. Displaying a bitmap after reading data from a database

Free Downloads:

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.