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