Wednesday, March 7, 2012

Displaying Blob Image from db

I'm trying to retrieve an image that I have stored in a SQL Server db, and display it in an .aspx page. It's supposed to retrieve just one image, according to a unique key that's passed. However, when I view the page, nothing appears... just the Internet Explorer missing image placeholder.
I've tried code from several diff tutorials, and can't seem to get it to work.
I'm aware that some people think it's not a good idea to store images in a db, but I have to in this case, since the requirements for this project are that the images are stored in the database. I already know how to store references to file paths of images located in a server folder. I've just never done it this way... storing the whole images in the database. The table has 3 columns, one for the id, "FileType" stores the image type, and the "ImageBinary" is the image itself.
If anyone can shine some light on why my code doesn't display anything, I'd greatly appreciated your help.


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Response.Write("Page Loaded") 'This is just for testing to see if the page loads at all
Dim myCommand As New SqlCommand("Select * from UserImages WHERE id=1234", myConnection)
Try
myConnection.Open()
Dim DR As SqlDataReader
DR = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
If DR.Read() Then
Response.ContentType = DR.Item("FileType")
Response.BinaryWrite(DR("ImageBinary"))
Else
Response.Write("no image found")
End If
myConnection.Close()
Response.Write("<br>Image successfully retrieved!")
Catch SQLexc As SqlException
Response.Write("Read Failed : " & SQLexc.ToString())
End Try
End Sub

Documentation for datareader and blogs goes like this.
<documentation url="ms-help://MS.NETFrameworkSDKv1.1/cpguidenf/html/cpconobtainingblobvaluesfromdatabase.htm">

Dim pubsConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=pubs;")
Dim logoCMD As SqlCommand = New SqlCommand("SELECT pub_id, logo FROM pub_info", pubsConn)

Dim fs As FileStream ' Writes the BLOB to a file (*.bmp).
Dim bw As BinaryWriter ' Streams the binary data to the FileStream object.

Dim bufferSize As Integer = 100 ' The size of the BLOB buffer.
Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by GetBytes.
Dim retval As Long ' The bytes returned from GetBytes.
Dim startIndex As Long = 0 ' The starting position in the BLOB output.

Dim pub_id As String = "" ' The publisher id to use in the file name.

' Open the connection and read data into the DataReader.
pubsConn.Open()
Dim myReader As SqlDataReader = logoCMD.ExecuteReader(CommandBehavior.SequentialAccess)

Do While myReader.Read()
' Get the publisher id, which must occur before getting the logo.
pub_id = myReader.GetString(0)

' Create a file to hold the output.
fs = New FileStream("logo" & pub_id & ".bmp", FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)

' Reset the starting byte for a new BLOB.
startIndex = 0

' Read bytes into outbyte() and retain the number of bytes returned.
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)

' Continue reading and writing while there are bytes beyond the size of the buffer.
Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()

' Reposition the start index to the end of the last buffer and fill the buffer.
startIndex += bufferSize
retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)
Loop

' Write the remaining buffer.
bw.Write(outbyte, 0 , retval - 1)
bw.Flush()

' Close the output file.
bw.Close()
fs.Close()
Loop

' Close the reader and the connection.
myReader.Close()
pubsConn.Close()


</documentation>|||Interestingly enough, I actually already looked at that documentation and tried another test page based on that code. That page doesn't display anything either. I figure there must be something really basic that I've messed up along the way.

Here is how I customized the code that you just referenced:


Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Response.Write("Loading page...<br>") 'To test that the page has loaded

Dim conn As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim sql = "Select id, ImageBinary from UserImages WHERE id=1234"
Dim cmd As New SqlCommand(sql, conn)
Dim fs As FileStream
Dim bw As BinaryWriter
Dim bufferSize As Integer = 100 'The size of the BLOB buffer
Dim outbyte(bufferSize - 1) As Byte 'The BLOB byte() buffer to be filled
Dim retval As Long 'The bytes returned from GetBytes
Dim startIndex As Long = 0 'The starting position in the BLOB output
Dim strID As String = 0
conn.Open()
Dim DR As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SequentialAccess)
Do While DR.Read()
'Get the id, which must occur before the image
strID = DR.GetValue(0).ToString()
'Create a file to hold the output
fs = New FileStream("ImageBinary" & strID & ".bmp", FileMode.OpenOrCreate, FileAccess.Write)
bw = New BinaryWriter(fs)
'Reset the starting byte for a new BLOB
startIndex = 0
'Read bytes into outbyte() and retain the number of bytes returned
retval = DR.GetBytes(1, startIndex, outbyte, 0, bufferSize)
'Continue reading and writing while there are bytes beyond the size of the buffer
Do While retval = bufferSize
bw.Write(outbyte)
bw.Flush()
'Reposition the start index to the end of the last buffer and fill the buffer
startIndex += bufferSize
retval = DR.GetBytes(1, startIndex, outbyte, 0, bufferSize)
Loop
'Write the remaining buffer.
bw.Write(outbyte, 0, retval - 1)
bw.Flush()
'Close the output file
bw.Close()
fs.Close()
Loop
DR.Close()
conn.Close()

End Sub

No comments:

Post a Comment