Monday, August 22, 2005

Saving an image as binary data into SQL Server ...

This article would explain the different ways of storing an image as binary content into a SQL Server database. Lets get started.

1. Open a C# - web project using Visual Studio.NET
2. Drag and drop a Server side button on to the webform.
3. Copy paste the following namespace into your code behind file.

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;

Method 1:

4. Copy-paste the content within the button click event shown below into your page.

private void Button1_Click(object sender, System.EventArgs e)
{

DataRow oDRow;
SqlConnection oSqlConn = new SqlConnection("Server=YourDbServername;uid=username;pwd=password;database=northwind");

SqlDataAdapter oSqlDA = new SqlDataAdapter("Select Photo From ImgTable", oSqlConn);
SqlCommandBuilder oSqlCmdBldr = new SqlCommandBuilder(oSqlDA);
DataSet oDS = new DataSet("TblBinaryImages");

FileStream oFStream = new FileStream(Server.MapPath("DummyImage.jpg"), FileMode.OpenOrCreate, FileAccess.Read);

byte[] bytImgData = new byte[oFStream.Length];
oFStream.Read(bytImgData, 0, Convert.ToInt32(oFStream.Length));
oFStream.Close();

oSqlDA.Fill(oDS,"TblBinaryImages");
oDRow = oDS.Tables["TblBinaryImages"].NewRow();
oDRow["Photo"] = bytImgData;
oDS.Tables["TblBinaryImages"].Rows.Add(oDRow);
oSqlDA.Update(oDS, "TblBinaryImages");
oSqlConn.Close();
}

Method 2:

4. We can store the binary content as a base64string also. If your need is one such then use the below code snippet.

string data = null;
Bitmap bmp = new Bitmap(@"C:\YourPicture.bmp");

using (MemoryStream mem = new MemoryStream())
{
bmp.Save(mem, System.Drawing.Imaging.ImageFormat.Bmp);

//Convert the bytes to a string.
data = Convert.ToBase64String(mem.ToArray());
}

Now in the string variable "data" the base64string would be there. Use it to insert it into the database.

Sample table structure:

Create Table [dbo].[ImgTable]
(
[ImgId] [int] IDENTITY (1, 1) NOT NULL ,
[Photo] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Go

2 comments:

Anonymous said...

hi! Mr.Vadivel.
Thanks for ur reply

And i'm getting the error like

"Access to the path "c:\inetpub\wwwroot\images1\images[1].jpg" is denied."

Sankalp said...

very good article