Monday, March 05, 2007

Using a local SQL Express database

I like to keep my software artifacts together during development. Preferrably I organize them under a single folder for a whole project. That´s also true for databases. Fortunately SQL Server 2005 lets you accomplish this for its databases by using the AttachDbFilename parameter in a connection string.

Using this feature I now want to switch from a global sample database to a local database. But that´s easier said than done. Where should I enter this connection string option? There is no place in the VOA wizard and I don´t see a connection string in the App.Config generated by the wizard.

Fortunately the VOA support was able to help me. Here´s the relevant part of my App.Config:

    8 <connections>

    9   <connection id="DatabaseConnection1">

   10     <databasename>VOASamples01SimpleWithLocalDB</databasename>

   11     <servername>.\SQLEXPRESS</servername>

   12     <integratedSecurity>True</integratedSecurity>

   13     <backendconfigurationname>mssqlConfiguration</backendconfigurationname>

   14     <connectionParams>AttachDbFileName=C:\Samples\VOASamples01\SimpleWithLocalDB\VOASamples01SimpleWithLocalDB.mdf</connectionParams>

   15   </connection>

   16 </connections>

I created a new project called SimpleWithLocalDB which contains right the same code as my first one. I also used the VOA wizard in the same way, and just put in a different database name.

Then I added a SQL Database through the Add Item menu item which was put into the project directory. I adjusted the App.Config like above so it pointed to the newly created local database and compiled the project. All went well.

However, when I then ran my test I realized, the database which was automatically copied to the execution directory, was not changed. Copying a local database for execution is the default for VS2005. Rather the database in the project directory was updated with the object´s data the code stored. Sure this should have been obvious since the connection string pointed to the database in the project folder... but from using ADO.NET I kinda was used to accessing a temporary copy in the execution directory. But of course, then I used |DataDirectory| as a placeholder for the actual path to the database.

Unfortunately, |DataDirectory| does not work with VOA. VOA needs a written out full path to the database with AttachDbFilename. It currently can only deal with a single database for automatic schema adaption as well as persisting objects. So I left the path to the project folder database in the App.Config and switched the Copy to Output Directory property of the database to "Do not copy" (see figure above).

Now my simple sample works just fine with a local database. However, I suggest, Vanatec improves its connection string handling a bit and understand |DataDictionary|. That would make rolling back test data easier.

Is the current limitation a major drawback, though? No, I don´t think so. Most projects use central/global databases anyway like I did in my initial sample.

The only small real drawback I see is, you need to adjust such paths to databases in my samples you can download. Putting a placeholder of my own into the connection string would not help, since VOA needs the connection string not only during runtime but also at compiletime.

But anyway... Let´s move on with actually persisting objects.


[see "Updating VOA to version 4.3.2" for an update on some of the issues raised in this post]

1 comment:

Anonymous said...