Adding Stored Procs to your LightSwitch Intrinsic Database

Article
07/25/2013

When you build a LightSwitch application the very first question you are asked is whether you want to connect to an existing data source or if you want to create a new one. If you choose to create new, this means that you want to build the data model using the LightSwitch data designer and have LightSwitch manage the deployment of your database. We refer to this database as the Intrinsic (or Internal) database.

One of the downsides we hear from customers when using the intrinsic database is that it is difficult to manage many database related tasks – things like managing reference data, stored procedures, indexes, etc. Currently these tasks need to be done outside the LightSwitch development experience. This is why many customers choose to use external databases and manage them completely separate from LightSwitch. The downside to that, however, is that you have to deploy and manage the database schema updates yourself instead of letting LightSwitch do this for you.

In Visual Studio 2013 preview we’ve addressed this by allowing you to add a SQL Server Data Tools (SSDT) database project to your LightSwitch solution that can run database scripts against your intrinsic database. Chris introduced this feature here: Intrinsic Database Management with Database Projects and we also have created a sample & walkthrough on MSDN demonstrating how to use database projects to deploy reference data here: Managing Data in a LightSwitch Application.

You can also use database projects to add stored procs to your intrinsic database. LightSwitch entities still need to originate from the data model in the data designer, but you can use stored procs to kick off complex calculations, return data to reporting dashboards, or to perform other database-specific tasks.

Let’s see how we can add a stored proc to our intrinsic database using Visual Studio 2013 Preview.

Our LightSwitch Intrinsic Data Model

For this example I have a simple intrinsic data model of Customer –< Order –< OrderDetail >– Product. Let’s say we want to build an administrative dashboard that lets us keep tabs on the amount of data in each table in our database. We can write a stored proc to give us that information.

Adding the SSDT Database Project

There are two steps to using a database project with LightSwitch. First, you need to add the project to the LightSwitch Solution, then you need to tell LightSwitch to use it.

Right-click on your LightSwitch Solution in the Solution Explorer, then select Add –> New Project…

Select the SQL Server node and you will see the SQL Server Database Project template. Give it a name then click OK.

Next you need to hook it up to your LightSwitch project. Double-click on your LightSwitch solution properties. On the General Properties tab you will now see a setting for “SQL Database Project”. Select the project you just added from the dropdown.

Writing the Stored Procedure

Now we can add items to the database project. We want to write a stored proc that will generically go find all the tables in our database and then count all the records in each. We’ll write it so that we don’t need to change it if we add/remove tables from our schema. So right-click on the database project and select Add –> Stored Procedure. For this example I will name it uspGetTableCounts.

Now you can create your stored proc. You can use the CREATE statement here and it will re-deploy correctly – that’s the beauty of database projects. For more information on what you can do with SSDT head over to the docs: SQL Server Data Tools (SSDT) .

 CREATE PROCEDURE [dbo].[uspGetTableCounts] 
AS BEGIN 
SET NOCOUNT ON;  

-- Create the Temp Table to hold all the counts 
CREATE TABLE #TableCounts (TableName NVARCHAR(255), Count Int) 

-- Create a cursor to scroll through for selecting the COUNT(*) for each table 
DECLARE c Cursor for 
 SELECT name 
 FROM Sysobjects 
 WHERE xtype = 'u' 
 ORDER BY name 

