Project Description

Integrates SQL Server 2012 and later FileTable functionality with Lightswitch rapid application development tools. Works with ALL types of Lightswitch clients.


What is Lightswitch?

Lightswitch is a rapid application development (RAD) suite of tools and features integrated into Microsoft Visual Studio. It makes quick work of simple, line-of-business applications, and does a lot of the heavy lifting for you, when it comes to UI design and basics of implementation. It's a good set of tools. However, it has its limitations, and one of them has been its inability to work directly with FileTables. MSDN documentation on Lightswitch can be found here:

What is a FileTable?

A FileTable is a feature of Microsoft SQL Server (at a minimum Express with Advanced Services) since SQL Server 2012. This feature builds on the FileStream feature introduced in SQL Server 2008, and provides a feature-rich alternative to storing files in the file system and accessing files in a file system from a Web-based application. Without getting into the details, FileTables are really cool; they allow compression and encryption of files, as well as non-transactional (traditional drag, drop, save, open) access to files, and at the same time, provide better secure the files (vice storage directly in the file system) and advanced features such as full text search. Official documentation on this feature can be accessed here:

Note - FileTables are a feature of the free as in beer version of SQL Server.

What's the problem?

The problem, up to this point, has been that there was no really good way to make these two good things work together. This project provides a working method of combining Lightswitch projects with SQL Server FileTables - something that I've been unable to locate elsewhere, and that, I'm pretty sure, others have wanted as well for a long time (since pretty much any modern business application has to deal with files, and SQL Server provides a terrific way to do that).

Others have demonstrated how to upload and download files using other methods with Lightswitch. However, all other methods I've seen only work when the Lightswitch client application is published as a Browser application. When it's published as a Desktop application (out-of-browser), they don't work (for various reasons). The difference between a browser-based app and a desktop app is substantial; for example, the application is in a whole different and much more limited security context as a browser application; it can't launch other programs or work with locally-attached devices easily in that context. Part of the purpose of this project is to get around that limitation. Even with that, the other methods known to allow file upload and download with Lightswitch don't connect to FileTables in SQL Server. They could be modified to do so, but even if they were, the Desktop mode would still not be supported by those other alternative methods.

Also, while Lightswitch continues to provide a terrific way to build rich Internet applications targeting both Silverlight and HTML5/CSS3 clients, Silverlight 5 appears to be the last gasp of breath left for the Silverlight platform. Accordingly the way that one builds services for these kinds of applications - WCF RIA services - was removed from Visual Studio a while back. So, even building a service to do anything for Lightswitch (which is in part a Silverlight Rich Internet Application) has become inordinately complex.

Finally, we've become far too reliant on add-on tools such as Entity Framework for dealing with our data. Lightswitch was built on top of data access technologies that (at least in the versions used for Lightswitch) do not understand HierarchyId columns (which are central to the FileTable schema). This is why Lightswitch bombs miserably when trying to attach directly to a data source that is a database containing a FileTable - the underlying framework doesn't understand the Primary Key field, and therefore the data cannot be imported or worked with easily. One cannot simply attach to a database containing a FileTable in a Lightswitch project and expect this to work.

What's in this project?

Parts and Pieces

The Solution published here consists of the following projects:
  • FileStore - an SQL Server project containing a FileTable (may target SQL Server 2012 or SQL Server 2014 as long as it's an edition that has FileTable features enabled)
  • SqlFileTableExtension - a solution (several projects) containing a C# Lightswitch Data Source Extension exposing the WCF RIA services implementation necessary to connect a Lightswitch application to a database containing a FileTable; in particular this project expresses metadata structures that Lightswitch can consume, and connects to the SQL Server database containing the FileTable that Lightswitch cannot consume

What's not here?

In the future I will consider putting up a sample Lightswitch app demonstrating how these work. Not doing this now due to time constraints.

Last edited May 25, 2014 at 7:35 PM by DonYeske, version 13