locked
How to open and view PDF from BLOB field of a oracle database RRS feed

  • 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 this
     

     

    As 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