-- Open the cursor 
OPEN c 
 DECLARE @tableName VARCHAR(255) 
 DECLARE @tSQL NVARCHAR(MAX) 


 FETCH NEXT 
 FROM c 
 INTO @tableName 


 WHILE @@Fetch_Status=0 
 BEGIN 

 SET @tSQL = 'SELECT TableName=''' + @tableName + ''', Count = COUNT(*) FROM [' + @tableName + ']'
 
 INSERT INTO #TableCounts 
 EXEC sp_executesql @statement = @tSQL 

 FETCH NEXT 
 FROM c 
 INTO @tableName 

 END

CLOSE c 
Deallocate c 

SELECT TableName, Count FROM #TableCounts 
DROP TABLE #TableCounts 

END

F5 Deployment

F5 to deploy the intrinsic database and debug your LightSwitch application. When debugging, the intrinsic database lands on your (LocalDB)\v11.0 instance. To verify that the stored proc made it into the intrinsic database, you can use the SQL Server Object Explorer in Visual Studio.

There are a plethora of things you can do with database projects in LightSwitch, this is just one common example. Keep in mind, however, that you should NOT modify the schema of any of the tables in your intrinsic database that you are using in the LightSwitch data designer, otherwise you run the risk that your model will get out of sync.

For more information on working with LocalDB and using SQL Server Object Explorer with LightSwitch see: Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 2012

Calling Stored Procs

So now that our stored proc is being deployed into our intrinsic database, how can we call it? There are a few ways you can call stored procs from LightSwitch, regardless of what version you have. Eric explained a way to do this in his older articles: Executing a Simple Stored Procedure in LightSwitch and Updating Records in LightSwitch using Stored Procedures.

However, starting in Visual Studio 2012 Update 2 (LightSwitch V3), we added the ability to use the ServerApplicationContext on the middle-tier so you can create custom web services that utilize all the business and data logic inside LightSwitch. Thus, calling a stored procedure from a custom web method became a lot more flexible. Steve shows how to do this with an external database and Web API at the end of his article: Dashboard Reports with LightSwitch, WebAPI and ServerApplicationContext– Part Deux.

In my next post, I’ll show how you can call this stored proc in the intrinsic database using Web API and display the results on a mobile device. (For more info on Web API see these Getting Started guides.)

For now, I encourage you to download and install Visual Studio 2013 Preview and let us know what you think of the new database project support in LightSwitch. Add a comment below and/or give us feedback in the LightSwitch forum. Until next time…

Enjoy!

UPDATE 7/31/2013: Read part 2 - Calling Stored Procs in your LightSwitch Databases using Web API

Comments

Anonymous
July 26, 2013
The SQL Server project type is not available from the VS 2013 preview made available around Build 2013. Are you using a newer build or are there SSDT updates available for VS 2013 preview?
Anonymous
July 26, 2013
@Eliemaz - no I'm using the public VS2013 Preview (go.microsoft.com/fwlink) It should be in Pro or higher (I'm using Ultimate). Did you customize your install?
Anonymous
August 01, 2013
Thanks Beth This is a great improvement in Lightswitch.
Anonymous
November 08, 2013
For some reason when publishing the Lightswitch HTML application when linked to SSDT if a stored procedure has a warning none of the other stored procedures or scripts will be run. Shouldn't only ERRORS prevent publishing? I resolved the warnings by adding database refererences.
Anonymous
November 12, 2013
Hi Chad, Sorry for the trouble. Thanks for brining this to our attention, we've logged the issue. There are a couple work-arounds -

Fix the SQL script to no longer have a warning (this is what you did).
You can also suppress the warning in the database project. This can be done by going to the Build tab in the database project’s properties window. That page has a field named “Suppress Transact-SQL warnings”. You can enter a comma-delimited list of warning numbers that your code has. This will cause the database project to no longer recognize those as warnings and allow the publish to succeed. Cheers, -Beth

Anonymous
February 17, 2015
Creating the stored procedure listed above results in unresolved reference to Sysobjects...It can be fixed by adding a Database Reference to the project as shown here: stackoverflow.com/.../unresolved-reference-to-object-information-schema-tables
Anonymous
April 09, 2015
I'm trying to do the relationship between the Database Project and my Lightswitch project, but the database project are not listed in the "SQL Database Project" dropdown.
Anonymous
February 26, 2016
It's very effortless to find out any topic on net as compared to books, as I found this piece of writing at this web site.
Anonymous
March 18, 2018
Very rapidly this web page will be famous amid all blogging and site-building users, due to it's nice articles

Share via