Objective:
To insert images into m.s.access file and also to perform search, update and delete operations.
Design:
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:
- To store the location of the image in the database
- 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