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


                                                           

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()

     {                 

         adapter = new SqlDataAdapter(“select sno,sname,course,fee,photo from student”,con);

         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 newbtn_Click(object sender, EventArgs e)

     {

         cmd = new SqlCommand(“select max(sno)+10 from student”, con);

         con.Open();

         textBox1.Text = cmd.ExecuteScalar().ToString();

         con.Close();

         textBox2.Text = textBox3.Text = textBox4.Text = “”;

         pictureBox1.Image = null;

     }

     private void insert_Click(object sender, EventArgs e)

     {

         cmd = new SqlCommand(“insert into student(sno,sname,course,fee,photo) values(“+textBox1.Text+”,'”+textBox2.TabIndex+”‘,'”+textBox3.Text+”‘,”+textBox4.Text+”,@photo)”, con);

         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:

                //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:

             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 set sname='” + textBox2.Text + “‘, course='” + textBox3.Text + “‘, fee='” + textBox4.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 delete_Click(object sender, EventArgs e)

     {

         cmd = new SqlCommand(“delete from student where sno=”+textBox1.Text, con);

         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)

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: