How To Save Image To SqlServer

Problem: I am currently working on my college project where I was required to save images to the database and I had no idea how to do that.

Impact:
Unable to save images to the database.

Solution:
After doing some research I found some material on this topic, so I wrote a small program for this post to share my knowledge with you guys. First create a windows form application, add four button, load, browse, save and clear, Add two text boxes, A picture box and from Dialog Tool Box select openFileDialog. Here is the screen shot of my form.

First we need to up load the image to the picture box using open file dialog. When user click on browse open file dialog will pop up and the select the image. Following code shows how to get image in picture box.


private void BrowseButton_Click(object sender, EventArgs e)
{
            openFileDialog1.ShowDialog();
            //_file is a string declared as a private field at class level.
            _file = openFileDialog1.FileName;
            pictureBox1.Image = Image.FromFile(_file);
            FileBrowserTextBox.Text = _file; 
}


Now this is the hard part. Once the image is loaded in the picture box and you are sure that this is the image you want to save, then click on Save button. But before we save the image, we need to convert the image into  byte of array, and then pass it to the database. I used separate layer (Project) for database and created a class called ImageRepository. Image Repository does two things save and load, before I show you how to do that we need to convert the image into byte[]. Following code shows how to convert image into byte[] and then pass it to the database.


private void SaveButton_Click(object sender, EventArgs e)
        {
            try
            {
                //use ReadFile method to get byteArray
                byte[] data = ReadFile();
                ImageRepository repository = new ImageRepository();
                int result = repository.SaveImage(data);
                if(result==1)
                {
                    MessageBox.Show(@"Image Saved Successfully");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


private byte[] ReadFile()
        {
            byte[] data;
            //Use FileInfo object to get file size.
            FileInfo fileInfo = new FileInfo(_file);
            long fileLength = fileInfo.Length;
            //Open FileStream to read file
            FileStream fileStream = new FileStream(_file,FileMode.Open,FileAccess.Read,FileShare.Read);
            //Use BinaryReader to read file stream into byte array.
            BinaryReader binaryReader = new BinaryReader(fileStream);

             //When you use BinaryReader, you need to supply number of bytes

//to read from file.
      //In this case we want to read entire file. 
      //So supplying total number of bytes.

            data = binaryReader.ReadBytes((int)fileLength);
            //Close the file stream
            fileStream.Close();
            return data;
        }


Now we will use Repository to save the image.

public int SaveImage(byte[] imagedata)
        {
            try
            {
                byte[] imageData = imagedata;
                //get the connectin string.
                string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
                SqlConnection connection = new SqlConnection(connectionstring);

        //Open the connection to the database.

                connection.Open();
                SqlCommand command = new SqlCommand();
                //Insert image to the database. I am updating my record since I had a table with a column name Image. you could use Insert command instead of update.
                command.CommandText = "UPDATE Movie SET Image = @image where Name = 'Your                  Highness'";
                command.Connection = connection;
                //Adding the parameter to the command object.
                command.Parameters.AddWithValue("@image", imageData);
                //if the result is 1 that means that image was saved.
                int result = command.ExecuteNonQuery();
                connection.Close();
                return result;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

Now we need to load the image from the database. Since I have my Image stored in Movie table, so I will use Movie name to search for the image I am looking for. Getting the image from the database is other way round, before we were converting the image into byte[] array and then saving it, now we are getting the image from that database as byte[] array and we need to convert it back into image and load it to picture box. First I am providing the name of the movie to look for the image. Here is what happens when user clicks on LoadImage button.

private void LoadButton_Click(object sender, EventArgs e)
        {
          
            try
            {
                ImageRepository repository = new ImageRepository();
                //get the byte[] data from database using repository class.
                byte[] image = repository.LoadImage(LoadTextBox.Text);
                Image newImage;

        //Read image data into a memory stream

                using(MemoryStream memoryStream = new MemoryStream(image,0,image.Length))
                {
              
                    memoryStream.Write(image,0,image.Length);
                    //Set image variable value using memory stream.
                     newImage = Image.FromStream(memoryStream, true);
                }
                //Set picturebox image.
                pictureBox1.Image = newImage;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


//get image using the repository class loadImage method.

public byte[] LoadImage(string movieName)
        {
            byte[] image = null;
            string connectionstring = @"Data Source=FARAZ-PC\FARAZ_K1;Initial Catalog=RentAMovieDB;Integrated Security=True";
            SqlConnection connection = new SqlConnection(connectionstring);
            connection.Open();
            SqlCommand command = new SqlCommand();
            command.CommandText = "Select image from Movie where Name = '" + movieName+"'";
            command.Connection = connection;
            SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
            DataSet dataset = new DataSet();
            dataAdapter.Fill(dataset);
            foreach (DataRow row in dataset.Tables[0].Rows)
            {
                image = (byte[])row["Image"];
            }
            return image;
        }

Here is the form after loading the image from the database.