Monday, September 7, 2009

Upload file to database with ASP.NET MVC and Castle Active Record

A file upload function to web application raises the question where to store the uploaded data. One possibility is to just store into the file system. This is inexpensive and easy to implement, but you have to think for file backups. If you don’t have huge amount of data you can also upload files directly into a database using binary format for table column to store the content of the file.

In this post I’m going to give a try of the second option and create simple application to store uploaded files directly into the database.

I will use Postgres database to store the file uploads and as ORM I will use Active Record from the Castle project. Then I need a driver for .NET to connect to the database, we will use the Npgsql driver.

With Castle Active Record you can easy switch to another database server; it’s just matter of switching the referenced driver and replace appropriately the configuration into Web.config file.

Assume we have PostgreSQL Server on localhost up and running and we will connect to the database "postgres" as user "postgres".




Create the project and set the configuration



We start with creating new ASP.MVC Application in Visual Studio and give it the name of "UploadToDb".

We then add references to the following assemblies from our project:

NHibernate.dll
Castle.ActiveRecord.dll
Castle.Components.Validator.dll
Castle.Core.dll
Castle.DynamicProxy.dll
Iesi.Collections.dll
Npgsql.dll

We change the Web.config file and add the appropriate settings for Active Record.

In "configSections" we add new section sub element for the Active Record. Next to "configSections" we add "activerecord" element where we specify the connection parameters and the database driver we are going to use.




<configuration>
<configSections>
<section name="activerecord" type="Castle.ActiveRecord.Framework.Config.ActiveRecordSectionHandler, Castle.ActiveRecord" />
….
</configSections>
<activerecord isWeb="false" isDebug="true">
<config>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="hibernate.dialect" value="NHibernate.Dialect.PostgreSQL81Dialect"/>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.NpgsqlDriver"/>
<add key="hibernate.connection.connection_string" value="Server=localhost;Port=5432;Database=postgres;User ID=postgres;Password=;"/>
</config>
</activerecord>







Database table and domain model



First we'll create Active Record class for the domain object "Upload" into Models directory. This is an entity object and has property Id which is a primary key into the database. We’ve got also data property which holds the binary data from the file we upload. There is also mimetype property which holds the MIME type of the uploaded file.




using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework.Scopes;
using NHibernate.Expression;
using System.Data;
using System.Collections;

namespace UploadToDb.Models
{
[ActiveRecord("uploads")]
public class Upload : ActiveRecordBase<Upload>
{
private int id = 0;
[PrimaryKey(PrimaryKeyType.Sequence, SequenceName = "uploads_seq")]
public int Id
{
get { return id; }
set { id = value; }
}

private byte[] data = new byte[] { };
[Property]
public byte[] Data
{
get { return data; }
set { data = value; }
}

private string mimetype = "";
[Property]
public string Mimetype
{
get { return mimetype; }
set { mimetype = value; }
}

public static Upload FindById(int id)
{
return FindOne(Expression.Eq("Id", id));
}
}
}




To search uploaded files into the database we use the above implemented method FindById(int) which is just used to enclose and wrap that extra Actie Record syntax with the expressions. Otherwise we have a lot of search capabilities coming with Active Record, for example to get list of all file uploads we use Models.Upload.FindAll() as the class Models.Upload inherits from ActiveRecordBase class.


We create table to store the uploaded files



CREATE TABLE "uploads" (
"id" int DEFAULT 0 NOT NULL,
"mimetype" Varchar NOT NULL,
"data" Bytea
);

ALTER TABLE "uploads" ADD CONSTRAINT "uploads_pk" PRIMARY KEY ("id");

CREATE SEQUENCE uploads_seq START 1;
ALTER TABLE uploads ALTER COLUMN id SET DEFAULT nextval('uploads_seq'::text);








Set up the application



At application start we will initialize Active Record. Open Global.asax.cs file and edit method Application_Start(). After RegisterRoutes we add the initialization piece of code:



protected void Application_Start()
{
RegisterRoutes(RouteTable.Routes);

// load configuration from Web.config
Castle.ActiveRecord.Framework.IConfigurationSource source =
System.Configuration.ConfigurationManager.GetSection("activerecord")
as Castle.ActiveRecord.Framework.IConfigurationSource;

// Initialize and mention which types define our model
Castle.ActiveRecord.ActiveRecordStarter.Initialize(source,
typeof(Models.Upload));
}







The UploadController class



Basically we’d need three different URLs: Add – to upload new file; Get- to get uploaded file and finally Index which gives us list of all uploads.

We’ll create UploadController class in Controllers directory. Then we implement the three methods.




using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;

namespace UploadToDb.Controllers
{
public class UploadController : Controller
{
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Add(HttpPostedFileBase file)
{
if (file != null)
{
Models.Upload upload = new UploadToDb.Models.Upload();
upload.Mimetype = file.ContentType;
upload.Data = new byte[file.ContentLength];
file.InputStream.Read(upload.Data, 0, file.ContentLength);
upload.Save();
}
return Index();
}

public FileContentResult Get(int id)
{
Models.Upload upload = Models.Upload.FindById(id);
return File(upload.Data, upload.Mimetype);
}

public ActionResult Index()
{
this.ViewData["uploads"] = Models.Upload.FindAll();
return this.View("Index");
}

}
}



Method Add() calls Index() after the file upload finished, those way we get the list of the uploads after we upload new file.


The HTML view



Created directory Views\Upload and add there view Index. I won’t select a master page for the purpose of the presentation. I also won’t use strongly-typed view, but just will thread the ViewData as a dictionary.




<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>File uploads</title>
</head>
<body>

<h3>1.List of uploads</h3>
<%
UploadToDb.Models.Upload[] uploads = (UploadToDb.Models.Upload[])this.ViewData["uploads"];
foreach (UploadToDb.Models.Upload upload in uploads.OrderBy(x=> x.Id))
{
%>
<div><%= Html.ActionLink(upload.Id.ToString(), "Get", new { upload.Id })%></div>
<%
}
%>


<h3>2.Upload new file</h3>
<% using (Html.BeginForm("Add", "Upload", FormMethod.Post, new { enctype = "multipart/form-data" }))
{ %>
Choose file: <input type="file" name="file" />
<input type="submit" />
<% } %>


</body>
</html>






Basically we have two parts here.

Part one is a list of all the uploaded files. It creates list of links to open the uploaded files via the "Get" method.

Part two is an html multipart form used to upload new file. After the file upload finished, we invoke again the Index controller method to list all the files uploaded to the database.


To upload file to database run the application and navigate the browser to ~/Upload/ directory.


I've got that screen after two files uploaded:







Enjoy!

Atanas Hristov

3 comments:

  1. hi, i would like to ask something that is related to database. why do i need some "ID" and set as primary key? is it really necessary to use it or not?

    Smith | polo shirts

    ReplyDelete
  2. ID acts as an identifying entity key in a database table. The main purpose of the ID is that it makes the table entry unique, because if set as a primary key there can only be one occurrence of the specific ID. By convention the primary key usually happens to be declared as int type and named as id. It could be something else like a string type and if set as primary key the database engine handles it as a unique entry and does not allow similar string values as a primary key. The advantage of using int typed ID's as primary keys is that incremental numerical value can be created automatically by the database engine whereas string values must be set manually. In some rare cases if the entity represents something that can easily be identified by a string value e.g. project category (where we might not want to include duplicate category names) we can simply use category name as an id.

    ReplyDelete
  3. thanks. do you happen to use jquery and ajax?

    ReplyDelete