How to save and retrieve Images using LINQ to SQL with SQL Compact

The code below demonstrates several features of working with System.Drawing.Image object together with LINQ to SQL, which maps a byte[] to the System.Data.Linq.Binary type.

In addition, the code also demonstrates how to convert between byte[] and System.Drawing.Image. Hope you find it useful.

 

The CREATE TABLE statement for the sample database looks like this:

-- Script Date: 22-11-2009 15:03  - Generated by ExportSqlCe version 2.2.0.3
CREATE TABLE [Images] (
  [ImageName] nvarchar(100) NOT NULL
, [Image] image NULL
);
GO
ALTER TABLE [Images] ADD PRIMARY KEY ([ImageName]);
GO
CREATE UNIQUE INDEX [UQ__Images__000000000000000A] ON [Images] ([ImageName] ASC);
GO

- thank to Georgi Yankov for inspring this blog entry!

 

using System;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace StoreImagesToSqlCe
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void buttonStoreImageToDb_Click(object sender, EventArgs e)
        {
            // Open the DataContext
            Database1 db = new Database1("Data Source=Database1.sdf");
            try
            {
                // Convert System.Drawing.Image to a byte[]
                byte[] file_byte = ImageToByteArray(pictureBox1.Image);
                // Create a System.Data.Linq.Binary - this is what an "image" column is mapped to
                System.Data.Linq.Binary file_binary = new System.Data.Linq.Binary(file_byte);
                Images img = new Images
                {
                    Image = file_binary,
                    ImageName = "Erik testing "
                };
                db.Images.InsertOnSubmit(img);
            }
            finally
            {
                // Save
                db.SubmitChanges();
            }
        }

        private void buttonRetireveImageFromDb_Click(object sender, EventArgs e)
        {
            // Open the DataContext
            Database1 db = new Database1("Data Source=Database1.sdf");

            // Get as single image from the database
            var img = (from image in db.Images
                       where image.ImageName == "Erik testing"
                       select image).Single();
            // Convert the byte[] to an System.Drawing.Image
            pictureBox1.Image = ByteArrayToImage(img.Image.ToArray());
        }

        private byte[] ImageToByteArray(System.Drawing.Image imageIn)
        {
            using (MemoryStream ms = new MemoryStream())
            {
                imageIn.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
                return ms.ToArray();
            }
        }

        public Image ByteArrayToImage(byte[] byteArrayIn)
        {
            using (MemoryStream ms = new MemoryStream(byteArrayIn))
            {
                Image returnImage = Image.FromStream(ms);
                return returnImage;
            }
        }

    }
}