Asked by:
How to open and view PDF from BLOB field of a oracle database

Question
-
User238037111 posted
Hello
I am fetching data from a oracle using asp.net/C# in aspgrid view succesfuly. Now I want to open pdf file which is stored in a field called "Content" in seprate window/aspx page by clicking on HyperLink field. How can I do this<%@ Page Language="C#" CodeFile="GridviewwithFiltering.cs" Inherits="GridviewwithFiltering" AutoEventWireup="true" EnableSessionState="False"%> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head profile="http://gmpg.org/xfn/11"> <link rel="stylesheet" type="text/css" href="gridview.css" media="all" /> </head> <body> <form id="form1" runat="server"> <asp:ScriptManager ID="ScriptManager" runat="server" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server"> <ContentTemplate> <h3>Res </h3> <asp:Label ID="litlist" visible=false runat="server"></asp:Label> <div class="GridviewDiv"> <table style="width: 540px" border="0" cellpadding="0" cellspacing="1" class="GridviewTable"> <tr > <td style="width: 240px;" > CUST No <asp:Label runat="server" visible="false" ID="lblVariable" /> </td> <td style="width: 100px;"> Transaction Date </td> <td style="width: 100px;"> Year </td> <td style="width: 100px;"> Month </td> </tr> <tr > <td style="width: 240px;"> </td> <td style="width: 100px;"> </td> <td style="width: 100px;"> <asp:DropDownList ID="ddlYear" DataSourceID="dsPopulateYear" AutoPostBack="true" DataValueField="Year" runat="server" Width="85px" Font-Size="11px" AppendDataBoundItems="true"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> </td> <td style="width: 100px;"> <asp:DropDownList ID="ddlMonth" DataSourceID="dsPopulateMonth" AutoPostBack="true" DataValueField="Month" runat="server" Width="85px" Font-Size="11px" AppendDataBoundItems="true"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> </td> </tr> <tr> <td colspan="4"> <asp:GridView ID="Gridview1" runat="server" AutoGenerateColumns="False" AllowPaging="True" AllowSorting="true" DataSourceID="dsGridview" Width="540px" PageSize="10" PagerSettings-Mode=NumericFirstLast CssClass="Gridview"> <Columns> <asp:Hyperlinkfield DataNavigateUrlFields="Attachment_Name" DataTextField="CUST_NO" HeaderText="Sort" SortExpression="CUST_NO" ItemStyle-Width="240px" /> <asp:BoundField DataField="trans_date" HeaderText="Sort" SortExpression="trans_date" ItemStyle-Width="100px" HtmlEncode="False" DataFormatString = "{0:d}" ItemStyle-HorizontalAlign="Center"/> <asp:BoundField DataField="Year" HeaderText="Sort" SortExpression="Year" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center" /> <asp:BoundField DataField="Month" HeaderText="Sort" SortExpression="Month" ItemStyle-Width="100px" ItemStyle-HorizontalAlign="Center"/> </Columns> </asp:GridView> </td> </tr> </table> </div> <asp:SqlDataSource ID="dsGridview" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand="SELECT CUST_no,trans_date,year, month,ACCT_code, attachment_name FROM trans_view where length(year)>0 and length(month)>0 " FilterExpression="Convert(Year, 'System.String') like '{0}%' and Convert(Month, 'System.String') like '{1}%' and Convert(ACCT_Code,'System.String') like '{2}%'"> <FilterParameters> <asp:ControlParameter Name="Year" ControlID="ddlYear" PropertyName="SelectedValue" /> <asp:ControlParameter Name="Month" ControlID="ddlMonth" PropertyName="SelectedValue" /> <asp:ControlParameter Name="ACCT_Code" ControlID="lblVariable" PropertyName="Text" /> </FilterParameters> </asp:SqlDataSource> <asp:SqlDataSource ID="dsPopulateYear" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT Year from trans_view where length(year)>0 order by year"> </asp:SqlDataSource> <asp:SqlDataSource ID="dsPopulateMonth" runat="server" ConnectionString="<%$ ConnectionStrings:OracleConnectionString %>" ProviderName="<%$ ConnectionStrings:OracleConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT Month FROM trans_view where length(month)>0 order by month"> </asp:SqlDataSource> </div> </ContentTemplate> </asp:UpdatePanel> </form> </body> </html>
Friday, October 9, 2009 11:58 AM
All replies
-
User220959680 posted
Now I want to open pdf file which is stored in a field called "Content" in seprate window/aspx page by clicking on HyperLink field. How can I do thisAs you requested here is the source code to read BLOB type from Oracle db.
//ODP.NET is Oracle native data provider, so improves data access performance //Namespace for ODP.Net classes using Oracle.DataAccess.Client; //Namespace for ODP.Net Types using Oracle.DataAccess.Types; using System.Runtime.Serialization; using System.Runtime.Serialization.Formatters.Binary; using System.IO; public partial class SerializationPage : System.Web.UI.Page { /// <summary> /// Reading blob Type from Database example /// http://msdn.microsoft.com/en-us/library/cydxhzhz(VS.80).aspx /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Button1_Click(object sender, EventArgs e) { string connString = ConfigurationManager.ConnectionStrings[Your ConnString key].ConnectionString; //Connection object OracleConnection conn = new OracleConnection(oradb); //Open connection conn.Open(); //Command OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT * FROM YourTable"; //Error Handler try { //OracleDataReader object OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); //Get your blob type column which is Index 4 OracleBlob blob = dr.GetOracleBlob(4); //Test Response.Write(blob.Value); } catch( Exception ex ) { throw new Exception("error Reading data from blog Types Table", ex); } //Close connection conn.Dispose(); } /// <summary> /// on button click event to insert values into BLOB Type column /// </summary> /// <param name="sender"></param> /// <param name="e"></param> // Transaction Lock with Blobs example ///http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/rlock/index.html /// Serilization Example: /// http://progtutorials.tripod.com/C_Sharp.htm#_Toc65227888 protected void button2_Click(object sender, EventArgs e) { //Connection string string oradb = Read from web.config //Connection OracleConnection conn = new OracleConnection(oradb); //Open connection conn.Open(); // Start a transaction OracleTransaction txn = conn.BeginTransaction(); //create Command object OracleCommand cmd = new OracleCommand(); cmd.Connection = conn; cmd.CommandText = "SELECT object FROM your Table WHERE <condition> FOR UPDATE "; //Error Handler try { //Reader OracleDataReader dr = cmd.ExecuteReader(); dr.Read(); //Read BLOB from DataReader OracleBlob blob = dr.GetOracleBlob(0); //byte array byte[] bytes; //get the byte array MemoryStream memStream1 = new MemoryStream(); BinaryFormatter formatter = new BinaryFormatter(); //Serializing your object formatter.Serialize(memStream1, Your object); bytes = memStream1.GetBuffer(); memStream1.Close(); //write blob object to Database blob.Write(bytes, 0, bytes.Length); //Commit data and close connection txn.Commit(); Response.Write("updated to new data"); } catch( Exception ex ) { throw new Exception("error writing session data to Databse", ex); } conn.Close(); } }
Saturday, October 10, 2009 6:15 AM -
User238037111 posted
Hello
I am using following code, it works perfectly. But If I try to implement with AJAX, it gives me following error. Any Idea how to implement with Ajax?
--- Error
A Runtime Error has occurred
Do you wish to Debug?
Line 5 Error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server couldnot be parsed. Common Cause for this error are when the response modified by calls to Response.Write(), response.filters, HttpModules, or server trace is enabled.
----------------------------------------- Code
using System; using System.Collections.Generic; using System.Text; using System.Web; using System.Drawing; using System.Data; using System.Data.SqlClient; using System.IO; using System.Data.OracleClient; using System.Configuration; using System.Collections; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; public partial class GridviewwithFiltering : System.Web.UI.Page { protected Label lblVariable; protected override void OnLoad(EventArgs e) { } } protected void Button2_Click(object sender, EventArgs e) { string connstr = read from web.config ; OracleConnection conn = new OracleConnection(connstr); conn.Open(); OracleDataAdapter sdPdfSource = new OracleDataAdapter(); ImageButton btn = (ImageButton)sender; string pdfFilename = btn.CommandArgument; sdPdfSource.SelectCommand = new OracleCommand("SELECT * FROM table where attachment_name='" + pdfFilename + "'", conn); DataSet dsPdf = new DataSet(); sdPdfSource.Fill(dsPdf); string strfn = Convert.ToString(DateTime.Now.ToFileTime()); strfn = dsPdf.Tables[0].Rows[0]["Attachment_Name"].ToString(); FileStream fs = new FileStream(strfn, FileMode.CreateNew, FileAccess.Write); //retrieving binary data of pdf from dataset to byte array byte[] blob = (byte[])dsPdf.Tables[0].Rows[0]["Content"]; HttpContext.Current.Response.ContentType = "application/pdf"; HttpContext.Current.Response.AddHeader("content-disposition", "Attachment; filename=" + pdfFilename); HttpContext.Current.Response.AddHeader("content-length", blob.Length.ToString()); HttpContext.Current.Response.BinaryWrite(blob); HttpContext.Current.Response.Flush(); HttpContext.Current.ApplicationInstance.CompleteRequest(); //instead of Response.End() HttpContext.Current.Response.End(); } }
Monday, October 12, 2009 11:06 AM