Adding databases with SSE (SQL Server Express) to Visual Studio

At the announcment for Visual Studio and SSE, they talked about the Xcopy functionality.  I should explain a bit about what this means in practical terms for Visual Studio.   Most everyone is used to creating a new database by going to “Add new database“ from the Server Explorer.  This approach will still work. (Although you need to use the instance name of SQLExpress. [So, type “.\SQLExpress“ for the server name.] and there is a bug in the WebExpress SKU where this doesn't work. But, it does work in the other SKUs]) 

   In fact, if you see something like this:

   “Visual Web Developer 2005 Express Edition Beta, This feature is not supported
   by Microsoft Visual Web Developer Express“

   it's probably because you didn't specify the “.\SQLExpress” instance name.

But, this isn't the preferred way.  The preferred way is to simply copy the database into your project.  So, from the project menu, right click and choose “Add new item“ or “Add existing item“.  In either case the MDF (and LDF if present) is copied into your project.  Then a connection is automatically opened for you in Server Explorer.  So, you can directly party on the database structure.

SSE supports a connection style where you do not specify the logical database name.  You simply leave it blank in the connection string and one is generated for you automatically on the fly (based on the unique path of the MDF in the directory structure).  When your app closes down (app domain), the MDF is auto-closed which leaves the MDF in a copyable state. 

So, you can build your app and directly reference the MDF that will be in the same directory as your .exe.  When you hit F5, the project system builds your .exe/dlls and copies the MDF to the output directory.  When your app runs, it directly references the MDF that is in same directory as your exe (or relative to it).  When your app spins down and the app domain closes, both your executables and your database are Xcopyable (mailable, zippable, whatever ...) to the destination of your choice.

Note that when you debug, the same thing happens.   The exes are built in the debug directory and the MDF is copied to the debug directory as well.  So, when you are debugging your app, it's hitting the version of the MDF that's in the debug directory.  There is an option in the project system to *not* keep copying the MDF to the output dir each time that you can configure.  But, you need it there at least on the first build so that the app can reference it as it will when it's deployed.

When you deploy, you have two options.  1.) You can Xcopy.  And, you should Xcopy and pick up both the MDF and LDF files.  The VS project system does some work to manage the LDF for you automatically behind the scenes.  and 2.) you can use the explicit deploy features.  Note that you have the option of bundling up both the framework and SSE with your application.

Anyway, thought I'd better get this out on a blog so that people can see how to use SSE in the “file style“ format and so that they know about the SQLExpress instance name.

Lance