Udostępnij za pośrednictwem

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

  • Artykuł

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.

image

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

image

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.

    image

  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.

    image

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…”

    image

  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.

    image

  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.

    image

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

image

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.

image

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!

Comments

  • Anonymous
    October 29, 2012
    Beth, 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? Thanks, Joe

  • Anonymous
    May 09, 2013
    Hello Beth, 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..

  • Anonymous
    June 13, 2013
    Hi Beth, 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:UserscmcguiganDocumentsVisual Studio 2012ProjectsProject Issue TrackingMyProjectbinData Where am I going wrong? Cheers Chris

  • Anonymous
    June 14, 2013
    @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).

  • Anonymous
    July 05, 2013
    The comment has been removed

  • Anonymous
    July 26, 2013
    @Joao - We enabled this feature in VS2013. See: blogs.msdn.com/.../adding-stored-procs-to-your-lightswitch-intrinsic-database.aspx

  • Anonymous
    April 22, 2014
    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.

  • Anonymous
    September 15, 2014
    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.

  • Anonymous
    September 18, 2014
    Lightswitch 2012 project failed to publish when using SQL Server EXPRESS 2012 when I Publish the Database Schema Hi again. As long as I stay with sql servwe 2008 r2 , there is no problem publishing. But try to use the same vs 2012 with sql server 2012 express and I get the following error when I when I Publish the Database Schema. Of course, without publishing the schema, it fails on the login scrren. THIS WORKS WELL WITH sql server 2008. An exception occurred when deploying the d. atabase for the application. Errors occurred while modeling the target database.  Deployment can not continue. error is in: C:Program Files (x86)MSBuildMicrosoftVisualStudioLightSwitchv3.0Publishv1.8Microsoft.LightSwitch.Publish.targets My guess is there is incompatible versions of code running together, so here is the platform a) vs 2012 regular b) sql server 2012 express (sp2) c) WPI inststalled d) LightSwitch for Visual Studio 2012 - Server Configuration with local SQL. (note: there is the run time for 2012 on machine too)