OracleClient Lob display through ASP
I searched for the answer to the question "How do I retrieve and display a
LOB field from Oracle using .Net?" and found nothing truely helpful. I found
information about reading in chunks of bytes and displaying the number of
bytes read, usually through a command prompt. My delima was that I have BLOB
data in an Oracle Database that needs to be displayed to users. Anyway, I
figured it out (maybe not rocket science, but beautiful in its simplicity).
Below is the code that I am using to display a BLOB file to the end user
using ASP.NET, OracleClient and VB.Net. I hope this helps someone. If there
are other, better ways of displaying a BLOB file, I am willing to take a look
at options and suggestions.
Here is what I am doing:
1) Have a search page where the user can determing which file (BLOB) they
want to view.
2) Pass the primary key (actually, I set a property in the main page that is
called from the display page, but... either way) to a secondary (retrieve
file) ASP page that will force a download or save of the document.
The search is pretty basic, so I am just going to show the retrieve page. I
used ASP.Net, and this code is set directly in the Source of the page rather
than use a code-behind page.
At the top of the page you need to declare the previous page to access the
property:
<%@ PreviousPageType VirtualPath="~/Default.aspx" %>
This is the code used to retrieve and display the BLOB field. Classes wil be
generated to perform much of the grunt work and streamline the process, but
this is all in one procedure in the ASP.Net Source code:
<% Dim key_in As String = New String(PreviousPage.bulletinSelected)
Dim iActual As Integer = 0
' create an OracleConnection object to connect to the
' database and open the connection
Dim myOracleConnection As _
New Data.OracleClient.OracleConnection( _
"Data Source=db;Persist Security Info=True;User
ID=user;Password=pword;Unicode=True")
myOracleConnection.Open()
' create an OracleCommand object
Dim myOracleCommand As _
Data.OracleClient.OracleCommand = myOracleConnection.CreateCommand()
' read the row
myOracleCommand.CommandText = _
"SELECT blob_filename, blobfield" & _
"FROM blobdatabase" & _
"WHERE blob_key = '" & key_in & "'"
' use datareader to retrieve record
Dim myOracleDataReader As _
Data.OracleClient.OracleDataReader = myOracleCommand.ExecuteReader()
Try
' get the LOB locator and put it in a variable
myOracleDataReader.Read()
Dim blobIn As Data.OracleClient.OracleLob =
myOracleDataReader.GetOracleLob(1)
' get the record data for display
Dim blobName As String = myOracleDataReader("blob_filename")
' write the content header information to open file
'This section sets the content type that forces IE to deal with the documnt
type and force download or save
Response.AppendHeader("Content-type:", "application/force-download")
Response.AppendHeader("Content-Disposition", "attachment; filename="
& blobName)
Response.AppendHeader("Content-Length", blobIn.Length)
Response.Charset = "UTF-8"
' write the blob data - this will begin to force the download
Response.BinaryWrite(blobIn.Value)
Catch ex As Exception
' typical errro message
MsgBox("Error Message: " & ex.Message & vbCrLf & vbCrLf & "Error
String: " & ex.ToString, MsgBoxStyle.OkOnly, "Error Error")
Finally
' close the OracleDataReader and the OracleConnection object
myOracleDataReader.Close()
myOracleConnection.Close()
End Try
%>
Thats it, no loops and reading piece and piecing them togheter, just print
out the blob data the the page and IE does the rest for us.
Works for me.
--
JonSteng ~ Making Magic in Sunny Central Florida ~
date: Sat, 8 Apr 2006 18:45:01 -0700
author: _BigDawg_
