Shabdar.org
Webshabdar.org
Store or save images in SQL Server database using C# PDF Print E-mail
Written by Shabdar   
Tuesday, 25 November 2008 11:59

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 easity store this image data in sql server using sql parameters. Following code explains you how to do this.

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

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

//Set insert query
string qry = "insert into ImagesStore (OriginalPath,ImageData) _
values(@OriginalPath, @ImageData)";

//Initialize SqlCommand object for insert.
SqlCommand SqlCom = new SqlCommand(qry, CN);

//We are passing Original Image Path and
//Image byte data as sql parameters.
SqlCom.Parameters.Add(new SqlParameter("@OriginalPath",
(object)txtImagePath.Text));

SqlCom.Parameters.Add(new SqlParameter("@ImageData",
(object)imageData));

//Open connection and execute insert query.
CN.Open();
SqlCom.ExecuteNonQuery();
CN.Close();

//Close form and return to list or images.
this.Close();
}

Following code explains how to read image file in to a byte array.

        
//Open file into a filestream and
//read data in a byte array.
byte[] ReadFile(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);

//When you use BinaryReader, you need to

//supply number of bytes to read from file.
//In this case we want to read entire file.

//So supplying total number of bytes.
data = br.ReadBytes((int)numBytes);
return data;
}

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.

    void GetImagesFromDatabase()
{
try
{
//Initialize SQL Server connection.
SqlConnection CN = new SqlConnection(txtConnectionString.Text);

//Initialize SQL adapter.
SqlDataAdapter ADAP = new SqlDataAdapter("Select * from ImagesStore", CN);

//Initialize Dataset.
DataSet DS = new DataSet();

//Fill dataset with ImagesStore table.
ADAP.Fill(DS, "ImagesStore");

//Fill Grid with dataset.
dataGridView1.DataSource = DS.Tables["ImagesStore"];
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
}

Once you have image data in grid, get image data from grid cell. Alternatively you can also get image data from Dataset table cell.

            //Store image to a local file.
pictureBox1.Image.Save("c:\test_picture.jpg",
System.Drawing.Imaging.ImageFormat.Jpeg);

If you want you can extend this code to save image from Picture Box to a local image file.

    //Store image to a local file.
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.

window.google_render_ad();

Requirements

Visual Studio.Net 2005

.Net Framework 2.0

MS SQL Server 2000 database or MS SQL Server 2005 database.

Special  Notes

I have published this article on codeproject.com as well. Here is the link to this article.

http://www.codeproject.com/useritems/Store_images_in_SQL_Serve.asp


Comments/Questions

hi thanks for nice article.
but can u tell me how to create the tables in sql server.which datatype i hav to use while creating the table?
=> thams (Friday 25-Apr-08 02:05 AM)
Reply
Create table table_name(i int,names varchar(20),address(varchar(20))


This is the way to create table in database
=> subaganesh (Thursday 22-May-08 04:36 AM)
Reply
can u please explain what to store in txtImagePath.Text
=> jyoti (Tuesday 16-Sep-08 01:37 AM)
Reply
Super..........

it worked perfect. so you continue this type issue in net!!!

Thankssssssssssssssss.......

=> Loganathan (Friday 13-Jun-08 02:02 AM)
Reply
hi, this is mukhtar.
Thank you very much for this code.
bye takecar :)
=> SARDAR MUKHTAR ALI KHAN (Thursday 19-Jun-08 06:57 AM)
Reply
What to write in the connection string text box? I am confused :( Kindly help me by giving an example.
=> Holy Harem (Saturday 26-Jul-08 12:54 AM)
Reply
String s="Server=servername;database=databasename;uid=sa;pwd=pwd;";




use this connection string for ur connection string textbox ...


Thanks
Suba(www.computerleaders.co.nr)
=> Suba Ganesh (Sunday 27-Jul-08 11:43 PM)
Reply
Thanks Suba :) I already explored it, but still thank you very much ...
=> Holy Harem (Monday 04-Aug-08 01:00 AM)
Reply
Ok ok n thanks
=> Suba (Monday 04-Aug-08 03:54 AM)
Reply
Suba!

How r u?

Hope to see u in the best of ur health and spirits,

Can u plz help me in solving the following problem:-
I have used this code in an image editor. When I open image from hard disk, all edit functions work perfectly but when I open image from database and try to edit it, no function works :( Kindly help me out...

Regards,
=> Holy Harem (Wednesday 06-Aug-08 11:55 PM)
Reply
Ya am fine.....Holy i cant get ur question ...can u plz send me the code t my mail ...clearly



My ID:endrum18rediffmail.com
=> Suba Ganesh (Thursday 07-Aug-08 02:32 AM)
Reply
Hi, I want to same thing in VB6 using SQL Sever. Please help me.
=> Kashif (Tuesday 12-Aug-08 01:50 AM)
Reply
hi,
hru,
how to insert imageurl in database(SQL Server)
and retrive that imageurl in crystalreport10 and that url
show the image type
how get this.
pls help me
=> vanaja (Friday 29-Aug-08 07:34 AM)
Reply
How to create a table using array data type in one of its field? Or any alternative way to store array in a table.
=> Shastri (Wednesday 24-Sep-08 01:55 AM)
Reply
Thats a very nice code but i want the same thing in VB6 using SQL Server.
=> Kashif (Friday 26-Sep-08 12:28 AM)
Reply
I need help
=> Jon Berg (Monday 13-Oct-08 05:33 AM)
Reply
Great.! Thanks LOTS!
=> Jeremy (Wednesday 29-Oct-08 11:40 AM)
Reply
hi could u tell me whc datatype i have to use in sql for image save and and can u explain the readfile function
pls i will be very obliged
thx and regards
Dinesh sharma
=> Dinesh sharma (Tuesday 16-Dec-08 07:44 AM)
Reply

{mos_fb_discuss:18}

Comments
Add New Search
+/-
Write comment
Name:
Email:
 
Website:
Title:
UBBCode:
[b] [i] [u] [url] [quote] [code] [img] 
 
 
Please input the anti-spam code that you can read in the image.
Sundar |122.165.28.xxx |2009-03-24 02:42:37
Very useful code, thanks
sumon  - help |120.50.178.xxx |2009-03-27 22:12:36
hi I am a beginer in asp.net c# 2008 i want to make a simple project so that i
need to connect sql server2005 database.please help me giving a simple c# code
to connect to the data base and insert data.

thank u.
Kumar Sethuraman  - Source for ReadFile |202.177.159.xxx |2009-04-08 21:44:04
public static byte[] ReadFile(string filePath)

{

byte[] buffer;

FileStream fileStream = new FileStream(filePath, FileMode.Open,
FileAccess.Read);

try

{

int length = (int)fileStream.Length; // get file length

buffer = new byte[length]; // create buffer

int count; // actual number of bytes read

int sum = 0; // total number of bytes read



// read until Read method returns 0 (end of the stream has been reached)

while ((count = fileStream.Read(buffer, sum, length - sum)) > 0)

sum += count; // sum is a buffer offset for next reading

}

finally

{

fileStream.Close();

}

return buffer;

}
K.V.Yespal |121.246.240.xxx |2009-07-06 20:50:22
Great Thanks a lot!!!!!!!!!!!!!!
hitesh  - hitesh |122.161.164.xxx |2009-04-09 05:04:02
:cheer: its a very good helping way.
Anonymous |118.103.230.xxx |2009-04-18 10:22:06
private void button1_Click(object sender, EventArgs e)
{
SqlCommand insert = null;


insert = new SqlCommand("insert into table2(Product,Price) values('" +
Product + "', '" + Price + "')", Connection);
insert.ExecuteNonQuery();


}


could anybody help me in this regard? whats wrong in this code
Nitika |58.68.11.xxx |2009-05-03 21:07:48
hi! i need to store all the records/data from dataset into database
table.records are upto 1000
i executed the code but it giving error that too many parameters/arguments
specified.

here is the code :

try
{
string st = "select a.Facultyname,b.Days,b.Times,b.Sub from dbo.faculty a
cross join dbo.TimeT b";
SqlDataAdapter daTimefac;
DataSet dsTimefac = new DataSet();
daTimefac = new SqlDataAdapter(st, con);
daTimefac.Fill(dsTimefac);

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "addTimefac";
int cont = dsTimefac.Tables[0].Rows.Count;
cmd.Parameters.Add("@Noofrecords", SqlDbType.Int).Value = cont;


for (int i = 0; i < cont; i++)
{

cmd.Parameters.Add("@Tname", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Facultyname"] .ToString();
cmd.Parameters.Add("@Days", SqlDbType.NChar).Value =
dsTimefac.Tables[0].Rows[i]["Days"].ToStri ng();
cmd.Parameters.Add("@Timeslot", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Times"].ToStr ing();
cmd.Parameters.Add("@Sub", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Sub"].ToStrin g();
con.Open();
int rowsAffected;
rowsAffected = cmd.ExecuteNonQuery();


con.Close();
}
}
catch (SqlException ex)
{ MessageBox.Show(ex.Message); }
...
mati |213.55.69.xxx |2009-07-15 08:04:04
it happens when u send a extra parameters which are more than the parameters
added on your stored procedure
Nitika |114.142.142.xxx |2009-05-03 21:09:50

for (int i = 0; i < cont; i++)
{

cmd.Parameters.Add("@Tname", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Facultyname"] .ToString();
cmd.Parameters.Add("@Days", SqlDbType.NChar).Value =
dsTimefac.Tables[0].Rows[i]["Days"].ToStri ng();
cmd.Parameters.Add("@Timeslot", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Times"].ToStr ing();
cmd.Parameters.Add("@Sub", SqlDbType.NVarChar).Value =
dsTimefac.Tables[0].Rows[i]["Sub"].ToStrin g();
con.Open();
int rowsAffected;
rowsAffected = cmd.ExecuteNonQuery();


con.Close();
}
}
catch (SqlException ex)
{ MessageBox.Show(ex.Message); }
Nitika |114.142.142.xxx |2009-05-03 21:11:07
please help me out!! :(
Barkgj  - Alternative suggestion; store to file system rathe |92.254.25.xxx |2009-05-30 00:58:03
Hiya,

As an alternative suggestion you might want to experience with NFileStorage, its
a free 100% .NET code library I made to store images in a file, and easily
exposes picture to (for example) webbrowsers. I use it myself for a website that
stores over 150.000 images and it works stable.

Gert-Jan
Project  - Inserting Image and Information |196.21.192.xxx |2009-06-11 13:50:30
I want to include a picture which is in a picturebox, to save it in the same
table, along with other information like student details I have a business layer


this is the code



bl.InsertStudent(new Student(txtmanagerID.Text,
txtEmailAddress.Text,picStudentPicture.Image,
Convert.ToString(cmbRace.SelectedItem), Convert.ToBoolean(chkfirstYear.Checked),
chkMentor.Checked, Convert.ToBoolean(chkHouseCom.Checked),
Convert.ToString(cmbGender.SelectedItem));

what must I convert the picture to? :unsure:
nurunnabi  - image save in sql database |115.127.15.xxx |2009-07-20 17:10:34
image save in sql database
Muthu  - Muthu |59.92.140.xxx |2009-08-17 04:21:39
how to load image in an image box from sql database using asp.net?
mukesh  - well done |59.177.103.xxx |2009-09-09 03:56:14
it looks perfect..i am using it..
shan  - Compare two images |122.164.201.xxx |2009-11-28 08:10:28
Hi i am storing the image successfully..,Is that possible to compare two images
in sql server with data types of "image"
using VB.NET .
Bcoz i am plan to use that in login authentication
D |122.160.195.xxx |2010-03-23 04:11:36
thnx a lot
ato  - codes for saving in .net |82.206.136.xxx |2010-07-14 14:26:34
i am a biginner in .net and i want to know the codes for save and delete.thank
you

granadaCoder |96.10.6.xxx |2010-07-15 14:24:53
I created a stored procedure...which accepts xml...thus you can insert/update
multiple rows at once (upsert).

BE CAREFUL, don't send too much data going across the wire! bytes[] get big
quickly!

--........... START TSQL CODE


IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[uspImageStoreUpsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspImageStoreUpsert]
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspImageStoreUpsert] (
@xml_doc XML,
@numberRowsAffected int output --return
)

AS

SET NOCOUNT ON

declare @VariableHolderTable table
(
ImageId int
, OriginalPath varchar(200)
, ImageData varbinary(max)
)


INSERT @VariableHolderTable
(
ImageId
,OriginalPath
,ImageData
)
SELECT
T.Item.value('ImageId[1]', 'int') AS ImageId
, T.Item.value('OriginalPath[1]', 'varchar(200)') AS OriginalPath
, T.Item.value('ImageData[1]', 'varbinary(max)') AS ImageData
FROM
@xml_doc.nodes('/NewDataSet/ImagesStore') T(Item)

/*
print '//@VariableHolderTable//'
select * from @VariableHolderTable
*/

BEGIN TRY

BEGIN TRANSACTION


UPDATE
[dbo].[ImagesStore]
SET
OriginalPath = vart.OriginalPath
,ImageData = vart.ImageData
FROM
[dbo].[ImagesStore] imagesTable , @VariableHolderTable vart
WHERE
imagesTable.ImageId = vart.ImageId

SELECT @numberRowsAffected = @@ROWCOUNT
if(@numberRowsAffected IS NULL)
begin
select @numberRowsAffected = 0
end


INSERT INTO [dbo].[ImagesStore]
(
--ImageId
OriginalPath
, ImageDat...
granadaCoder |96.10.6.xxx |2010-07-15 14:26:19
--I think my post is being truncated.

INSERT INTO [dbo].[ImagesStore]
(
--ImageId
OriginalPath
, ImageData
)
select
--ImageId
OriginalPath
, ImageData
From
@VariableHolderTable vart
Where
not exists ( select null from dbo.ImagesStore innerTable
where vart.ImageId = innerTable.ImageId )


SELECT @numberRowsAffected = @numberRowsAffected + @@ROWCOUNT
if(@numberRowsAffected IS NULL)
begin
select @numberRowsAffected = 0
end


COMMIT TRAN

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
print 'ROLLBACK'
ROLLBACK TRAN --RollBack in case of Error
END

declare @ErrorMsg varchar(512)
declare @ErrorSeverity int
select @ErrorMsg = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY()

RAISERROR(@ErrorMsg,@ErrorSeverity , 1)

SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;


RETURN

END CATCH

SET NOCOUNT OFF

GO


GRANT EXECUTE ON dbo.uspImageStoreUpsert TO public

GO



/*

--Highlight or uncomment the code below to see the stored procedure in action

declare @rowCount int

EXEC [dbo].[uspImageStoreUpsert]

'



1
C:\windows-vista-logo.jpg
/9j/4AAQSkZJRgABAgAAZABkAAD/7AARRHVja3kAAQAEAAAAUA
AA/+4ADkFkb2JlAGTAAAAAAf/bAIQAAgICAgICAgICAgMCAgID
BAMCA...
granadaCoder |96.10.6.xxx |2010-07-15 14:29:08

/*

--Highlight or uncomment the code below to see the stored procedure in action

declare @rowCount int

EXEC [dbo].[uspImageStoreUpsert]

'



1
C:\Documents and Settings\sholliday\My Documents\My
Pictures\vs2010logo-small.jpg
/9j/4AAQSkZJRgABAQIAJQAlAAD/2wBDAAUDBAQEAwUEBAQFBQ
UGBwwIBwcHBw8LCwkMEQ8SEhEPERETFhwXExQaFRERGCEYGh0d
Hx8fExciJCIeJBweHx7/2wBDAQUFBQcGBw4ICA4eFBEUHh4eHh
4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4e
Hh4eHh4eHh7/wAARCAAgACADASIAAhEBAxEB/8QAHwAAAQUBAQ
EBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUF
BAQAAAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0f
AkM2JyggkKFhcYGRolJicoKSo0NTY3ODk6Q0RFRkdISUpTVFVW
V1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmq
KjpKWmp6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi
4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEAAwEBAQEBAQEBAQAAAA
AAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAEC
AxEEBSExBhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNO
El8RcYGRomJygpKjU2Nzg5OkNERUZHSElKU1RVVldYWVpjZGVm
Z2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqK
mqsrO0tba3uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq
8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD7LzRmuK1HxRqE+uT6To
NpFPJbnbK8gJGR1HBGADxk1geIfizH4Vtr2DxRo13aanFB5tnG
iExXpyBhH5CkEjOScDnnpXYsDWdrLV9Ov3HPTxdKpNxi/K9tL+
p6pmjNfDt1r3xU+K/ilrWxvNRkkZty2tpK0NtbJnqcEAAf3mJJ
9TXT/BL4k+L/AAp8Uo/h/wCK7+6v7WS9OnutzIZDBNu2q0bk5K
lsDB4wcjmuurlE6cX7yckr2O90Va6Z7jqkWveD9a1fUtF0M6vb
arIsxKMS8DgHIKgElSSTx+nfz3UPAfjf4k64b/xAW02AAhHnjK
iJeyxx9fxOPrX0XtHoKNo9BWVLMpUleMVzbc3U8p4FOfxvl3t0
/wAzxHwj4q0/4Y6QPD2v+Db/AEq4i+/dWMBngvXH/LQOecn0Oc
dOOlcj8OfAmp+Mfit/wnupadPY2Ed+b8GaMoZHDbkRAQCQDjLY
xx619ObV9BRtHoKFmPKpOEbSlu73+7/hzqjBpq72P//Z


2
C:\Documents and Settings\sholliday\My Documents\My
Pictures\windows-vista-logo-small.jpg
/9j/...
granadaCoder |96.10.6.xxx |2010-07-15 14:32:58
I can't post any xml.

So add this line.

if (null != DS)
{
DS.WriteXml(@"C:\wuwutemp\dsWithIm
ageData"+Guid.NewGuid().ToString("N")+ ".xml");
}

(the above will go right below the existing code line
ADAP.Fill(DS, "ImagesStore");
)

Then open the file in notepad.
Highlight the first line, and delete it. (The xml declaration line).
Highlight everything, and paste in below where it says.
Note, you'll have to have added some images with the original code to see
anything in the result xml.


/*

--Highlight or uncomment the code below to see the stored procedure in action

declare @rowCount int

EXEC [dbo].[uspImageStoreUpsert]

'

The XML From Notepad seen above


'
, @rowCount output

print '/@rowCount/'
print @rowCount

granadaCoder |96.10.6.xxx |2010-07-15 15:36:12

Note, my code is specific to Sql Server 2005 or higher.

I also changed one of the columns to a different data-type.

[ImageData] varbinary(max) NOT NULL

.....
akm |117.196.237.xxx |2010-08-04 09:32:10
hi I am a beginer in asp.net c# 2008 i want to make a simple project so that i

need to connect sql server2005 database.please help me giving a simple c# code

to connect to the data base and insert data.
Last Updated on Monday, 01 March 2010 15:51