Adding and retrieving Images from a SQL Server Table

Mamta M

C#ADO.NET OpenFileDialogAccess image from sql server databaseAdding and retrieving Images from a SQL Server Tableadding images to tableadd or view imagesImageToStreamRestoreDirectoryretrieving images from table

10 Comments

Join the conversation! Your thoughts help the community grow.

kishore kumar16y ago
im getting error as parameter is not valid ,while reteriving image,here is my code.. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Drawing.Imaging ; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using System.Data.SqlClient; namespace WindowsFormsApplication1 { public partial class Form1 : Form { SqlConnection con = new SqlConnection("user id=sa;password=kiran;database=kiran"); MemoryStream ms; public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { openFileDialog1.ShowDialog(); pictureBox1.ImageLocation = openFileDialog1.FileName; } private void button2_Click(object sender, EventArgs e) { SqlDataAdapter da = new SqlDataAdapter("insert into img values(" + textBox1.Text + ",'" + pictureBox1.ImageLocation + "')", con); DataSet ds = new DataSet(); da.Fill(ds, "img"); ms = new MemoryStream(); pictureBox1.Image.Save(ms, ImageFormat.Jpeg); byte[] data = new byte[ms.Length]; ms.Position = 0; ms.Read(data, 0, Convert.ToInt32(ms.Length)); MessageBox.Show("Saved"); } private void button3_Click(object sender, EventArgs e) { // SqlDataAdapter da = new SqlDataAdapter("select * from img where id=" + textBox1.Text, con); SqlCommand cmd = new SqlCommand("img_ret", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@id", textBox1.Text); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "img"); byte[] data = (byte[])ds.Tables["img"].Rows[0][0]; ms = new MemoryStream(data); pictureBox1.Image= Image.FromStream(ms); } } }
Santhonabin16y ago
hi.. thanks.. its really working well
Santhonabin16y ago
hi.. thanks.. its really working well
Mojammil Ahmed17y ago
this is vry helpful code.
Aamir Nadeem19y ago
hi, very good article regarding adding and retrieving images to/from db. ======= http://www.mobilescollection.com http://www.freewallpaperspoint.com http://www.islamic-wallpapers.com
Ken19y ago
I still can't get this to work. Will some one please help me? Please send me the database file for this code at [email protected] Thanks
Ken19y agoedited
How Can I change this line below to work with sql server 2005 express on my PC? conn = new SqlConnection("data source=VIJAYK;uid=sa;pwd=playware;database=Images;"); Please help me. Thank you, -Ken
karthick m19y ago
please send solution in my mail id, that is [email protected]
Bhasker Das21y ago
Hi Mamata, Good Article ... Bhasker
Swapnil Palande21y agoedited
Hi, Please how to insert image in SQL table using vb.net as frontend. Thanks,

This article describes the process to add and retrieve images from a SQL Server table using ADO.NET. You can possibly have an entry form that will allow the user to choose what operation he wants to do: add or view images. Depending on the option chosen you can display the relevant form.

To add images to the form, the following procedure can be used. A textbox can be displayed on the form to accept the desired image filename from the user. This file must be existing on the local machine and can be chosen using an OpenFileDialog instance.

OpenFileDialog oFileDialog1 = new OpenFileDialog();
oFileDialog1.InitialDirectory = "c:\\";
oFileDialog1.Filter = "Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.*";
oFileDialog1.FilterIndex = 1;
oFileDialog1.RestoreDirectory = true;
if (oFileDialog1.ShowDialog() == DialogResult.OK) {
if (oFileDialog1.FileName != null) {
txtPic.Text = oFileDialog1.FileName;
}
}

Once the image file name has been given, it can be passed to another method to convert the underlying image to stream format.

filename = txtPic.Text;
byte [] content = ImageToStream(fileName);
StoreImage(content);

The user-defined method ImageToStream will create a MemoryStream instance based on the given file and then convert it into a byte array.

private byte[] ImageToStream(string fileName) {
Bitmap image = new Bitmap(fileName);
MemoryStream stream = new MemoryStream();
image.Save(stream, System.Drawing.Imaging.ImageFormat.Bmp);
return stream.ToArray();
}

The method StoreImage will create parameters and populate them with values so that they can be used with an insert command to add the image and its details to the table. It is assumed that the table has 3 fields - image, filename and image number. Given the byte array as input, this method constructs an Insert command and creates 3 parameters for the 3 field values. The image number is an incremental value which is calculated by getting the last value and adding 1 to it. If the table is empty, it begins with 1.

private void StoreImage(byte[] content) {
if (MainForm.conn.State.Equals(ConnectionState.Closed))
MainForm.conn.Open();
try {
SqlCommand insert = new SqlCommand("Insert into Images values (@image, @picid,@name)", MainForm.conn);
int id;
try {
SqlCommand selectcmd = new SqlCommand("Select max(picid) from images", MainForm.conn);
id = (int) selectcmd.ExecuteScalar() + 1;
} catch (Exception ex) {
Console.WriteLine(ex.StackTrace);
id = 1;
}
SqlParameter picParameter = insert.Parameters.Add("@picid", SqlDbType.Int);
picParameter.Value = id;
picParameter.Size = 4;
SqlParameter imageParameter = insert.Parameters.Add("@image", SqlDbType.Binary);
imageParameter.Value = content;
imageParameter.Size = content.Length;
SqlParameter nameParameter = insert.Parameters.Add("@name", SqlDbType.Char);
nameParameter.Value = fileName;
nameParameter.Size = fileName.Length;
insert.ExecuteNonQuery();
MessageBox.Show("Image has been added successfully");
success = true;
} catch (Exception ex) {
MessageBox.Show(ex.Message.ToString());
MessageBox.Show(ex.StackTrace.ToString());
} finally {
MainForm.conn.Close();
}
}

If you try to look at the contents of the image field in the Sql Server table, you will find that it is stored in binary format.

On loading the View form, a listbox can be populated with the existing image file names from the table. The user can then select a particular image file name from the list to display it.

MainForm.conn.Open();
SqlCommand cmd = new SqlCommand("Select picname from images", MainForm.conn);
SqlDataReader content = cmd.ExecuteReader();
while (content.Read()) {
lstImages.Items.Add(content.GetString(0));
}
content.Close();

The user selection from the listbox can be captured using SelectIndexChanged event.

private void lstImages_SelectedIndexChanged(object sender, System.EventArgs e) {
image = lstImages.SelectedItem.ToString();
}

To view images, the following code can be used:

if (MainForm.conn.State.Equals(ConnectionState.Closed))
MainForm.conn.Open();
SqlCommand cmd = new SqlCommand("Select picture from images where picname like '" + image + "';", MainForm.conn);
byte[] content = (byte[]) cmd.ExecuteScalar();
try {
MemoryStream stream = new MemoryStream(content);
pic1.Image = Image.FromStream(stream);
} catch (Exception ex) {
MessageBox.Show(ex.Message.ToString());
MessageBox.Show(ex.StackTrace.ToString());
}

As you can see, the procedure is quite simple.