Display database pictures in a ListView control in C#

[display database pictures]

This example shows how you can display database pictures in a ListView control. My previous post, Create ListView icons at run time in C#, explained how to make images at runtime to display in a ListView control. This example follows from that one. Simply get the images you want from the database and the use them to create the images you want to display in the ListView.

That begs the question, “How do you get the images from the database? The example Load images from an Access database in C# shows how to do that.

This is a fairly long example but the separate pieces aren’t too bad. The basic steps are:

  • Read book records from the database.
  • Get the image in each record.
  • Resize the image appropriately without distorting it and save the resized image in the appropriate ImageList control, using the book’s title as the images’ keys.
  • Create the ListView item, using the book’s title as the item’s key so the ListView control knows which images to use for the item.

The paragraphs that follow explain the details.

The following code shows the part of the form’s Load event handler that works with the database.

// Compose the database file name.
// This assumes it's in the executable's directory.
string db_name = Application.StartupPath +
    "\\books_with_images.mdb";

// Connect to the database
using (OleDbConnection conn =
    new OleDbConnection(
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=" + db_name + ";" +
        "Mode=Share Deny None"))
{
    // Get the book information.
    OleDbCommand cmd = new OleDbCommand(
        "SELECT Title, URL, ISBN, CoverUrl, " +
        "Pages, Year, CoverImage FROM Books ORDER BY Year DESC",
        conn);
    conn.Open();
    using (OleDbDataReader reader = cmd.ExecuteReader())
    {
        lvwBooks.Items.Clear();
        imlLargeIcons.Images.Clear();
        imlSmallIcons.Images.Clear();
        while (reader.Read())
        {
            // Make the images.
            if (!reader.IsDBNull(6))
            {
                // Get the image.
                Bitmap bm = BytesToImage((byte[])reader.GetValue(6));
                float source_aspect = bm.Width / (float)bm.Height;

                // Make the large image.
                AddImageToImageList(imlLargeIcons,
                    bm, reader[0].ToString(),
                    imlLargeIcons.ImageSize.Width,
                    imlLargeIcons.ImageSize.Height);

                // Make the small image.
                AddImageToImageList(imlSmallIcons,
                    bm, reader[0].ToString(),
                    imlLargeIcons.ImageSize.Width,
                    imlLargeIcons.ImageSize.Height);
            }

            // Add the data row.
            lvwBooks.AddRow(
                reader[0].ToString(),   // Image key
                reader[0].ToString(),   // Title
                reader[1].ToString(),   // URL
                reader[2].ToString(),   // ISBN
                reader[3].ToString(),   // CoverUrl
                reader[4].ToString(),   // Pages
                reader[5].ToString());  // Year
        }
    }
}

This code connects to an Access database. (You should modify that part to connect to your database if you’re not using Access or if you need to use a different provider.) It then executes a database query that selects data from the Books table. The CoverImage field holds the books’ cover images.

The code clears the ListView and ImageList controls, and then enters a while loop that runs as long as the query returns another row of data.

Inside the loop, the code uses the reader’s IsDBNull method to see whether the image is empty. (There’s no point in doing a lot of work building a bitmap if there’s no image in the database.)

If the image exists, the program uses BytesToImage method described shortly to convert the data in that field into a Bitmap. The code then calls the AddImageToImageList method (also described shortly) twice to make large and small versions of the cover image and to add them to the program’s ImageList controls. The images are saved in the ImageList controls with the book’s title used as a key.

Having created the images, the program calls the AddRow extension method to create the ListView row.

The following code shows the BytesToImage method.

// Convert a byte array into an image.
private Bitmap BytesToImage(byte[] bytes)
{
    using (MemoryStream image_stream =
        new MemoryStream(bytes))
    {
        Bitmap bm = new Bitmap(image_stream);
        return bm;
    }
}

This method creates a MemoryStream representing the bytes that hold the image data. It passes the Bitmap constructor the stream and returns the resulting Bitmap object.

The following code shows the AddImageToImageList method.

// Scale the image to fit in the ImageList and add it.
private void AddImageToImageList(ImageList iml, Bitmap bm,
    string key, float wid, float hgt)
{
    // Make the bitmap.
    Bitmap iml_bm = new Bitmap(
        iml.ImageSize.Width,
        iml.ImageSize.Height);
    using (Graphics gr = Graphics.FromImage(iml_bm))
    {
        gr.Clear(Color.Transparent);
        gr.InterpolationMode = InterpolationMode.High;

        // See where we need to draw the image to scale it properly.
        RectangleF source_rect = new RectangleF(
            0, 0, bm.Width, bm.Height);
        RectangleF dest_rect = new RectangleF(
            0, 0, iml_bm.Width, iml_bm.Height);
        dest_rect = ScaleRect(source_rect, dest_rect);

        // Draw the image.
        gr.DrawImage(bm, dest_rect, source_rect,
            GraphicsUnit.Pixel);
    }

    // Add the image to the ImageList.
    iml.Images.Add(key, iml_bm);
}

