.NET Musings

I’m working on some presentation samples, and I need a database.  This code will be distributable, so it’s a perfect excuse to use the latest version of SQL Server Express (more information here). It’s perfect for applications (in this case demo code) that needs a database, but also needs to be portable.

I *always* forget the syntax of the connection string for Express, so here’s the process that I end up going through each time:

• View Server Explorer (View –> Server Explorer) in Visual Studio (if the database file already exists)
  • If it doesn’t exist, I create it through SQL Server Management Studio and not Visual Studio (just because it gives more control)
• Create a new Data Connection in Server Explorer (if it doesn’t already exist)
  • On the Add Connection Dialog:
    • Change the Data Source from “Microsoft SQL Server (SqlClient)” to “Microsoft SQL Server Database File”
    • Enter the Database Name in the format <drive>:\Directory\Foo.mdf
    • Leave the Authentication method as “Use Windows Authentication”
    • On the Advanced Tag, change Data Source to the correct Express Instance (if you have more than one installed)
• Click on the Connection and hit “F4” (View Properties Window)
• Copy and paste the connection string. It should look something like this:
  • Data Source=.\EXP_2008_R2;AttachDbFilename=E:\Directory\Foo.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Or, you can just type it in if you remember the syntax for Express (I don’t use it enough to have it committed to memory).

Data Source=[Express Instance];AttachDbFilename=[Path and File Name of mdf Database File];Integrated Security=true;User Instance=true;

In the Path, you can use the shortcut “|DataDirectory|” which maps to the Path.GetDirectory(Assembly.GetExecutingAssembly.Location). In human speak, this is the directory where the application is executing.

The Connect Timeout is optional, but will save you thrash time when something goes wrong if you just leave it in there.  Otherwise, you might be sitting watching a debug window for a long time before control returns to the IDE.

The User Instance parameter specifies a User Instance instead of a Server Instance (no big surprise here). For code and data that is to be run in isolation, that is definitely what want.  MSDN has a pretty good writeup of the details for user instances here.

Last, do NOT specify an initial catalog if you are running a User Instance (see my next blog post for why).

Happy Coding!