C# Tutorials and offshore development in India
    Tutorials   Resources   Forum   Communities   Interview   Jobs   Projects   Training   ASP.NET Web Hosting    
Silverlight Games | Mentor | Code Converter | Articles | Code Factory | Computer Jokes | Members | Peer Appraisal | IT Companies | Bookmarks | Polls | Revenue Sharing |


Prizes & Awards
My Profile



Active Members
TodayLast 7 Days more...

Play Silverlight Games or Submit your Silverlight applications and earn 90% AdSense revenue.

New Feature: Community Sites: Create your own .NET community website and start earning from Google AdSense ! It's Free !




Resources » Articles » Databases »

Store and Retrieve Images in SQL Server database


Posted Date: 18 Jun 2004    Resource Type: Articles    Category: Databases
Author: srunokshiMember Level: Bronze    
Rating: Points: 10



Click here to download the demo application.

Introduction

In addition to storing text, numbers and other bits of data, SQL Server also allows you to store Binary data (usually images). This article will talk about storing (To SQL) and using the stored image (from SQL) through VB.Net Windows Application.

Prerequisites


  • Knowledge in Using SQL Server Stored Procedure

  • Knowledge in VB.NET Windows Application

  • Knowledge in ADO.NET



Steps Invloved:

Step 1: Creating the Table SampleImageTable

Create a table SampleImageTable with the field sampleimage of data type
image. Use the following SQL Satement to create the table.

create table SampleImageTable (sampleimage image)


Step 2: Creating a Stored Procedure

Create a stored procedure UploadImage which is used to store the images. Use the following SQL Query.

create procedure UploadImage(@imgsamgetimage as image)
as
insert into SampleImageTable (sampleimage) values (@imgsamgetimage)


Step 3: Creating the Sample Project

  • Create a Project "ImageDemo"

  • Add a new Windows Form and name it as "IamgeDemo"

  • Add One Textbox to the form and name it as txtImg

  • Add One OpenFileDialog to the form

  • Add three Command Buttons to the form and name them as btnseltfile, btnUpload, and btnDownload

  • Add one to the form

  • Design the form as shown below





Step 4: Writing the Code for save and reteriving the files

Write the following code in the btnseltfile click event

Private Sub btnseltfile_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles

btnseltfile.Click
OpenFileDialog1.ShowDialog()
txtImg.Text = OpenFileDialog1.FileName
End Sub


Explaination for the code.

The above is used to display the OpenFileDialog to get the Image file name which needs to be uploaded. Write the following code in the btnUpload button click event

Private Sub btnUpload_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnUpload.Click
Try
If Trim(txtImg.Text) = "" Then
MsgBox("Please select a image.")
Exit Sub
End If
Dim fs As New FileStream(Trim(txtImg.Text), FileMode.Open)
Dim Data() As Byte = New [Byte](fs.Length) {}
fs.Read(Data, 0, fs.Length)

