If you have been following progress on SQL Server 2008 you have probably seen mention of FILESTREAM which is an enhancement of the varbinary(Max) data type.  As an application developer you might be asking yourself...  "So what does this mean to me?"

imageMy partner-in-crime Roger Doherty and I grabbed early builds of SQL Server 2008 (Katmai) CTP5 and Visual Studio 2008 (Orcas) from the source trees and fled to the East coast for the week to develop a demo which FILESTREAM is an important part of.  We've had a great time working into the middle of the night making some interesting discoveries.  Roger is covering some of the database level lessons learned over on his blog which I recommend you checkout

As Rog pointed out we are trying to highlight "SQL Server is the Engine that Powers Rich User Experiences".  What does that mean?  It means that SQL Server is not just a bit bucket.  It can be an integral part of high-impact applications built for the desktop and Web.  With the new FILESTREAM support SQL Server becomes a much more viable solution for storing rich media such as images and video. 

Question: Now that binary data is in the database how can I get to it and what can I do with it?

Answer: Use WPF's MediaElement to display it!

Well...  It's not quite that simple.  In order for SQL Server to maintain transaction consistency, etc. you have to use some of it's API's to get at the data stored in the FILESTREAM which I'll cover in more detail below.

Windows Presentation Foundation (WPF) in conjunction with Visual Studio 2008 and Expression Blend is an amazing combo for developing desktop and web applications.  In WPF the MediaElement tag/object can be used to display images and videos.  MediaElement requires the Source URI to be set.

XAML 
<mediaelement x:name="MyMediaPlayer" />

C#
MyMediaPlayer.Source = new System.Uri("PATH_TO_MEDIA");

In the C# code above the "PATH_TO_MEDIA" needs to be a valid URI which is where things get tricky working with FILESTREAM.  Below are a few different options.

  • Socket level application (working with bits == lots of control)
  • Custom data source for Windows Media Services (very powerful)
  • Extension to IIS Media Pack (very cool and should be done)
  • Web application (quick and easy)

There may be other options then the ones mentioned above but those are what come to mind.  They all have ups and downs including performance, control, accessibility.  Roger and I chose to write a ASP.NET application for the following reasons.

  • Experience with webapps, so it was a comfortable choice
  • Quick to implement
  • It's probably a scenario that a lot of developers will try

image

A quick look at the diagram above highlights one of the tradeoffs of this architecture.  Every time a user requests a media file it must be sent through a web server via HTTP.  This could be a benefit if offering this application as a service.  It could be a performance bottleneck if the entire application runs locally. 

Now that we have laid the architectural foundation the following articles will go into more of the implementation details.

Part 2 (Writing FILESTREAM Data)