Inserting & retrieving images from SQL Server database using stored procedures

Sign in to leave a comment.

• MP

  Manish Prajapati6y agoPosted Jan 24, 2020, 3:04 PM

  Nice article
• AO

  Adesanya Olusegun13y agoPosted Feb 14, 2013, 5:35 AM

  good, but am trying to use it on web_page(something like google trader) have done all except getting my data(images and user detail) and since am expecting lot of users i wouldnt want more than 5images on a page so i can use d next button to view the remaining images
• 

  sandeep raju dantuluri14y agoPosted Nov 23, 2011, 6:31 AM

  Easy to understand....good source for freshers
• DS

  digvijay singh15y agoPosted Feb 25, 2011, 5:33 AM

  using System; using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Drawing.Imaging; using System.IO; using Microsoft.VisualBasic; namespace inserting_imgs { public partial class Form2 : Form { public Form2() { InitializeComponent(); } SqlConnection con; SqlCommand cmd; SqlDataAdapter adapter; DataSet ds; int rno = 0; MemoryStream ms; byte[] photo_aray; private void Form2_Load(object sender, EventArgs e) { con = new SqlConnection("user id=sa;password=sql;database=library1"); loaddata(); showdata(); } void loaddata() { cmd = new SqlCommand("get_teacher", con); cmd.CommandType = CommandType.StoredProcedure; adapter = new SqlDataAdapter(cmd); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; ds = new DataSet(); adapter.Fill(ds, "teacher"); } void showdata() { if (ds.Tables[0].Rows.Count > 0) { 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(); textBox4.Text = ds.Tables[0].Rows[rno][3].ToString(); textBox5.Text = ds.Tables[0].Rows[rno][4].ToString(); textBox6.Text = ds.Tables[0].Rows[rno][5].ToString(); textBox7.Text = ds.Tables[0].Rows[rno][6].ToString(); pictureBox1.Image = null; if (ds.Tables[0].Rows[rno][7] != System.DBNull.Value) { photo_aray = (byte[])ds.Tables[0].Rows[rno][7]; MemoryStream ms = new MemoryStream(photo_aray); pictureBox1.Image = Image.FromStream(ms); } } else MessageBox.Show("No Records"); } private void browse_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 clear_Click(object sender, EventArgs e) { textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = textBox5.Text = textBox6.Text = textBox7.Text = ""; pictureBox1.Image = null; } private void insert_Click(object sender, EventArgs e) { cmd = new SqlCommand("insert_teacher", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@cardno", textBox1.Text); cmd.Parameters.AddWithValue("@name", textBox2.Text); cmd.Parameters.AddWithValue("@department", textBox3.Text); cmd.Parameters.AddWithValue("@entrybatch", textBox4.Text); cmd.Parameters.AddWithValue("@dob", textBox5.Text); cmd.Parameters.AddWithValue("@contact", textBox6.Text); cmd.Parameters.AddWithValue("@emailid", textBox7.Text); conv_photo(); con.Open(); int n = cmd.ExecuteNonQuery(); con.Close(); if (n > 0) { MessageBox.Show("record inserted"); loaddata(); } else MessageBox.Show("insertion failed"); } void conv_photo() { //converting photo to binary data if (pictureBox1.Image != null) { //using FileStream: (will not work in updating record without changing photo) //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); //using MemoryStream: (works for updating record without changing photo) 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 search_Click(object sender, EventArgs e) { try { int n = Convert.ToInt32(Interaction.InputBox("Enter cardno:", "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(); textBox4.Text = drow[3].ToString(); textBox5.Text = drow[4].ToString(); textBox6.Text = drow[5].ToString(); textBox7.Text = drow[6].ToString(); pictureBox1.Image = null; if (drow[7] != System.DBNull.Value) { photo_aray = (byte[])drow[7]; MemoryStream ms = new MemoryStream(photo_aray); pictureBox1.Image = Image.FromStream(ms); } } else MessageBox.Show("Record Not Found"); } catch { MessageBox.Show("Invalid Input"); } } private void update_Click(object sender, EventArgs e) { cmd = new SqlCommand("update_teacher", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@cardno", textBox1.Text); cmd.Parameters.AddWithValue("@name", textBox2.Text); cmd.Parameters.AddWithValue("@department", textBox3.Text); cmd.Parameters.AddWithValue("@entrybatch", textBox4.Text); cmd.Parameters.AddWithValue("@dob", textBox5.Text); cmd.Parameters.AddWithValue("@contact", textBox6.Text); cmd.Parameters.AddWithValue("@emailid", textBox7.Text); 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 delete_Click(object sender, EventArgs e) { cmd = new SqlCommand("delete_teacher", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@cardno", textBox1.Text); 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 first_Click(object sender, EventArgs e) { rno = 0; showdata(); MessageBox.Show("First record"); } private void previous_Click(object sender, EventArgs e) { if (rno > 0) { rno--; showdata(); } else MessageBox.Show("First record"); } private void next_Click(object sender, EventArgs e) { if (rno < ds.Tables[0].Rows.Count - 1) { rno++; showdata(); } else MessageBox.Show("Last record"); } private void last_Click(object sender, EventArgs e) { rno = ds.Tables[0].Rows.Count - 1; showdata(); MessageBox.Show("Last record"); } private void exit_Click(object sender, EventArgs e) { this.Close(); } } } this code is giving error that column 7 does not exist
• 

  hilda r15y agoeditedPosted Feb 13, 2011, 6:56 AMEdited Feb 22, 2011, 11:44 PM

  thank you for your professinal code,this code is thing i really need it,but I have a request:I have a dataGridView in my form and all info present in it,when I explore rows of datagridview the info apeare in textBox and picture must show in pictureBox,how I do that?please help me. thank you so much
• BS

  Bhabani Sankar16y agoPosted Nov 8, 2010, 11:19 AM

  thank u very much
  • 

    Prashanth Chindam16y agoPosted Nov 8, 2010, 1:24 PM

    private void update_Click(object sender, EventArgs e) { cmd = new SqlCommand("update_student", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@sno", textBox1.Text); cmd.Parameters.AddWithValue("@sname", textBox2.Text); cmd.Parameters.AddWithValue("@course", textBox3.Text); cmd.Parameters.AddWithValue("@fee", textBox4.Text); conv_photo(); con.Open(); int n = cmd.ExecuteNonQuery(); con.Close(); if (n > 0) { MessageBox.Show("Record Updated"); loaddata(); } else MessageBox.Show("Updation Failed"); }