Upload/Import Download/Export a File to/from Oracle DB

What motivated me to write this post is that this is a really interesting task.

The general idea one have about databases is that they only serve the purpose of storing plain values as numbers, dates and text. A database does not do just that but even more allowing you to store a whole file as an Excel spreadsheet or an MP3 file in a column you define in your table.

To illustrate that, let’s suppose you were assigned the task of saving a spreadsheet in the database. This spreadsheet contains valuable data (as taxes values for each federal state) that’ll be used throughout your application. A user will upload that spreadsheet to the database and when someone else want to see the content of that spreadsheet it’s just a matter of clicking a download button to get it back. Pretty simple, isn’t it?

To accomplish that I’ll use Oracle database, more specifically Oracle Database 10g Express Edition (free to use).

The SQL code I used to create the database objects is as follows:

----------------------------------------------------------
-- Export file for user ORACLETEST                      --
-- Created by Leniel Macaferi on 3/13/2010, 11:43:17 PM --
----------------------------------------------------------

-- Create database tablespace
create tablespace oracletest datafile 'C:\oracletest.dbf'
size                                  20M
autoextend on maxsize                 unlimited
extent management local uniform size  64K;

-- Create user and Grant permissions
create user oracletest identified by oracletest default tablespace oracletest;
grant connect, resource to oracletest;
grant create session to oracletest;
grant unlimited tablespace to oracletest;
grant dba to oracletest;

-- Create table
create table FILES
(
  ID          NUMBER not null,
  UPLOAD_DATE DATE not null,
  CONTENT     BLOB not null,
  TYPE        VARCHAR2(64) not null,
  NAME        VARCHAR2(128) not null,
LENGTH NUMBER not null
);
alter table oracletest.FILES
  add constraint ID primary key (ID);

-- Create sequence
create sequence oracletest.SEQ_FILES
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;
-- Create import/upload procedure
create or replace procedure sp_import_file(
upload_date
in varchar2, content files.content%type, type in varchar2, name in varchar2,
length in number, id
out number) is begin select seq_files.nextval into id from dual; insert into files (id, upload_date, content, type, name, length) values (id, to_date(upload_date, 'mm/dd/yyyy hh:mi:ss pm'), content, type, name,
length
); end sp_import_file;

I’ll use an ASP.NET MVC application to show the process of uploading/importing downloading/exporting a file to/from an Oracle database.

The app has a tab called Import/Export File. Clicking on this tab takes you to a View that contains a file upload control and two buttons. When the user clicks the Import button the file will be uploaded/imported/saved to/in the database. When the user clicks the Export button the last file saved in the database will be downloaded/exported to the user’s computer.

The following is a screenshot of the application:

Oracle Import Export File ASP.NET MVC Application
Figure 1 - ASP.NET MVC Application UI

The following is a screenshot of the Files table which already has some files:

Oracle Import Export Files Table
Figure 2 - Oracle Files Table

As you can see the thing that makes it possible to import a whole file to a table is the type of the column that’ll hold the file; in this case the type is BLOB.

In the lines that follow I’ll present the source code I used to implement this sample ASP.NET MVC application.

It’s always good to view the Solution Explorer inside Visual Studio so that I can highlight the files I have created:

Oracle Import Export File Visual Studio Solution Explorer Figure 3 - Solution Explorer in Visual Studio

This is the C# code used in the upload/import process (inside OracleFileModel.cs):

/// <summary>
/// Imports any file to an Oracle database table that has a column of type BLOB.
/// </summary>
/// <param name="uploadDate">Date of upload/importation</param>
/// <param name="fileName">File name</param>
/// <param name="filePath">File path on the server</param>
/// <param name="fileType">File type</param>
/// <param name="fileSize">File size in bytes</param> /// <returns>Id of Row just inserted</returns> public static int Import(string uploadDate, string fileName, string filePath, string fileType, int fileSize) { OracleConnection connection = GetOracleConnection(); connection.Open(); OracleTransaction transaction; transaction = connection.BeginTransaction(); OracleCommand cmd; cmd = connection.CreateCommand(); cmd.Transaction = transaction; // Creates a temporary blob object on the database. This object will store the file content. cmd.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"; cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Blob)).Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); // Getting the content of the file... byte[] buffer = GetFileContent(filePath); // Oracle object responsible for storing the File content. OracleLob tempLob; // Assigning tempLob the blob object created on the database. tempLob = (OracleLob)cmd.Parameters[0].Value; tempLob.BeginBatch(OracleLobOpenMode.ReadWrite); // Writing the file content to tempLob. tempLob.Write(buffer, 0, buffer.Length); tempLob.EndBatch(); cmd.Parameters.Clear(); // The name of the Procedure responsible for inserting the data in the table. cmd.CommandText = "SP_IMPORT_FILE"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new OracleParameter("upload_date", OracleType.VarChar)).Value = uploadDate; cmd.Parameters.Add(new OracleParameter("content", OracleType.Blob)).Value = tempLob; cmd.Parameters.Add(new OracleParameter("type", OracleType.VarChar)).Value = fileType; cmd.Parameters.Add(new OracleParameter("name", OracleType.VarChar)).Value = fileName;
cmd.Parameters.Add(new OracleParameter("length", OracleType.Number)).Value = fileSize; cmd.Parameters.Add(new OracleParameter("id", OracleType.Number)).Direction = ParameterDirection.Output; try { cmd.ExecuteNonQuery(); } catch (Exception ex) { transaction.Rollback(); throw new Exception(ex.Message); } transaction.Commit(); connection.Close(); // Returning the Id of the row just inserted in table FILES. // This Id could be used to associate the file inserted with another table, for example, // if you had to parse the content of a spreadsheet and save each line in other table. return int.Parse(cmd.Parameters[4].Value.ToString()); }
This is the C# code used in the download/export process (inside OracleFileModel.cs)::
/// <summary>
/// Exports the last file imported to an Oracle database table that has a column of type BLOB.
/// </summary>
/// <returns>File</returns>
public static MyFileModel Export()
{
    MyFileModel file = new MyFileModel();

    OracleConnection connection = GetOracleConnection();
    connection.Open();

    OracleCommand cmd;
    cmd = connection.CreateCommand();

    // Gets the last File imported.
    cmd.CommandText = "select content, name, type from (select content, name, type from files f order by f.upload_date desc) where rownum = 1";

    byte[] fileContent;

    try
    {
        OracleDataReader reader = cmd.ExecuteReader();

        using (reader)
        {
            reader.Read();

            if (!reader.HasRows)
            {
                return file;
            }

            OracleLob blob = reader.GetOracleLob(0);
            OracleString fileName = reader.GetOracleString(1);
            OracleString fileType = reader.GetOracleString(2);

            fileContent = new byte[blob.Length];

            blob.Read(fileContent, 0, (int)blob.Length);

            file.Content = fileContent;
            file.Name = fileName.Value;
            file.Type = fileType.Value;
        }
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }

    connection.Close();

    return file;
}
/// <summary>
/// Gets an Oracle connection to the database.
/// </summary>
/// <returns>OracleConnection</returns>
private static OracleConnection GetOracleConnection()
{
    string user = ConfigurationManager.AppSettings["user"];
    string password = ConfigurationManager.AppSettings["password"];
    string dataSource = ConfigurationManager.AppSettings["dataSource"];

   return new OracleConnection("Data Source=" + dataSource + ";User Id=" + user + ";Password=" + password + ";");
}

