Insert and Retrieve Image to mysql with C# Step by Step

The easy way to insert image in mysql and retrieve it from mysql.
To insert image data to mysql by C# just follow the step,

Step 1:

Create a database and a table with 3 field name,size,file column.

or use this sql code in your mysql database query.

sample code : 


CREATE DATABASE /*!32312 IF NOT EXISTS*/`image_test` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `image_test`;

/*Table structure for table `file` */

DROP TABLE IF EXISTS `file`;

CREATE TABLE `file` (
  `name` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `file` longblob
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



Step 2 :

Create a C# windows form application>like this

Step 3 :

To insert image into mysql first we need to convert the image data to binary data then insert this data into mysql,we need to insert the size of that file to retrieve data.


Sample code to insert image into mysql by C#: 



private void button2_Click(object sender, EventArgs e)
        {
            //database connection
            MySql.Data.MySqlClient.MySqlConnection conn;
            MySql.Data.MySqlClient.MySqlCommand cmd;

            conn = new MySql.Data.MySqlClient.MySqlConnection();
            cmd = new MySql.Data.MySqlClient.MySqlCommand();
            //
            //image retrieve
            string SQL;
            UInt32 FileSize;
            byte[] rawData;
            FileStream fs;
            string strFileName = "";
            conn.ConnectionString = "server=127.0.0.1;uid=root;" +
                "pwd=;database=image_test;";
            try
            {
                fs = new FileStream(@"d:\image.png", FileMode.Open, FileAccess.Read);
                FileSize = (UInt32)fs.Length;
                rawData = new byte[FileSize];
                fs.Read(rawData, 0, (int)FileSize);
                fs.Close();
                conn.Open();
                SQL = "INSERT INTO file VALUES(@FileName, @FileSize, @File)";
                cmd.Connection = conn;
                cmd.CommandText = SQL;
                cmd.Parameters.AddWithValue("@FileName", strFileName);
                cmd.Parameters.AddWithValue("@FileSize", FileSize);
                cmd.Parameters.AddWithValue("@File", rawData);
                cmd.ExecuteNonQuery();
                MessageBox.Show("File Inserted into database successfully!",
                    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

                conn.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


The image location is d:\image.png , you can change as your requirement.

Step 4 :

To retrieve image from mysql by C# just look out the code and easily you will get it.

use this code and the new retrieved file location is d:\newfile.png ;



Sample code for view image :



 private void button3_Click(object sender, EventArgs e)
        {
            MySql.Data.MySqlClient.MySqlConnection conn;
            MySql.Data.MySqlClient.MySqlCommand cmd;
            MySql.Data.MySqlClient.MySqlDataReader myData;
            conn = new MySql.Data.MySqlClient.MySqlConnection();
            cmd = new MySql.Data.MySqlClient.MySqlCommand();

            string SQL;
            UInt32 FileSize;
            byte[] rawData;
            FileStream fs;

            conn.ConnectionString = "server=127.0.0.1;uid=root;" +
                "pwd=;database=image_test;";

            SQL = "SELECT name, size, file FROM file";

            try
            {
                conn.Open();

                cmd.Connection = conn;
                cmd.CommandText = SQL;

                myData = cmd.ExecuteReader();

                if (!myData.HasRows)
                    throw new Exception("There are no BLOBs to save");

                myData.Read();

                FileSize = myData.GetUInt32(myData.GetOrdinal("size"));
                rawData = new byte[FileSize];

                myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, (int)FileSize);

                fs = new FileStream(@"d:\newfile.png", FileMode.OpenOrCreate, FileAccess.Write);
                fs.Write(rawData, 0, (int)FileSize);
                fs.Close();
                Image image = Image.FromFile(@"d:\newfile.png");
                pictureBox1.Image = image;
                MessageBox.Show("File successfully written to disk!",
                    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                myData.Close();
                conn.Close();
            }
            catch (MySql.Data.MySqlClient.MySqlException ex)
            {
                MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }



Download Full demo for this Project :


ImageInsertRetrieve.zip


Thanks all