This method scales an image to make it as large as possible within specific dimensions and without distorting it. It then adds the image to an ImageList control. This program uses the method twice per image to add pictures to the small and large ImageList controls.

The method first creates a Bitmap that is the right size for the ImageList control. It then makes a Graphics object associated with the Bitmap.

The code clears the Bitmap with a transparent background. It then creates two rectangles. The rectangle source_rect represents the size of the input Bitmap that must be scaled. The rectangle dest_rect represents the size available in the new Bitmap. (The size of the images in the ImageList control.)

Next, the code calls the ScaleRect method to get the largest rectangle that fits in dest_rect without distortion. The code uses the resulting rectangle and source_rect to draw the input image into the new Bitmap. The method finishes by adding the new image to the ImageList control. It gives the new image the key value that was passed into the method.

The following code shows the ScaleRect method.

// Scale an image without disorting it.
// Return a centered rectangle in the destination area.
private RectangleF ScaleRect(
    RectangleF source_rect, RectangleF dest_rect)
{
    float source_aspect =
        source_rect.Width / source_rect.Height;
    float wid = dest_rect.Width;
    float hgt = dest_rect.Height;
    float dest_aspect = wid / hgt;

    if (source_aspect > dest_aspect)
    {
        // The source is relatively short and wide.
        // Use all of the available width.
        hgt = wid / source_aspect;
    }
    else
    {
        // The source is relatively tall and thin.
        // Use all of the available height.
        wid = hgt * source_aspect;
    }

    // Center it.
    float x = dest_rect.Left + (dest_rect.Width - wid) / 2;
    float y = dest_rect.Top + (dest_rect.Height - hgt) / 2;
    return new RectangleF(x, y, wid, hgt);
}

This method calculates the aspect ratios (ratio of width to height) of the two rectangles. If the source rectangle is relatively short and wide compared to the destination rectangle, the code makes the result as wide as the destination rectangle and calculates its height. If the source rectangle is relatively tall and thin compared to the destination rectangle, the code makes the result as tall as the destination rectangle and calculates its width.

The method centers the resulting rectangle and returns the result.

The final interesting piece of the program is the following AddRow extension method.

// Add a row to the ListView.
public static void AddRow(this ListView lvw, string key,
    string item_title, params string[] subitem_titles)
{
    // Make the item.
    ListViewItem new_item = lvw.Items.Add(item_title, key);

    // Make the sub-items.
    for (int i = subitem_titles.GetLowerBound(0);
             i <= subitem_titles.GetUpperBound(0);
             i++)
    {
        new_item.SubItems.Add(subitem_titles[i]);
    }
}

This method creates a new ListViewItem giving it the indicated text and key string. This key string lets the ListView figure out which image in the ImageList controls to use for the item. The method then loops through the item's other values to create the ListView sub-items.

Download the example to see additional details.


Download Example   Follow me on Twitter   RSS feed   Donate




This entry was posted in controls, user interface and tagged , , , , , , , , , , , , , , , , , , , . Bookmark the permalink.

10 Responses to Display database pictures in a ListView control in C#

  1. ghasem says:

    please create with SQL server 2008r2 …
    i try … butt —> error in arrey !!!

    my table name is “TKala” :
    id —> int
    cod —> int
    kala —> nvarChar
    vahed —> nvarChar
    Mojodi —> int
    Price —> int
    anbar —> nvarChar
    Pic —> “image”
    toz —> nvarChar

    please help !

    • RodStephens says:

      Sorry but I don’t see a problem with what you’re trying to do. Is SQL Server itself not allowing you to create the table? Or are you writing code to insert values and that’s failing?

  2. ghasem says:

    Thank you
    Sql code but no problem, the problem is that the array can not remove images from binary …

  3. ghasem says:

    very thanks …
    is OK , error (change field —> binary to image in sql)

    this pic :
    http://s4.picofile.com/file/8170623768/Untitledarrey_ok.png

    good luck
    🙂

  4. ghasem says:

    The program works! (With your help)
    OK
    ——————————————-
    excuse me …
    I do not speak English well ….

  5. Barbaro says:

    Awesome work, If only it was on sql tho..thanks at least i know now its possible to do it

  6. Pingback: Use a password protected Access database in C# - C# HelperC# Helper

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.