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:
Figure 1 - ASP.NET MVC Application UI
The following is a screenshot of the Files table which already has some files:
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:
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.
Table files is linked to table exams through a foreign key. I disabled this foreign key constraint in my local database to avoid conflicts/errors during the import process. Beware because conflicts do appear most of the time. Conflicts are generally related to key violation/duplication.
… but that’s the price you must pay when using free tools.
