Would just like to mention Nick Mayne who introduced me to SQLServer CE. check his blog out http://themayneissue.com/ he also works on the great orchard CMS project for MVC which is definitely worth taking a look at http://www.orchardproject.net/
If you're interested, read on ....
We are using the northwind database and the POCO templates for the entity framework so if you haven't already done so, you will need to download the northwind samples and the POCO templates which can be found at :
http://visualstudiogallery.msdn.microsoft.com/23df0450-5677-4926-96cc-173d02752313
Then open Visual Studio.
Create the data class library
In your solution add a new class library project. Then add a new a new item and select Entity Data Model like so:
This then prompts you to generate from db or empty, i'm choosing empty..
I now have the following solution structure:
_2012-03-29_21-22-11.png)
Now select your Edmx and right click in the pane and select Update Model From Database
_2012-03-29_21-23-24.png)
and then set the connection string and add a couple of tables. My edmx now looks like this (i've added the northwind-employees table)
_2012-03-29_21-25-34.png)
To add the POCO generation, right click in the edmx white pane and select "Add Code Generation Item" and select the POCO template:
This then creates your context and tt files required for generating the entities code. Save you EDMX and close then select the model.context.tt and right click and select "Run Custom Tool" as you can see I now have the employees entities generated in the project :
_2012-03-29_21-32-11.png)
Ok, so now we have a model to test ! next lets use SQL SERVER CE to test it. What we are essentially going to do wrap the object context in a db context in order for us to be able to test our repositories. So lets get started.
Create a new unit test project, you have guessed by now I am using the new hideous VS 11 IDE...
We then need to add 3 packages, EntityFramework, the SQLServer CE installation and the entity framework package. Firstly, with your new test project selected, select Manage NuGet Packages and select the online option and search for entity framework.
Then hit install, this will add the libraries to the test project, next select manage nuget packages again and search for SqlServerCompact and install:
_2012-03-29_21-32-11.png)
Finally, you will need the entity framework sql package:
And we are good to go.
First we need to add a new class that effectively "Wraps" the object context. So I will add a new class called ObjectContextWrapper.cs
We start by fleshing out the code and accepting the object context in the constructor along with a dbconnection (you will need to add references to your data project, system.data, system.data.common and entity)
We basically wrap the context and the entities available in the normal object context. We override CreateDatabaseScript and pass this to the object context and then provide the same method in the object context to get the employees. Our code for the wrapper looks like so:
public class ObjectContextWrapper : DbContext
{
Model1Container _objectContext;
public ObjectContextWrapper(Model1Container objectContext, DbConnection connection) : base(connection, true)
{
_objectContext = objectContext;
_objectContext.ContextOptions.LazyLoadingEnabled = false;
}
public string CreateDatabaseScript()
{
return _objectContext.CreateDatabaseScript();
}
public IDbSet<Employee> Employees
{
get { return this.Set<Employee>(); }
}
}
Finally, you add the unit test, sorry I have put all the setup in the test, this would ideally go in TestInitialize
[TestMethod]
public void TestMethod1()
{
MetadataWorkspace workspace = new MetadataWorkspace(new string[] { "res://*/" }, new Assembly[] { Assembly.GetAssembly(typeof(Model1Container)) });
Database.DefaultConnectionFactory = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
//Database.SetInitializer(new
ObjectContextInitialiser ());
DbConnection connection = new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0").CreateConnection("SqlServerCEDB");
EntityConnection entityConnection = new EntityConnection(workspace, connection);
ObjectContextWrapper context = new ObjectContextWrapper(
new Model1Container(entityConnection), connection
);
Employee emp = new Employee() { EmployeeID = 1, FirstName = "Andrew", LastName = "Smith" };
context.Employees.Add(emp);
context.SaveChanges();
Employee newemp = (
from d in context.Employees
where d.EmployeeID == 1
select d
).Single();
Assert.AreEqual(newemp.EmployeeID, emp.EmployeeID);
Database.Delete(connection);
}
Bit of explanation needed here. Firstly, I tried a few different ways to work with the context but wanted to pass our object context an entityconnection as it seems the easiest thing to do.
To create an EntityConnection, we need a db connection and a metadata workspace, so we create the metadataworkspace using the assembly where the object context can be found and a simple resource string.
We call the SQLCE connection factory to get a dbconnection and then create the Entity connection using these two items.
we pass our wrapper the connection and the model container (ok I pass the connection twice, we can tidy that up I guess but its late)
We then create an employee and add it to tour wrapper class (remember we have an employees property)
we then save the changes.
to test, we get the employee back and perform an assert
if you comment out the Database.Delete and check the test bin directory, you will find the sdf database file. and hey presto ! unit test with SQLServerCE
Initialising with some data
We can initialise the data with the setInitializer method on the database object, we first write the initializer:
public class ObjectContextInitialiser : DropCreateDatabaseAlways<ObjectContextWrapper>
{
ObjectContextWrapper _objectContextWrapper;
protected override void Seed(ObjectContextWrapper context)
{
_objectContextWrapper = context;
var Employees = new List<Employee> {
new Employee() { EmployeeID = 500, FirstName = "test", LastName="name" }
};
Employees.ForEach(d => _objectContextWrapper.Employees.Add(d));
}
public IEnumerable<Employee> Employees
{
get
{
return _objectContextWrapper.Employees;
}
}
}
This simply creates a seed method to populate the context with an employee and a convienience method to get the employees from the context so we can compare in the unittest.
We can then uncomment the line :
//Database.SetInitializer(new ObjectContextInitialiser ());
and the db is populated.
If you find this useful, drop us a line
