Source code for Embedding Webcast (Finally)

Updated Sample Available! 

 Steve Lasker (of SQL Server Compact fame) and I spent some time reworking this deployment sample for a presentation that Steve did at TechEd Barcelona at the end of 2007. You can find Steve's post including links to all his presentations here. Of particular interest to this group is the updated Deploying Database with Scripts sample code. The core principles of the sample have not changed from what is described below with a few changes:

• Database deployment is 100% script based.
• Better organization of database scripts into a custom Resource
• Cleaned up the the code to handle setting the data directory to use the right API to verify a ClickOnce deployed app (It's nice to have a VS expert helping out!)

This sample is still based on a user instance connection string, but that is not required. This example could be tailored to create the database on the parent instance as well by just changing the connection string that is passed to the VerifyDatabase method.

Original sample code (for reference if you want it)

At long last, I've posted the code for the embedding web cast. I've made a few changes to the code from what I showed in the webcast, here are some details about implementation and alternatives you can consider when thinking about this.

Alternative Databases

When I first put this webcast together, SQL Express was the primary database for creating end-user, data bound application in Visual Studio 2005. Since then, SQL Server 2005 Everywhere Edition has become available in CTP. For single user, local data scenarios, you should give SQL Everywhere Edition a serious look. You can check out more details about this product in Steve Lasker's blog and in the SQL Everywhere Edition team blog.

Implementation Details

There are a couple of things worth mentioning about this code:

User Instances: I've chosen to do this sample with User Instances in order to showcase the ability to do all this in a non-admin framework, which is the point of ClickOnce. This is not a requirement and the code can be easily changed to use the normal instance of SQL Express if you'd rather go that way.

Database Versioning: I'm synching the database version using an Application Property (dbVersion) and a field in the AppInfo table in the database. I really wanted to use a built in property of the application to do this so it would happen automatically, but at the end of the day, there was no good way to do this, so it's one more property to remember to set. You can store the property in the database any way you like, just remember to update the property when you run a database update. The source code show the update script that I put into the application to install database version 1.0.0.1, which you'll notice updates the Version field in the AppInfo table.

|DataDirectory| macro: SQL Server 2005 and VS 2005 introduce the functionality to attach a database from a dynamic location using the |DataDirectory| macro. This is tied into ClickOnce deployment, so it can handle debuging an application and deploying an application. I wanted to have a bit more flexibility to also deploy by manually XCopy'ing the application to an arbitrary location and to allow the developer to specify a common directory for database installation. This is handled in SetDataPath(), which makes use of the DataDirectory property to change the directory path returned by the |DataDirectory| macro in the non-standard scenarios. Setting a customer directory is handled using the CustomDataDirectory property of the application.

Calling the Class

This whole thing is implemented in a single class, calling it is pretty standard, instantiate the class and then call the single, Public method, VerifyDatabase():

CheckDbVersion getDB = new CheckDbVersion();

if (getDB.VerifyDatabase())

{

PopulateGrid();

}

In this case, the PopulateGrid() method was a simple method that creates and fills a DataSet based on the sample database and uses the data to fill a DataGrid. It's straight forward code that you can find on MSDE and numerous other sources, so I'll not go into the details.

     

Go forth and embed those databases.

Mike