Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Store or Save images in SQL Server

Rate me:
Please Sign up or sign in to vote.
3.05/5 (33 votes)
5 Mar 2010CPOL2 min read 481.1K   16.7K   116   28
A sample program to demonstrate how to save or store images in SQL server

Introduction

This sample code explains how you can store images in a SQL Server database. It uses ADO.NET System.Data.SqlClient namespace. Images can be stored in SQL server using SQL parameters.

Image 1
Image 2

How to Store Images in SQL Server Table

To store an image into SQL Server, you need to read an image file into a byte array. Once you have image data in a byte array, you can easily store this image data in SQL Server using SQL parameters. The following code explains how to do this:

C#
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();
      }

The following code explains how to read an image file into a byte array:

C#
//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 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.

C#
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 the grid, get image data from grid cell. Alternatively you can also get image data from Dataset table cell.

C#
//When user changes row selection, display image of selected row in picture box.
private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
{
    try
    {
        //Get image data from gridview column.
        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;
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
}

If you want, you can extend this code to save image from PictureBox to a local image file.

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

Points of Interest

If you see frmImageStore in design mode, I have placed picturebox1 into a panel. This panel's AutoScroll property is set to True and SizeMode property of PictureBox1 is set to True. This allows picturebox to resize itself to the size of the original picture. When picturebox's size is more than Panel1's size, scrollbars becomes active for Panel.

How to Download and Run the Program

  • Download the source zip from my website http://www.shabdar.org and extract it.
  • Restore the database from SQL Database folder.
  • If somehow you cannot restore the provided database, you can generate the necessary table using the script provided in SQL Database directory.
  • Open the solution and change connection string on frmImagesStore form.

Requirements

  • Visual Studio .NET 2005
  • .NET Framework 2.0
  • Microsoft SQL Server 2000 database or Microsoft SQL Server 2005 database

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Comments and Discussions

 
GeneralMy vote of 4 Pin
Mahdi Nejadsahebi23-Apr-14 6:21
Mahdi Nejadsahebi23-Apr-14 6:21 
Questiondatabase shows only <binarydata> Pin
Member 103832125-Nov-13 22:04
Member 103832125-Nov-13 22:04 
QuestionAwesome Pin
Member 839786419-Sep-13 21:29
Member 839786419-Sep-13 21:29 
GeneralMy vote of 1 Pin
NeenaM4-Apr-13 12:25
NeenaM4-Apr-13 12:25 
QuestionWhat is the Data Type? Pin
Sanjaya Hapuarachchi17-Mar-13 23:11
Sanjaya Hapuarachchi17-Mar-13 23:11 
QuestionThanks Pin
Noor Fathima21-Jan-13 1:00
Noor Fathima21-Jan-13 1:00 
thank u so much.... Smile | :)
QuestionGetting error like Image is namespace but used like a type? what to do now Pin
sandy58587-Jan-13 21:47
sandy58587-Jan-13 21:47 
QuestionDatagrid column just shows a cross Pin
rekha gunathilake7-Aug-12 0:59
rekha gunathilake7-Aug-12 0:59 
QuestionRe: Datagrid column just shows a cross Pin
Member 1034723820-Oct-13 0:21
Member 1034723820-Oct-13 0:21 
GeneralAnswer Pin
rekha gunathilake6-Aug-12 19:52
rekha gunathilake6-Aug-12 19:52 
Questionhow to insert imagedata in ms access database OLE Object Pin
akit_kmr26-Sep-11 7:13
akit_kmr26-Sep-11 7:13 
GeneralMy Vote of 5 Pin
Vivek Johari23-Nov-10 5:46
Vivek Johari23-Nov-10 5:46 
GeneralRe: My Vote of 5 Pin
awadhendra tiwari22-Aug-11 4:50
awadhendra tiwari22-Aug-11 4:50 
GeneralThanks Very Much Pin
mohamed sameeh22-Sep-10 14:21
mohamed sameeh22-Sep-10 14:21 
QuestionNice, ... Pin
a codeproject fan6-Mar-10 6:01
a codeproject fan6-Mar-10 6:01 
GeneralAwesome Pin
Kelvin Armstrong12-Apr-09 9:02
Kelvin Armstrong12-Apr-09 9:02 
GeneralRe: Awesome Pin
peyush.goel8-Jun-09 13:31
peyush.goel8-Jun-09 13:31 
QuestionOpen from, edit and then save to database? Pin
Sobia Saeed Magray3-Aug-08 19:48
Sobia Saeed Magray3-Aug-08 19:48 
Generalstoring images Pin
Aamir Mustafa6-Mar-08 19:39
Aamir Mustafa6-Mar-08 19:39 
GeneralMR. DANH.... Pin
MrGoodly21-Jan-08 5:18
MrGoodly21-Jan-08 5:18 
GeneralRe: MR. DANH.... Pin
Gevorg6-Mar-08 17:05
Gevorg6-Mar-08 17:05 
GeneralRe: MR. DANH.... Pin
modotx14-Feb-11 0:24
modotx14-Feb-11 0:24 
GeneralThink before deciding to put files into a database PinPopular
Dankarmy9-Dec-07 9:56
Dankarmy9-Dec-07 9:56 
GeneralRe: Think before deciding to put files into a database Pin
Paw Jershauge13-Jan-08 8:30
Paw Jershauge13-Jan-08 8:30 
GeneralRe: Think before deciding to put files into a database Pin
User 99267414-Jan-08 10:55
User 99267414-Jan-08 10:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.