display oracle blob in gridview column

[bookouri]

I've been struggling for a couple of weeks with this project and I havnt made any real progress.

I have a table in an oracle database that contains employee photos (jpegs) in blob columns. From all my research it appears that what i need to use is an httphandler file to display these photos on a web page. I have tried modifying every example I have found and just can't get anything to work. I think part of my problem is that I dont know enough about asp.net to translate between the examples which are always sqlserver centric and oracle, and this is my first asp.net project so I am at a loss at this point.

I can put a gridview control on my page and display my records except that the blob column shows up in the grid as "System.Byte []"

From what I have found so far I think I need a call in my gridview columns to an httphandler to return the actual images. I've found several examples of code that are supposed to do this but I have not been able to make any of them work.

My table is something like: emp_id, emplname, empfname, emp_photo so my gridview is basically a "select * from employees."
From what i can find, I need to add a column to the grid that contains something like

<asp:imageField="imImage.ImageUrl = "~/ImageHandler.ashx?ImID=emp_id; emp_photo" HeaderText="photo" SortExpression="photo">
</asp:imagefield>

and the imagehandler.ashx file would contain something like:

<%@ WebHandler Language="C#" Class="ImageHandler" %>

using System;

using System.Web;

using System.Configuration;

using System.Data.SqlClient;

public class ImageHandler : IHttpHandler {


public void ProcessRequest (HttpContext context) {

string imageid = context.Request.QueryString["ImID"];

SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings

["connectionString"].ConnectionString);

connection.Open();

SqlCommand command = new SqlCommand("select photo from employees where emp_id="+imageid, connection);

SqlDataReader dr = command.ExecuteReader();

dr.Read();

context.Response.BinaryWrite((Byte[])dr[0]);

connection.Close();

context.Response.End();


}


public bool IsReusable {

get {

return false;

}

}



}

This example seems simpler than most of the examples I have found, but I have not had any luck modifying it to make it work.

Can anybody point me to any examples that are simple enough that I might be able to make some progress?

thanks in advance for any help or suggestions

 

[jmeckley]

you are 90% there.

public class ImageHandler : IHttpHandler 
{
   public void ProcessRequest (HttpContext context) 
   {
      var id= context.Request.QueryString["ImID"];
      var settings = ConfigurationManager.ConnectionStrings
      var factory = DbProviderFactories.GetFactory(settings.ProviderName);
      using(var connection = factory.CreateConnection())
      using(var command = connection.CreateCommand())
      {
         connection.ConnectionString = settings.ConnectionString;
         command.CommandText = "select photo from employees where emp_id=@id";
         
         var parameter = command.CreateParameter();
         parameter.ParameterName = "id";
         parameter.Value = id;
         command.Parameters.Add(parameter);

         connection.Open();
         var image = (byte[])command.ExecuteScalar();
         context.Response.BinaryWrite(image);
         context.ContentType = "image/jpeg";
      }
   }

   public bool IsReusable 
   {
      get { return true; }
   }
}

things to note:
1. we abstract the type of database. configure the oracle provider in the web.config
2. we are using a parameterized query
3. we are properly cleaning up our resources
4. we can reuse the handler because this object is stateless
5. because we are returning exactly one value we can use execute scalar instead of execute reader.
6. we are setting the context type so the browser knows what type to do with the stream.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[bookouri]

thanks, so Ive got that code in my imagehandler.ashx file and in my test.aspx file I have my gridview and it looks like this:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Page Language="C#" %>
<html dir="ltr" xmlns="

http://www.w3.org/1999/xhtml">

<head runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>
<body>
<form id="form1" runat="server">
<asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="emp_ID" HeaderText="emp_ID" SortExpression="emp_ID">
</asp:BoundField>
<asp:BoundField DataField="emplNAME" HeaderText="emplNAME" SortExpression="emplNAME">
</asp:BoundField>
<asp:ImageField DataImageUrlField="imagehandler.ashx?imid=emp_id">
</asp:ImageField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:tag %>" ProviderName="<%$ ConnectionStrings:empdatasource.ProviderName %>" SelectCommand="SELECT emp_ID, emplNAME FROM employees;">
</asp:SqlDataSource>
</form>
</body>
</html>

Is this
<asp:ImageField DataImageUrlField="imagehandler.ashx?imid=emp_id">
</asp:ImageField>
the right way to call my imagehandler.ashx file?

 

[jmeckley]

i think it's

<asp:ImageField DataImageUrlField="emp_id" DataImageUrlFormatString="~/imagehandler.ashx?imid={0}">

I haven't used webforms in years so I may be off. MSDN is a good resource for API documentation (

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.imagefield.aspx).

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[bookouri]

Getting closer. I can now run my page and the grid columns appear, except that the photo column has the Red X. I can see that its trying to retrieve the photos. The page is walking through all the emp_id numbers. Its just not returning anything but Red X's.

Here's how the aspx file looks now:

<asp:ImageField DataImageUrlField="emp_id" dataimageurlformatstring="imagehandler.ashx?imid={0}">
</asp:ImageField>


and the imagehandler.ashx


public class ImageHandler : IHttpHandler
{
public void ProcessRequest (HttpContext context)
{
var id= context.Request.QueryString["ImID"];
var settings = ConfigurationManager.ConnectionStrings
var factory = DbProviderFactories.GetFactory(settings.ProviderName);
using(var connection = factory.CreateConnection())
using(var command = connection.CreateCommand())
{
connection.ConnectionString = settings.ConnectionString;
command.CommandText = "select photo from employees where emp_id=@id";

var parameter = command.CreateParameter();
parameter.ParameterName = "id";
parameter.Value = id;
command.Parameters.Add(parameter);

connection.Open();
var image = (byte[])command.ExecuteScalar();
context.Response.BinaryWrite(image);
context.ContentType = "image/jpeg";

}
}

public bool IsReusable
{
get { return true; }
}
}


I can't see anything wrong. Any suggestions where to look for the problem?

 

[jmeckley]

case sensitivity with the query string. set a break point in the http handler and step through the code to confirm you are getting the id.

also verify the content header text. my code is all from memory, so there could be problems with that.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[bookouri]

thanks, im still hacking away at it.