Adding Stored Procs to your LightSwitch Intrinsic Database

Adding Stored Procs to your LightSwitch Intrinsic Database

  • Comments 5

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.

image 

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…

image

 

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

image

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.

image

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.

image

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.

image

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

Leave a Comment
  • Please add 7 and 7 and type the answer here:
  • Post
  • 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?

  • @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?

  • Thanks Beth

    This is a great improvement in Lightswitch.

  • 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.

  • Hi Chad,

    Sorry for the trouble. Thanks for brining this to our attention, we've logged the issue. There are a couple work-arounds -

    1) Fix the SQL script to no longer have a warning (this is what you did).

    2) 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

Page 1 of 1 (5 items)