Retrieve files from Oracle 10g using Asp.net

In this post “Retrieve files from Oracle 10g using Asp.net”, we will see how to retrieve Document, Excel and PDF files saved in Oracle database, in Byte format using Asp.Net.  

Refer the post Save files to Oracle 10g using Asp.net, to see on how to save the documents to the database using Asp.Net

Add a button to the .aspx page as follows

<asp:Button ID="cmdDownload"

runat="server"

Text="Download"

onclick="cmdDownload_Click" / >

Once the button is added move to the code-behind file to write the download logic as follows.

protected void cmdDownload_Click(object sender, EventArgs e)

{

    DownloadDocument();

}

//

private void DownloadDocument()

{

    // Retrieve Details from the DB

    string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

    objConn = new OracleConnection(strConn);

    objCmd = new OracleCommand("SELECT DOCUMENT_NAME, DOCUMENT_TYPE, DOCUMENT From BUSINESS_REQUIREMENTS_DOCUMENT Where DOCUMENT_ID = 3", objConn);

    //

    objConn.Open();

    OracleDataReader dr = objCmd.ExecuteReader();

    dr.Read();

    strFileName = dr.GetString(0);

    strFileExtension = dr.GetString(1);

    Byte[] byteDoc = new Byte[(dr.GetBytes(2, 0, null, 0, int.MaxValue))];

    dr.GetBytes(2, 0, byteDoc, 0, byteDoc.Length);

    dr.Close();

    objConn.Close();

    //

    Response.Clear();

    Response.Buffer = true;

    if (strFileExtension.ToUpper() == ".DOC" || strFileExtension.ToUpper() == ".DOCX")

    {

       Response.ContentType = "application/vnd.ms-word";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    else if (strFileExtension.ToUpper() == ".XLS" || strFileExtension.ToUpper() == ".XLSX")

    {

       Response.ContentType = "application/vnd.ms-excel";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    else if (strFileExtension.ToUpper() == ".PDF")

    {

       Response.ContentType = "application/pdf";

       Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + "." + strFileExtension);

    }

    //

    Response.Charset = "";

    Response.Cache.SetCacheability(HttpCacheability.NoCache);

    Response.BinaryWrite(byteDoc);

    Response.End();

}

Run the application click on the Download button, the file will be retrieved from the Database, converted to the actual format and allows the user to save the file.

That’s it we have Retrieved a file stored in Oracle 10g using Asp.net

RELATED POST

Save files to Oracle 10g using Asp.net

Retrieve files from Oracle 10g using Asp.net

ORA-01460: unimplemented or unreasonable conversion requested

Save files to Oracle 10g using Asp.net

In this post Save files to Oracle using Asp.net, we will see how to save Document, Excel and PDF files into Oracle database from an Asp.Net form.  

Uploading files is a common activity in Asp.net application, in general we upload the files and save them to the file system and store the path of the saved file in the database, but there are specific scenarios which demand us to store the entire file in the database in binary format, we shall see how this can be achieved using Asp.Net

First create a table to store the Documents uploaded, add a column with type BLOB, this column will be used to store the content of the uploaded file in binary format.

Column Name	DataType
PROJECT_ID	NUMBER
DOCUMENT_ID	NUMBER
DOCUMENT_NAME	VARCHAR2(100)
DOCUMENT_TYPE	VARCHAR2(10)
DOCUMENT	BLOB

Next create an Asp.net application and add a new .aspx file to the application.
In the .aspx file add a FileUpload control as follows

<asp:FileUpload ID="fileUploadDocument" runat="server"/> 

<asp:Button ID="cmdUpload" runat="server" Text="Upload"

    onclick="cmdUpload_Click" /><br /><br />

<asp:Label ID="lblMsg" runat="server"ForeColor="Red">asp:Label>

Once the controls are added move to the code-behind file to write the upload logic as follows.

protected void cmdUpload_Click(object sender, EventArgs e)

{

    try

    {

        if (fileUploadDocument.PostedFile.ContentLength > 0)

        {

            // Get the File name and Extension

            strFileName = Path.GetFileName(fileUploadDocument.PostedFile.FileName);

            strFileExtension = Path.GetExtension(fileUploadDocument.PostedFile.FileName);

            //

            // Extract the content of the Document into a Byte array

            int intlength = fileUploadDocument.PostedFile.ContentLength;

            Byte[] byteData = new Byte[intlength];

            fileUploadDocument.PostedFile.InputStream.Read(byteData, 0, intlength);

            //

            // Save the file to the DB

            string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();

            objConn = new OracleConnection(strConn);

            //

            strQuery = "INSERT INTO DOCUMENTS(DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TYPE, DOCUMENT) VALUES (";

            strQuery += "'1', ";

            strQuery += "'" + strFileName + "', ";

            strQuery += "'" + strFileExtension + "', ";

            strQuery += " :Document)";

            //

            OracleParameter blobParameter = new OracleParameter();

            blobParameter.ParameterName = "Document";

            blobParameter.OracleType = OracleType.Blob;                   

            blobParameter.Direction = ParameterDirection.Input;

            blobParameter.Value = byteData;

            objCmd = new OracleCommand(strQuery, objConn);

            objCmd.Parameters.Add(blobParameter);

            //

            objConn.Open();

            objCmd.ExecuteNonQuery();

            objConn.Close();

            //

            lblMsg.Text = "Document Uploaded Succesfully" ;

        }

    }

    catch (Exception ex)

    {

        lblMsg.Text = " Error uploading Document: " + ex.Message.ToString();

    }

}

Run the application, browse the required file and click on the Upload button, the file will be conterted into BYTEs and will be stored in the ORACLE database.

That’s it we have Stored a file into ORACLE database using Asp.net

Refer the post Retrieve files from Oracle 10g using Asp.net ,to see on how to retrieve the saved files from the Oracle database.

RELATED POST

Save files to Oracle 10g using Asp.net

Retrieve files from Oracle 10g using Asp.net

ORA-01460: unimplemented or unreasonable conversion requested