Working with SQL Server LocalDB in LightSwitch Projects in Visual Studio 2012

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.

SQL Server Object Explorer in Visual Studio 2012

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

Attaching Databases to LocalDB using SQL Server Object Explorer

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:

1. After you download the AdventureWorksLT2012 Data File, create a folder for your local databases (like C:\Data) and copy the AdventureWorksLT2012_Data.MDF file to that location.
2. In the SQL Server Object Explorer right click on your LocalDB instance and choose “New Query…” to open a new SQL Query editor.
   
   
3. In the query editor type the following commands:

   USE [master] 
   GO 
   CREATE DATABASE [AdventureWorksLT] ON 
   ( FILENAME = N'C:\Data\AdventureWorksLT2012_Data.mdf' ) 
   FOR ATTACH ; 
   GO

4. Click the execute button on the toolbar (Ctrl+Shift+E) to execute the commands. You will see a message that there was a file activation failure and that a new log file (.LDF) was generated. You may also see some upgrade messages depending on the version of your LocalDB. All normal.
5. Refresh the Databases node in the SQL Server Object Explorer and you will now see the AdventureWorksLT database attached. You will also see any LightSwitch internal databases that you are working with.
   
   

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.

Using a LocalDB Database as an External Data Source in LightSwitch

Once you have your databases attached, it’s easy to add them as an external data source.

1. In the Solution Explorer for your LightSwitch project right-click on the Data Sources node and select “Add Data Source…”
   
   
2. This opens the Attach Data Source wizard where you can select from a variety of external data source types. Select Database and click Next.
   
   
3. In the Connection Properties dialog set the server name to (localdb)\v11.0 and then select the database you want. Note that here you can also attach to a database file as well. This will have the same effect as the attach steps above.
   
   
4. From there you can select the tables and views you want to bring into your LightSwitch application.

Syncing Schema Changes for External Data Sources

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.

Wrap Up

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.

Enjoy!