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