Store image in SQL Server database using C#

Download Source from GitHub

This sample code explains you how you can store image in SQL Server database using C#. It uses ADO.Net System.Data.SqlClient namespace. Images can be stored in SQL server using SQL parameters. For storing Image, we can use “Image” data type in SQL Server.

Please note that in general it’s not recommended to store images in database. It’s better to store images on a file system and save their paths in a table. This is better for overall database performance and preventing large size of database. But there could be some scenarios where you want to save images in database. For example you want to store student photos or employee photos for their profile picture. Those are usually small in sizes. So that should not increase database size significantly. Also it’s a good idea to keep pictures in a separate table which is not used frequently in regular queries. This will improve query performance.

How to download and run program

• Clone or Download source from above Github link.
• Open solution and change connection string on frmImagesStore and frmNewImage forms to point to correct path for ImageStore.mdf file in project.
• Run project then click Connect button. This should display existing images stored in sql server database.
• Click on “Store New Image” button to store a new image. This will pop up a new form where you can select location of image to upload.
• To delete image record from database, select entire row on data grid, i.e. click on very first cell of row. Then click “Delete Selected Image”

How to Store or Save Image in SQL Server table

To store image in to SQL server database, we need to read image file into a byte array. Once we have image data in byte array, we can easily store this image data in SQL server using SQL parameters. Following code explains you how to do this.

    private void cmdSave_Click(object sender, EventArgs e)
    {
        try
        {
            //Read Image Bytes into a byte array
            byte[] imageData = ReadFile(txtImagePath.Text);

            //Initialize SQL Server Connection
            SqlConnection CN = new SqlConnection(txtConnectionString.Text);

            //Set insert query
            string qry = "insert into ImagesStore (OriginalPath,ImageData) values(@OriginalPath, @ImageData)";

            //Initialize SqlCommand object for insert.
            SqlCommand SqlCom = new SqlCommand(qry, CN);

            //We are passing Original Image Path and Image byte data as sql parameters.
            SqlCom.Parameters.Add(new SqlParameter("@OriginalPath", (object)txtImagePath.Text));
            SqlCom.Parameters.Add(new SqlParameter("@ImageData", (object)imageData));

            //Open connection and execute insert query.
            CN.Open();
            SqlCom.ExecuteNonQuery();
            CN.Close();

            //Close form and return to list or images.
            this.Close();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

Following code explains how to read image file in to a byte array.

       //Open file in to a filestream and read data in a byte array.
        byte[] ReadFile(string sPath)
        {
            //Initialize byte array with a null value initially.
            byte[] data = null;

            //Use FileInfo object to get file size.
            FileInfo fInfo = new FileInfo(sPath);
            long numBytes = fInfo.Length;

            //Open FileStream to read file
            FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read);

            //Use BinaryReader to read file stream into byte array.
            BinaryReader br = new BinaryReader(fStream);

            //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 = br.ReadBytes((int)numBytes);
            return data;
        }

How to read image data bytes from SQL Server table

To read images from SQL Server, prepare a dataset first which will hold data from SQL Server table. Bind this dataset with a gridview control on form.

    void GetImagesFromDatabase()
    {
        try
        {
            //Initialize SQL Server connection.
            SqlConnection CN = new SqlConnection(txtConnectionString.Text);

            //Initialize SQL adapter.
            SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImagesStore", CN);

            //Initialize Dataset.
            DataSet DS = new DataSet();

            //Fill dataset with ImagesStore table.
            ADAP.Fill(DS, "ImagesStore");

            //Fill Grid with dataset.
            dataGridView1.DataSource = DS.Tables["ImagesStore"];
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

Once you have image data in grid, get image data from grid cell. Alternatively you can also get image data from Dataset table cell.

                byte[] imageData = (byte[])dataGridView1.Rows[e.RowIndex].Cells["ImageData"].Value;

                //Initialize image variable
                Image newImage;
                //Read image data into a memory stream
                using (MemoryStream ms = new MemoryStream(imageData, 0, imageData.Length))
                {
                    ms.Write(imageData, 0, imageData.Length);

                    //Set image variable value using memory stream.
                    newImage = Image.FromStream(ms, true);
                }
                //
                //set picture
                pictureBox1.Image = newImage;

If you want you can extend this code to save image from Picture Box to a local image file.

//Store image to a local file.
pictureBox1.Image.Save("c:\test_picture.jpg",System.Drawing.Imaging.ImageFormat.Jpeg);