locked
Extract data from Lightswitch database to integrate with existing database (SQL Server 2012) RRS feed

  • Question

  • I have a customer survey lightswitch app which uses a standalone customer list as the basis for my team to call.  Once the call is completed I can see all of the survey responses etc etc.  However, I require the data to be able to be integrated with all of the rest of my customer data, profile and related information.  Can anyone offer a solution of how to match the data between the two databases please?

    I had hoped to have the lightswitch as a pretty front end, and then use stored procedures inside SQL Server to extract the relevant information to bring across into our data warehouse, but unfortunately I cannot seem to get the lightswitch database to be "attached" inside SQL Server as well as being live for data to be inserted. 

    Is there a way to be connected to the lightswitch .mdf from both ends - i.e. the App front end and management studio at the other end to extract the data and integrate it into our other databases please?

    Or is it a matter of attaching and detatching nightly to get the data out once everyone has gone home? Or is there a better way of getting meaningful data out of Lightswitch itself?

    Thanks.

    Tuesday, August 18, 2015 4:26 PM

Answers

  • All you should have to do is publish the Lightswitch App to a database on a server where you have sufficient privileges to either create a new database and its tables, or alter the schema of an existing one. Once published, the app's tables should be accessible just like any other table via SQL Server Management Studio.

    If you're trying to use SSMS to look at the local copy of the tables that Visual Studio creates when you're building the app on your PC, that's possible, but it's a bit trickier to do.  The local MDF file is created in your project's bin/Data folder.  I have forgotten the particulars of how to access it via your SQL Express instance, but that should be available from a short Bing search.

    • Proposed as answer by Otis Ranger Wednesday, August 19, 2015 1:03 PM
    • Marked as answer by sven texas Thursday, October 22, 2015 7:50 AM
    Tuesday, August 18, 2015 9:31 PM

All replies

  • All you should have to do is publish the Lightswitch App to a database on a server where you have sufficient privileges to either create a new database and its tables, or alter the schema of an existing one. Once published, the app's tables should be accessible just like any other table via SQL Server Management Studio.

    If you're trying to use SSMS to look at the local copy of the tables that Visual Studio creates when you're building the app on your PC, that's possible, but it's a bit trickier to do.  The local MDF file is created in your project's bin/Data folder.  I have forgotten the particulars of how to access it via your SQL Express instance, but that should be available from a short Bing search.

    • Proposed as answer by Otis Ranger Wednesday, August 19, 2015 1:03 PM
    • Marked as answer by sven texas Thursday, October 22, 2015 7:50 AM
    Tuesday, August 18, 2015 9:31 PM
  • This should help:

    How to: Connect to Data

    1) Create your LightSwsitch Application

    2) Connect the LightSwitch application to your external data

    3) Make LightSwitch Screens that show the external data

    4) Publish the LightSwitch application


    Unleash the Power - Get the LightSwitch HTML Client / SharePoint book

    http://LightSwitchHelpWebsite.com

    Wednesday, August 19, 2015 2:40 AM
  • Thankyou RT for the help - that has answered my question.  With this being my first lightswitch app, I was concerned that the database wasn't going to be so easy to interrogate, but now it should be plain sailing!

    Thanks, Sven.

    Wednesday, August 19, 2015 12:43 PM
  • Thankyou Michael, I am looking to extract the survey data to put it into an existing cube - so its more about it going the other way than being displayed in lightswitch, as we have SSRS reports on the back of the cube that need to use this data alongside data from an external survey provider too.

    Thanks for taking the time to answer.

    I had tried to amend your WCF_RIA survey (download) but couldn't quite get it to work as we need as we have many questions, some with free text, and some with differing predetermined answers (I am sure it was my lack of skills not a reflection on the download!).

    Thanks again.

    Wednesday, August 19, 2015 12:49 PM