Saving pictures to an Access database

What is the best method for storing images in a server? That's a discussion that we will never see the end.

In my opinion, has in everything in life, it depends. But in this post we will not contribute to that dilemma. The objective of this post is to show how to store the pictures in an Access database.

We start by creating a new project in VS 2013.

Next, we create a class that will manage the database.

In this class we will need a reference to a DLL that is responsible for creating the database file.

In the Reference Manager with search in the COM section.

 

 Back in the class with add a namespace: using ADOX;

Now let's add three properties to the class: the path to the database file, a connection string and an object to make the connection with the database.

class BaseDados
    {
        string caminhoBD;
        string strLigacao;
        OleDbConnection ligacaoBD;
    }

 The connection object needs another namespace: using System.Data.OleDb;

In the constructor of the class will be defined the connection string, the path to the database file and three function to create the database, open the database and create the table.

         //construtor
        public BaseDados()
        {
            caminhoBD = Environment.GetFolderPath(System.Environment.SpecialFolder.ApplicationData) + @"\BD_Access";
            if (Directory.Exists(caminhoBD) == false)
                Directory.CreateDirectory(caminhoBD);

            string nomeBD = @"\myAccessFile.accdb";
            caminhoBD += nomeBD;
            Console.WriteLine(caminhoBD);
            strLigacao = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + caminhoBD + ";";
            strLigacao += "Jet OLEDB:Database Password='12345';";  
            createDB();
            openDB();
            createTables();
        }

 In the connection string is defined a password for added security, although this password isn't secure.

In the next step we will define the three functions:


        private void createDB()
        {
            if(File.Exists(caminhoBD)==false)
            {
                Catalog cat = new Catalog();
                cat.Create(strLigacao);
            }
        }

        private void openDB()
        {
            try
            {
                ligacaoBD = new OleDbConnection(strLigacao);
                ligacaoBD.Open();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
        }

        private void createTables()
        {
            string strSQL = "CREATE TABLE Images(";
            strSQL += "_id COUNTER,";
            strSQL += "_name VARCHAR(200),";
            strSQL += "_image OLEOBJECT,";
            strSQL += "PRIMARY KEY(_id));";
            OleDbCommand comando = new OleDbCommand(strSQL, ligacaoBD);
            try
            {
                comando.ExecuteNonQuery();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
            comando.Dispose();
            comando = null;
        }

There should be some caution with the name of the fields because Access Engine won't allow some reserved words.

To close the connection with the database we will create a destructor.

//destrutor
        ~BaseDados()
        {
            closeDB();
        }

        private void closeDB()
        {
            try
            {
                ligacaoBD.Close();
                ligacaoBD.Dispose();
                ligacaoBD = null;
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
            }
        }

 And now we need two functions. One that will read the image file and returns a vector with the content, that vector will be inserted in the OLEOBJECT field. The other function will get the vector and create a file with it.
This functions will be static and created in a helper class.

class Helper
    {
        static public byte[] ImagemParaVetor(string imagem)
        {
            FileStream fs = new FileStream(imagem, FileMode.Open, FileAccess.Read);
            byte[] dados = new byte[fs.Length];
            fs.Read(dados, 0, (int)fs.Length);
            fs.Close();
            fs.Dispose();
            return dados;
        }

        static public void VetorParaImagem(byte[] imagem, string nome)
        {
            FileStream fs = new FileStream(nome, FileMode.Create, FileAccess.Write);
            fs.Write(imagem, 0, imagem.GetUpperBound(0));
            fs.Close();
            fs.Dispose();
        }
    }

To insert a image in the database will create a form like this.

The first button let's the user choose the picture.

         private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ficheiro = new OpenFileDialog();

            ficheiro.Filter = "Imagens|*.JPG;*.PNG";
            if (ficheiro.ShowDialog() == DialogResult.Cancel) return;
            string nome = ficheiro.FileName;

            pictureBox1.Image = Image.FromFile(nome);
            lbFile.Text = nome;
        }

 The second one will call the function in the helper class that returns the vector and call a new function in the class database that executes the SQL statement to create a new record in the table.
         private void button2_Click(object sender, EventArgs e)
        {
            byte[] image = Helper.ImagemParaVetor(lbFile.Text);
            bd.insertImage(textBox1.Text, image);
        }

The function in the class.
 public bool insertImage(string name, byte[] foto)
        {
            string strSQL = "INSERT INTO Images (_name,_image) VALUES (?,?);";
            OleDbCommand comando = null;
            try
            {
                comando = new OleDbCommand(strSQL, ligacaoBD);
                ////////////////////////////////fill the parameters
                comando.Parameters.AddWithValue("?", name);
                comando.Parameters.AddWithValue("?", foto);
                ////////////////////////////////execute
                comando.ExecuteNonQuery();
            }
            catch (Exception erro)
            {
                Console.WriteLine(erro.Message);
                comando.Dispose();
                comando = null;
                return false;
            }
            comando.Dispose();
            comando = null;
            return true;
        }

To be able to view the inserted pictures we will create a new form, that shows all the pictures with a scroll.

 All the controls are added dynamically. There is a panel that holds all the pictures boxes with the images.

 public partial class Form2 : Form
    {
        Form1 f = Application.OpenForms[0] as Form1;
        BaseDados bd;
        Panel panel1 = new Panel();

        public Form2()
        {

            InitializeComponent();
            bd = f.bd;
            panel1.Dock = DockStyle.Fill;
            panel1.AutoScroll = true;
            this.Controls.Add(panel1);
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            DataTable dados = bd.listImages();
            int count=0;
            foreach(DataRow row in dados.Rows)
            {
                PictureBox pb = new PictureBox();
                pb.Name = row[0].ToString();
                pb.Parent = this.panel1;
                pb.Size = new Size(320, 240);
                pb.SizeMode = PictureBoxSizeMode.StretchImage;
                pb.Location = new Point(0, count * 240);

                //criar um ficheiro com a imagem
                byte[] image=(byte[])row[2];
                Helper.VetorParaImagem(image, "temp"+count+".jpg");
                pb.Image = Image.FromFile("temp"+count+".jpg");

                count++;
            }
        }

        private void Form2_FormClosing(object sender, FormClosingEventArgs e)
        {
            foreach(Control ctrl in this.Controls)
            {
                ctrl.Dispose();
            }
            GC.Collect();
        }
    }

To access the database we use the object that is created in the main form, for that we use a reference to the form and to the object.

We need one last function that returns a data table with all the records in the database.

        public DataTable listImages()
        {
            DataTable dados = new DataTable();
            string strSQL = "SELECT * FROM Images;";
            OleDbDataReader registos;
            OleDbCommand comando = new OleDbCommand(strSQL, ligacaoBD);

            registos = comando.ExecuteReader();

            dados.Load(registos);

            return dados;
        }

 When the form is closed the garbage collector is called and all the controls in the form are disposed.

The final result.

The project.