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));

oDRow = oDS.Tables["TblBinaryImages"].NewRow();
oDRow["Photo"] = bytImgData;
oSqlDA.Update(oDS, "TblBinaryImages");

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


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