Title: Use filenames stored in a database to display images in C#

Approach

The reason so many people ask how to store and retrieve images in a database is that it's hard. You need to use a special data type such as the BLOB (Binary Large Object) or MEMO data type depending on the database. You also need to convert images to and from a binary format. I plan to explain how to do this soon, but there's actually a method that is much easier and in many ways more effective: simply store the images in files and store their file names in the database.

One of the biggest drawbacks to storing images in a database is that databases don't provide tools for working with images. If you want to insert an image into a record, you need to write software to do that. If you want to edit an image, you need to extract it into a file, edit it with some other tool, and store it again in the database. If an image becomes corrupted somehow, for example, a program accidentally overwrites one image with the wrong one, you need to write software to display the images until you figure out what is wrong and then you need to write more software to fix the problem.

Storing file names in the database avoids all of those problems. It also gives you more flexibility about where the files actually are. For example, you could put some images on your hard drive, others that you need less often on a DVD, and others scattered around your intranet or the internet.

These things are all possible if you store images inside a database, but things are a lot simpler if you just store the images in normal files somewhere and then load them as needed. If you need to examine, edit, or replace images, they're simple files so this is easy. You can also add images without even opening the database. The database only contains the file names, which are strings, so it can even let you search for particular images by name, something that you can't do if the images are stored in a binary format.

There are two disadvantages to this method. First, you cannot distribute the data in a single big database file. You can distribute the images in a single zip file and put them all in one directory (or more if that makes sense), however, so this isn't too big a problem. (You could also store them on a network or distributed more easily than you can with a database.

The second drawback to storing file names is that the data can become inconsistent. For example, if you delete a database record and forget to delete the corresponding image file, then that file will be sitting around taking up space but the program won't be able to use it. Similarly you might add a new image file but then store its name in the database with a typo in it so the program won't be able to find it. If the images are stored in a database, the images are in their records so these issues cannot arise. (For information about database design and issues such as data integrity during insertions and deletions, see my book Beginning Database Design Solutions.)

Code

Enough talk. Time for some code.

The example Display database records without data binding in C# explains how to fetch a database record when the user picks one from a list. The only thing this example needs to add is the ability to fetch a record's image. The following code shows how this example displays the selected record.

// Display information about the selected title. private void lstTitles_SelectedIndexChanged(object sender, EventArgs e) { if (lstTitles.SelectedIndex < 0) return; // Make a command object to get information about the title. string title = lstTitles.SelectedItem.ToString().Replace("'", "''"); OleDbCommand cmd = new OleDbCommand( "SELECT * FROM Books WHERE Title='" + title + "'", Conn); // Execute the command. cmd.Connection = Conn; Conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); // Display the text data. txtURL.Text = reader["URL"].ToString(); txtYear.Text = reader["Year"].ToString(); txtISBN.Text = reader["ISBN"].ToString(); txtPages.Text = reader["Pages"].ToString(); // Display the cover image. string filename = reader["CoverUrl"].ToString(); picCover.Image = new Bitmap(filename); // Clean up. reader.Close(); Conn.Close(); }

Most of this code is similar to the code used by the previous example. See that post for information about how it works.

The blue code shows the new step. That code gets the image file's name, loads the image into a Bitmap, and displays it in the PictureBox control named picCover. That's all there is to it!

If you want to let the user edit records, you would need to add code to let the user select a new image and to save the image in the appropriate file, but that's reasonably straightforward.

Hopefully I'll show how to truly save and restore images in a database soon.

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