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

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

Configuring the FILESTREAM group

Configuring the FILESTREAM group

2. Create a File for the new FILESTREAM group

Configuring the FILESTREAM file

Configuring the FILESTREAM file

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.

Document Table

Document Table - used to store metadata about the document

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.

Folder table

Stores the location of the document

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:

 

About Chris Rouw

Chris is a runner, which might explain why he has so much more energy than the rest of us. This guy is a lean, mean coding machine who works really hard to make our clients happy. He succeeds, too. Not only that, but he can sing any Weird Al Yankovic song on demand. Try him!
This entry was posted in Silverlight, Silverlight - Technical and tagged , , , . Bookmark the permalink.

15 Responses to Storing Files in SQL Server using WCF RIA Services and Silverlight – Part 1

  1. Prasad Kulkarni says:

    waiting for part 2

  2. Dave says:

    Great Article awaiting for your next Part 2 and 3 Cheers

  3. Pingback: Storing Files in SQL Server using WCF RIA Services and Silverlight – Part 2 | Far Reach Blog

  4. Pingback: silverlight filestream « Jack's Code

  5. Pingback: Latest links – Sql Filestream, WCF RIA, local storage, Silverlight drag and drop external files « Jack's Code

  6. Pingback: Storing Files in SQL Server using WCF RIA Services and Silverlight – Part 3 | Far Reach Blog

  7. Pingback: Storing Files in SQL Server using WCF RIA Services and Silverlight – Wrap up | Far Reach Blog

  8. Elliott Chaves says:

    Great post!

    Can you post the scripts to generate the other tables!

    Best Regards!

    Elliott

    • Chris says:

      Elliott – to which other tables are you referring?

      • Elliott Chaves says:

        Folder and document table!

        • Chris says:

          Elliott – here you go. Note that you will need to create FK from Document to Folder and FK from Document to File. Hope this helps!

          CREATE TABLE [dbo].[Folder](
          [FolderID] [int] IDENTITY(1,1) NOT NULL,
          [ParentFolderID] [int] NULL,
          [FolderName] [nvarchar](100) NOT NULL,
          [Description] [nvarchar](200) NULL,
          [IsPrivate] [bit] NOT NULL,
          [IsShared] [bit] NOT NULL,
          [IsSystemFolder] [bit] NOT NULL,
          [IsDeleted] [bit] NOT NULL,
          [Active] [bit] NOT NULL,
          [DateAdded] [datetime] NOT NULL,
          [UpdatedBy] [nvarchar](50) NOT NULL,
          [UpdateDate] [datetime] NOT NULL,
          [CreatedBy] [nvarchar](50) NOT NULL,
          CONSTRAINT [PK_Folder] PRIMARY KEY CLUSTERED
          (
          [FolderID] ASC
          )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
          ) ON [PRIMARY]

          GO

          CREATE TABLE [dbo].[Document](
          [DocumentID] [int] IDENTITY(1,1) NOT NULL,
          [FileID] [int] NOT NULL,
          [FolderID] [int] NULL,
          [Description] [nvarchar](50) NOT NULL,
          [guid] [varchar](50) NULL,
          [IsDeleted] [bit] NOT NULL,
          [Active] [bit] NOT NULL,
          [DateAdded] [datetime] NOT NULL,
          [CreatedByUser] [nvarchar](50) NOT NULL,
          [UpdatedBy] [nvarchar](50) NOT NULL,
          [UpdateDate] [datetime] NOT NULL,
          [Path] [nvarchar](255) NULL,
          [FileExtension] [nvarchar](5) NULL,
          CONSTRAINT [PK_Document] PRIMARY KEY CLUSTERED
          (
          [DocumentID] ASC
          )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
          ) ON [PRIMARY]

          GO

Leave a Reply

Your email address will not be published. Required fields are marked *

*


+ 1 = two

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>