How to retrieve a BLOB Field from Oracle DB via ASP.NET

At this article, I will demonstrates How to integrate ASP.NET Web Application with Oracle Database to retrieve a BLOB  field from Oracle database side and show it in ASP.NET page.

Steps :

Open Visual Studio > Open existing  ASP.NET Solution > Add <img> tag within a page that should be look like this

<img id="EmpImg" 
src="ShowImage.ashx?Account=<%=Request.QueryString["Account"]%>"
alt="" width="100px" height="100px" style="float:right"/>

Note:  The image source is a generic handler called  ShowImage.ashx? 

What’s the Generic Handler !?

Some ASP.NET files are dynamic. They are generated with C# code or disk resources. These files do not require web forms. Instead, an ASHX generic handler is ideal.

It can return an image from a query string, write XML, or any other data.to know more about Geariec Handler you can click here

When I can use Generic Handler(ASHX) rather than Web Forms (ASPX)?

• When WebForms(aspx)to be used
  • Simple Html Pages.
  • Asp.net Custom Controls.
  • Simple Dyanamic Pages.
• When Handlers(ashx) to be used
  • Binary files.
  • Dynamic Image Views.
  • Performance critical web pages.
  • XML files.
  • Minimal Web Pages.

Create a new ShowImage.ashx Generic Handler

• Open your ASP.Net Web Site and go to File menu > New >Check Generic Handler.

• Rename the generic handler with any appropriate name. In this case it is  ShowImage.
• Once click on add, the following structured file will be shown.

Note : Handler is  an interface that include a function called “ProcessRequest” which will be used to execute the code that has been placed within it.

• In Handler, add System.Data.OracleClient namespace

using System.Data.OracleClient; //Oracle client to can Work with Oracle Database

• In “ProcessRequest”  function Add the following code to retrieve a BLOB field from OracleDB.

public void ProcessRequest(HttpContext context)
 { 
string Account;
 if (context.Request.QueryString["Account"] != null) 
{ 
 Account = context.Request.QueryString["Account"].ToString();
 OracleConnection con = new OracleConnection(OracleConn);
 con.Open();
 OracleCommand com = con.CreateCommand();
 com.CommandText = "(SELECT pi.image img  FROM fnd_user fu,per_images pi WHERE 
fu.user_name = '" + Account.ToUpper() + "'   AND fu.employee_id = pi.parent_id)";
 OracleDataReader img = com.ExecuteReader(); 
  try { 
       img.Read();
       context.Response.BinaryWrite((byte[])img[img.GetOrdinal("img")]);
       img.Close(); 
      }
 catch { } finally { con.Close(); } } }

• Finally, Make sure that <img> tag source parameters are provided correctly .
• Go back to run the Web Application that should now be working properly.

Enjoy 🙂

See also

• Download and install Oracle Client.
• Configure Oracle Connection string and tnsnames.ora file.