Inserting images into MS Access file using OLEDB

Objective

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

Design

1. Design the form as above with an OpenFileDialog control, 3 Labels, 3 TextBoxes, 11 buttons.
2. PictureBox1 Properties.
3. BorderStyle=Fixed3D; SizeMode=StrechImage.

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

Introduction

• In order to use OleDb Connection include the namespace: 'using System.Data.OleDb'
• For accessing records from the M.S.Access-2003 file we use the 'Jet' driver,
• And for accessing records from the M.S.Access-2007 file we use the '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 a reference to Microsoft.VisualBasic.

Adding a Reference

• Goto Project Menu->Add Reference -> select 'Microsoft.VisualBasic' from the .NET tab.
• In order to use this we have to include the namespace: 'using Microsoft.VisualBasic'

Converting images 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 inserts that binary data into the database and converts that back to the image while retrieving the records.
3. If we store the location of an image in the database, and suppose that image is deleted or moved from that location, we will face problems while retrieving the records. So it is better to convert the image into binary data insert that binary data into the database and convert that back to the image while retrieving records.

We can convert an image into binary data using.

1. FileStream
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[] photoArray = new byte[fs.Length];
fs.Read(photoArray, 0, photoArray.Length);

2. MemoryStream: So it is better to use MemoryStream which uses images 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);

In order to use FileStream or MemoryStream we have to include the namespace:'using System.IO'.

OpenFileDialog Control

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

Creating a primary key in the Data Table

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

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

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 the access_img.zip file along with the source code.