Working With Database Code Sample

As usual the pictures are stored as image files at the hard drive. But sometimes it is necessary to store it in the database as BLOB fields. It is rather popular in the some Web solutions which deals with a lot of images. Graphics Processor allow you to get the images from (and put it to) the database. The following code samples demonstrates you how to work with database.

RasterObject object supports methods LoadStream and SaveStream. You may retrieve any data via Stream objects, for example ADODB.Stream. This object allows you to deal with database via ADO, the most explotable way to work with databases in the ASP.

The first code sample loads an image from file and saves it to DB. The second one loads it from database, resizes and saves to the Response stream.

Let's consider the first code sample. We will use Microsoft Access database in this example (see the connection string):

[VB Script]
<%
Option Explicit
Dim objRaster, objConnection, objRecordset, objStream, lngID
'Create and open ADO Connection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("images.mdb") & ";"

'Open ADO Recordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")
objRecordset.Open "SELECT * FROM [Image]", objConnection, 2, 3

'Add new record
objRecordset.AddNew "Image_Data", ""

'Create ADODB Stream
Set objStream = Server.CreateObject("ADODB.Stream")
objStream.Type = 1 'Indicate binary data.
objStream.Open

'Create RasterObject and load image from file
Set objRaster = Server.CreateObject("GraphicsProcessor2002.RasterObject")
objRaster.LoadFile Server.MapPath("source.jpg")

' [ ..... do some actions over the image ..... ]

'Save data to ADODB Stream
objRaster.SaveStream objStream

' Write the stream to the Image_Data field of new record
objStream.Position=0
objRecordset.Fields("Image_Data").Value = objStream.Read 

objRecordset.Update

lngID = objRecordset.Fields("Image_ID").Value

objRecordset.Close
objConnection.Close%>
<a href="LoadFromDB.asp?ID=<%=lngID%>">Click to see saved image</a>
<%
Set objRaster = Nothing
Set objRecordset = Nothing
Set objConnection = Nothing
Set objStream = Nothing
%>

[Visual Basic]
'Create and open ADO Connection
Dim objConnection As New ADODB.Connection
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\images.mdb;"

'Open ADO Recordset
Dim objRecordset As New ADODB.Recordset
objRecordset.Open "SELECT * FROM [Image]", objConnection, adModeWrite, adLockOptimistic

'Add new record
objRecordset.AddNew "Image_Data", ""

'Create ADODB Stream
Dim objStream As New ADODB.Stream
objStream.Type = adTypeBinary
objStream.Open

'Create RasterObject and load image from file
Dim objRaster As New RasterObject
objRaster.LoadFile "C:\source.jpg"

' [ ..... do some actions over the image ..... ]

'Save data to ADODB Stream
objRaster.SaveStream objStream

' Write the stream to the Image_Data field of new record
objStream.Position = 0
objRecordset.Fields("Image_Data").Value = objStream.Read

objRecordset.Update

Text1.Text = objRecordset.Fields("Image_ID")

objRecordset.Close
objConnection.Close

The second code sample, as noticed above, demonstrates how to load the images from the database:

[VB Script]
<%
Option Explicit
Dim objRaster, objConnection, objRecordset, objStream, objField
Dim lngID, strSQL

lngID = CLng(Request.QueryString("ID"))

'Create and open connection to database
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("images.mdb") & ";"

'Open Recordset
Set objRecordset = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM [Image] WHERE Image_ID=" & lngID
objRecordset.Open strSQL, objConnection, 2, 3


If Not objRecordset.EOF Then
    Set objField = objRecordset("Image_Data")
    'Create ADODB Stream
    Set objStream = Server.CreateObject("ADODB.Stream")
    objStream.Open 
    objStream.Type = 1 'Indicates binary data
    objStream.Write objField.Value
    'Create RasterObject and load image from the stream we obtained from database
    Set objRaster = Server.CreateObject("GraphicsProcessor2002.RasterObject")
    objRaster.LoadStream objStream

    ' [ ..... do some actions over the image ..... ]

    'Write data to response stream
    Response.Clear
    objRaster.SaveStream Response
Else
    Response.Write "Requested image doesn't exist in database"
End If

' Freeing resources
objRecordset.Close
objConnection.Close

Set objRaster = Nothing
Set objRecordset = Nothing
Set objConnection = Nothing
Set objField = Nothing
Set objStream = Nothing
%>

[Visual Basic]
Dim lngID As Long, strSQL As String

lngID = CLng(Text1.Text)

'Create and open connection to database
Dim objConnection As New ADODB.Connection
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\images.mdb;"

'Open Recordset
Dim objRecordset As New ADODB.Recordset
strSQL = "SELECT * FROM [Image] WHERE Image_ID=" & lngID
objRecordset.Open strSQL, objConnection

Dim objField As ADODB.Field

If Not objRecordset.EOF Then
    Set objField = objRecordset("Image_Data")
    'Create ADODB Stream
    Dim objStream As New ADODB.Stream
    objStream.Open
    objStream.Type = adTypeBinary
    objStream.Write objField.Value
    'Create RasterObject and load image from the stream we obtained from database
    Dim objRaster As New RasterObject
    objRaster.LoadStream objStream

    ' [ ..... do some actions over the image ..... ]

    'Draw on PictureBox
    Set Picture1.Picture = objRaster.SavePicture
Else
    MsgBox "Requested image doesn't exist in database"
End If

' Freeing resources
objRecordset.Close
objConnection.Close

Set objRaster = Nothing
Set objRecordset = Nothing
Set objConnection = Nothing
Set objField = Nothing
Set objStream = Nothing

Working With Database Code Sample

See also