UXFileUpload: Store Uploaded Files in Database
Handling client files which are uploaded to a server is a common requirement for application. Files can be uploaded either to the FileSystem of your server or directly to the database as BLOB.
However, there has been a lot of debate about the best way to store the files. I’m sure there are many people who have strong favor for why one option is better than the other and vice versa.
So there must be interesting differences between the two solutions. Let’s have a brief comparison between these two options.
FileSystem – Pros and Cons
Storing the files on disk is much easier and simple to implement. Having the files in the filesystem allow accesing it from many different standard applications such FTP, web browser etc. and users as well, independent of access to the database itself etc.
Another advantage is that files on disk are easy to backup; you just copy the files to another location. This also makes it easier to do incremental backups; files that have already been backed up don’t need to be copied again.
Probably the most problematic issue is the loosely coupled nature of the files on disk as they have no strong relation with a record in the database. So, when you delete, say, a customer from the database, you may end up with an orphaned customer’s image/photo. There is no direct way to do a JOIN between the customer table and your images folder to determine what orphaned files you have left.
Nevertheless, to store uploaded files on disk, your web server needs permissions to write to the file system. This is easy to overcome by when you run your own server, but may prove to be more problematic in an ISP scenario.
Database – Pros and Cons
The loosely coupled nature of the files on disk problem will not happen when the uploaded files are stored in database. They have strong relation with a record in the database. It will automatically gets deleted when record of an employee is terminated.
Another advantage of storing files in a database is the fact that all data is contained in a single location. Make a backup of your database and you’re ready to go. No need to copy files, set up permission and so on.
Performance is probably one of the disadvantages of storing files in a database. Storing files in a database somehow degrading the performance as putting binary data into the database has obviously some overhead.
Another cons is that whenever you make a full backup of your database, all the files are included, whether they have been changed or not. If you copy your backups to a different machine or network for safety reasons, you need to move the entire backup file. With a file based solution, you can use diff programs that can determine which files have been changed since the last backup, and only download those.
Realizing that both methods have their pros and their cons, the question is: what to choose? Personally, due to its easier and simpler to be implemented, I’d prefer to store the uploaded files on FileSystem.
However, in this occasion, I will not discuss further on the pros and cons of the file storage matter. I’m going to present the solution on how to store the uploaded files of UXFileUpload ClientUI control to database.
UXFileUpload Introduction
UXFileUpload is a feature-rich file upload control supporting Silverlight 3, Silverlight 4, and WPF. It includes all standard features you expected in a file upload control, plus a multitude of innovative features that unique to UXFileUpload such as multiple upload worker process, comprehensive MVVM and commanding support, smart file chunk algorithm, very large file upload support, file-level cancellation, drag-and-drop files from operating system, and more.
Despite of the large number of features, UXFileUpload is designed for an ultimate ease-of-use. The simplest UXFileUpload control can be defined with only two properties set, the ServiceUrl and the TargetFolder property. You set the ServiceUrl property to a value that determines the absolute web address where the server-side handler is configured to accept the file upload requests. The TargetFolder determines where the files should be stored in the server.
ClientUI includes a built-in ASP.NET server-side handler that you can use to accept the file upload requests from the UXFileUpload control. When using the built-in server-side handler, you can set the TargetFolder to a relative path in your web server, for an instance, ~/Upload.
Creating a Simple UXFileUpload Control
The following code shows the most basic configuration of a UXFileUpload control.
|
1 2 |
<Intersoft:UXFileUpload ServiceUrl="http://localhost:9041/UXFileUploadHandler.ashx" TargetFolder="~/ClientBin/Assets/Documents" /> |
You need to register the server-side upload handler in your ASP.NET web project in order for the UXFileUpload control to work properly. For more information configuring the server-side handler for the upload control, see How-to: Configure ASP.NET Server-side Handler for UXFileUpload.
When viewing the control in either design or runtime, you will find the results similar to the illustration in the following:
With the upload control shown above, users can start to add files immediately by clicking the Add Files command in the toolbar and click on the Start Upload command to start uploading the selected files.
Once the Start Upload command is invoked, the file upload request will be handled by UXFileUploadHandler.ashx server-side upload handler. The uploaded files will be stored inside the Upload folder of the server.
In order to store the uploaded files to database instead of keep them in file system, we need to prepare following items.
- Add and configure database which will be used as file storage.
- Add and configure custom UXFileUpload handler.
Adding and Configuring Database
A database, called Files.mdf, is added into the App_Data folder of the web project. This database has Files table which consist of following fields.
| Column Name | Data Type | Allow Nulls |
|---|---|---|
| Id | uniqueidentifier | False |
| FileData | varbinary(MAX) | True |
| OriginalName | nvarchar(50) | False |
| DateCreated | datetime | False |
A Stored Procedure, sprocFilesInsertSingleItem, will be used to insert a single item of file into Files.mdf database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
INSERT INTO Files ( Id, FileUrl, FileData, OriginalName ) VALUES ( @id, @FileUrl, @FileData, @originalName ) |
Adding and Configuring Custom UXFileUpload Handler
UXFileUpload implements a smart file chunk logic where multiple files can fit into a single upload request thus minimizing the client-server requests. With the smart file chunk logic, UXFileUpload allows you to upload very large files without have to worry about the performance and memory consumption in both client and server side. It is not necessary to change the maximum upload request length or other configuration in the server-side to accommodate the large files upload using the UXFileUpload control.
The built-in UXFileUpload server handler is designed with a great level of customizability, allowing you to inherit the UXFileUploadHandler class and override the properties as necessary. We can also customize the upload post processing logic, by overriding the provided methods.
We will create a custom UXFileUpload handler that will store the uploaded files into the database by overriding the OnUploadCompleted method. Within this method, we can find all the information we need, such as: the number of uploaded files; the file path of uploaded files; saved name of the uploaded files; etc. In OnUploadCompleted we have files that has been completely uploaded. This is important to ensure that there is no file problem such as corrupted files or incomplete files during the saving of the files to the database.
Generally, the custom handler will process each of uploaded files and do the following:
- Read and manipulate the file using FileStream class.
- Invoke sprocFilesInsertSingleItem stored procedure to store the file on Files.mdf database.
- Delete the specified file from the location specified in TargetFolder property of UXFileUpload.
Open the UXFileUpload project and add a class library project, named as ExtendedUXFileUploadHandler.
Add the following code into the DatabaseFileUploadHandler.cs file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
public class DatabaseFileUploadHandler : UXFileUploadHandler { protected override void OnUploadCompleted(List<FileUploadInfoResponse> uploadedFiles) { if (uploadedFiles.Count > 0) { int FilesNumber = uploadedFiles.Count; for (int i = 0; i < FilesNumber; i++) { byte[] fileData = ReadFile(uploadedFiles[i].TargetFilePath); string originalName = uploadedFiles[i].SavedName; using (SqlConnection mySqlConnection = new SqlConnection( @"Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Files.mdf;<br> Integrated Security=True;Connect Timeout=30;User Instance=True")) { // Set up the Command object SqlCommand myCommand = new SqlCommand("sprocFilesInsertSingleItem", mySqlConnection); myCommand.CommandType = CommandType.StoredProcedure; // Set up the ID parameter SqlParameter prmId = new SqlParameter("@id", SqlDbType.UniqueIdentifier); prmId.Value = uploadedFiles[i].ID; myCommand.Parameters.Add(prmId); // Set up the FileData parameter SqlParameter prmFileData = new SqlParameter("@fileData ", SqlDbType.VarBinary); prmFileData.Value = fileData; prmFileData.Size = fileData.Length; myCommand.Parameters.Add(prmFileData); // Set up the OriginalName parameter SqlParameter prmOriginalName = new SqlParameter("@originalName", SqlDbType.NVarChar, 50); prmOriginalName.Value = uploadedFiles[i].SavedName; myCommand.Parameters.Add(prmOriginalName); // Execute the command, and clean up. mySqlConnection.Open(); bool result = myCommand.ExecuteNonQuery() > 0; mySqlConnection.Close(); } File.Delete(uploadedFiles[i].TargetFilePath); } } base.OnUploadCompleted(uploadedFiles); } private static byte[] ReadFile(string filePath) { byte[] buffer; FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read); try { int length = (int)fileStream.Length; // get file length buffer = new byte[length]; // create buffer int count; // actual number of bytes read int sum = 0; // total number of bytes read // read until Read method returns 0 (end of the stream has been reached) while ((count = fileStream.Read(buffer, sum, length - sum)) > 0) { sum += count; // sum is a buffer offset for next reading } } finally { fileStream.Close(); } return buffer; } } |
The ReadFile function will reads the source file into a byte array. The Read method of FileStream object returns zero only after reaching the end of the stream. Otherwise, Read always reads at least one byte from the stream before returning. Finally, close the current stream and releases any resources associated with the current stream when Read method returns zero.
After completing the read process and set up the required parameter, fileData byte array will then be inserted into Files table by invoking the sprocFilesInsertSingleItem stored procedure.
Finally, delete the uploaded file from the specified path after execute the stored procedure.
Before running the solution, build the project and add the reference of ExtendedUXFileUploadHandler.dll into the web project. Then re-define the handler in Web.Config file into the following.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<configuration> ... <system.web> <compilation debug="true" targetFramework="4.0" /> <httpHandlers> <add verb="*" path="UXFileUploadHandler.ashx"<br> type="ExtendedUXFileUploadHandler.DatabaseFileUploadHandler, ExtendedUXFileUploadHandler"/> </httpHandlers> </system.web> <system.webServer> <handlers> <add name="UXFileUpload" verb="*" path="UXFileUploadHandler.ashx"<br> type="ExtendedUXFileUploadHandler.DatabaseFileUploadHandler, ExtendedUXFileUploadHandler" /> </handlers> </system.webServer> ... </configuration> |
Save all the changes and run the project.
Add files to be uploaded and click Start Upload. After the uploading process completed, the uploaded files is no longer available in the TargetFolder location. The file is now stored in database.
In this post, we have learnt how to store uploaded files of UXFileUpload to database by overriding the UXFileUpload handler.
Click here to download the sample project and feel free to drop me a line in the comment box if you find that this post useful, or have any questions and feedback regarding this topic.
Regards,
Yudi Ariawan
Hi yudhiy;
You stated the Pros & Cons of each storage system. Well MSFT solved this problem SQL2008 FileStream Storage system where you can have best of both worlds. I think your article should be adopting this new system and the users get advantages of both systems. Have a look:
http://msdn.microsoft.com/en-us/library/cc949109%28v=sql.100%29.aspx
Hope this helps!
..Ben
What’s up to all, the contents existing at this site are in fact amazing for people knowledge, well, keep up the nice work fellows.
You ought to be a part of a contest for one of the most useful blogs online.
I most certainly will highly recommend this website!
Your style is so unique compared to other people
I have read stuff from. Many thanks for posting when you’ve got the opportunity, Guess I will just book mark this blog.