Inserting images into MS Access file using OLEDB

In this article I show to insert images into MS Access file and also to perform search, update and delete operations.


Objective:

To insert images into m.s.access file and also to perform search, update and delete operations.

Design:

1.gif

Design the form as above with a OpenFileDialog contol, 3 Labels, 3 TextBoxes, 11 buttons.

PictureBox1 Properties:

BorderStyle=Fixed3D; SizeMode=StrechImage

Note that OpenFileDialog control appears below the form(not on the form), which can be used for browsing an image.

Introduction:

Inorder to use OleDb Connection include the namespace: 'using System.Data.OleDb'

For accesing records from M.S.Access-2003 file we use 'Jet' driver,

And for accesing records from M.S.Access-2007 file we use 'Ace' driver.

As we want to insert images into the msaccess, first we have to create a table in the m.s.access file, we can use the data type 'ole object' for storing the image.

In this application, we will search a record by taking input from the InputBox. For this we have to add reference to Microsoft.VisualBasic.

Adding a Reference:

Goto Project Menu->Add Reference -> select 'Microsoft.VisualBasic' from .NET tab.

Inorder to use this we have to include the namespace: 'using Microsoft.VisualBasic'

Converting image into binary data: 

We can't store an image directly into the database. For this we have two solutions:
  1. To store the location of the image in the database
  2. Converting the image into binary data and insert that binary data into database and convert that back to image while retrieving the records.
If we store the location of an image in the database, and suppose if that image is deleted or moved from that location, we will face problems while retrieving the records. So it is better to convert image into binary data and insert that binary data into database and convert that back to image while retrieving records.

We can convert an image into binary data using 1. FileStream (or) 2. MemoryStream

1. FileStream uses file location to convert an image into binary data which we may/may not provide during updation of a record.

Ex:

FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open, FileAccess.Read);
byte[] photo_aray = new byte[fs.Length];
fs.Read(photo_aray, 0, photo_aray.Length);

2. So it is better to use MemoryStream which uses image in the PictureBox to convert an image into binary data.

Ex:

MemoryStream ms = new MemoryStream();
pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
byte[] photo_aray = new byte[ms.Length];
ms.Position = 0;
ms.Read(photo_aray, 0, photo_aray.Length);

Inorder to use FileStream or MemoryStream we have to include the namespace: 'using System.IO'.
             
OpenFileDialog Control:

We use OpenFileDialog control inorder to browse for the images (photos) to insert into the record.

Creating a primary key in the dataTable:

In this app. we use Find() method to search a record, which requires details of primarykey column for database tables this provided using statement: adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

But as we don't have any primarykey column in M.S.Access table, we have to create a primary key column in datatable.

Eg:

ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);

Code:

using System;
using System.Data;
using System.Drawing;
using System.Drawing.Imaging;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;
using Microsoft.VisualBasic;

