In Focus

How to Save Image Path in Database Using MySQL & C#

In this article we will first create a table in a MySQL database and then we will use a Windows Forms application to save an image into the My SQL database.

Introduction

In this article we will first create a table in a MySQL database and then we will use a Windows Forms application to save an image into the database.

Description

Create a table in a MySQL database using MySQL browser as in the following. To store the image in the database, here is the script for the table:

  1. CREATE TABLE `image` (  
  2. `namevarchar(100) default NULL,  
  3. `type` varchar(100) default NULL,  
  4. `descvarchar(100) default NULL,  
  5. `docname` varchar(5000) default NULL,  
  6. `docdisc` varchar(500) default NULL  
  7. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Create a new Windows Forms application and arrange controls on the form as in the following:

Save.jpg

Add a reference to the MySql.Data DLL using the Add Reference dialog box and include the following two namespaces:
  1. using MySql.Data.MySqlClient;    
  2. using System.IO;
Write the following code in the Click event of PictureBox to select an image to be saved in the database:
  1. //The String used to store the location of the file that is currently loaded in the picture box picFile  
  2. String location;  
  3.   
  4. //The String used to store the name of the file that is currently loaded in the picture box picFile  
  5. String fileName;   
  6. private void Browse_Click(object sender, EventArgs e)  
  7. {  
  8.       openPic.Filter = "JPeg Image|*.jpg|Bitmap Image|*.bmp|Gif Image|*.gif";  
  9.       //Showing the fileopen dialog box  
  10.       openPic.ShowDialog();  
  11.       //showing the image opened in the picturebox  
  12.       pictureBox1.BackgroundImage = new Bitmap(openPic.FileName);  
  13.       //storing the location of the pic in variable  
  14.       location = openPic.FileName;  
  15.       textBox2.Text = location;  
  16.       //storing the filename of the pic in variable  
  17.       fileName = openPic.SafeFileName;  
  18. }
Write the following code in the Click event of the btnSave Button:
  1. private void btn_SaveImage_Click(object sender, EventArgs e)  
  2. {  
  3.       MySqlConnection con = new MySqlConnection(ConString);  
  4.       MySqlCommand cmd;  
  5.       FileStream fs;  
  6.       BinaryReader br;   
  7.       try  
  8.       {  
  9.            //Creating a filestream to open the image file  
  10.            FileStream fs = new FileStream(location, FileMode.Open, FileAccess.Read);  
  11.   
  12.            //Getting the legth of the fil in bytes  
  13.            int fileLength = (int)fs.Length;  
  14.   
  15.            //creating an array to store the image as bytes  
  16.            byte[] rawdata = new byte[fileLength];  
  17.   
  18.            //using the filestream and converting the image to bits and storing it in  
  19.            //an array  
  20.            fs.Read(rawdata, 0, (int)fileLength);  
  21.   
  22.            //Creating a new mysql command object which will be used to store the image  
  23.            MySqlCommand cmd = new MySqlCommand();  
  24.     
  25.            //creating sql command  
  26.            String sql = "insert into doc1 values(@pfno,@depname,@doctype,@docdesc,@docexpdate,@docname,@docdisc)";  
  27.      
  28.           //Connection  
  29.           con = new MySqlConnection();  
  30.           con.ConnectionString = ConfigurationSettings.AppSettings["constr"];  
  31.           con.Open();  
  32.   
  33.           //Setting the connection of the command  
  34.           cmd = new MySqlCommand(sql, con);  
  35.   
  36.           //setting the sql of the command  
  37.           //cmd.CommandText = sql;  
  38.           //Setting up the parameter values to be used when storing the image to a  
  39.           //table  
  40.          //cmd.Parameters.AddWithValue("@docsize", rawdata);  
  41.           cmd.Parameters.AddWithValue("@name", label8.Text);  
  42.           cmd.Parameters.AddWithValue("@type", comboBox1.Text);  
  43.           cmd.Parameters.AddWithValue("@desc", textBox1.Text);  
  44.           cmd.Parameters.AddWithValue("@docname", textBox2.Text);  
  45.           cmd.Parameters.AddWithValue("@docdisc", fileLength);  
  46.           //Executing the query and writing the image to the database  
  47.           cmd.ExecuteNonQuery();  
  48.           //Closing the filestream  
  49.           con.Close();  
  50.           MessageBox.Show("Done");  
  51.      }  
  52.     catch (Exception ex)  
  53.     {  
  54.            MessageBox.Show(ex.Message);  
  55.     }   
  56. }
The comments are given in // blocks.