Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Creating a Code First Database Initializer Strategy

, 30 May 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
In this post, I’ll show an example of how to create your own database initializer.

Introduction

Creating Code First Database Initializer Strategy

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gil Fink
Technical Lead sparXys
Israel Israel
Gil Fink is a web development expert and ASP.Net/IIS Microsoft MVP. He is the founder and owner of sparXys. He is currently consulting for various enterprises and companies, where he helps to develop Web and RIA-based solutions. He conducts lectures and workshops for individuals and enterprises who want to specialize in infrastructure and web development. He is also co-author of several Microsoft Official Courses (MOCs) and training kits, co-author of "Pro Single Page Application Development" book (Apress) and the founder of Front-End.IL Meetup. You can read his publications at his website: http://www.gilfink.net
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 PinmemberYgor Thomaz13-Mar-12 15:33 
QuestionThanks, and a question PinmemberMember 456543314-Feb-12 4:39 
AnswerRe: Thanks, and a question PinmemberGil Fink14-Feb-12 5:14 
GeneralRe: Thanks, and a question PinmemberMember 456543314-Feb-12 23:30 
GeneralMy vote of 2 PinmemberShimmy Weitzhandler17-Jan-12 20:56 
GeneralRe: My vote of 2 PinmemberGil Fink23-Jan-12 2:11 
GeneralRe: My vote of 2 [modified] PinmemberShimmy Weitzhandler23-Jan-12 2:24 
GeneralRe: My vote of 2 PinmemberGil Fink23-Jan-12 2:47 
QuestionUsing Database.SetInitializer in a WPF application? PinmemberClayer5-Dec-11 12:38 
AnswerRe: Using Database.SetInitializer in a WPF application? PinmemberGil Fink6-Dec-11 3:06 
Hi,
 
Try putting it in the App class of the WPF application. For example:
public partial class App : Application 
{ 
    static App() 
    { 
       Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyContext>());
    }
}  

GeneralRe: Using Database.SetInitializer in a WPF application? [modified] PinmemberClayer6-Dec-11 11:49 
GeneralGood article PinmemberJV999931-May-11 21:10 
GeneralRe: Good article PinmemberGil Fink3-Jun-11 4:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 30 May 2011
Article Copyright 2011 by Gil Fink
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid