LINQ SQL Compact – DatabaseExists()

If like me you are trying to use this really handy method and are having problems, it’s not your code … There is a bug with this method if you are using the, also very handy, |DataDirectory| macro in your application settings. Basically, using this macro (with SQL Compact 3.5) in your connection string, then instantiating a new LINQ data context with your nice friendly application connection string this method will always return false. Meaning your application will throw an exception. Your code may look something like:
[source:c-sharp] DContext datacontext = new DContext(Properties.Settings.Default.DBConnection);

if (!datacontext.DatabaseExists())
{
datacontext.CreateDatabase();
}
[/source] However, there is a simple workaround to get this method working as you would like. Simply build the connection string manually replacing the |DataDirectory| with an explicit path to your database. If you create your context this way in your class all will be well. Its not so much of a problem as a niggle since this sort of functionality will be in a utility class in your application and your most likely to be doing real database work elsewhere. To do this you can:
[source:c-sharp] AppPath = AppDomain.CurrentDomain.BaseDirectory;
DatabaseDir = Path.Combine(AppPath, “Database”);

datacontext = new DContext(“Data Source=” + DatabaseDir + “\Database.sdf;Password=**************;Persist Security Info=True”);
if (!Directory.Exists(DatabaseDir))
{
DirectoryExisted = false;
Directory.CreateDirectory(DatabaseDir);
DirectoryCreated = true;
DatabaseExists = false;
datacontext.CreateDatabase();
datacontext.Dispose();
}
else
{
if (!datacontext.DatabaseExists())
{
datacontext.CreateDatabase();
}
}
[/source] And hey presto, your back on the road to productivness rather than bug finding!