Friday, October 19, 2012

Store or save images in SQL Server database using C#

Store or save images in SQL Server database using C#


Store Images in SQL Server
This sample code explains you how you can store or save images in SQL Server database using C#.
It uses ADO.Net System.Data.SqlClient namespace.
Images can be stored in sql server using Sql parameters.

How to Store or Save Image in SQL Server table

To store an image in to sql server, you need to read image file into a byte array. Once you have image data in byte array, you can easily store this image data in sql server using sql parameters. Following code explains you how to do this.
01.private void cmdSave_Click(object sender, EventArgs e)
02.{
03.try
04.{
05.//Read Image Bytes into a byte array
06.byte[] imageData = ReadFile(txtImagePath.Text);
07. 
08.//Initialize SQL Server Connection
09.SqlConnection CN = new SqlConnection(txtConnectionString.Text);
10. 
11.//Set insert query
12.string qry = "insert into ImagesStore (OriginalPath,ImageData) _
13.values(@OriginalPath, @ImageData)";
14. 
15.//Initialize SqlCommand object for insert.
16.SqlCommand SqlCom = new SqlCommand(qry, CN);
17. 
18.//We are passing Original Image Path and
19.//Image byte data as sql parameters.
20.SqlCom.Parameters.Add(new SqlParameter("@OriginalPath",
21.(object)txtImagePath.Text));
22. 
23.SqlCom.Parameters.Add(new SqlParameter("@ImageData",
24.(object)imageData));
25. 
26.//Open connection and execute insert query.
27.CN.Open();
28.SqlCom.ExecuteNonQuery();
29.CN.Close();
30. 
31.//Close form and return to list or images.
32.this.Close();
33.}
Following code explains how to read image file in to a byte array.
01.//Open file into a filestream and
02.//read data in a byte array.
03.byte[] ReadFile(string sPath)
04.{
05.//Initialize byte array with a null value initially.
06.byte[] data = null;
07. 
08.//Use FileInfo object to get file size.
09.FileInfo fInfo = new FileInfo(sPath);
10.long numBytes = fInfo.Length;
11. 
12.//Open FileStream to read file
13.FileStream fStream = new FileStream(sPath, FileMode.Open,
14.FileAccess.Read);
15. 
16.//Use BinaryReader to read file stream into byte array.
17.BinaryReader br = new BinaryReader(fStream);
18. 
19.//When you use BinaryReader, you need to
20. 
21.//supply number of bytes to read from file.
22.//In this case we want to read entire file.
23. 
24.//So supplying total number of bytes.
25.data = br.ReadBytes((int)numBytes);
26.return data;
27.}

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.
01.void GetImagesFromDatabase()
02.{
03.try
04.{
05.//Initialize SQL Server connection.
06.SqlConnection CN = new SqlConnection(txtConnectionString.Text);
07. 
08.//Initialize SQL adapter.
09.SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImagesStore", CN);
10. 
11.//Initialize Dataset.
12.DataSet DS = new DataSet();
13. 
14.//Fill dataset with ImagesStore table.
15.ADAP.Fill(DS, "ImagesStore");
16. 
17.//Fill Grid with dataset.
18.dataGridView1.DataSource = DS.Tables["ImagesStore"];
19.}
20.catch(Exception ex)
21.{
22.MessageBox.Show(ex.ToString());
23.}
24.}
Once you have image data in grid, get image data from grid cell. Alternatively you can also get image data from Dataset table cell.
1.//Store image to a local file.
2.pictureBox1.Image.Save("c:\test_picture.jpg",
3.System.Drawing.Imaging.ImageFormat.Jpeg);
If you want you can extend this code to save image from Picture Box to a local image file.
1.//Store image to a local file.
2.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 original picture. When picturebox's size is more than Panel1's size, scrollbars becomes active for Panel.

How to download and run program

  • Download source zip file from here . Extract in a folder.
  • Restore database from SQL Database sub folder.
  • If some how you can not restore provided database, you can generate necessary table using script provided in SQL Database directory.
  • Open solution and change connection string on frmImagesStore form.

Requirements

Visual Studio.Net 2005
.Net Framework 2.0
MS SQL Server 2000 database or MS SQL Server 2005 database.

No comments:

Post a Comment