Creating a Code First Database Initializer Strategy

Introduction

Yesterday I helped a colleague with his ASP.NET MVC 3 site deployment. That colleague implemented the data access layer using EF4.1 Code First. One of the restrictions that he had was that he didn’t have database permissions to create a new database and couldn’t use SQL Express or SQL CE in his application. Instead he had an empty database for his disposal in the hosting environment without a way to run SQL scripts… In such a situation, the provided Code First database initializer strategies weren’t sufficient (he couldn’t drop or create the database and couldn’t run scripts). In this post, I’ll show an example of how to create your own database initializer and show the strategy that helped my colleague to make his deployment.

The Database Initializer Interface

The first thing you have to get familiar with when you want to create your own database initializer is the IDatabaseInitializer generic interface. The IDatabaseInitializer interface is available in the EF4.1 EntityFramework DLL in the System.Data.Entity namespace. It exposes only one method - InitializeDatabase:

namespace System.Data.Entity
{
  public interface IDatabaseInitializer<in TContext> where TContext : 
                   global::System.Data.Entity.DbContext
  {
    // Summary:
    //   Executes the strategy to initialize
    //   the database for the given context.
    // Parameters:
    //   context: The context.
    void InitializeDatabase(TContext context);
  }
}

Creating the Database Initializer Strategy

When you want to create your own strategy, you will implement the IDatabaseInitializer interface and create your desired initialization behavior. Since all the colleague wanted was to drop the database tables during the application initialization and then to create all the relevant tables, here is a sample code that can perform that:

public class DropCreateDatabaseTables : IDatabaseInitializer<Context>
{
#region IDatabaseInitializer<Context> Members

    public void InitializeDatabase(Context context)
    {
        bool dbExists;
        using (new TransactionScope(TransactionScopeOption.Suppress))
        {
            dbExists = context.Database.Exists();
        }
        if (dbExists)
        {
            // remove all tables
            context.Database.ExecuteSqlCommand(
               "EXEC sp_MSforeachtable @command1 = \"DROP TABLE ?\"");

            // create all tables
            var dbCreationScript = ((IObjectContextAdapter)
                   context).ObjectContext.CreateDatabaseScript();
            context.Database.ExecuteSqlCommand(dbCreationScript);
            
            Seed(context);
            context.SaveChanges();
        }
        else
        {
            throw new ApplicationException("No database instance")
        }
    }

#endregion

#region Methods

    protected virtual void Seed(Context context)
    {
        /// TODO: put here your seed creation
    }

#endregion
}

So what am I doing in the code sample? At first, I check that the the database exists; if not, an exception will be thrown. If the database exists, I use a undocumented SQL Server Stored Procedure which is sp_MSforeachtable in order to drop all the existing tables. After that, I get the context’s underlining ObjectContext in order to get the script that will generate the database using the CreateDatabaseScript method. Then, I run the script using the ExecuteSqlCommand method. After that, I run the Seed method in order to enable the insertion of seed data into the database.

Another thing that you will need to do is to supply the relevant connection string for the existing database:

<connectionStrings>
    <add name="Context" 
      connectionString="Data Source=Server Name;Initial Catalog=Database Name;
                        Persist Security Info=True;User ID=Username;
                        Password=Password" 
      providerName="System.Data.SqlClient"/>
</connectionStrings>

Pay attention that when such a strategy is deployed, whenever the application starts over, all the database tables will be recreated! This strategy should only run once. After the deployment with the previous strategy, my colleague deployed the application again with the default Code First database initialization strategy!

Using the Database Initializer Strategy

In order to use the initialization strategy in an ASP.NET MVC application, all you have to do is set the initializer. The best place to do that is in the Global.asax file in the Application_Start handler. You will use the SetInitializer method that exists in the Database class to wire up the strategy. Here is a code sample that shows how to wire up the previous strategy:

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    RegisterGlobalFilters(GlobalFilters.Filters);lFilters.Filters);
       RegisterRoutes(RouteTable.Routes);

    Database.SetInitializer(new DropCreateDatabaseTables());
}

Summary

In the post, I showed you how to create a new database initializer strategy. The provided strategy isn’t a silver bullet solution for the problem I mentioned in the post’s prefix. The ADO.NET team is working on a migration feature for EF Code First that might provide a solution for such a scenario. In the meantime, the strategy presented here helped my colleague to solve his problem.