Jakob Lithner Jakob Lithner - 1 month ago 10
C# Question

EF 5 + SQL CE 4: How to specify custom location for database file?

I am developing a client system that needs a small local database.
I want to avoid installation of SQL Express and have decided to go with SqlCe 4.

I use EntityFramework 5 for data access and have created my custom context.
Everything works fine in development where I can use app.config to either set specific file location or dynamic "Data Source=|DataDirectory|\MyDatabase.sdf".

But on deploy I want the database to be located in the users documents folder: \My Documents\ApplicationName\MyDatabase.sdf

How can I do that?

All I need is actually to be able to set custom connection string in code!

This is what I tried so far:

private MyApplicationDataContext(string connectionString)
: base(connectionString)
{
}

public static MyApplicationDataContext CreateInstance()
{
var directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
var path = Path.Combine(directory, @"ApplicationName\MyDatabase.sdf");

//var connectionString = string.Format("provider=System.Data.SqlServerCe.4.0;provider connection string=\"Data Source={0}\"", path);
var connectionString = string.Format("Data Source={0}", path);

return new MyApplicationDataContext(connectionString);
}


As you can see I tried two kinds of connection strings but both caused exceptions.


Keyword not supported: 'provider'.


and


The provider did not return a ProviderManifestToken string.

Answer

Ah, I finally got it right!

I include the adjusted code if someone else has the same problem. The trick was to set the connection string on the Database.DefaultConnectionFactory

    private MyApplicationDataContext()
    { }

    public static MyApplicationDataContext CreateInstance()
    {
        var directory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
        var path = Path.Combine(directory, @"ApplicationName\MyDatabase.sdf");

        // Set connection string
        var connectionString = string.Format("Data Source={0}", path);
        Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0", "", connectionString);

        return new MyApplicationDataContext();
    }