Our previous article has covered the subject of File Upload / Download. We have discussed storing and retrieving of files in binary database table columns. Web apps created with Code On Time can greatly benefit from automatic capturing of content type, file name, and length in the specially named table columns that compliment a binary column. This approach requires no programming and works great if the database is expected to store the binary content.
Recent innovations in data storage methods make it highly efficient and cost-effective to externalize binary content from the database. The database becomes much more compact and is easier to backup and manage.
Online storage systems such as Amazon S3 and Microsoft Azure Storage provide a robust and cheap storage for large data files. A web application can also benefit if the binary content does not leave the premises of the data center and is stored in Network Attached Storage.
Creating a Sample Web App
Let’s create a sample web application to illustrate the mechanism of externalizing binary data that remains associated with table rows in a database.
Start Code On Time, select “Create new web application task”, and choose Web Site Factory type for our web application.
Enter UploadDownload in Project Name and make sure to select the programming language that you are comfortable to use. Click Create button.
Leave the namespace and framework unchanged and click Next.
In this tutorial we will be using Microsoft SQL Server 2008. Other types of supported database will involve exactly the same implementation techniques.
Select SQL Server Data Provider and click the button located next to Connection String input field.
Enter the name of your local server or SQL Azure server and specify login credentials if needed.
Enter My Upload Download Demo in Database input field and click Create. Confirm that you want to create a database by press Yes button.
Select Northwind in Sample Tables drop down and click Install button. Wait for the installation script to finish. You will see a popup message indicating a successful installation.
The next step is optional. We suggest implementing a built-in membership system in your application. Click Add button shown in the picture. Wait for ASP.NET Membership installation script to finish.
Click OK button at the bottom of connection configuration screen to save the database connection string.
Click Next twice to reach Reporting section. Select “Enable dynamic and static reports in my application”.
Click Next button to get to Features page of the project wizard. Enter Upload / Download Demo (External) in Page Header box.
Continue clicking Next until you arrive to the summary page presenting a list of project data controllers. Proceed to generate a project by clicking Next. The web app will start in your default browser shortly thereafter.
Login using admin/admin123% user account. Select Categories tab to activate the list of product categories stored in the sample database.
Next we will change the structure of the database and create a virtual binary field that will have its content stored in the file system on your computer.
Preparing the Database
The following picture shows the structure of Categories table.
Table column Picture has been automatically processed by web app generator and implemented as a file upload field. The previous screen shot shows the thumbnails of the images stored in the database.
Our intent is to implement a virtual field that will coexist alongside the field Picture but will have its content stored outside of the application database. Think of it as if there in invisible column that looks exactly as Picture but is not actually present in the database table.
Let’s call this field ExternalDoc (short for External Document). The field implementation will require a few utility columns even though the binary field itself will not be present in the database .
These special utility columns must have their name start with the name of the binary column/field. Execute the following query in SQL Management Studio or SQL Azure Database Manager against My Upload Download Demo database.
alter table dbo.Categories
add ExternalDocFileName nvarchar(150) null
go
alter table dbo.Categories
add ExternalDocLength integer null
go
alter table dbo.Categories
add ExternalDocContentType nvarchar(150) null
go
The scrip will add three utility columns to the structure of Categories table to allow capturing and storing of the uploaded file name, its length, and content type.
This is how the table structure will look now.
Implementing ExternalDoc Field in the Project
Let’s incorporate the new columns and the corresponding virtual field in our project.
Bring forward the web app generator window and click on the project name.
Our database has changed but the code generator is not aware of that. Database metadata (tables, columns, indexes, etc.) are cached in the project files. Click Refresh button to refresh the metadata. The code generator will bring you straight to Data Controller summary page.
Click Start Designer button to activate Project Designer. Select Categories data controller on All Controllers tab.
Enter CategoriesBusinessRules in Handler input and click OK button.
Select Categories data controller one more time. Activate Fields tab and select New | New Field on the action bar.
Enter ExternalDoc as Name. Change the type of the field to Byte[]. Select check box “Allow null values”.
Our field is virtual. We need to specify a SQL expression that will be evaluated by the database server when the field value is about to be retrieved. Select “The value of this field is computed at run-time by SQL expression” check box and enter NULL in SQL Formula text box.
Our field will store large binary objects (BLOB). This type of field requires special handling by the application. Select “Value is retrieved on demand” check box. On Demand Properties section will become visible in the designer.
Enter the name of the primary key column of the Categories table in the Source Fields input. The name of the column is CategoryID.
Your web application will also need a special construct that will help handling details of basic uploading/downloading just before/after you save the file to the external storage or have it retrieved.
Your application uses the term On Demand Handler to describe this construct in the source code. Any unique name will do. You can use the same on-demand handler name if a binary column is displayed in the views of other data controllers
We will call our handler CategoriesExtenalDoc, which combines the table name and the name of the virtual column. This is the default convention used for all on-demand handlers of your web application.
Set On Demand Style to Thumbnail to have a preview thumbnail displayed when the field is rendered.
Finally enter the field label as External Doc.
The project designer property page will look as follows. Click OK button to save the field.
Changing Visual Properties of ExternalDoc and Utility Fields
Select Data Fields tab to activate bindings of data controller fields to presentation views. Data fields allow controlling many presentation aspects of your application.
Let’s bind the virtual field External Doc to editForm1 and grid1 views.
The first binding will insert the field ExternalDoc in the view grid1. Select New | New Data Field on action bar. Also make sure to enter 15 in the columns property of the data field. This will limit the space taken by the field on automatically produced reports if you choose an option to print the list of categories when running the application.
Next bind our virtual field to the view editForm1. Make sure to select a category for the field. Category selection is mandatory if the view is a form.
Save the second binding and enter External in the Quick Find area on the action bar.
Delete all bindings of utility fields to the view createForm1. Uploading of binary content is only supported for existing records. Select each binding and click Delete button or select Delete option from the row context menu in the list of bindings.
Change Text Mode property of all utility fields from Text to Static. This will make the field values read-only from the user standpoint but will allow application to make changes to the values when the files are uploaded. Optionally assign n0 as a data format string for both bindings of ExternalDocLength to improve readability of the field value for very long files.
The list of ExternalDocXXX bindings will looks as follows when you finish making changes.
Click Exit button to exit the project designer and proceed to generate the web application.
Navigate to Categories tab. The screen will look similar to the screen shot.
Select any category and observe that we now have the virtual field and utility fields presented at the bottom of the form view.
Implementing Upload / Download Business Rules
If you try to upload a file in External Doc field then you will see that utility fields are acquiring the properties of the file but the actual data is no saved and an error message is displayed stating “Error: failed to upload categories external doc. Invalid column name \u0027ExternalDoc\u0027.”
You can edit the record again and this time push Clear button instead of performing an upload.
The same error message will be displayed at the top of the page but the utility fields will get cleared.
You have probably guessed already that we need to get involved in the process and save the file when the browser has finished uploading the file.
Bring up the code generator and click develop link under Actions column next to the name of your project. Visual Studio or Visual Web Developer will start.
Press Ctrl+Alt+L to activate Solution Explorer and find CategoriesBusinessRules.cs(vb) in the project tree. Double-click the file to open the text editor.
The business rules class will require three methods to support an externally stored binary content.
- The first method will need to intercept the event of uploading, prevent the application framework from trying to save the file, and persist the file contents to the file system.
We accomplish that by implementing a method with ControllerAction attribute. The parameters of the attribute indicate the data controller, the name of the action, and the name of the field.
Method arguments include CategoryID and ExternalDoc stream to let us interact with the uploaded content. You can also include optional arguments with names ExternalDocFileName, ExternalDocLength, and ExternalDocContentType if you need this information to correctly externalize the file.
Our implementation is simply storing the file in the predefined folder in My Documents. We use the category ID and generic “.bin” extension to persist the file
- The second method will be invoked when the binary content needs to be streamed to the client browser. Make sure to prevent the default processing logic from being executed by calling PreventDefault method.
Again we are using the category ID to find the location of the previously saved file and this time we write the contents of the file to the stream passed as externalDoc argument. The application framework will automatically take care of supplying the file name and content type to the browser.
- The purpose of the third method is t0 let the application know if there is a value in a given binary column of a given row.
If an external file exists then the method will update the value of the binary field with the primary key of the record. The client library will use this ID to communicate with the server components of your application and to display a thumbnail if needed.
If the binary field is empty then the method will return the same category ID converted to a string and preceded by the word null in lower case and the symbol “|”. For example, if the category ID is 7491 then the value stored in ExternalDoc will be null|7491.
We are not actually probing if the file exists in the designated folder. Instead we make an assumption that if ExternalDocFileName field value is not blank then the file has been previously uploaded without errors. Your own implementation may do whatever it takes including possible communicating with external systems to verify that the file does exist.
Note that method names are absolutely arbitrary and play no role in application execution. ControllerAction and RowBuilder attributes turn these methods in the special sauce of your business rules class.
Before you proceed any further make sure to fire up Windows Explorer. Go to Documents folder and create My External Doc Files folder referenced in the implementation below. The folder must exist by the time you try uploading the content.
C#:
using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using MyCompany.Data;
using System.IO;
namespace MyCompany.Rules
{
public partial class CategoriesBusinessRules : MyCompany.Data.BusinessRules
{
[ControllerAction("Categories", "UploadFile", "ExternalDoc")]
protected void SaveExternalDocToFileSystem(int categoryID, Stream externalDoc)
{
PreventDefault();
string fileName = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
String.Format(@"My External Doc Files\{0}.bin", categoryID));
Stream output = File.Create(fileName);
externalDoc.CopyTo(output);
output.Close();
}
[ControllerAction("Categories", "DownloadFile", "ExternalDoc")]
protected void LoadExternalDocFromFileSystem(int categoryID, Stream externalDoc)
{
PreventDefault();
string fileName = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
String.Format(@"My External Doc Files\{0}.bin", categoryID));
Stream input = File.OpenRead(fileName);
input.CopyTo(externalDoc);
input.Close();
}
[RowBuilder("Categories", RowKind.Existing)]
public void VerifyPresenceOfExternalFile()
{
int categoryID = Convert.ToInt32(SelectFieldValue("CategoryID"));
string externalDocFileName = Convert.ToString(
SelectFieldValue("ExternalDocFileName"));
// update ExternalDoc field to reflect existence of a file
if (!String.IsNullOrEmpty(externalDocFileName))
UpdateFieldValue("ExternalDoc", categoryID);
else
UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryID));
}
}
}
Visual Basic:
Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq
Imports System.IO
Namespace MyCompany.Rules
Partial Public Class CategoriesBusinessRules
Inherits MyCompany.Data.BusinessRules
<ControllerAction("Categories", "UploadFile", "ExternalDoc")> _
Protected Sub SaveExternalDocToFileSystem(ByVal categoryID As Int32, _
ByRef externalDoc As Stream)
PreventDefault()
Dim fileName As String = Path.Combine( _
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
String.Format("My External Doc Files\\{0}.bin", categoryID))
Dim output As Stream = File.Create(fileName)
externalDoc.CopyTo(output)
output.Close()
End Sub
<ControllerAction("Categories", "DownloadFile", "ExternalDoc")> _
Protected Sub LoadExternalDocFromFileSystem(ByVal categoryID As Int32, _
ByRef externalDoc As Stream)
PreventDefault()
Dim fileName As String = Path.Combine( _
Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), _
String.Format("My External Doc Files\\{0}.bin", categoryID))
Dim input As Stream = File.OpenRead(fileName)
input.CopyTo(externalDoc)
input.Close()
End Sub
<RowBuilder("Categories", RowKind.Existing)> _
Protected Sub VerifyPresenceOfExternalFile()
Dim categoryId As Integer = Convert.ToInt32(SelectFieldValue("CategoryID"))
Dim externalDocFileName As String = Convert.ToString( _
SelectFieldValue("ExternalDocFileName"))
' update ExternalDoc field to reflect existence of a file
If Not String.IsNullOrEmpty(externalDocFileName) Then
UpdateFieldValue("ExternalDoc", categoryId)
Else
UpdateFieldValue("ExternalDoc", String.Format("null|{0}", categoryId))
End If
End Sub
End Class
End Namespace
Upload a few files to the ExternalDoc column. You should be able to see the file thumbnail if the upload was successful or the file type displayed in a white box if the file is not an image.
Here is how the content of the folder that we use to store the uploaded content may look like.
Your externalized content will also print on reports. For example, select Categories tab and choose Report|PDF Document option on the action bar.
A prompt to download a PDF document will be displayed. Open the file and you should be able to see the uploaded content on it.
Conclusion
Code On Time offers an excellent mechanism of implementing storage of a binary content outside of the database.
We expect that future updates to the framework will allow file uploading not only when users are editing existing records but also when a new record is being created.
Upcoming releases will also support code-free uploading of binary content through built-in business rules to external file system and Microsoft Azure Storage.
Project Azure Factory available with Code On Time will support popular annotation feature as well.