Dim con As New System.Data.SqlClient.SqlConnection("data source=mt5;initial
catalog=master;
user id=sa;password=mms")
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand("UploadImage")
cmd.Connection = con
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@imgsamgetimage", Data)
cmd.ExecuteNonQuery()
con.Close()
fs.Close()
Catch ex As System.Data.SqlClient.SqlException
MsgBox(ex.Message)
End Try
End Sub


Explaination for the above code.

The above code is used to get the file content in a bit array and store the image content in the SQL Server database.

Write the following code in the btndownload button click event

Private Sub btnDownload_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnDownload.Click

Dim con As New System.Data.SqlClient.SqlConnection("data source=mt5;initial
catalog=master;
user id=sa;password=mms")
con.Open()
Dim cmd As New System.Data.SqlClient.SqlCommand("select * from SampleImageTable")
cmd.Connection = con
cmd.CommandType = CommandType.Text
Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds)
Dim bits As Byte() = CType(ds.Tables(0).Rows(0).Item(0), Byte())
Dim memorybits As New MemoryStream(bits)
Dim bitmap As New Bitmap(memorybits)
PictureBox1.Image = bitmap
End Sub


Click here to download the demo application.

Explaination for the above code.

The above code is to read the image field content and transform that into a bitmap image and display that in a picture box.

Please feel free to mail me if you have any doubts.

Email: srunosru@rediffmail.com




Responses

Author: Abhishekh    18 Jun 2004Member Level: Bronze   Points : 0
the article was neat. i would also like to know how we can store images as thumbnails.


Author: critic    18 Jun 2004Member Level: Bronze   Points : 0
You have given a link to an image and a demo application, but both are missing. If I click, "Click here to download the demo application.", no demo is available... !!


Author: VISU    06 Jul 2004Member Level: Bronze   Points : 0
Its Very good article. its neat and Understnadable by all the Programmers and Beginners also.

Thanks.


Author: Faniel    08 Jul 2004Member Level: Bronze   Points : 0
Hi
I was just searching for this for last 2 day..
Thanks man.. it works fine and
looks good..



Author: Shailendra Gupta    09 Jul 2004Member Level: Bronze   Points : 0
hi,
it was asked in interview.
thnx.


Author: jitender    06 Aug 2004Member Level: Bronze   Points : 0


the article is good, it will help many ppls.
But i think there should be some other method also to store large images.

jitu.


Author: Erick Shuai    11 Aug 2004Member Level: Bronze   Points : 0
You do those in Windows Application , How to do these in Web Application?


Author: Rathish Nair    08 Sep 2004Member Level: Bronze   Points : 0

Your article is quiet good, but I would like to know about storing documents into SQL server. Is that possible ?


Author: chakravarthy    07 Oct 2004Member Level: Bronze   Points : 0
The article is simply superb. Its working fine with sql server database. May i know how to do the same with oracle database.

Looking forward ur reply
Cheers
Chakri from Vizag


Author: siva kumar    14 Oct 2004Member Level: Bronze   Points : 0
ur article is giving encouragement to a lot for me and everyone. please continue to mail ur documents with ode. freshers like we need ur guide.

thanks for ur article once agin



Author: Anantha    27 Nov 2004Member Level: Bronze   Points : 0
Hi,

It is a very goodArticle.It is working fine.

Thannx
Satya


Author: Tushar    29 Nov 2004Member Level: Bronze   Points : 0
Error: The Selected file is not valid Visual studeio application


Author: c.ashok    02 Feb 2007Member Level: Bronze   Points : 0
really it is very nice
i tried a lot regarding storing images into sql atlast i found in this website
thanks a lot for publishing this article



Author: Mohammed Shafeek    23 Nov 2007Member Level: Bronze   Points : 0
Its getting error while running ur application
upload is working
Problem in download
error description is
'An unhandled exception of type 'System.ArgumentException' occurred in system.drawing.dll'


Author: senthil kumar    20 Mar 2008Member Level: Silver   Points : 0
Superrrrrrrrrrrrr.................


Author: hannahgrace    01 Jul 2008Member Level: Bronze   Points : 1
i tried to use the code but it didn't work. it gives me PARAMETER IS NOT VALID error..


Author: surender    01 Aug 2008Member Level: Silver   Points : 0
the article was good and thanks for sharing your knowledge...


Author: ankit_champaneriya    07 Mar 2009Member Level: Silver   Points : 1
like image u can store and retrive other files also..
If you want to retrive/download file.. do following.
put button.
popup attanchd download.aspx file through code.
just change dataset used in that.
and u will get your file..

regards,
ANKIT CHAMPANERIYA


download.rar
Author: AMARJIT SINGH    24 Mar 2009Member Level: Gold   Points : 2
Hi friend,

Your article is realy very good.

it is quite interesting.

BUT THIS CAN BE DONE WITHOUT USING STORED PROCEDURE BY SOME SLIGHT CHANGES.

i find at some places in your code that if instead of your this code that you have mentioned(in your code) below :

--------------

Dim con As New System.Data.SqlClient.SqlConnection("data source=mt5;initial catalog=master; user id=sa;password=mms")

con.Open()

Dim cmd As New System.Data.SqlClient.SqlCommand("select * from SampleImageTable")

cmd.Connection = con
cmd.CommandType = CommandType.Text

Dim da As New System.Data.SqlClient.SqlDataAdapter(cmd)

---------------


instead of the above code you can also use these few lines of codes :---
----------------

str = "select * from SampleImageTable"

Dim constring As String

constring = "data source=mt5;initial catalog=master; user id=sa;password=mms"

Dim da As New SqlDataAdapter(str, constring)

----------------
rest of your code is perfect.

try it.
Enjoy programming.

(AMARJIT SINGH)


Author: sri    25 Apr 2009Member Level: Silver   Points : 1
Hi srunokshi,

Your article was simply superb.I dont have any idea about about store image in sql ..

But ur article explains me too good..

Tx
Krishna


Feedbacks      
Popular Tags   What are tags ?   Search Tags  
Sign In to add tags.
Store images in database  .  Store images from sql server  .  Store and retrieve images from sql server  .  Save images in sql server  .  Save images in database  .  Retrieve images from sql server  .  Retrieve images from database  .  

Post Feedback


This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.
Next Resource: Generate Next Numbers with SQLServer
Previous Resource: Getting a List of all the table Names created by the db USER
Return to Discussion Resource Index
Post New Resource
Category: Databases


Post resources and earn money!
 
Related Resources



dotNet Slackers

About Us    Contact Us    Privacy Policy    Terms Of Use