Post
Ask Question

Inserting & retrieving images from SQL Server database using stored procedures

Objective

 
To insert & retrieve images from SQL server database using stored procedures and also to perform insert, search, update and delete operations & navigation of records.
 

Introduction

 
As we want to insert images into the database using stored procedures, we have to create a table and stored procedures in the database.
 
Query for creating table in our application:
  1. create table student(sno int primary key,sname varchar(50),course varchar(50),fee money,photo image) 
Stored procedures:
  1. create procedure get_student  
  2. as  
  3. select sno, sname, course, fee, photo from student  
  4. -- -- -- -- -- -  
  5. create procedure insert_student(@sno int, @sname varchar(50), @course varchar(50), @fee smallmoney, @photo image = null)  
  6. as  
  7. insert into student(sno, sname, course, fee, photo) values(@sno, @sname, @course, @fee, @photo)  
  8.     -- -- -- -- -- -- -- -  
  9.     create procedure update_student(@sno int, @sname varchar(50), @course varchar(50), @fee smallmoney, @photo image = null)  
  10. as  
  11. update student set sname = @sname, course = @course, fee = @fee, photo = @photo where sno = @sno  
  12.     -- -- -- -- -- --  
  13. create procedure delete_student(@sno int = null)  
  14. as  
  15. if not(@sno = null)  
  16. delete from student where sno = @sno  
  17.     -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 
Design
 
1.gif
 
Design the form as above with 1 PictureBox control, 1 OpenFileDialog control, 4 Labels, 4 TextBoxes, and 11 Buttons.
 
PictureBox1 Properties:
  1. BorderStyle=Fixed3D; SizeMode=StrechImage 
Note that OpenFileDialog control appears below the form (not on the form).
 
Introduction to code
 
In order to communicate with the SQL server database, including the namespace using System.Data.SqlClient.
 
In this application, we will search for a record by taking input from the InputBox. For this, we have to add a reference to Microsoft.VisualBasic.
 
Adding a Reference to 'Microsoft.VisualBasic'
 
Goto Project Menu ->Add Reference -> select 'Microsoft.VisualBasic' from .NET tab.
 
In order to use this reference, we have to include the namespace: 'using Microsoft.VisualBasic' in the code.
 
Converting an image into binary data: 
 
We can't store an image directly into the database. For this we have two solutions:
i) To store the location of the image in the database
ii) Convert the image into binary data and insert that binary data into the database and convert that back to the image when retrieving the records.
 
If we store the location of an image in the database and suppose if that image is deleted or moved from that location, we will face problems while retrieving the records. So it is better to convert an image into binary data and insert that binary data into the database and convert that back to an image while retrieving records.
 
->We can convert an image into binary data using 1. FileStream (or) 2. MemoryStream
1. FileStream uses file location to convert an image into binary data which we may/may not provide when updating a record.
Ex:
  1. FileStream fs = new FileStream(openFileDialog1.FileName, FileMode.Open,FileAccess.Read);  
  2. byte[] photo_aray = new byte[fs.Length];  
  3. fs.Read(photo_aray, 0, photo_aray.Length); 
     
2. So it is better to use MemoryStream which uses the image in the PictureBox to convert an image into binary data.
Ex:
  1. MemoryStream ms = new MemoryStream();  
  2. pictureBox1.Image.Save(ms, ImageFormat.Jpeg);  
  3. byte[] photo_aray = new byte[ms.Length];  
  4. ms.Position = 0;  
  5. ms.Read(photo_aray, 0, photo_aray.Length); 
->In order to use FileStream or MemoryStream we have to include the namespace: 'using System.IO'.
           
OpenFileDialog Control
 
We use OpenFileDialog control to browse for the images (photos) to insert into the record.
 
Using DataAdapter with StoredProcedures
 
We can use command object to work with stored procedures bypassing the stored procedure name to command object and specifying CommandType as StoredProcedure
Ex:
  1. cmd = new SqlCommand("get_student", con);  
  2. cmd.CommandType = CommandType.StoredProcedure; 
DataAdapter can't interact directly with the stored procedures, but if we need to use DataAdapter while working with stored procedures, we can achieve this by passing the command object to the DataAdapter.
 
Loading the constraint details into the dataTable
 
In this app. we use Find() method to search a record, which requires details of primarykey column, which can be provided using the statement: 
  1. adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; 
