Sunday, December 09, 2012

Chinook Database is now available on NuGet

Chinook sample database is now available on NuGet. There are 3 packages available: 
  • Chinook Database SQL Scripts: contains the SQL scripts for all supported databases (SQL Server, SQL Server Compact Edition, SQLite, Oracle, MySQL, PostgreSQL, DB2 and EffiProz. In addition, it also contains batch files to run these scripts with the respective command prompt tools.
  • Chinook Database for SQLite: contains a SQLite file with the Chinook database.
  • Chinook Database for SQL Server Compact 4.0: contains a SQL Compact 4.0 file with the Chinook database.

You can install the Chinook database within Visual Studio using the following instructions and searching for 'Chinook':

For now on, new releases will be available on Codeplex download page and also on Nuget.


Sunday, December 02, 2012

Chinook Sample Database 1.4 Released

The Chinook Database is a sample database available for multiple database systems. It can be created by running a single SQL script. Chinook database is an alternative to the Northwind database, being ideal for demos and testing ORM tools targeting single and multiple database servers.

The Chinook Database represents a media store, including tables for artists, albums, media tracks, invoices and customers. The media information was generated from my iTunes library file, but anyone can download its source code and use their own iTunes library information. The sales information is auto-generated for a 4 years period. You can see the Chinook data model here.

This new version includes support for DB2 and PostgreSQL. Thanks to @brice_lambson for implementing the PostgreSQL support! Chinook database is available for:

  • DB2 (new)
  • EffiProz
  • MySQL
  • Oracle
  • PostgreSQL (new)
  • SQL Server
  • SQL Server Compact
  • SQLite

You can download the latest release from here. There are available SQL scripts for each database system and also embedded database files for SQL Server Compact and SQLite.

Monday, March 05, 2012

Unit Testing an Entity Framework Data Access Layer

One approach to write unit tests for a data access layer implemented using Entity Framework that targets SQL Server is to use a local test database SQL Server Compact Edition. The test database will have the same schema as the real application database, and it will contain the minimun necessary data for your test cases. 

The only thing to keep in mind is that you will need to restore the previous state of the test database after each unit test run. There are multiple approaches such as to recreate the database before each test or simply restore a backup file. One approach that I like is to run the test under a TransactionScope.  In this approach, you create a TransactionScope object before the test runs and call TransactionScope.Dispose after the test is completed. Since TransactionScope.Complete is not called, the Dispose method will rollback the changes. That is, you SQL Compact database will be back to the state before running the test.

If you are using xUnit, then your unit test class will need to implement the IDisposable interface, create the TransactionScope in the constructor and dispose it in the Dispose method as shown below:

public class MyDataAccessUnitTests : IDisposable
  private TransactionScope _transactionScope;

  public MyDataAccessUnitTests()
    _transactionScope = new TransactionScope();

  void Test1()
    // TODO: implement it.

  void Test2()
    // TODO: implement it.

  public void Dispose()

If you are using NUnit or MSTest, then you can create and dispose the TransactionScope object in the following methods:

xUnit NUnit MSTest
new TransactionScope() Constructor [SetUp] [TestInitialize]
TransactionScope.Dispose IDisposable.Dispose [TearDown] [TestCleanup]

I hope this helps!