Title: Save images in an Access database in C#

This example shows how you can save images in an Access database.

The example Use filenames stored in a database to display images in C# shows how to display images by saving the image file names in a database and then loading the images when needed. That's my preferred method for displaying images with records but it does have a few drawbacks.

For example, with this method the images are not included in the database so you cannot distribute all of the data in a single file. This method also requires you to change the code (or some sort of configuration setting) if you move the images so the program can still find them. It may also result in orphaned image files if you delete a record and don't delete the corresponding image file.

This example shows how to save images inside an Access database. Similar techniques should work for other database types, although you may need to change a few details such as the image field's data type.

This example's database books.mdb has a single Books table that has a CoverImage field of type OLE Object. Unfortunately Access does not provide a way to initialize that field with an image. In fact, Access has a rather annoying problem (at least version 2007) which results in this warning:

IMPORTANT: If you open the database with Access, it will erase all of the image data in the CoverImage column.

The example program available for download can repopulate the CoverImage column for you so it's okay to open the database in Access to see how it works (or better, make a copy of the database and open that), but this could be a real problem in a real application. It not only means you cannot easily change the table's structure, but it also means you cannot easily examine and modify other data fields.

Assuming you've created an Access database with a table that has a field of type OLE Object, you can save images into that field. To do so, you must first convert the image into an array of bytes and then save the array using the data type OleDbType.Binary.

In this example, if you select a record and then click the Set Image button, the following code executes.

// Set the image for this record. private void btnSetImage_Click(object sender, EventArgs e) { if (ofdImage.ShowDialog() == DialogResult.OK) { try { // Display the image. Bitmap bm = new Bitmap(ofdImage.FileName); picCover.Image = bm; // Set the image in the database. // The CoverImage field has type OLE Object. string title = lstTitles.SelectedItem.ToString().Replace("'", "''"); OleDbCommand cmd = new OleDbCommand( "UPDATE Books SET CoverImage=@Image WHERE Title='" + title + "'", Conn); // Create a byte array holding the image. byte[] image_bytes = ImageToBytes(bm, ImageFormat.Png); // Add the image as a parameter. OleDbParameter param = new OleDbParameter(); param.OleDbType = OleDbType.Binary; param.ParameterName = "Image"; param.Value = image_bytes; cmd.Parameters.Add(param); // Execute the command (with no return value). cmd.Connection = Conn; Conn.Open(); cmd.ExecuteScalar(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { if (Conn.State != ConnectionState.Closed) Conn.Close(); } } }

This code displays an OpenFileDialog to let you pick an image file for the record.

If you select a file and click OK, the program loads the image into a Bitmap. It then builds a string holding the title of the book that is currently selected. It replaces single quotes (') with paired single quotes ('') in the title so the SQL statement that it builds next doesn't have mismatched single quotes.

The code then composes a SQL INSERT statement of the form:

UPDATE Books SET CoverImage=@Image WHERE Title='Beginning Database Design Solutions'

The code then calls the ImageToBytes method described shortly to convert the Bitmap into an array of bytes.

Next the program defines the parameter that is represented by the @Image part of the SQL UPDATE statement. To do that, it creates an OleDbParameter object. It sets the object's type to Binary, its name to Image (the name used in the UPDATE statement), and its value to the image's array of bytes. After creating the parameter, the program adds it to the command's Parameters collection.

The code then connects the command to the database connection Conn, opens the connection, and executes the UPDATE command. This command doesn't return any value so the program uses the ExecuteScalar method to execute it. (ExecuteScalar simply executes a command and doesn't return anything.)

This code uses a try catch block to protect itself against errors such as the selected file not being an image file. The finally block closes the database connection if it is still open.

The following code shows the ImageToBytes method that converts an image into a byte array.

// Convert an image into a byte array. private byte[] ImageToBytes(Image image, ImageFormat format) { using (MemoryStream image_stream = new MemoryStream()) { image.Save(image_stream, format); return image_stream.ToArray(); } }

This method creates a MemoryStream. It then uses the Image object's Save method to save the image into the MemoryStream with the desired format. (The example uses the png format.)

The method then simply calls the MemoryStream object's ToArray method and returns the result.

That's how you save an image into the database. In my next post I'll explain how the program recovers an image from the database.

Download the example to experiment with it and to see additional details.