Specifying the directory to create SQL CE databases when using Entity Framework

In the last few posts I've been describing how to create instances of SQLCE in order to perform automated Integration Testing using NUnit and accessing the dB using Entity Framework.  I covered creating the dB using both Entity Framework and the SQL CE classes.  In particular I wanted control over the directory the dB was created in but I didn't want to tie to a specific location rather let it use the current working directory.

Using the Entity Framework's DbContext constructor that takes the name of a connection string or database name it's suddenly very easy to end up NOT creating the dB you expected where you expected it to be.  This post shows how to avoid these.  Generally speaking the use of the DbContext constructor that takes a Connection String should be avoided unless the name of a connection string from the .config file is being specified.

Example 1 - Using the SqlCeEngine class

1:  const string DB_NAME = "test1.sdf";  
2:  const string DB_PATH = @".\" + DB_NAME; // Use ".\" for CWD or a specific path  
3:  const string CONNECTION_STRING = "data source=" + DB_PATH;  
4:    
5:  using (var eng = new SqlCeEngine(CONNECTION_STRING))  
6:  {  
7:    eng.CreateDatabase();  
8:  }  
9:    
10:  using (var conn = new SqlCeConnection(CONNECTION_STRING))  
11:  {  
12:    conn.Open(); // do stuff with db...  
13:  }  
14:    

The important thing to note is that the constructor for SqlCeEngine that takes an argument requires a Connection String, i.e. a string containing the "data source=...".  Just specifying the dB path is not sufficient.  To specify a specific directory  include the absolute or relative path.  To specify the current working directory, e.g. bin\debug then just use ".\".

