Inserting Images and Reading the Images Through ADO.NET

In this article we are going to learn about storing an image in a database like that of SQL Server 2005. And after storing, how to retrieve stored images.

As we all know the size of an image is large; in a database like Oracle we need to store the image in the form of Blob data type or a bfile data type, but in SQL Server we have one data type for storing the images and that is image data type. So for storing the image we need to create one table with image data type; the following is the SQL code.

create database Test

create table TestBlob
(
imPhoto image not null
)

In the above code we just created one database and within that database we created one table whose name is TestBlob which contains only one column imPhoto whose data type is image.

Now for adding the image from the front end to the back end we need to create a user interface; the design is:

We specified the anchor property of the button to be the top and right and that of the textbox to be top and left.

We are using six buttons:

1. One for browsing
2. For inserting data into database
3. Getting the first images from the database
4. Getting the Previous image from the database
5. Moving to next image
6. Moving to last image.

Now the following is the coding which will add the data to the database and we can read the data as and when we need it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace ImagePractice
{
    public partial class Form1 : Form
    {
        SqlConnection con;
        SqlCommand cmd;
        SqlDataReader dr;
        OpenFileDialog ofd;
        SqlDataAdapter da;
        DataSet ds;
        int indexno = 0;
        public Form1()
        {
            InitializeComponent();
        }
          //for Browing the image files from our computer.
        private void button1_Click(object sender, EventArgs e)
        {
            ofd = new OpenFileDialog();
            //ofd.InitialDirectory = @"c:\";
           // for filtering the openfiledialog so that it should display only the images file
            ofd.Filter = "Image Files(*.bmp;*.jpg;*.gif)|*.bmp;*.jpg;*.gif";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                textBox1.Text = ofd.FileName;
            }
            else if(ofd.ShowDialog()==DialogResult.Cancel)
            {
                MessageBox.Show("You Clicked Cancelled");
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new SqlConnection("Data Source=localhost;Initial Catalog=Test;User Id=sa;word=faculty");
            LoadImage();
        }
        private void LoadImage()
        {
            da = new SqlDataAdapter("Select * from TestBlob", con);
            ds = new DataSet();
            da.Fill(ds, "TestBlob");
            if (ds.Tables[0].Rows.Count > 0)
            {
                byte[] photo = (byte[])ds.Tables["TestBlob"].Rows[indexno][0];
                MemoryStream ms = new MemoryStream(photo);
                pictureBox1.Image = Image.FromStream(ms);
            }
        }
        private void btnNext_Click(object sender, EventArgs e)
        {
            try
            {
                if (indexno < ds.Tables[0].Rows.Count)
                {
                    indexno++;
                    LoadImage();
                }
                else
                {
                    MessageBox.Show("You are on Last Record");
                }
            }
            catch (Exception e1)
            {
                MessageBox.Show("You are on Last Record");
            }
        }
 
        private void btnInsert_Click(object sender, EventArgs e)
        {
            FileStream fs = new FileStream(textBox1.Text, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            byte[] rawbyte = new byte[fs.Length];
            fs.Read(rawbyte, 0, Convert.ToInt32(fs.Length));
            con.Open();
            cmd = new SqlCommand("Insert into TestBlob values(@photo)", con);
            cmd.Parameters.AddWithValue("@photo", rawbyte);
            int rows = cmd.ExecuteNonQuery();
            if (rows > 0)
            {
                MessageBox.Show("Inserted Successfully");
                textBox1.Text = "";
            }
            else
            {
                MessageBox.Show("Error Inserting Data");
            }
        }
 
        private void btnPrev_Click(object sender, EventArgs e)
        {
            if (indexno > 0)
            {
                indexno--;
                LoadImage();
            }
            else
            {
                MessageBox.Show("You are on First Image");
            }
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            indexno = 0;
            LoadImage();
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            indexno = ds.Tables[0].Rows.Count - 1;
            LoadImage();
        }
    }
}