using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; using System.IO; namespace imageDB { public partial class Form1 : Form { DataSet ds; string imageName; public Form1() { InitializeComponent(); } //Without using statements /*private void insertData() { try { if (imageName != "") { //Initialize a file stream to read the image file FileStream fs = new FileStream(@imageName, FileMode.Open, FileAccess.Read); //Initialize a byte array with size of stream byte[] imgByteArr = new byte[fs.Length]; //Read data from the file stream and put into the byte array fs.Read(imgByteArr, 0, Convert.ToInt32(fs.Length)); fs.Close(); //Save binary data in database string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True"; SqlConnection conn = new SqlConnection(constr); conn.Open(); string sql = "insert into tbl_Image(id,img) values('" + textBox1.Text + "',@img)"; SqlParameter imageParameter = new SqlParameter(); imageParameter.SqlDbType = SqlDbType.Image; imageParameter.ParameterName = "img"; imageParameter.Value = imgByteArr; SqlCommand cmd = new SqlCommand(sql, conn); cmd.Parameters.Add(imageParameter); int result = cmd.ExecuteNonQuery(); if (result == 1) { MessageBox.Show("Image Added"); } cmd.Dispose(); conn.Dispose(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }*/ private void insertData() { if (imageName == "") { return; } try { //Initialize a file stream to read the image file FileStream fs = new FileStream(@imageName, FileMode.Open, FileAccess.Read); //Initialize a byte array with size of stream byte[] imgByteArr = new byte[fs.Length]; //Read data from the file stream and put into the byte array fs.Read(imgByteArr, 0, Convert.ToInt32(fs.Length)); fs.Close(); //Save binary data in database string constr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True"; using (SqlConnection conn = new SqlConnection(constr)) { conn.Open(); string sql = "insert into tbl_Image(id,img) values('" + textBox1.Text + "',@img)"; using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add(new SqlParameter("img", imgByteArr)); int result = cmd.ExecuteNonQuery(); if (result == 1) { MessageBox.Show("Image Added"); refreshImageList(); } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void refreshImageList() { try { string connstr = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\imageDB.mdf;Integrated Security=True;User Instance=True"; using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM tbl_Image", conn)) { ds = new DataSet("myDataSet"); adapter.Fill(ds); DataTable dt = ds.Tables[0]; //Alternatively can fill data into a data table directly //DataTable dt2 = new DataTable(); //adapter.Fill(dt2); comboBox1.Items.Clear(); foreach (DataRow dr in dt.Rows) { comboBox1.Items.Add(dr["id"].ToString()); } comboBox1.SelectedIndex = 0; //Display queried data in a grid view dataGridView1.DataSource = dt; } } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void button1_Click(object sender, EventArgs e) { try { FileDialog fldlg = new OpenFileDialog(); fldlg.InitialDirectory = Environment.SpecialFolder.MyPictures.ToString(); fldlg.Filter = "Image File (*.jpg;*.bmp;*.gif)|*.jpg;*.bmp;*.gif"; if (fldlg.ShowDialog() == DialogResult.OK) { imageName = fldlg.FileName; Bitmap bmp = new Bitmap(imageName); pictureBox1.SizeMode = PictureBoxSizeMode.StretchImage; pictureBox1.Image = (Image)bmp; } fldlg = null; } catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); } } private void button2_Click(object sender, EventArgs e) { insertData(); } private void button3_Click(object sender, EventArgs e) { DataTable dataTable = ds.Tables[0]; //If there is an already an image in picturebox, then delete it if (pictureBox2.Image != null) { pictureBox2.Image.Dispose(); } //Initialize a file stream to write image data FileStream fs = new FileStream("image.jpg", FileMode.Create); foreach (DataRow row in dataTable.Rows) { if (row[0].ToString() == comboBox1.SelectedItem.ToString()) { //Store binary data read from the database in a byte array byte[] blob = (byte[])row[1]; //Write data in image file using file stream fs.Write(blob, 0, blob.Length); fs.Close(); fs = null; pictureBox2.Image = Image.FromFile("image.jpg"); pictureBox2.SizeMode = PictureBoxSizeMode.StretchImage; pictureBox2.Refresh(); } } } } }
Save and Retrieve images in C# from SQL Database
24
Jun
John William
March 11, 2014 at 3:37 pm
Hello. Do you know how to update image + info on database without create a new info? I can do INSERT, SELECT and DELETE but not UPDATE.
kordepatil
April 20, 2016 at 1:06 am
Thanks u very much…I had looking for a image uploading and retrieving solution since a couple of day..but every time failed…this is best solution given by u..keep it up ..
stegil
April 20, 2016 at 6:45 pm
it worked!! thank you!!