C#: How to save and retrieve image to MS SQL

This morning, my fried buzz me and asking for help. He is creating a customer profile module which contains images like company logo and customer picture. He want to save actual data of the images to a Microsoft SQL server database rather than just storing the file's full path. On this post, using C#, I will try to show you the following:
- How to save or store images to MS SQL?
- How to retrieve and display images from MS SQL?
The complete source code of the sample project used on this post can be downloaded from this link.


Storing images to MS SQL Server is easy, first you have to create a field with an Image type. Just copy and paste the following CREATE TABLE script on your query analyzer to create the table.

CREATE TABLE [dbo].[tblImages](
[FullPath] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MyImageSample] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

On your C# project, add the following controls:

- Textbox for image path
- Textbox for connection string (database)
- Button for saving image to database
- Button for retrieving image from database
- Picture control to display the image

Your form is more or less should look like this:



Now lets do the coding, Add the ReadImageToBytes function written below (this will be called on the Save event):

//Open image file then convert into byte array
byte[] ReadImageToBytes(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);

data = br.ReadBytes((int)numBytes);
return data;
}

ReadImageToBytes convert the image into bytes that will be stored to database later. Now on your save button, put the following code inside the Click event as shown below:

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

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

//Set insert query
string query = "INSERT INTO tblImages (FullPath,MyImageSample) values(@FullPath, @MyImageSample)";

//Initialize SqlCommand object for insert.
SqlCommand cmd = new SqlCommand(query, con);

cmd.Parameters.Add(new SqlParameter("@FullPath",
(object)txtImagePath.Text));

cmd.Parameters.Add(new SqlParameter("@MyImageSample",
(object)imageSampleData));

//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close();


}
catch
{
MessageBox.Show("Error while saving image.", "Error");
}
}

The save button code will insert the byte image into SQL table with image type. You can verify this by opening your table using Query Analyzer or Enterprise Manager.


Now lets double click the retrieve button and add the following code to fetch our stored image:

private void btnRetrieveImage_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(txtConnectionString.Text);
SqlCommand cmd = new SqlCommand();
try
{
con.Open();
cmd.Connection = con;
cmd.CommandText = "SELECT Top 1 MyImageSample FROM tblImages"; // for this example only get top record
byte[] Img = (byte[])cmd.ExecuteScalar();

// Convert the image record to file stream and display it to picture control
string str = Convert.ToString(DateTime.Now.ToFileTime());
FileStream fs = new FileStream(str, FileMode.CreateNew, FileAccess.Write);
fs.Write(Img, 0, Img.Length);
fs.Flush();
fs.Close();
pic1.Image = Image.FromFile(str);
}
catch
{
MessageBox.Show("Error while saving image.", "Error");
}
finally
{
con.Close();
}

}

We're done. Now you have a program than can save and retrieve images. You can also download the complete sample project at mediafire.



For more C# tips and tricks, subscribe now