Share via

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

  • Article

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!

Comments

  • Anonymous
    October 30, 2011
    Wouldn't publishing the app and using that DB server's diagram functionality be a much cleaner approach?

  • Anonymous
    October 31, 2011
    Hi Holger, You can do that, but you may want the diagram of the database in development, not production, and that's what this shows how to do. Cheers, -B

  • Anonymous
    November 02, 2011
    Using SQL Server Express i can also create Database Diagrams. What features or facility does Light Switch gives over SQL Server Express?

  • Anonymous
    November 04, 2011
    Hi Beth Very good explanation as always. Few times I explained how to connect to intrinsic database on LightSwitch Forum, and now with this blog post, I would just point to this article if needed in the future. A little suggestion to the article. It would be very useful to add a part with changing the database owner to a local account with SQL Server Management Studio. It would be for those who already have installed it and then your article would have a whole story. Thanks for your contribution Spaso Lazarevic

  • Anonymous
    December 16, 2011
    BETH YOU ARE THE BEST WOMEN OF THE WORD I LOVE YOU

  • Anonymous
    September 24, 2012
    Beth, There is no “Database Diagrams” node on VS 2012. How can I diagram the database? Thank you in advance

  • Anonymous
    April 07, 2013
    I have the same problem: There is no “Database Diagrams” node on VS 2012. How can I diagram the database? This is a fresh install of VS2012 and VS2012 Update 2 ...no native SQL Server installation.

  • Anonymous
    April 07, 2013
    I have the same problem: There is no “Database Diagrams” node on VS 2012. How can I diagram the database? This is a fresh install of VS2012 and VS2012 Update 2 ...no native SQL Server installation.

  • Anonymous
    July 06, 2013
    Thank you my friend Your post was useful for me .

  • Anonymous
    September 04, 2013
    Hola Beth: I have to thank you so much for this and others explanations... forgive my english, I'm just learning. One thing, I can't find the DataBase Diagram on VS2012... can you tell me what I have to do? Gracias!!! Cuidate mucho...

  • Anonymous
    October 16, 2013
    Hi Beth I am also having same problem as some of the other people here. There is no “Database Diagrams” node on VS 2012 Thanks in advance

  • Anonymous
    December 14, 2013
    not working in my pc after cheking 100 times

  • Anonymous
    January 06, 2014
    Thank you

  • Anonymous
    March 19, 2014
    Plzzzzzzzzz help Database Diagram in vs 2012???????????????????

  • Anonymous
    November 02, 2014
    Found this on another thread: Posted by Microsoft on 4/5/2012 at 7:01 PM Thank you for your feedback. This is a by design change in the product. Diagrams are no longer supported in the new version of the SQL database, so the node is removed when you work with a new SQL server. Posted by MS-Moderator09 [Feedback Moderator] on 4/4/2012 at 10:53 PM Thank you for submitting feedback on Visual Studio and .NET Framework. Your issue has been routed to the appropriate VS development team for investigation. We will contact you if we require any additional information.

  • Anonymous
    November 21, 2014
    no matter what I've tried, when I try to add a connection, i just get a message that it couldn't connect. I am the only user on my machine and I am the admin. Any idea what else we can try?