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 tutorial 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.
Now that the FILESTREAM is enabled for the server you need to configure your database.
The basic steps include:
1. Create a Filegroup of type Filestream
2. Create a File for the new Filestream Group
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.
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.
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); }