Save images in an Access database in C#

[save images]

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 Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in database, graphics, image processing, Office and tagged , , , , , , , , , , , , , , , , , . Bookmark the permalink.

4 Responses to Save images in an Access database in C#

  1. Mike Griffiths says:

    Not sure we should be using Access databases for anything even remotely serious these days. [Mind it was a fantastic resource in the heady days of the jet “engine” for VB3 but you know, time passes ;)].

    SQL Server for anything serious and the Express version can handle some pretty exacting tasks as it is only limited by database size as far as I can see. The SQL Server Management Studio is a free download and manages Express databases as well as the paid licence variety.

    SQLite is my file system of choice for “desktop” apps these days. It is fast and brilliant for single user access. Multi-user can be managed very well with a little care although I have hit issues with multiple threads trying simultaneous transactions. There is a great .NET library for C# – might make some great posts.

    Best wishes.

    • RodStephens says:

      Yes and no. For anything really serious, Access is not a great choice. It has limited features and doesn’t support multiple users very well.

      But a lot are still using it. It also lets you easily distribute a database in a file, doesn’t require you to start a separate server process (which I still think is weird), and is much less complicated.

      If you’re planning to move to a bigger application, then yes, you should start with SQL Server Express or some other product like SQLite or MySql.

      Some of the concepts in this post still apply. For example, they all have different ways to store binary data and it’s a hassle to move image data in and out.

      (The truth is in a post like this trying to explain how to install and run SQL Server Express or even one of the simpler products like MySql isn’t worth the effort and takes away from the main topic I’m trying to cover. Maybe as a separate post or a series of posts.)

  2. Pingback: Load images from an Access database in C# - C# HelperC# Helper

  3. Pingback: Save several images into Access in C# - C# HelperC# Helper

Leave a Reply

Your email address will not be published. Required fields are marked *