By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,575 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

How to insert and retrieve images to/from Sqlserver database

P: n/a
Hi all!!

i am working on C#, ASP.Net with Sql server2000 and i am facing a
problem, my Question is:
"How to insert and retrieve images to/from Sqlserver2000 Database"
I created a table in database there are only two Columns:

ImageID( Datatype int) and ImageURL (Binary)

Thanks in advance!!

Ahuja;)

Feb 14 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Feb 14, 7:09 am, "ahujasa...@gmail.com" <ahujasa...@gmail.com>
wrote:
Hi all!!

i am working on C#, ASP.Net with Sql server2000 and i am facing a
problem, my Question is:

"How to insert and retrieve images to/from Sqlserver2000 Database"

I created a table in database there are only two Columns:

ImageID( Datatype int) and ImageURL (Binary)

Thanks in advance!!

Ahuja;)
I wanted to store a JPG file a SQL2000 database. Here's how I
accomplished the task in Visual Basic:

The database PhotoLog has a PhotoImages table that has a column called
PhotoImage of datatype IMAGE.
I load the file into an ADODB.Stream object. Then I insert the stream
into the PhotoImage column using an ADODB.Recordset object. Next I
retrieve the image and save it as a file under a different name.
Finally, I display it in a Picture control.

There may be some differences between C# and VB, but the use of ADODB
objects should be the same. Note that this code borrows from some I
found when researching saving and retrieving scanned images.

Lou Phillips

PS: I hope this snippet has not lost anything in my cut-and-paste
preparation of this posting.

Global cn As ADODB.Connection

Sub Main()

Dim sConnect As String

sConnect = "Provider=SQLOLEDB;" _
& "Data Source=Gateway;" _
& "Integrated Security=SSPI;" _
& "Initial Catalog=PhotoLog"

'Create a new connection object
Set cn = New ADODB.Connection

With cn
.ConnectionTimeout = 60
.ConnectionString = sConnect
.Open
End With

Call DataLoadImages( "C:\TEMP\Picture.JPG" )

Call FileLoadImages( "C:\TEMP\Image.JPG" )

cn.close

set cn = Nothing

Form1.Picture1.LoadPicture("c:\temp\Image.jpg")

End Sub
Private Sub DataLoadImages( sFileToSave as String )

Dim rs As ADODB.Recordset
Dim st As ADODB.Stream

Dim sqlStmt As String
Dim szFullPath As String

Dim var As Variant

Set rs = New ADODB.Recordset

sqlStmt = "PhotoImages"
rs.Open sqlStmt, cn, adOpenKeyset, adLockOptimistic

'Initialize the stream object used to load the file.
Set st = New ADODB.Stream

st.Type = adTypeBinary

st.Open
st.LoadFromFile (sFileToSave)

var = st.Read

rs.AddNew
rs.Fields("PhotoImage").Value = var

rs.Update
st.Close
rs.Close

End Sub
Private Sub FileLoadImages( sFileToWrite as String )

Dim rs as ADODB.Recordset
Dim st as ADODB.Stream

Set rs = New ADODB.Recordset

rs.Open sqlStmt, cn, adOpenForwardOnly, adLockReadOnly

'Initialize the stream object used to persist to a file
Set st = New ADODB.Stream
st.Type = adTypeBinary
st.Open

rs.MoveFirst

var = rs.Fields(1).Value

'Write the value of the field to the stream
st.Write var

'Save the content of the stream to a file
st.SaveToFile ( sFileToWrite )

'Close the stream
st.Close

'Close recordset
rs.Close

'Free resources
Set rs = Nothing
Set st = Nothing

End Sub


Feb 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.