Tips for Using Northwind with Visual Studio Express

The C# team's Charlie Calvert posted "Connecting to Northwind" on February 16, 2007. The post contains instructions for enabling programmers new to Visual Studio 2005 Express editions to connect to the Northwind sample database. Note: Charlie's post is intended to assist readers of his "LINQ Farm: Connecting to a Database with LINQ to SQL" to create a local Northwind database SQL Server Express instance for demonstrating LINQ to SQL queries. The post is for beginners, so here are a few additions and clarifications that might aid new users:

1. Viewing Northwind in SQL Server Management Studio. "SQL Server Management Studio ships as part of SQL Server and SQL Server Express." Technically, SQL Server Management Studio Express (SSMSX) ships as a part of SQL Server 2005 Express Edition with Advanced Services SP1. Alternatively, you can download SSMSX separately or as a component of Microsoft SQL Server 2005 Express Edition Toolkit SP1.

2. Vista Problems. SQL Server Express (SSX) SP1 creates a login for members of the BUILTIN\Administrators and BUILTIN\Users groups. Administrators have SysAdmin (sa, or system administrator) permissions; Users have no permissions. Under Vista's User Account Control (UAC) feature, ordinary users can log in to SSX, but can't do anything else. The SQL Server Express Weblog's "Getting things working on Vista" post explains how to add a new login with SysAdmin privileges.

SSX SP2 will correct this problem; you can download the December 2006 CTP here. According to the post, Microsoft Update will automatically install SSX SP2 at RTM.

Tip: If you're running Vista, download SQL Server Management Studio Express Service Pack 2 - Community Technology Preview (CTP) December 2006.

3. Under Figure 3. Visual Studio Express editions are restricted to User Instances by default. A User Instance attaches an SQL Server database file (Northwind.mdf) as a database (Northwind) when you open a project that uses the database and detaches it when you close the project. This feature enables XCopy distribution of your project by releasing SSX's lock on the database file when it's not open in your project.

When you first connect to a database file, you are offered the option to create a copy of the file in the folder with your project's .exe file. Unless you have a good reason not to do this, create the copy in the default file location.

4. Under Figure 5. Another option is "If you are using a remote instance of SQL Server Express, type the name of the server followed by \SQLEXPRESS: MyServer\SQLEXPRESS. If the remote server is a named instance of SQL Server, substitute the instance name for SQLEXPRESS."

5. Connecting to a pre-attached database. If you want to connect a VS Express Edition project to the database you've attached to SSX with SSMSX instead of attaching and detaching the file as a User Instance, you must change the default connection string or write your own. Creating a starter User Instance connection string and then modifying it is the easiest approach.

Here's a typical User Instance connection string in the project's app.config file:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; AttachDbFilename=C:\Path\Northwind.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient" />

Open the project's app.config file from Solution Explorer in the VS XML editor and change the connection string to the following:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=.\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

Note: You can't open the database of the preceding instance in VS Express editions' Database Explorer. Use SSMSX SP1 or SP2 CTP instead.

6. Connecting to a remote SQL Server or SSX instance. By default VS Express editions can't connect to databases of remote SQL Server or SSX instances. However, you can modify the project's connection string to connect to the remote instance:

<add name="Test.My.MySettings.NorthwindConnectionString" connectionString="Data Source=SERVERNAME\SQLEXPRESS; Database=Northwind;Integrated Security=True;Connect Timeout=30" providerName="System.Data.SqlClient" />

You must use SSMSX SP1 or SP2 CTP instead of Server Explorer in this case, too.

Note: Mary Jo Foley says SQL Server 2005 SP2 RTM should be available for download on Monday, February 19, 2007. However, that's Presidents Day, so it might be delayed until Tuesday.

Update 2/19/2007: Mary Jo was right (as usual). See SQL Server 2005 SP2 Released to Web.

Technorati tags: Visual Studio Express, Visual Basic Express, VB Express, Visual C# Express, C# Express, Visual Web Developer Express, Web Developer Express, SQL Server Express, SQL Server 2005, SQL Server