Getting Images into a Repeater from SQL Server

[gemel]

I am developing an application that uses SQL 2000 as my source of
images. I have successfully created the code to load the images onto
the SQL Server and also to retrieve the images into a dataset. I
tested the application by populating a Dataset with the images from
SQL Server and rendered just one of the images by using a bitmap and
inserting the resulting stream into the response stream as a Jpeg
format.

My next move was to bind this returned set to an image control that I
included in a Repeater control. I set the datasource property in code
as follows:

ds = si.SqlBLOB2DS("GdsTest", "Picture", "Category", "Test")
GdsPicsRepeater.DataSource = ds.Tables(0).DefaultView
GdsPicsRepeater.DataBind()

The I included the follwing in the HTML view:

<asp:Repeater id="GdsPicsRepeater" runat="server">
<ItemTemplate>
<table>
<tr>
<td>
<img src= <%#
DataBinder.Eval(Container,"DataItem.Picture")%>
</td>
</tr>
</table>
</ItemTemplate>
</asp:Repeater>


When the repeater is rendered it shows the correct number of images
from the dataset, but it doesn't show the actual image.


I am not using the response steam in this case.

Any help please?

Regards


John L

 

[billmiami2]

You're almost there. Just a couple of things:

*Create a separate page that extracts the image from SQL Server and
saves it to a stream (just as you've already done!). Pass the image id
to this page in the querystring (i.e., generateimage.aspx?pictureid=27)
so that it knows which image to extract.

In your repeater, set the src for the image to the page above, passing
the image identifier as in

<img src=<%# "generateimage.aspx?pictureid=" &
DataBinder.Eval(Container.DataItem, "pictureid")%>

Bill E.
Hollywood, FL

 

[gemel]

Thank you very much for your response. I can understand the need to
call on the aspx page to render the picture deltails. However, I'm
concerned about passing a specific ID for this picture. After all, the
repeater is going to want a different picture as it populates each
entry in the list so I need the repeater to generate a different value
each time.


Regards

John L

 

[billmiami2]

gemel,

You would be passing the id of each picture in your repeater. If, for
example, your repeater will show ten pictures, you will pass the id for
all ten to the page that generates the image. That page will be called
ten times--once for each image shown in the repeater.

Personally, I never store images in the database. I instead I place
the image itself on the file server and I store a string that
represents the path to the image file in the database. I modify the
path a bit to create the src for the img control. Therefore, there is
no need to call another page to generate a stream for each image.

Bill

 

[gemel]

Bill,
Thanks for your patience on this one. I agree that storing the
images directly on the server is easier than this approach. My problem
is that my ISP gives me storage on the web server and also on the SQL
server. If I use the SQL server for images then I don't have to use my
quota of space on the web server. I also wanted to get experience with
disconnected operation using datasets.

This has led me to the current problem. I'm not sure that I fuly
understand the repeater when it comes to this particular function. I
know you gave me a good example but this applied to just one image (I
think). My dataset contains only the list of images stored in a single
BLOB column, I didn't pouplate it with IDs of those BLOBs. I assumed I
would just be looking at the range of row numbers contained in the
dataset which is a contiguous range starting at 0.


I would therefore be grateful (but tedious) if you would give me an
example to use in these circumstances. Its the arguments that I send
to this page as part of the source address that is confusing me.

Regards

John L

 

[billmiami2]

gemel,

The example I gave you applied to multiple images, not just one.
However, I suppose that I was not clear enough in my explanation so let
me try to clarify.

I assume that your SQL server database has a table that contains a list
of images, including a BLOB column to store the actual image. However
this table MUST also contain a way to identify each row in the table so
that you can retrieve a single image. If you don't have such a column,
you can add an identity column to the table so that it generates a
sequential identifier for each row. On the other hand, if you're
storing the name if each image in one of the columns and this name is
unique, you could also use the name to identify the image, but this
could present some problems if the names use strange characters. Let's
assume that you create an identity column called PictureID so that you
now have a unique identifier associated with each of your images.

You will need to create an image generator page which creates a
response stream for ONE IMAGE AT A TIME. Let's assume that you call it
"generateimage.aspx" This page would likely contain code that extracts
a single BLOB into a byte array then saves it to an output stream (I
assume that you already know how to do this because you seemed to
indicate that you do). The code in generageimage.aspx will take a
single PictureID passed to it in the query string and retrieve a single
image from the database using this PictureID by creating a
dataset/datatable with a single row, BLOB column included. The single
image in the BLOB column is retrieved into a byte array and then sent
to the output stream.

Lastly, you will have another page with a repeater used to view
multiple pictures. Let's call it "viewpictures.aspx". You will
populate the repeater with a dataset based on a query of the table with
the BLOB, but YOU WILL NOT INCLUDE THE BLOB ITSELF in this query. You
will only include the identifier (PictureID) for the pictures that you
want in this query. In your repeater, you will have an image control
(IMG) whose source (SRC) is set to the URL of your image generator page
(generateimage.aspx) to which you will append a query string that
contains the PictureID of each image. For example,

<img src=<%# "generateimage.aspx?pictureid=" &
DataBinder.Eval(Container.DataItem, "pictureid")%>

might translate to

<img src=generateimage.aspx?pictureid=25 />

for the first row that the repeater creates and


<img src=generateimage.aspx?pictureid=57 />

for the next row and so on.

Notice that we set the src of each image to an aspx page, not an actual
image file location. As a result, FOR EACH <img> generated by the
repeater, a separate request will be submitted to generateimage.aspx
which will in turn stream the appropriate picture into the html output.
The browser will show a nice table with multiple images from your SQL
Server database.

I hope that this is clear now.

Bill

 

[Patrick Olurotimi Ige]

Bill that was a detailed explanation but can you back it up with a
sample!
That would be great

 

[billmiami2]

OK Patrick. I threw this together untested, but it's probably very
close.

Bill.
____________________


generateimage.aspx (page declaration only, no html/tags needed)
_____________________
<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="generateimage.aspx.vb" Inherits="myproject.generateimage"%>


generateimage.aspx.vb (Code behind)
_____________________
Imports System.Data.SqlClient
Public Class generateimage
Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
If Request.QueryString("pictureid") <> "" Then
'Specify the connection string and SQL
Dim strConnect As String = "Enter your connection string
here or get it from your web.config"
Dim strPictureID As String =
Request.QueryString("pictureid")
Dim strSQL As String = "SELECT PictureBLOB FROM tblPictures
WHERE PictureID=" & strPictureID

'Open the connection and the adapter. Create a dataset
Dim objConn As New SqlConnection(strConnect)
Dim objAdapt As New SqlDataAdapter(strSQL, objConn)
Dim objDS As New DataSet()
objAdapt.Fill(objDS, "PictureTable")

'Store the BLOB as byte
Dim byteData() As Byte
byteData = objDS.Tables(0).Rows(0)(0)

'Output the image in the response
Response.Buffer = True
Response.ContentType = "Image/JPEG"
Response.BinaryWrite(byteData)
End If
End Sub

End Class



viewpictures.aspx
_______________________

<%@ Page Language="vb" AutoEventWireup="false"
Codebehind="viewpictures.aspx.vb" Inherits="test2.viewpictures"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>viewpictures</title>
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<!--repeater for display of images-->
<asp:repeater id="rptpictures" runat="server"
enableviewstate="false">
<headertemplate>
<table width=100%>
</headertemplate>
<itemtemplate>
<tr>
<td width="50%"><span><%# DataBinder.Eval(Container.DataItem,
"PictureDescription")%></span></td>
<td width="50%"><img src='<%# "generateimage.aspx?pictureid=" &
DataBinder.Eval(Container.DataItem, "PictureID")%>' /></td>
</tr>
</itemtemplate>
<footertemplate>
</table>
</FooterTemplate>
</asp:repeater>
</form>
</body>
</html>


viewpictures.aspx.vb (code behind)
____________________________

Imports System.Data.SqlClient
Public Class viewpictures
Inherits System.Web.UI.Page
Protected WithEvents rptpictures As Repeater

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Specify the connection string and SQL
Dim strConnect As String = "Enter your connection string here
or get it from your web.config"
Dim strPictureID As String = Request.QueryString("pictureid")
Dim strSQL As String = "SELECT PictureID, PictureDescription
FROM tblPictures"

'Open the connection and the adapter. Create a dataset
Dim objConn As New SqlConnection(strConnect)
Dim objAdapt As New SqlDataAdapter(strSQL, objConn)
Dim objDS As New DataSet()
objAdapt.Fill(objDS, "PictureTable")

'Bind the dataset to the repeater
rptpictures.DataSource = objDS
rptpictures.DataBind()
End Sub

End Class