Example 2 - Using DbContext (doesn't work)

1:  using (var ctx = new DbContext("test2.sdf"))  
2:  {  
3:    ctx.Database.Create();  
4:  }  

This code appears to work but doesn't create an instance of an SQL CE dB as desired.  Instead it creates a localDB instance in the user's home directory.  In my case: C:\Users\Pete\._test.sdf.mdf (& corresponding log file).  This is not really surprising as Entity Framework had no way of knowing that a SQL CE dB should be created.

Example 3 - Using DbContext (does work)

1:  Database.DefaultConnectionFactory =  
2:    new SqlCeConnectionFactory(  
3:      "System.Data.SqlServerCe.4.0",  
4:      @".\", "");  
5:    
6:  using (var ctx = new DbContext("test2.sdf"))  
7:  {  
8:    ctx.Database.Create();  
9:    // do stuff with ctx...  
10:  }  

The difference between the last and this example is changing the default type of dB that EF should create.  As shown this is done by installing a different factory.

The 3rd parameter to SqlCeConnectionFactory is the directory that the dB should be created in.  Just like the first example specifying ".\" means the current working directory and specifying an absolute path to a directory will lead to them being created there.

NOTE: As per the post Integration Testing with NUnit and Entity Framework be aware that creating a dB using the Entity Framework results in the additional table '_MigrationHistory' being created which EF uses to keep the model and dB synchronized.

NOTE1: Whereas SqlCeEngine is a SQL CE class from the System.Data.SqlServerCe assembly, SqlCeConnectionFactory appears to be part of the System.Data.Entity assembly which is part of the Entity Framework.


In the above example the string passed to DbContext can be a name (of a connection string from the .config file) or a connection string.  In this case passing the name of the db, i.e. test2.sdf is equivalent to passing "data source=test2.sdf", well more or less.  If the '.sdf' suffix is omitted with "data source" then the resultant dB is called test2 but if just test2 is passed then the resulting dB will be called test2.sdf.

Example 4 - Using DbContext and the .config file

1:  using (var ctx = new DbContext("test5"))  
2:  {  
3:    ctx.Database.Create();  
4:  }  

App or Web .config

1:  <connectionStrings>  
2:    <add name="test5"  
3:      providerName="System.Data.SqlServerCe.4.0"  
4:      connectionString="Data Source=test5.sdf"/>  
5:  </connectionStrings>  

This time no factory is specified but the argument to DbContext is the name of a Connection String in the .config file.  As can be seen this contains similar information to that in the factory method enabling EF to create a dB of the correct type.

To use these the instances of these databases rather than calling the create method on the context just use the context directly or more likely in the case of EF a derived context which brings us to one last example.

Example 5 - Using a derived context and .config file

1:  public class TestCtx : DbContext  
2:  {      
3:  }  
4:  using (var ctx = new TestCtx())  
5:  {  
6:    ctx.Database.Create();  
7:  }    

App or Web .config

1:  <connectionStrings>  
2:    <add name="TestCtx"  
3:      providerName="System.Data.SqlServerCe.4.0"  
4:      connectionString="Data Source=test6.sdf"/>  
5:  </connectionStrings>  

If a derived context is created which will almost certainly be the case then if an instance of this is created and a dB created then EF will look for a Connection String in the .config file that has the same name as the context and take the information from there.

Using NUnit and Entity Framework DbContext to programmatically create SQL Server CE databases & specify the databse directory

NOTE: An update to the method used here is provided in this newer post: Integration Testing with NUnit and Entity Framework.

I won't go into the why but I wanted to write what probably amounts to an integration test of an Entity Framework model.  I decided to use NUnit for this.  Even though it's predominately for Unit Testing there's no reason why it can't be used for other forms of testing.  In fact I think the SpecFlow BDD framework eventually performs its tests using NUnit: It's not what you've got but what you do with it!

The application I'm writing uses SQL Express but for my tests I wanted to create & destroy a database frequently and locally so I opted to use SQL Server Compact Edition.  This is pretty easy to do with EF's DbContext: just create an instance of it, pass in either the name of the database or a Connection String, e.g.


[Test]
public void TestWithConnectionString()
{
  var db = new DbContext("Name=Foo");


  db.Database.Create();
}

which references the following Connection String in the .config file


<connectionStrings>
  <add name="Foo"
    providerName="System.Data.SqlServerCe.4.0"
    connectionString="Data Source=foo.sdf"/>
</connectionStrings>



As I'm going to be potentially creating many of these I wanted precise control over their name and location.  This meant I didn't really want to use a Connection String as this is more or less hard-coded in the .config file.

Instead I opted to create it programmatically.  By default DbContext expects to be using SQL Server in particular an instance of SQLExpress named .\SQLExpress.  This is easily changed by replacing the DefaultConnectionFactory with the factory class for SQL Server CE: SqlCeConnectionFactory.

The first parameter to the constructor is the type of the factory class which allows instances of different versions to be created. This is simply the providerName as per the Connection String.  The second parameter is the directory to create any new databases in.  The third parameter (unused in the example below) are options to append to the generated Connection String.  The code below will result in the creation of C:\UsersPete\TestDBs\bar.sdf assuming the directory exists and the user has appropriate permissions and bar.sdf does not already exist.


[Test]
public void TestWithFactory()
{
  Database.DefaultConnectionFactory = 
    new SqlCeConnectionFactory(
      "System.Data.SqlServerCe.4.0", 
      @"C:\Users\Pete\TestDBs\", "");


  var db = new DbContext("bar.sdf");

  db.Database.Create();
}

That's mainly it:  A simple way to programmatically create databases.  This code should really be moved to the SetUp method and a corresponding TearDown method added to delete the database; oh, and some tests!

I haven't got that far yet and the actual reason for writing the entry was for a short while despite specifying the directory for the databases, i.e. C:\Users\Pete\TestDBs they were being created in the current working directory.  This was because rather than just specifying the name of the database as the argument to DbContext I was passing the string "DataSource=bar.sdf" of which the name part appears to be an absolute path and thus override that specified in the SqlCeConnectionFactory.  As only a file name is present it uses the current working directory for the path element. If you've got the same issue (whether integration testing or not) this might save you a little time.