namespace access_img
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        OleDbConnection con;
        OleDbCommand cmd;
        OleDbDataAdapter adapter;
        DataSet ds;
        int rno=0;
        MemoryStream ms;
        byte[] photo_aray;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new OleDbConnection(@" provider=microsoft.ace.oledb.12.0; data source=e:\prash\stud.accdb; persist securityiInfo=false");//stud.accdb->access07 filename
            //con = new OleDbConnection(@" provider=microsoft.jet.oledb.4.0; data source=e:\prash\stud.mdb");//stud.mdb->access03 filename
            loaddata();
            showdata();
        }
        void loaddata()
        {
            adapter = new OleDbDataAdapter("select * from student", con);
            ds = new DataSet();//student-> table name in stud.accdb/stud.mdb file
            adapter.Fill(ds, "student");
            ds.Tables[0].Constraints.Add("pk_sno", ds.Tables[0].Columns[0], true);//creating primary key for Tables[0] in dataset
        }
        void showdata()
        {
            textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();
            textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();
            textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();
            pictureBox1.Image = null;
            if (ds.Tables[0].Rows[rno][3] != System.DBNull.Value)
            {
                photo_aray = (byte[])ds.Tables[0].Rows[rno][3];
                MemoryStream ms = new MemoryStream(photo_aray);
                pictureBox1.Image = Image.FromStream(ms);
            }
        }
        private void btnBrowse_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";
            DialogResult res = openFileDialog1.ShowDialog();
            if (res == DialogResult.OK)
            {
                pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);
            }
        }
        private void btnInsert_Click(object sender, EventArgs e)
        {
            cmd = new OleDbCommand("insert into student(sno,sname,course,photo) values(" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "',@photo)", con);
            conv_photo();
            con.Open();
            int n = cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            {
                MessageBox.Show("record inserted");
                loaddata();
                rno++;
            }
            else
                MessageBox.Show("insertion failed");
        }
        void conv_photo()
        {
            //converting photo to binary data
            if (pictureBox1.Image != null)
            {
                //using MemoryStream:
                ms = new MemoryStream();
                pictureBox1.Image.Save(ms, ImageFormat.Jpeg);
                byte[] photo_aray = new byte[ms.Length];
                ms.Position = 0;
                ms.Read(photo_aray, 0, photo_aray.Length);
                cmd.Parameters.AddWithValue("@photo", photo_aray);
            }
        }
        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 100, 100));
                DataRow drow;
                drow = ds.Tables[0].Rows.Find(n);
                if (drow != null)
                {
                    rno = ds.Tables[0].Rows.IndexOf(drow);
                    textBox1.Text = drow[0].ToString();
                    textBox2.Text = drow[1].ToString();
                    textBox3.Text = drow[2].ToString();
                    pictureBox1.Image = null;
                    if (drow[3] != System.DBNull.Value)
                    {
                        photo_aray = (byte[])drow[3];
                        MemoryStream ms = new MemoryStream(photo_aray);
                        pictureBox1.Image = Image.FromStream(ms);
                    }
                }
                else
                    MessageBox.Show("Record Not Found");
            }
            catch
            {
                MessageBox.Show("Invalid Input");
            }
        }
        private void btnUpdate_Click(object sender, EventArgs e)
        {
            cmd = new OleDbCommand("update student set sname='" + textBox2.Text + "', course='" + textBox3.Text + "', photo=@photo where sno=" + textBox1.Text, con);
            conv_photo();
            con.Open();
            int n = cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            {
                MessageBox.Show("Record Updated");
                loaddata();
            }
            else
                MessageBox.Show("Updation Failed");
        }
        private void btnDelete_Click(object sender, EventArgs e)
        {
            cmd = new OleDbCommand("delete from student where sno=" + textBox1.Text, con);
            con.Open();
            int n = cmd.ExecuteNonQuery();
            con.Close();
            if (n > 0)
            {
                MessageBox.Show("Record Deleted");
                loaddata();
                rno = 0;
                showdata();
            }
            else
                MessageBox.Show("Deletion failed");
        }
        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                rno = 0;
                showdata();
                MessageBox.Show("First Record");
            }
            else
                MessageBox.Show("no records");
        }
        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (rno > 0)
                {
                    rno--;
                    showdata();
                }
                else
                    MessageBox.Show("First Record");
            }
            else
                MessageBox.Show("no records");
        }
        private void btnNext_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (rno < ds.Tables[0].Rows.Count - 1)
                {
                    rno++;
                    showdata();
                }
                else
                    MessageBox.Show("Last Record");
            }
            else
                MessageBox.Show("no records");
        }
        private void btnLast_Click(object sender, EventArgs e)
        {
            if (ds.Tables[0].Rows.Count > 0)
            {
                rno = ds.Tables[0].Rows.Count - 1;
                showdata();
                MessageBox.Show("Last Record");
            }
            else
                MessageBox.Show("no records");
        }
        private void btnClear_Click(object sender, EventArgs e)
        {
            textBox1.Text = textBox2.Text = textBox3.Text = "";
        }
        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

Note: 'stud.accdb' and 'stud.mdb' files are provided in access_img.zip file along with source code