The mission was to embed a SQL Express database into a desktop application and have it use User Instances.  In addition, the database itself will be stored in the project as a resource and copied to the application directory the first time it is used by code that's included in the project.  This is done so that subsequent updates to the database can be deployed as a SQL script and can be run by the application against its copy of the database preventing the application from loosing user data by overwriting previous versions of the database.  This technique is discussed in this webcast that I've linked to before, but today we are going to talk about the next step. 

 

UNIT TESTING

In my project I created a Visual Studio unit test to verify the functionality of copying the database to a file from an embedded application resource, connect to it with Linq to SQL and run some queries against the database to confirm it worked.  Everytime the query ran I was plagued with a SQL Exception error....

System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file c:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\AppData.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share..

 The real key to fixing this error was the database path.  Notice in the error message it's pointing to c:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\AppData.mdf however, in the context of running the unit test, the path should be something like... C:\Development\EOD\TestResults\mcstar_HP_LAPTOP 2008-10-15 09_21_35\Out\AppData.mdf

so, why is the unit test looking in the wrong location for the mdf file?  The secret to understanding that is the connection string.  Here's my connection string :

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\AppData.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

That keyword DataDirectory informs SqlExpress to use the current application directory.  Since the Visual Studio IDE is the current executing process whenever you run unit tests, DataDirectory is referring to Visual Studio's startup path!  That's not going to work.  In my case, the fix was to force my data context to open with the correct path by setting the connection string in the unit test to include the correct path to the mdf file created by the application.

 

Is it possible that thousands of scientist could actually be wrong?  Perhaps the answer is simpler than you think.  This video puts a little perspective around the debate.

Click Once

Mike Wachal has done some excellent work discussing how to embed a SQL Express

database into an application for use with click once.  In his approach, the mdf file is packaged as an application resource and is copied to the application directory by the application at run time.  Later versions of the application are deployed with update transact SQL scripts that are then run by the application against the local copy of the embedded database to install changes.  This avoids the problem where you overwrite a user's prior data each time an application is updated.  Check out Mike's blog at SQL Express Blog.  A link to the web cast can be found at SQL Express Weblog

More information on Click Once is at...

Click Once deployment on MSDN

 

User Instances

From my background using SQL Server in an Enterprise environment I had no frame of reference for understanding user instances, so I found this SQL Express User Instances on MSDN very helpful. This is a highly recommended read if you're trying to use SQL Express in an embedded application.  It explains the connection string argument AttachDbFilename which makes it possible to connection to a database file in the current application's directory.  Using this statement also requires administrator access unless you use the User Instances feature which is also discussed at length.

 

Detecting SQL Express

This Whitepaper also contains code showing how to detect whether SQL Express is installed and tips on how to get it installed.

jParallax is javascript that works on layered objects (images, divs, etc) and moves them in response to your mouse.  The affect is to simulate depth in the way old school side-scrolling games used to work.  (think Super Mario Brothers).  It's a very cool affect. Grab this tool and add it to your web programming arsenal and give your site some distinction.  

The Ultimate AI Primer

AI is handled superbly in this AI textbook..Artificial Intelligence: A Modern Approach (On Amazon).  This book teaches the art and science Artificial Intelligence at the graduate level.  It's a great read for the developer wanting to add that edge to his programming skills.   It's done in a language agnostic way which presents the algorithms as pseudo code. It's left to the developer to come up with an implementation, but what you learn is priceless.

Implementation

I've been studying this book for over a year now.  About 6-months into it, I actually used what I learned in a production application.  The problem at hand was to sort a list addresses (lat/long) by the shortest possible path between them for a mapping application.  This was done to minimize travel distance and fuel cost for field personnel as they perform on-site service. The problem was solved using a variant of the traveling salesman algorithm and a node based path costing algorithm. I created these algorithms from knowledge gained in this book, so I can truly attest to it's value.  This system has become a valuable time saver that the business now uses daily.