/// <summary>
/// Get the content of the file in byte array buffer.
/// </summary>
/// <param name="path">File path on the server</param>
/// <returns>File content</returns>
private static byte[] GetFileContent(string path)
{
    Stream fs = File.OpenRead(path);
    
    byte[] buffer = new byte[fs.Length];

    int q = fs.Read(buffer, 0, Convert.ToInt32(fs.Length));

    fs.Close();

    return buffer;
}

This is the code implemented for the OracleFileController:

public class OracleFileController : Controller
{
    //
    // GET: /OracleFile/

    public ActionResult OracleFile(FormCollection formCollection)
    {
// Handles the button that posted to the server...
// OracleFile.aspx has 2 submit buttons.


if (formCollection["Import"] != null) { Import(); } else if (formCollection["Export"] != null) { return Export(); } return View(); } /// <summary> /// Imports a file from the user computer to the database. /// </summary> /// <returns></returns> private void Import() { foreach (string fileName in Request.Files) {
// Gets the file the user selected. HttpPostedFileBase file = Request.Files[fileName];
if (file.ContentLength > 0) {
// Getting the name of the folder in which the file will be saved on the server. string saveFolder = ConfigurationManager.AppSettings["saveFolder"];
                // Creating the path for the file on the server.

string filePath = Path.Combine(Request.ServerVariables["APPL_PHYSICAL_PATH"] + saveFolder, Path.GetFileName(file.FileName));
                // Saving a copy of the user's posted file on the server.
file.SaveAs(filePath);
                // Save the file in the database.
                // Although id isn't being used, this shows you how to get the id of the file just inserted in the database.
                int id = OracleFileModel.Import(DateTime.Now.ToString(), file.FileName, filePath, file.ContentType, file.ContentLength);
                // Deleting the file just imported so that the server disk does not get full.
                System.IO.File.Delete(filePath);

                TempData["message"] = "File imported with sucess.";
            }
        }
    }

    /// <summary>
    /// Exports a file from the database to the user's computer.
    /// </summary>
    /// <returns></returns>
    public ActionResult Export()
    {
        // Get the file from the database.
        MyFileModel file = OracleFileModel.Export();

        if (file.Content != null)
        {
            TempData["message"] = "File exported with success.";

            // Return the file to the user's computer.
            return File(file.Content, file.Type, file.Name);
        }
        else
        {
            TempData["message"] = "There's nothing to download.";

            return View("OracleFile");
        }
    }
}

This is a really big post full of code. I think the code has sufficient comments that should shed some light on how to accomplish the task.

Important note
All properties read in the form of ConfigurationManager.AppSettings["property"]; including the database name and password are stored in the Web.config file under the appSettings section.

<appSettings>
   <add key="user" value="oracletest"/>
   <add key="password" value="oracletest"/>
   <add key="dataSource" value="localhost"/>
   <add key="saveFolder" value="Files"/>
</appSettings>

Hope you make good use of it.

Visual Studio 2010 C# ASP.NET MVC Application
You can get the Microsoft Visual Studio Project at:

http://sites.google.com/site/leniel/blog/OracleImportExportFile.zip

To try out the code you can use the free Microsoft Visual Studio 2010 Professional Release Candidate (ISO file) that you can get at: http://www.microsoft.com/downloads/details.aspx?FamilyID=f9c0b89b-4964-4906-94c6-60ad8a429690&displaylang=en

Although I’ve used the 2010 version of Visual Studio I think the code presented in this post works just fine with previous versions of Visual Studio and ASP.NET MVC framework. You shouldn’t have problems while copying/pasting the code in previous versions. Maybe you’ll have to change DLL references or something like that.