inserting & retrieving images from database using stored procedures   Leave a comment


                                                                                                                                                                                                                                                

Note:

Goto Project Menu

->Add Reference -> select ‘Microsoft.VisualBasic’ from .NET tab; include namespace ‘using Microsoft.VisualBasic’(To get input box)

———

Add OpenFileDialog control to the form, which appears below the form

Add PictureBox Control and change properties as following:

   ->BorderStyle=Fixed3D; SizeMode=StrechImage

———————————————————————–

Code: 

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 prash_studentinfo

 {

  public partial class student_info : Form

  {

     public student_info()

     {

         InitializeComponent();

     }

     SqlConnection con;

     SqlCommand cmd;

     SqlDataAdapter adapter;

     DataSet ds; int rptr = 0;

     MemoryStream ms;

     byte[] photo_aray;

     private void student_info_Load(object sender, EventArgs e)

     {

         con = new SqlConnection(“user;password=123;database=prash”);

         loaddata();

         showdata();

     }

     void loaddata()

     {                 

         cmd = new SqlCommand(“get_student”, con);

         cmd.CommandType = CommandType.StoredProcedure; adapter = new SqlDataAdapter(cmd);

         adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

         ds = new DataSet(); adapter.Fill(ds, “student”);

     }

     void showdata()

     {

         if (ds.Tables[0].Rows.Count > 0)

         {

          textBox1.Text = ds.Tables[0].Rows[rptr][0].ToString();

          textBox2.Text = ds.Tables[0].Rows[rptr][1].ToString();

          textBox3.Text = ds.Tables[0].Rows[rptr][2].ToString();

          textBox4.Text = ds.Tables[0].Rows[rptr][3].ToString();

          pictureBox1.Image = null;

          if (ds.Tables[0].Rows[rptr][4]!=System.DBNull.Value)

          {

             photo_aray = (byte[])ds.Tables[0].Rows[rptr][4];

             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 = “”;

         pictureBox1.Image = null;

     }        

     private void insert_Click(object sender, EventArgs e)

     {

         cmd = new SqlCommand(“insert_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 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 sno:”, “Search”, “20”, 100, 100));

             DataRow drow;           

             drow = ds.Tables[0].Rows.Find(n);

             if (drow != null)

             {

                 textBox1.Text = drow[0].ToString();

                 textBox2.Text = drow[1].ToString();

                 textBox3.Text = drow[2].ToString();

                 textBox4.Text = drow[3].ToString();

                 pictureBox1.Image = null;

                 if (drow[4] != System.DBNull.Value)

                 {

                     photo_aray = (byte[])drow[4];

                     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_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”);

     }

     private void delete_Click(object sender, EventArgs e)

     {

         cmd = new SqlCommand(“delete_student”, con);

         cmd.CommandType = CommandType.StoredProcedure;

         cmd.Parameters.AddWithValue(“@sno”, textBox1.Text);

         con.Open();

         int n = cmd.ExecuteNonQuery();

         con.Close();

         if (n > 0)

         {

             MessageBox.Show(“Record Deleted”);

             loaddata();

             rptr = 0;

             showdata();

         }

         else

             MessageBox.Show(“Deletion Failed”);

     }

     private void first_Click(object sender, EventArgs e)

     {

         rptr = 0; showdata();

         MessageBox.Show(“First record”);

     }

     private void previous_Click(object sender, EventArgs e)

     {

         if (rptr > 0)

         {

             rptr–; showdata();  

         }

         else

             MessageBox.Show(“First record”);

     }

     private void next_Click(object sender, EventArgs e)

     {

         if (rptr < ds.Tables[0].Rows.Count – 1)

         {

             rptr++; showdata();

         }

         else

             MessageBox.Show(“Last record”);

     }

     private void last_Click(object sender, EventArgs e)

     {

         rptr = ds.Tables[0].Rows.Count – 1;

         showdata(); MessageBox.Show(“Last record”);

     }

     private void exit_Click(object sender, EventArgs e)

     {

         this.Close();

     }       

  }

}

—————————————————————-

 Query for creating table:

 create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image)

 —————————————————————-

Stored procedures:

 create procedure  get_student 

as

 select  sno,sname, course, fee, photo from student

 —————

 create procedure insert_student

 (@sno int, @sname varchar(50), @course varchar(50), @fee smallmoney,@photo image=null)

 as

 insert into student(sno, sname,course, fee, photo) values (@sno, @sname, @course, @fee, @photo)

 —————

 create procedure  update_student

(@sno int, @sname varchar(50),@course varchar(50), @fee smallmoney,@photo image=null)

 as

 update student set sname=@sname, course=@course, fee=@fee, photo=@photo where sno=@sno

 —————

create procedure delete_student(@sno int=null)

as

if not(@sno=null)

delete from student where sno=@sno

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: