VS2013 and SQL Server Compact Tips

  1. You should install SQL Server Compact Toolbox via VS2013 –> Tools –> Extensions and Updates.  Remember to restart VS2013.
  2. Nuget EntityFramework.SqlServerCompact.  It should download SQL Server Compact 4.0 and EntityFramework 6.1.0.
  3. If your model entities reside in a different project (i.e. class library) with your start up project (i.e. console app), you need to be aware of a couple of things.
    1. You need to nuget EntityFramework.SqlServerCompact for both your projects.  The “app.config” in your model class library project is redundant; it is your start up project’s app.config file that counts.
    2. By default, the SQL Server Compact (*.sdf) is located in the bin folder of your model class library project when you do migration (i.e. update-database).  When in doubt, use Windows File Explorer to search for “*.sdf”.
    3. But when you run the application, it will create another *.sdf in your start project’s bin folder. 
    4. Having 2 sdf files is a problem.  What we really want is have have only 1 sdf in the startup project’s bin folder, to be used for code first migration and app runtime.  There are a couple of solutions:
      1. You can add a connection string to the app.config with the Data Source having an absolute path and reference that connection string in your DbContext class. 
      2. <connectionStrings>
        <add name="DefaultConnection"
        providerName="System.Data.SqlServerCe.4.0"
        connectionString="Data Source=C:\Program Files\xxxx\xxxx.sdf"/>
        </connectionStrings>

        public class XxxxContext : DbContext
        {
        public XxxxContext()
        : base("DefaultConnection")
        {
        Debug.Write(Database.Connection.ConnectionString);

        }
        ...
        }

      3. However, I am wary of absolute path, so I do the following hack:
        public class XxxxContext : DbContext
        {
        public XxxxContext()
        {
        string connectionString = string.Format(@"Data Source={0}XxxxDB.sdf", AppDomain.CurrentDomain.BaseDirectory);

        if (connectionString == @"Data Source=C:\Projects\Xxxx\Main\Source\Xxxx.Entity\bin\Debug\XxxxDB.sdf")
        connectionString = @"Data Source=C:\Projects\Xxxx\Main\Source\XxxxApp\bin\Debug\AnnClientDB.sdf";

        this.Database.Connection.ConnectionString = connectionString;
        Debug.Write(Database.Connection.ConnectionString);

        }

  4. When you update database, you can use “update-database -Verbose”.  This let you know the connection string it is using.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s