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

The Northwind database抯 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抣l 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.

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. 

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抰 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.

Listing C-12. Browse Button Click Event Handler

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

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.

Listing C-13. Save Image Button Click Event Handler

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") = "lzmsoft@126.com"
    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

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抣l 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.

Listing C-14. Reading Binary Data

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

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抳e 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.
Figure C-5. Displaying a bitmap after reading data from a database