Sharing the goodness…
Beth Massi is a Senior Program Manager on the Visual Studio team at Microsoft and a community champion for business application developers. Learn more about Beth.
More videos »
Note: This article applies to LightSwitch in Visual Studio 2012 (LightSwitch V2)
In Visual Studio LightSwitch, when you design your data model through the Data Designer you are either creating new tables or attaching to external data sources. When you create new tables, LightSwitch automatically creates them in the internal database, also known as the Intrinsic database or ApplicationData. In the first version of LightSwitch in Visual Studio 2010 we used SQL 2008 Express for the internal database development. Now with LightSwitch in Visual Studio 2012 we are using SQL Server LocalDB. (Note: LocalDB is only used during development time. When you deploy your app you can choose to deploy to any version of SQL Server.)
LocalDB is the new version of SQL Server Express that has a much lower memory footprint and is targeted for developers. It is installed automatically when you install Visual Studio 11. When developing LightSwitch projects, LocalDB is used in place of the SQL Server Express user instance feature (which is now deprecated). In this post I’ll show you some tricks on working with LocalDB databases in LightSwitch projects.
In Visual Studio 2010 you used the Server Explorer to access your SQL Express databases. Server Explorer still exists in Visual Studio 2012, but there is a new window called the SQL Server Object Explorer that you can use to work with your LocalDB. SQL Server Object Explorer provides a view of your database objects that’s similar to SQL Server Management Studio.
To connect to your LocalDB click the “Add SQL Server” button on the toolbar to bring up the connection dialog. The server name is: (localdb)\v11.0
Once you debug (F5) your LightSwitch project the first time, the internal ApplicationData database will show up under the Databases node. LocalDB will auto attach to the database the first time it is accessed.
IMPORTANT NOTE: If you are developing only new tables in a LightSwitch project, you should never need to use this window because you model your data through the LightSwitch Data Designer. In fact, if you modify the schema of the internal database outside of the Data Designer, then the LightSwitch model will get out of sync and you will get errors running the application. I REPEAT, DO NOT MODIFY YOUR INTERNAL DATABASE OUTSIDE THE LIGHTSWITCH DATA DESIGNER.
However, the SQL Server Object Explorer is very handy for working with external databases that you want to bring into your LightSwitch application. During development it is common to work with a local copy of your external databases and SQL Server Object Explorer is a handy way to manage these. One of my favorite features is the schema compare. For more information on the capabilities of the SQL Server Object Explorer please see: What's New for Data Application Development in Visual Studio 11 Beta
Keep in mind that external databases are just that -- they are external to LightSwitch. So you must manage their schema and deployment completely outside of the LightSwitch development environment. For more information on connecting to external data sources see - How to: Connect to Data
With a lot of samples out there in all sorts of versions of SQL Server this feature comes in handy: you can attach to a database file (.MDF) and it will automatically upgrade your database to the current version and attach it to your LocalDB instance. This makes it easier to connect to and develop against external databases in LightSwitch. (Note that if you upgrade the database, it will no longer be compatible with earlier versions of SQL Server.)
Let’s take an example.
The AdventureWorks family of sample databases are used in many modern database examples from Microsoft today. They show off features of the latest versions of SQL Server and are maintained on CodePlex. You can download them here. There is a simpler database included here called AdventureWorksLT (AdventureWorks “Light”) that is better for developers learning data since it has a simpler schema.
To attach the AdventureworksLT database:
USE [master] GO CREATE DATABASE [AdventureWorksLT] ON ( FILENAME = N'C:\Data\AdventureWorksLT2012_Data.mdf' ) FOR ATTACH ; GO
Using the SQL query editor you can also execute other SQL scripts to create and work with your databases. However, you can also use the Server Explorer to attach to a database using a wizard which was also available in Visual Studio 2010. For more information see - How to: Connect to a File-based Database.
Once you have your databases attached, it’s easy to add them as an external data source.
The LightSwitch Data Designer will not allow you to change the underlying schema of an external data source. Instead, you can use SQL Server Object Explorer (or any other favorite database tool) to make changes. When you modify the schema of your external data source you need to bring those changes back into LightSwitch (remember do not modify your internal database this way).
To sync the changes, from the Solution Explorer right-click on the external data source and select “Update Datasource”.
Choose all the tables you are using in your LightSwitch application and then click Finish and the changes will be reflected back in the Data Designer.
SQL Server LocalDB uses less memory than previous versions of SQL Server Express and still provides the automatic attach of databases in order to speed up development of your data-based projects. For external databases, the SQL Server Object Explorer is a welcome addition to Visual Studio and has a similar experience to SQL Server Management Studio. However, remember that you should only make changes to your internal database (ApplicationData) via the LightSwitch Data Designer.
When working with the internal database in LightSwitch, LocalDB is only used during development time. When you deploy your app that uses the internal database, you can choose to deploy to any version of SQL Server, including SQL Azure. Additionally, if you are using external data sources in your LightSwitch application, you are asked for the connection strings of the production databases upon deploy. For more information on deploying LightSwitch applications see Deploying LightSwitch Applications in the MSDN Library and my post LightSwitch IIS Deployment Enhancements in Visual Studio 2012.
When working with LS2, I am using an external SQL 2008 R2 database. When I used the Update Datasource, LS2 is able to pick up the new tables added to the database. However it won't pick up the datatype changes on a few tables. The original datatype was Byte and it is changed to SmallInt in SQL. Any suggestions?
in LS 2011 we can just deploy desktop app with database included in the application. My Question is how to do the same thing? I mean, deploy VS LS 2012 desktop application with embedded database, so when I install it in another computer I don't have to create any database or create any database connection on install..
I'm not seeing the database as you describe.
Using LS2012 in VS2012 Pro RTM with SSDT 11.1.21208.
I also have SQL Server 2012 SP1 Dev running on my workstation too.
In my project, in SQL Server Object Explorer I don't see the projects database.
I have a node (localdb)\Projects (SQL Server 11.0.3000) listed but no databases in it (except the usual system ones).
I also have a node called Projects - MyProject
I see the project database files in
C:\Users\cmcguigan\Documents\Visual Studio 2012\Projects\Project Issue Tracking\MyProject\bin\Data
Where am I going wrong?
@Sam -- The deployment options are the same in VS2012. The database should be published to a database server and then the client computers install the desktop client. See: msdn.microsoft.com/.../gg274326
@Chris - You'll need to run (F5) the application before you see the database in the list. It will be under the default instance (v11.0).
My congratulations for your excelent job.
Is it possible to create stored procedures in the intrinsic db, which i will execute through code in the server?
I need to execute several automatic data insertions (not in screen) and I think the best way is to do them through stored procedures, which I triger in lightswitch for instance with a button.
@Joao - We enabled this feature in VS2013. See: blogs.msdn.com/.../adding-stored-procs-to-your-lightswitch-intrinsic-database.aspx
Thank you, Beth,
LightSwitch initially threw me. I thought at first VB might be using MySQL somehow instead of SQL Server.
Yours was the only explanation of how to use Management Studio with the LocalDB, and even came with the caveat not to modify tables created in LS. All is well.
Hi Beth, Thanks for the Article
While creating Application i have SQL Server DB in the application, later on i need to switch the DB to SQL CE DB, i was able to Update the datasource to SQL CE using Update Data Source, however the tables listed in SQL CE are SCHEMA_TABLE NAME, but the tables displayed while creating application was just TABLE NAME, so its showing as change is tables which is making me to recreate all the screens, is there any other way.