Creating a Code First Database Initializer Strategy

May 30, 2011

Creating a Code First Database Initializer Strategy

Yesterday I helped a colleague with his ASP.NET MVC 3 site deployment. Creating Code First Database Initializer StrategyThat 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 the 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 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 not documented 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 start 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 to 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 show how to wire up the previous strategy:

protected void Application_Start()
{
    AreaRegistration.RegisterAllAreas();
    RegisterGlobalFilters(GlobalFilters.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.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

4 comments

  1. pythonyanMay 31, 2011 ב 13:48

    Hi,

    i test this code for my domain and i receive an error on foreign key constraint during code execution. With my standard approach, that use DropCreateDatabaseAlways strategy, i get what i want. My question is: why should I use this approach  that using a particular feature is not documented in SQL?

    Thks in advance

    Reply
  2. Gil FinkMay 31, 2011 ב 17:28

    @pythonyan,
    As I wrote in the post, the purpose of the post is to show how to develop a database initializer (I’m not suggesting to use this approach at all – see the bolded section). Also, I wrote that this initializer was a solution to a problem that my colleague had. This is not a strategy that I’ll use in real application.
    About sp_MSforeachtable, trying to drop a table (which is what the sp_MSforeachtable is doing) which has foreign key constraint will produce this error because of the constraint. There are other ways to drop all the tables in the database.

    Reply
  3. Juan FranciscoJuly 16, 2011 ב 15:56

    Hello, is it possible to have two initializers, like this:

    Database.SetInitializer(new InitializerIfNotExits());
    Database.SetInitializer(new InitializerIfModelChanges());

    or the second overrides the first initializer?

    Thanks in advance.

    Reply
  4. Gil FinkJuly 16, 2011 ב 18:11

    @Juan Francisco,

    You can have only one initializer per application.
    The reason for that is that the initialization faze occurs when the application starts (once per AppDomain). Writing the supplied code will make the second initializer override the first one.

    Reply