Blog

Recent Articles

Storing Files in SQL Server using WCF RIA Services and Silverlight - Part 1

Posted on Jun 07, 2011 | Chris Rouw
We have worked on several Silverlight Line of Business applications that require storing documents and files in a secure environment. There are several ways to accomplish this but one approach that has been successful for us is to store the documents using FILESTREAM Storage in SQL Server 2008. This is the first of three articles which will describe how you can create a Silverlight LOB application that stores and displays documents using FILESTREAM Storage in SQL Server 2008. 1. Configuring FILESTREAM in your database and WCF RIA Services setup. 2. Uploading and Saving files to the database from a Silverlight LOB application. 3. Viewing files stored in the FILESTREAM from a Silverlight LOB application. Configuring FILESTREAM in you database The first thing I would recommend is to read about FILESTREAM. Here is a white paper which describes FILESTREAM. Okay, now that you read the entire white paper we are ready to roll! Setting up your database Your database needs to enable FILESTREAM on the instance of the SQL Server Database Engine. Here is an article which describes this process. Now that the FILESTREAM is enabled for the server you need to configure your database. Here is an article that walks you through this process. The basic steps include: 1. Create a Filegroup of type FILESTREAM [caption id="attachment_537" align="alignnone" width="300" caption="Configuring the FILESTREAM group"]Configuring the FILESTREAM group[/caption] 2. Create a File for the new FILESTREAM group [caption id="attachment_535" align="alignnone" width="300" caption="Configuring the FILESTREAM file"]Configuring the FILESTREAM file[/caption] Now that your  database can handle FILESTREAM, the next is to create the SQL Tables that will store documents using the FILESTREAM.  In this example I will be using three tables:
  • File - storage for the document via the FILESTREAM
  • Document - metadata about the File
  • Folder - Virtual folder for the document
File table script
CREATE TABLE [dbo].[File]( [FileID] [int] IDENTITY(1,1) NOT NULL, [DocumentFileId] [uniqueidentifier] ROWGUIDCOL NOT NULL, [DocumentFile] [varbinary](max) FILESTREAM NULL, CONSTRAINT [File_PK] PRIMARY KEY CLUSTERED ( [FileID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1], UNIQUE NONCLUSTERED ( [DocumentFileId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
  One thing we have found is that you only want to access the File table when you are ready to display the document. If you include this table in RIA Service Domain Service it will really slow things down - a lot. That is why we separated the metadata from the FILESTREAM into two tables - File and Document. Document table. You can add as many columns for metadata as needed for your project. [caption id="attachment_534" align="alignnone" width="300" caption="Document Table - used to store metadata about the document"]Document Table[/caption] A few things to notice:
  1. This table contains a description and some metadata about the file. We use this table to bind a list of documents in a treeview or gridview control.
  2. The guid field is used to create a second unique field. More to come on this in part 3.
  3. Path will store the actual file name (e.g. MyDocument.pdf). We need this field so we can determine the type of file that is stored in the database (more on this in part 3).
  4. FolderID points to a Folder table (see below). We use this table to organize documents in Folders.
Folder table. We use the ParentFolderID to enable nested folders. [caption id="attachment_536" align="alignnone" width="295" caption="Stores the location of the document"]Folder table[/caption] Okay, now our database is configured for FILESTREAM and we have the necessary tables to store documents. We are creating a Silverlight LOB application using WCF RIA Services, so assuming we already have our Silverlight project created our next steps will be:
  1. Add/Update Entity Framework Entity Data Model (*.edmx) in the project. Include the File, Document, and Folder tables.
  2. Add/Update Domain Service class and metadata for the three tables.
Tip - I like to include two methods when returning a Document. One that includes the File (i.e. Heavy version) and one that does not include the File (i.e. Lightweight version). This gives me flexibility on the client side.
public Document GetDocumentById(int documentId) { return this.ObjectContext.Documents.Where(d => d.DocumentID == documentId).FirstOrDefault(); } public Document GetDocumentWithFileById(int documentId) { return this.ObjectContext.Documents.Include("File") .Where(d => d.DocumentID == documentId).FirstOrDefault(); }
  To get a list of documents for a folder I use the following query. This can be bound to a gridview control. public IQueryable GetDocumentsByFolderId(int folderId) { return this.ObjectContext.Folders .Include("Document") .Where(f => f.FolderID == folderId).OrderByDescending(com => com.Document.CreatedDate); } With these changes in place your server side should be ready to handle the storage of documents, which we will discuss in part 2. Resources:  

Related posts