RSS

Save vs. Retrieve images in SQL Server db using C# .NET.

23 Oct

In this post we will learn how can I save pictures and retrieve them from Microsoft SQL Server database using MS C# .NET.

Technically … saving and retrieving pictures depended on converting the real (binary) picture to bytes or array of bytes, then save these bytes in (image) data type field in SQL Server database.

Follow this code …:-

  • Create your table at first withe this T-SQL script:

CREATE TABLE [dbo].[tblPhotos]
( [picID] [int] IDENTITY(1,1) NOT NULL,
[Photo] [image] NULL,
CONSTRAINT [PK_tblPhotos]
PRIMARY KEY CLUSTERED ( [picID] ASC ))

  • You have to import or use this namespaces:

using System.Data.SqlClient;
using System.IO;

  • To save the picture to your SQL db …:-

FileStream fs = new FileStream(“C:\MyImages\NegmPhoto.png”, FileMode.Open, FileAccess.Read);
BinaryReader reader = new BinaryReader(fs);
byte[] img = reader.ReadBytes(int.Parse(fs.Length.ToString()));
using (SqlConnection cn = new SqlConnection(@”Server=Negm-PC;Database=SQL_Pics;UID=sa;PWD=0000″))
{
using (SqlCommand cmd = new SqlCommand(“INSERT INTO picTable VALUES (@Pic)”,cn))
{
cmd.Parameters.AddWithValue(“@Pic”, img);
cmd.ExecuteNonQuery();
}
}

  • To retrieve the picture from your SQL db:-

using (SqlConnection cn = new SqlConnection(@”Server=Negm-PC;Database=SQL_Pics;UID=sa;PWD=0000″))
{
using (SqlCommand cmd = new SqlCommand(“SELECT * FROM [tblPhotos] WHERE [picID]=@pic”,cn))
{
cmd.Parameters.AddWithValue(“@Pic”, TextBox1.Text);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//The following code means that if the third [second in index] field is not null load the image in the picture box
if (!reader.IsDBNull(1))
{
//The object named [img] get or held the field value on the form of array of bytes and then pass these bytes to the streamer to write it on the picture box
//The number (1) that you see represents the field index number in your table.
System.Data.SqlTypes.SqlBytes img = reader.GetSqlBytes(1);
Stream str = img.Stream;
picPhoto.Image = Image.FromStream(str);
}
}
}
}

Enjoy with this … !!

 
1 Comment

Posted by on October 23, 2011 in MS SQL Server, Visual C# . NET

 

Tags: , , , , , , , , , , ,

One response to “Save vs. Retrieve images in SQL Server db using C# .NET.

  1. Saleh Mohamed

    February 27, 2012 at 4:32 AM

    Thanks a lot for this good information,,,and i hope more of success for u 🙂

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: