Title: Display images in an Access database in WPF and C#

This example shows how you can display images stored in an Access database in a WPF program. You can do this with bound controls, but I find this approach less confusing because you do everything explicitly in code.

When the program starts, the following code prepares the form to display data.

using System.Data.OleDb; using System.IO; ... // The database connection. private OleDbConnection Conn; // Display a list of titles. private void Window_Loaded(object sender, RoutedEventArgs e) { // Compose the database file name. // This assumes it's in the executable's directory. string db_name = Directory.GetCurrentDirectory() + "\\Books.mdb"; // Connect to the database Conn = new OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + db_name + ";" + "Mode=Share Deny None"); // Get the titles. OleDbCommand cmd = new OleDbCommand( "SELECT Title FROM Books ORDER BY Title", Conn); Conn.Open(); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) lstTitles.Items.Add(reader.GetValue(0)); reader.Close(); Conn.Close(); }

The most efficient method for using a database connection in .NET is to open them, take action, and then close them in relatively short transactions. The program can keep the connection around for later use as long as it's closed.

This code declares an OleDbConnection variable at the class level so it can use it throughout the code. The form's Load event handler creates a string holding the database's name. It then makes the OleDbConnection object. This example uses the Microsoft.ACE.OLEDB.12.0 provider, which works for 64-bit systems like mine. This is the place where you are most likely to need to make a change to the program.

(To find a connect string that works for your computer and database, start a new project open the Project menu, and select Add New Data Source. Connect to the database and see what connect string the wizard builds for you.)

After it has created the connection, the code creates an OleDbCommand object that selects titles and that is associated with the connection object. It then opens the connection and calls the command's ExecuteReader method to perform the query. It loops through the results adding them to the program's ListBox, closes the reader, and then closes the connection.

When you click on an entry in the ListBox, the following code executes to display that record's data, including a picture of the book's cover.

// Display information about the selected title. private void lstTitles_SelectionChanged(object sender, SelectionChangedEventArgs 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[1].ToString(); txtYear.Text = reader[2].ToString(); txtIsbn.Text = reader[3].ToString(); txtPages.Text = reader[4].ToString(); // Display the cover image. if (reader.IsDBNull(6)) imgCover.Source = null; else imgCover.Source = BytesToImage((byte[])reader.GetValue(6)); // Clean up. reader.Close(); Conn.Close(); }

This code creates an OleDbCommand object associated with the connection to select the data for the chosen title. It then opens the connection and executes the command's ExecuteReader method. It then copies the resulting URL, Year, ISBN, and Pages data into TextBoxes.

If returned field number 6 is null, the record has no image. In that case the code sets the imgCover Image control's Image property to null so it doesn't display anything.

If the record's image isn't null, the program calls the BytesToImage method to convert the record's byte data into an image and displays the result. The code finishes by closing the reader and connection.

The following code shows the BytesToImage method that does the example's most interesting work.

// Convert a byte array into a BitmapImage. private static BitmapImage BytesToImage(byte[] bytes) { var bm = new BitmapImage(); using (MemoryStream stream = new MemoryStream(bytes)) { stream.Position = 0; bm.BeginInit(); bm.CreateOptions = BitmapCreateOptions.PreservePixelFormat; bm.CacheOption = BitmapCacheOption.OnLoad; bm.UriSource = null; bm.StreamSource = stream; bm.EndInit(); } return bm; }

This method creates a new BitmapImage object. It then makes a MemoryStream associated with the byte data obtained from the database.

It sets the stream's position to the beginning and calls the BitmapImage's BeginInit method to indicate that it is about to set the object's properties. It sets some properties and then sets the image's StreamSource property to the MemoryStream. When the code calls EndInit, the image loads itself from the stream. The method then returns the resulting BitmapImage.

BytesToImage is the key to this example. It lets you convert byte data from a database into a BitmapImage. In my next post, I'll show how you can do the reverse: convert a BitmapImage into byte data and save it into a database.

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