LightSwitch & Stored Procedures

Question

I've seen a few posts on running SQL Server stored procedures from within Lightswitch but what I would like to be able to do is:

1. Create a LightSwitch application (along with its own database)
2. Connect to that database from SQL Server Management Studio
3. Create one or many stored procedures w/in that database
4. Possibly schedule these stored procedures to run daily

So far, I have gotten hung up on step 2 where I'm trying to attach to the Lightswitch ApplicationDatabase.mdf. I get the error below, apparently because there is no associated log file (.ldf).

I understand that maybe it is not a best practice to be placing stored procedures in the LightSwitch generated database and appreciate input along these lines as well.

Thanks,

Keith

Error Message: "Unable to open the physical file "C:\Documents and Settings\keithm\My Documents\Visual Studio 2010\Projects\Application13\Application13\Bin\Data\Temp\ApplicationDatabase_log.LDF". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)"

Answer 1

When I add new enhanced features to a LightSwitch generated database, I deploy the database first, then I apply the new features (e.g. tables, functions, stored procedures) that I've designed. As long as I don't modify the tables that LightSwitch has generated, LightSwitch will not be affected, nor will it interfere with the modifications I've made when it updates the database. This process has been working well for me.

So, rather than trying to access the LS development DB datafile, I would suggest that you deploy your application, then add your stored procedures to the deployed database. For development purposes, you can deploy to a test environment and make a copy of the resulting database to work against.

Answer 2

Why not create your database outside of Lightswitch? I don't see any advantage to directly accessing the internal Lightswitch database. In fact it sounds like a terrible idea. It's not meant to be accessed directly.

If you do it outside of Lightswitch you can do whatever you want in the DB design, include all your tables, stored procedures etc. no problem. Then finally point Lightswitch at your external database and it will pick up the Tables and Views automatically. (NOT the stored procedures).

For scheduling things to run at set times, use SQLAgent Jobs within SQLServer (assuming its SQLServer you are using). This also is done completely outside of Lightswitch.

The restrictions of Lightswitch seem to nudge us away from stored procedures; don't fight against that. Think of Lighswitch as a very basic create/read/update/delete tool. Keep your stored procedures and scheduled jobs external as SQLAgent jobs. This approach has worked for me.