Accessing Other (non-FileTable) Database Content

Jul 2, 2014 at 5:39 PM
I have a sql server database with contains some file table... I would like to create a lightswitch application to manage some table(not the file table). I'm getting an error while creating a datasource through the wizard and I know it is because my database contains filetable. I add your extensions and I'm able to see the file... But the thing is I want to work with the other table(not the file table) how can I access those table?
Jul 2, 2014 at 10:52 PM

This extension will allow you to work with the FileTable just fine, as you said. Are you able to separate the other tables, by design, into another database? Or, conversely, are you able to remove the FileTable from the database you're talking about, that contains the other tables, and keep just the FileTable elsewhere?

Any relationships that involve the FileTable would also not be usable in Lightswitch for the same reason, which is actually the HierarchyID fields, not the FileTable itself. It's the Primary Key of the FileTable that is not understood by Lightswitch at this time. Frankly, I think that Microsoft isn't motivated to fix that problem. While I've tried to use later versions of Entity Framework that understand HierarchyID fields, I have not actually gotten that to work with Lightswitch.

But not all is lost. The stream_id field of the FileTable contains a Guid. It's guaranteed to be unique and not to change, regardless of file location (this is NOT true of the HierarchyID fields). If you can keep the FileTable in a separate database from the metadata, you can still associate between metadata and FileTable using the stream_id of the FileTable. You can even query across both data sources and use the stream_id as a key field, in Lightswitch, because my extension treats it as a key field (whereas the actual FileTable does not).

In practice:
  • Put your FileTable in one database
  • Put everything else in a different database
  • If you need an association between a file in one database and metadata in a separate database, you can create that association in your own query using the stream_id field as the unique constraint
Is this possible for you? It's what I'm doing.

Jul 2, 2014 at 11:37 PM

If you CANNOT separate the FileTable from the other tables, here is another approach that you can try. I've just tested it on my development machine and it does work.
  • Configure your SQL Server for mixed mode (SQL + Integrated) authentication
  • Create a new login under your SQL Server instance
  • Create a corresponding user in the database that contains BOTH the FileTable and the other tables
  • Associate the login (Instance-level) with the database user (database-level)
  • Explicitly deny that login that you just created permission to View definition on the FileTable
  • In your Lightswitch project, use the wizard to attach to a new data source
  • Choose Database
  • Connect using the login information that you just created
In effect, this will allow you to connect to the database that contains the FileTable, using Lightswitch, and not even be aware of the existence of the FileTable.

You can still use this data source extension to access the FileTable content - but you must connect using different login information (or using Integrated security) in order to read the FileTable.

Again, I just tested this approach and it works. Try it.
Marked as answer by nguemo on 7/7/2014 at 8:01 AM
Jul 3, 2014 at 12:59 AM
Thanks DonYeske

I will give it a try.

Thanks again for your help
Jul 7, 2014 at 4:01 PM
Hey DonYeske...

It works for me thanks a lot for this work around