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:
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>This is the C# code used in the download/export process (inside OracleFileModel.cs)::
/// <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()); }
/// <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.