Pointing to the current record in dataTable
 
After searching a record, we have to get the index of that record so that we can show next and previous records when we press '>>'(next) and '<<'(previous) buttons.
Ex:
  1. rno= ds.Tables[0].Rows.IndexOf(drow); 
Code:
  1. using System;  
  2. using System.Windows.Forms;  
  3. using System.Data;  
  4. using System.Data.SqlClient;  
  5. using System.Drawing;  
  6. using System.Drawing.Imaging;  
  7. using System.IO;  
  8. using Microsoft.VisualBasic;  
  9. namespace inserting_imgs {  
  10.     public partial class Form1: Form {  
  11.         public Form1() {  
  12.             InitializeComponent();  
  13.         }  
  14.         SqlConnection con;  
  15.         SqlCommand cmd;  
  16.         SqlDataAdapter adapter;  
  17.         DataSet ds;  
  18.         int rno = 0;  
  19.         MemoryStream ms;  
  20.         byte[] photo_aray;  
  21.         private void Form1_Load(object sender, EventArgs e) {  
  22.             con = new SqlConnection("user id=sa;password=123;database=prash");  
  23.             loaddata();  
  24.             showdata();  
  25.         }  
  26.         void loaddata() {  
  27.             cmd = new SqlCommand("get_student", con);  
  28.             cmd.CommandType = CommandType.StoredProcedure;  
  29.             adapter = new SqlDataAdapter(cmd);  
  30.             adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;  
  31.             ds = new DataSet();  
  32.             adapter.Fill(ds, "student");  
  33.         }  
  34.         void showdata() {  
  35.             if (ds.Tables[0].Rows.Count > 0) {  
  36.                 textBox1.Text = ds.Tables[0].Rows[rno][0].ToString();  
  37.                 textBox2.Text = ds.Tables[0].Rows[rno][1].ToString();  
  38.                 textBox3.Text = ds.Tables[0].Rows[rno][2].ToString();  
  39.                 textBox4.Text = ds.Tables[0].Rows[rno][3].ToString();  
  40.                 pictureBox1.Image = null;  
  41.                 if (ds.Tables[0].Rows[rno][4] != System.DBNull.Value) {  
  42.                     photo_aray = (byte[]) ds.Tables[0].Rows[rno][4];  
  43.                     MemoryStream ms = new MemoryStream(photo_aray);  
  44.                     pictureBox1.Image = Image.FromStream(ms);  
  45.                 }  
  46.             } else  
  47.                 MessageBox.Show("No Records");  
  48.         }  
  49.         private void browse_Click(object sender, EventArgs e) {  
  50.             openFileDialog1.Filter = "jpeg|*.jpg|bmp|*.bmp|all files|*.*";  
  51.             DialogResult res = openFileDialog1.ShowDialog();  
  52.             if (res == DialogResult.OK) {  
  53.                 pictureBox1.Image = Image.FromFile(openFileDialog1.FileName);  
  54.             }  
  55.         }  
  56.         private void clear_Click(object sender, EventArgs e) {  
  57.             textBox1.Text = textBox2.Text = textBox3.Text = textBox4.Text = "";  
  58.             pictureBox1.Image = null;  
  59.         }  
  60.         private void insert_Click(object sender, EventArgs e) {  
  61.             cmd = new SqlCommand("insert_student", con);  
  62.             cmd.CommandType = CommandType.StoredProcedure;  
  63.             cmd.Parameters.AddWithValue("@sno", textBox1.Text);  
  64.             cmd.Parameters.AddWithValue("@sname", textBox2.Text);  
  65.             cmd.Parameters.AddWithValue("@course", textBox3.Text);  
  66.             cmd.Parameters.AddWithValue("@fee", textBox4.Text);  
  67.             conv_photo();  
  68.             con.Open();  
  69.             int n = cmd.ExecuteNonQuery();  
  70.             con.Close();  
  71.             if (n > 0) {  
  72.                 MessageBox.Show("record inserted");  
  73.                 loaddata();  
  74.             } else  
  75.                 MessageBox.Show("insertion failed");  
  76.         }  
  77.         void conv_photo() {  
  78.             //converting photo to binary data  
  79.             if (pictureBox1.Image != null) {  
  80.                 ms = new MemoryStream();  
  81.                 pictureBox1.Image.Save(ms, ImageFormat.Jpeg);  
  82.                 byte[] photo_aray = new byte[ms.Length];  
  83.                 ms.Position = 0;  
  84.                 ms.Read(photo_aray, 0, photo_aray.Length);  
  85.                 cmd.Parameters.AddWithValue("@photo", photo_aray);  
  86.             }  
  87.         }  
  88.         private void search_Click(object sender, EventArgs e) {  
  89.             try {  
  90.                 int n = Convert.ToInt32(Interaction.InputBox("Enter sno:", "Search", "20", 100, 100));  
  91.                 DataRow drow;  
  92.                 drow = ds.Tables[0].Rows.Find(n);  
  93.                 if (drow != null) {  
  94.                     rno = ds.Tables[0].Rows.IndexOf(drow);  
  95.                     textBox1.Text = drow[0].ToString();  
  96.                     textBox2.Text = drow[1].ToString();  
  97.                     textBox3.Text = drow[2].ToString();  
  98.                     textBox4.Text = drow[3].ToString();  
  99.                     pictureBox1.Image = null;  
  100.                     if (drow[4] != System.DBNull.Value) {  
  101.                         photo_aray = (byte[]) drow[4];  
  102.                         MemoryStream ms = new MemoryStream(photo_aray);  
  103.                         pictureBox1.Image = Image.FromStream(ms);  
  104.                     }  
  105.                 } else  
  106.                     MessageBox.Show("Record Not Found");  
  107.             } catch {  
  108.                 MessageBox.Show("Invalid Input");  
  109.             }  
  110.         }  
  111.         private void update_Click(object sender, EventArgs e) {  
  112.             cmd = new SqlCommand("update_student", con);  
  113.             cmd.CommandType = CommandType.StoredProcedure;  
  114.             cmd.Parameters.AddWithValue("@sno", textBox1.Text);  
  115.             cmd.Parameters.AddWithValue("@sname", textBox2.Text);  
  116.             cmd.Parameters.AddWithValue("@course", textBox3.Text);  
  117.             cmd.Parameters.AddWithValue("@fee", textBox4.Text);  
  118.             conv_photo();  
  119.             con.Open();  
  120.             int n = cmd.ExecuteNonQuery();  
  121.             con.Close();  
  122.             if (n > 0) {  
  123.                 MessageBox.Show("Record Updated");  
  124.                 loaddata();  
  125.             } else  
  126.                 MessageBox.Show("Updation Failed");  
  127.         }  
  128.         private void delete_Click(object sender, EventArgs e) {  
  129.             cmd = new SqlCommand("delete_student", con);  
  130.             cmd.CommandType = CommandType.StoredProcedure;  
  131.             cmd.Parameters.AddWithValue("@sno", textBox1.Text);  
  132.             con.Open();  
  133.             int n = cmd.ExecuteNonQuery();  
  134.             con.Close();  
  135.             if (n > 0) {  
  136.                 MessageBox.Show("Record Deleted");  
  137.                 loaddata();  
  138.                 rno = 0;  
  139.                 showdata();  
  140.             } else  
  141.                 MessageBox.Show("Deletion Failed");  
  142.         }  
  143.         private void first_Click(object sender, EventArgs e) {  
  144.             rno = 0;  
  145.             showdata();  
  146.             MessageBox.Show("First record");  
  147.         }  
  148.         private void previous_Click(object sender, EventArgs e) {  
  149.             if (rno > 0) {  
  150.                 rno--;  
  151.                 showdata();  
  152.             } else  
  153.                 MessageBox.Show("First record");  
  154.         }  
  155.         private void next_Click(object sender, EventArgs e) {  
  156.             if (rno < ds.Tables[0].Rows.Count - 1) {  
  157.                 rno++;  
  158.                 showdata();  
  159.             } else  
  160.                 MessageBox.Show("Last record");  
  161.         }  
  162.         private void last_Click(object sender, EventArgs e) {  
  163.             rno = ds.Tables[0].Rows.Count - 1;  
  164.             showdata();  
  165.             MessageBox.Show("Last record");  
  166.         }  
  167.         private void exit_Click(object sender, EventArgs e) {  
  168.             this.Close();  
  169.         }  
  170.     }  
  171. } 


Similar Articles