• Download StoringImages - 764.23 KB

Introduction

This article is to demostrate how to load images into a SQLite database and retreve them for viewing. 
It is writen in VS2010, C#, .NET4.0 and uses a ADO.NET provider System.Data.SQLite to connect to the SQLIte database.
And this all in an windows XP environment.

Background

First of all one has to obtain a few files and install them according to the rules.

• SQLite Ado.Net provider http://sqlite.phxsoftware.com
• SQLite Administrator http://sqliteadmin.orbmu2k.de
• Sqlite Administrator http://www.sqlite.org

SQLite ADO.NET provider
I installed the package into my "C:\" directory chose not to register the DLL files,
due to only wanting to include the DLL files to my project.

Using the code

SQLite

First I created a new database named ImageLib.s3db and added a table and required fields.
[ Lib for Libruary ]

CREATE TABLE [ImageStore] (
[ImageStore_Id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[ImageFile] NVARCHAR(20)  NULL,
[ImageBlob] BLOB  NULL
);

VS2010 - C# - .NET 4.0

Next I created a VS2010 project named StoringImages, changed the default namespace 
and added a few folders and files.

• folder : Database
  • file : StoringImages.s3db
    • Property : Copy to Output Directory => Copy Always
• folder : Model
• dBFunctions.cs
• dBHelper.cs
• Image.cs
• ImageHelper.cs
• file : System.Data.SQLite.dll
  • Property : Copy to Output Directory => Copy Always
• file : SQLite.Interop.dll
  • Property : Copy to Output Directory => Copy Always
• form : DisplayImages
  • This is the startup form of the project

Both System.Data.SQLite.dll and SQLite.Interop.dll need to be placed just beneath the root (project) StoringImages.
This inshores that both files are installed into the same directory as the the projects "*.exe" file.

[pic. Solution Explore] 

Model 

Within the folder Model there are a few classes, two for handling all database transactions and two for handling Image transactions.
The two for handling database transactions, dBFunctions and dBHelper, I've used before in my previous article C# & SQLite
So next I'll be explaining how to use the remaining two classes, Image and ImageHelper 

The class Image.cs I'll be using as custom made varible,
which will be used to store the data of an imported image file, so it can be passed along between methods.

The class that will be doing all the hard work is the ImageHelper.cs
Within this class you'll find various methods for handling the Insert, Delete and SaveAs of an Image.
Insert uses an other method called LoadImage which handles the binary reading of an image.
Delete is for removal of the data from the database
SaveAs is for so one can save the image back to a directory of choice.
After every transaction a transaction STATE is generated in the form of isSucces.
The view (form) DisplayImages requires this state in order to or not to update itself. 
So the code that follows, one will find in the ImageHelper.cs

ImageHelper - Asigning of references

I try never to use more references than needed, but do sometimes forget to remove the ones VS2010 automatically adds to every new class.

using System;
using System.IO;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;

ImageHelper - Declairation of variables 

MaxImageSize is used to declaire the maximum number of bytes allowed when importing an image.
Which in this example is override in the LoadImage method.

        private dBHelper helper = null;
        private string fileLocation = string.Empty;
        private bool isSucces = false;
        private int maxImageSize = 2097152;

        //2MB   - 2097152
        //5MB   - 5242880
        //10MB  - 10485760

        /*  Conversion
         *  1 Byte = 8 Bit
         *  1 Kilobyte = 1024 Bytes
         *  1 Megabyte = 1048576 Bytes
         *  1 Gigabyte = 1073741824 Bytes
         * */

dBHelper is the class that handles transactions to the database.
maxImageSize is for default maximum number of Bytes allowed during upload.
isSucces lets the view know that a transaction [Insert, Delete, SaveAs] was a succes or not.

ImageHelper - Properties

        private string FileLocation
        {
            get { return fileLocation; }
            set
            {
                fileLocation = value;
            }
        }

ImageHelper - Method GetSucces

This method is used by the form DisplayImage to acquire if a transaction[insert,delete, saveAs] was a succes or not.

        public Boolean GetSucces()
        {
            return isSucces;
        }

ImageHelper - Method LoadImage

First we ask the user for the selectedImage filelocation[path] so that we uses this in our FileStream.
Once the Filestream is open we read the image as binary and store the acquired data in an instance of the Image.cs class.
Which we'll be sending to the caller of the method LoadImage, the InsertImage method.

        private Image LoadImage()
        {
            //Create an instance of the Image Class/Object
            //so that we can store the information about the picture an send it back for
            //processing into the database.
            Image image = null;


            //Ask user to select Image
            OpenFileDialog dlg = new OpenFileDialog();
            dlg.InitialDirectory = @"C:\\";
            dlg.Title = "Select Image File";
            //dlg.Filter = "Tag Image File Format (*.tiff)|*.tiff";
            //dlg.Filter += "|Graphics Interchange Format (*.gif)|*.gif";
            //dlg.Filter += "|Portable Network Graphic Format (*.png)|*.png";
            //dlg.Filter += "|Joint Photographic Experts Group Format (*.jpg)|*.jpg";
            //dlg.Filter += "|Joint Photographic Experts Group Format (*.jpeg)|*.jpeg";
            //dlg.Filter += "|Nikon Electronic Format (*.nef)|*.nef";
            //dlg.Filter += "|All files (*.*)|*.*";
            dlg.Filter = "Image Files  (*.jpg ; *.jpeg ; *.png ; *.gif ; *.tiff ; *.nef)
                                        |*.jpg;*.jpeg;*.png;*.gif;*.tiff;*.nef";
            dlg.ShowDialog();

            this.FileLocation = dlg.FileName;

            if (fileLocation == null || fileLocation == string.Empty)
                return image;

            if (FileLocation != string.Empty && fileLocation != null)
            {
                Cursor.Current = Cursors.WaitCursor;

                //Get file information and calculate the filesize
                FileInfo info = new FileInfo(FileLocation);
                long fileSize = info.Length;

                //reasign the filesize to calculated filesize
                maxImageSize = (Int32)fileSize;

                if (File.Exists(FileLocation))
                {
                    //Retreave image from file and binary it to Object image
                    using (FileStream stream = File.Open(FileLocation, FileMode.Open))
                    {
                        BinaryReader br = new BinaryReader(stream);
                        byte[] data = br.ReadBytes(maxImageSize);
                        image = new Image(dlg.SafeFileName, data, fileSize);
                    }
                }
                Cursor.Current = Cursors.Default;
            }
            return image;
        }

ImageHelper - Method InsertImage

InsertImage is called from the view (form) DisplayImages via the NewPicture method.
Once the Insert is succesfully completed it wil return the newly obtained image_id back to the view.

As you'll notice that an instance of the class Image.cs is used between methds InsertImage and LoadImage.

        public Int32 InsertImage()
        {
            DataRow dataRow = null;
            isSucces = false;

            Image image = LoadImage();

            //if no file was selected and no image was created return 0
            if (image == null) return 0;

            if (image != null)
            {
                // Determin the ConnectionString
                string connectionString = dBFunctions.ConnectionStringSQLite;

                // Determin the DataAdapter = CommandText + Connection
                string commandText = "SELECT * FROM ImageStore WHERE 1=0";

                // Make a new object
                helper = new dBHelper(connectionString);
                {
                    // Load Data
                    if (helper.Load(commandText, "image_id") == true)
                    {
                        // Add a row and determin the row
                        helper.DataSet.Tables[0].Rows.Add(
                                           helper.DataSet.Tables[0].NewRow());
                        dataRow = helper.DataSet.Tables[0].Rows[0];

                        // Enter the given values
                        dataRow["imageFileName"] = image.FileName;
                        dataRow["imageBlob"] = image.ImageData;
                        dataRow["imageFileSizeBytes"] = image.FileSize;

                        try
                        {
                            // Save -> determin succes
                            if (helper.Save() == true)
                            {
                                isSucces = true;

                            }
                            else
                            {
                                isSucces = false;
                                MessageBox.Show("Error during Insertion");
                            }
                        }
                        catch (Exception ex)
                        {
                            // Show the Exception --> Dubbel Id/Name ?
                            MessageBox.Show(ex.Message);
                        }

                    }//END IF
                }
            }
           //return the new image_id
           return Convert.ToInt32(dataRow[0].ToString());
        }

ImageHelper - Method DeleteImage

DeleteImage executes the removal of an image from the database.
The method requires an integer, the row number of the dataset to be exactly, given by the view (form) DisplayImages via the method DeletePicture.
And after processing DeleteImage returns the "state" back to DeletePicture.

        public void DeleteImage(Int32 imageID)
        {
            //Set variables
            isSucces = false;

            // Determin the ConnectionString
            string connectionString = dBFunctions.ConnectionStringSQLite;

            // Determin the DataAdapter = CommandText + Connection
            string commandText = "SELECT * FROM ImageStore WHERE image_id=" + imageID;

            // Make a new object
            helper = new dBHelper(connectionString);
            {
                // Load Data
                if (helper.Load(commandText, "image_id") == true)
                {
                    // Determin if the row was found
                    if (helper.DataSet.Tables[0].Rows.Count == 1)
                    {
                        // Found, delete row
                        helper.DataSet.Tables[0].Rows[0].Delete();
                        try
                        {
                            // Save -> determin succes
                            if (helper.Save() == true)
                            {
                                isSucces = true;
                            }
                            else
                            {
                                isSucces = false;
                                MessageBox.Show("Delete failed");
                            }
                        }
                        catch (Exception ex)
                        {
                            // Show the Exception --> Dubbel ContactId/Name ?
                            MessageBox.Show(ex.Message);
                        }
                    }
                }
            }
        }

ImageHelper - Method SaveAsImage

To top it all off I've added a SaveAs Method.
Save the binary data back to an image file, to an allocated directory of users choice.

Once again we need to know which row of the dataset needs to be saved to file, thus our method requirs an integer as parameter.

First we set the local variables to the there default values, an C# - .Net requirement and good standard programming practise.

Then we ask the user, via a SaveDialog, for the directory location and fileName for the new image.
A dialog.Filter range is set, that we allow, and a check is executed accordingly.

The binary data is retreaved from the database with the use of dBHelper, once again using an instance of the Image class.
If dBHelper.Load returns the value "true" the FileStream is executed and writing the binary to image processed.
To end the process the "state" isSucces is returned to the view (form) DisplayImages.

        public void SaveAsImage(Int32 imageID)
        {
            //set variables
            DataRow dataRow = null;
            Image image = null;
            isSucces = false;

            // Displays a SaveFileDialog so the user can save the Image
            SaveFileDialog dlg = new SaveFileDialog();
            dlg.InitialDirectory = @"C:\\";
            dlg.Title = "Save Image File";
            //1
            dlg.Filter = "Tag Image File Format (*.tiff)|*.tiff";
            //2
            dlg.Filter += "|Graphics Interchange Format (*.gif)|*.gif";
            //3
            dlg.Filter += "|Portable Network Graphic Format (*.png)|*.png";
            //4
            dlg.Filter += "|Joint Photographic Experts Group Format (*.jpg)|*.jpg";
            //5
            dlg.Filter += "|Joint Photographic Experts Group Format (*.jpeg)|*.jpeg";
            //6
            dlg.Filter += "|Bitmap Image File Format (*.bmp)|*.bmp";
            //7
            dlg.Filter += "|Nikon Electronic Format (*.nef)|*.nef";
            dlg.ShowDialog();

            // If the file name is not an empty string open it for saving.
            if (dlg.FileName != "")
            {
                Cursor.Current = Cursors.WaitCursor;
                //making shore only one of the 7 is being used.
                //if not added the default extention to the filename
                string defaultExt = ".png";
                int pos = -1;
                string[] ext = new string[7] {".tiff", ".gif", ".png",
                                              ".jpg", ".jpeg", ".bmp", ".nef"};
                string extFound = string.Empty;
                string filename = dlg.FileName.Trim();
                for (int i = 0; i < ext.Length; i++)
                {
                    pos = filename.IndexOf(ext[i], pos + 1);
                    if (pos > -1)
                    {
                        extFound = ext[i];
                        break;
                    }
                }
                if (extFound == string.Empty) filename = filename + defaultExt;

                // Determin the ConnectionString
                string connectionString = dBFunctions.ConnectionStringSQLite;

                // Determin the DataAdapter = CommandText + Connection
                string commandText = "SELECT * FROM ImageStore WHERE image_id=" + imageID;

                // Make a new object
                helper = new dBHelper(connectionString);

                // Load the data
                if (helper.Load(commandText, "") == true)
                {
                    // Show the data in the datagridview
                    dataRow = helper.DataSet.Tables[0].Rows[0];
                    image = new Image(
                                      (string)dataRow["imageFileName"],
                                      (byte[])dataRow["imageBlob"],
                                      (long)dataRow["imageFileSizeBytes"]
                                      );

                    // Saves the Image via a FileStream created by the OpenFile method.
                    using (FileStream stream = new FileStream(filename, FileMode.Create))
                    {
                        BinaryWriter bw = new BinaryWriter(stream);
                        bw.Write(image.ImageData);
                        isSucces = true;
                    }
                }
                Cursor.Current = Cursors.Default;
            }

            if (isSucces)
            {
                MessageBox.Show("Save succesfull");
            }
            else
            {
                MessageBox.Show("Save failed");
            }
        }

View - (form) DisplayImages

The form contains a splitpanel with on the one side (left) a picture box + label and on the other side (right) a dataGridView.
It also contains an contextMenuStrip which is linked to the dataGridView. The contextMenuStrip contains the three commands for this little project.
Commands being New, Delete and SaveAs.

The form itself contains a few extra methods for handling of the commands, retreaving the data from the database and filling up the dataGridView.
The filling up of the dataGridView is only executed, at the start of the application and after every execution of a command if the command was a succes, the boolean isSucces.

 
[Pic. DisplayImages] 

Remark 

I know that the class ImageHelper and it's methods needs refactoring but I specially left it like this so that all its functionalities are contained, this makes it easier to read.

I hate reading articles about code and it's all over the place, jumping in and out methods to get a grip on things.  
 

Points of Interest

For those you read my previuos article  C# & SQLite wil recognize the two dB classes for handling all database transactions. 

History

Keep a running update of any changes or improvements you've made here.