I am publishing this project as Open Source in the hope that others find it useful and use it widely; however, I do not warrant this code for any purpose at all, as noted in the license agreement. You may use this project in your own Lightswitch applications.

Native Language Support is not provided. This would require building a localized version, which is clearly achievable, but not done on this version of the Data Source Extension.

This project works with one FileTable per database. It automatically discovers and targets the first FileTable listed in the Database pointed to in the SQL Server connection string. If you have more than one FileTable, you will need to make sure that the one you want to work with is listed first. See the Initialize method in SqlFileTableDataSource for the exact query used to find the active FileTable.

This project does not yet support Semantic Search. It would be fairly easy to add that in.

You MUST present valid CONTAINS T-SQL syntax when performing a full text query. The result is going to be a CONTAINSTABLE ordered by RANK in the search results (highest rank first). I recommend building a simple little UI to build this query text for your users, vice allowing them to enter it directly. While SQL injection will be prevented - the Data Source Extension will still throw an exception if the presented query language is invalid in the context of a CONTAINS clause. Refer to MSDN documentation for CONTAINS syntax here:
Anything that would be valid inside the <containssearchcondition> may be passed into the Containing parameter, and will be run against the file_stream column of the FileTable. For example, you could pass the following:
  • Term
searches for Term in the full text of the files in the FileTable
  • Term1 AND Term2
searches for both Term1 and Term2
  • NEAR(Term1, "Term2 Term3", 5)
searches for the exact phrase Term2 Term3 within five non-search terms of Term1
  • And so on... It's pretty powerful stuff. Read up on it if you need to.

Also, this project is not designed nor optimized for dealing with very large files and very large collections of files all at once. The methods used in this project are meant to expose one folder, or one set of search results, at a time. These methods also don't typically allow the consuming application to request the actual file stream for more than one file at a time (you can but it's not the default and is not recommended). This should all work, and work pretty well, for individual files up to about 100 MB, but bigger than that, we would have to make some changes to both transport and methods to handle that. This project implements no changes that would be necessary to support larger files or bulk file transfers.

Note that Microsoft provides best practices and sample code in T-SQL for dealing bulk loading and bulk transfers into and out of FileTables. The simplest way to deal with that would be to attach to the Share that represents the FileTable and drag files into and out of that File Share (which is managed by the SQL Server instance itself). As such I didn't think that bulk transfers were an important goal.

Very large files would require changes beyond the initial intended scope of this project, and probably demanding a different type of application than Lightswitch (though, if you're interested, contact me and make your case).

Last edited May 21, 2014 at 4:46 PM by DonYeske, version 1