How to Connect to and Diagram your SQL Express Database in Visual Studio LightSwitch

Visual Studio LightSwitch makes it easy to create and model database tables using the Data Designer. With this designer you have a simple interface into the Tables, Columns and Relationships that make up your data model. When you create tables this way, under the covers LightSwitch creates a SQL Express database file at design time. SQL Express is installed with Visual Studio LightSwitch and is required for it to work properly. When you are ready to deploy your application you can choose to deploy it to any version of SQL Server, including SQL Azure, however during development time the LightSwitch development environment manages your database through SQL Express. We call this the Intrinsic Database.

Many folks have asked me how I created the database diagram in the last article I wrote and I’ve seen the question in other Visual Studio forums as well. All versions of Visual Studio (not just LightSwitch) allow you to connect to SQL Server from the Server Explorer window. This allows you to browse the tables and data. However getting the diagramming support to work is somewhat of a mystery for folks so in this post I’ll show you how you can get it to work on your local development machines.

PLEASE NOTE: This technique requires you be an administrator of your machine and you installed Visual Studio (and thus SQL Express) under that administrator login. This technique should only be used for development environments.

Connecting to the Server Explorer

To connect to the intrinsic database open the Server Explorer (Ctrl+Alt+S).

image

Then right-click on the Data Connections and select “Add Connection…” to add a new connection.

image

We need to connect to a specific database file on disk, not an attached database. During development time LightSwitch creates what’s called a User Instance database that is dynamically attached to the SQL Express service when it is accessed. This makes it possible for you to copy LightSwitch development solutions and samples from machine to machine and not lose any of the critical data information.

So when the “Add Connection” dialog comes up click the “Change…” button.

image

Then change the data source to “Microsoft SQL Server Database File” and click OK.

image

Now you need to specify the file location of the database. The database file is always named ApplicationDatabase and it is located in the \bin\Data folder under your solution. Select the ApplicationDatabase.mdf file and click OK.

image

Use Windows Authentication and click the Test Connection button. If it succeeds click OK. (If it doesn’t, make sure you are logged into the machine as the same administrator you used to install Visual Studio).

image

Now you can see all the internal tables that LightSwitch has created for you based on your model in the Data Designer.

image

The Server Explorer lets you make any number of changes to not only the data stored in the tables but also the structure (schema). BE CAREFUL here. Any changes you make directly to the database structure WILL NOT automatically be reflected in your data model and you could break it. The Data Designer makes sure your changes to the model stay in sync with the database but if you modify the database outside the Data Designer all bets are off. You have been warned! :-)

Setting Up Diagramming Support

However there are other nifty things you can do in the Server Explorer like enter more records of data as well as create database diagrams. In order to create a database diagram expand the “Database Diagrams” node. You will be presented with the following dialog:

image

Click Yes and another dialog will be presented:

image

Click Yes again. If the message goes away you’re all set. However, a lot of times it’s not that easy. Usually an error message appears similar to the following:

image

In order to get diagramming support working, the owner of the database must be a local login and a valid db_owner, not a domain login like I am above. So in order to fix this we need to change the database owner to a local account. When Visual Studio installs SQL Express, the service logs on as NETWORK SERVICE. You can check this by going to Services (Windows Start, search “Services”) and looking at the SQLEXPRESS service entry.

image

So if we change the database owner to this login, the diagramming will work. Although you can do this in SQL Server Management Studio, it’s not installed out of the box with Visual Studio so I’m going to show you how to get this working without installing anything else. First go back to the bin\Data folder where your intrinsic database is located and make a copy (just in case :-)).

Then grant the NETWORK SERVICE permissions to the folder, allowing Full Control. (Right click on the \Data folder, select properties, security tab, click the Add button, enter NETWORK SERVICE, OK, then check off Full Control.)

image

Now we need to create a small SQL script to attach the database to the SQL Express service, change the owner and then detach it again. Open notepad and paste the following into a new text file, modifying the path to your database files.

 USE master
GO
-- Change the path to your database files! 
-- Make sure the login that runs your SQLEXPRESS service (i.e. NETWORK SERVICE) has file access to this folder

CREATE DATABASE ApplicationData ON 
( FILENAME = N'C:\...\Projects\MyApplication\MyApplication\bin\Data\ApplicationDatabase.mdf' ),
( FILENAME = N'C:\...\Projects\MyApplication\MyApplication\bin\Data\ApplicationDatabase_log.ldf' )
FOR ATTACH
GO

ALTER AUTHORIZATION ON DATABASE::ApplicationData TO [NT AUTHORITY\SYSTEM]
GO

USE master
GO
EXEC master.dbo.sp_detach_db @dbname = N'ApplicationData'
GO

Save the file as C:\Temp\ChangeOwner.sql – with the SQL extension. Next open an administrator command prompt.

image

First close Visual Studio in order to close all connections to the database file.Then type the following to execute the script:

sqlcmd -S .\SQLEXPRESS –i "C:\Temp\ChangeOwner.sql"

Creating a Diagram

Now that’s all done open up Visual Studio again and you should see the ApplicationDatabase still sitting in the Server Explorer. To connect just expand the database node and then right click on the Database Diagrams folder and select “Add New Diagram”. Everything should be working now.

image

When you add a new diagram it will ask you what tables you want to include. In a LightSwitch database all the tables that start with “aspnet_” as well as the “RolePermissions” are internal and related to the authentication & authorization system. They cannot be modified so you can omit these from your diagram.

image

Click Add and the diagram will be generated based on the tables you chose.

image

In order to save the diagram just close the window by clicking on the “X” on the tab and you will be prompted to save the diagram back to the database.

REMEMBER

Using the Server Explorer against your intrinsic LightSwitch database can be useful for entering data or generating diagrams but anytime you want to make changes to the actual structure/schema of your tables you MUST do that in the Data Designer otherwise you risk getting your model out of sync.

I hope this helps not only LightSwitch users but also other Visual Studio users get diagramming support working on their databases.

Enjoy!