Click here to Skip to main content
15,867,756 members
Articles / Web Development / ASP.NET

Creating a Code First Database Initializer Strategy

Rate me:
Please Sign up or sign in to vote.
4.89/5 (7 votes)
30 May 2011CPOL3 min read 61.3K   7   13
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:

C#
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:

C#
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:

XML
<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:

C#
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.

This article was originally posted at http://feeds.feedburner.com/GilFinkBlog

License

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


Written By
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

Comments and Discussions

 
GeneralMy vote of 5 Pin
Ygor Thomaz13-Mar-12 14:33
Ygor Thomaz13-Mar-12 14:33 
QuestionThanks, and a question Pin
Member 456543314-Feb-12 3:39
Member 456543314-Feb-12 3:39 
AnswerRe: Thanks, and a question Pin
Gil Fink14-Feb-12 4:14
Gil Fink14-Feb-12 4:14 
GeneralRe: Thanks, and a question Pin
Member 456543314-Feb-12 22:30
Member 456543314-Feb-12 22:30 
GeneralMy vote of 2 Pin
Shimmy Weitzhandler17-Jan-12 19:56
Shimmy Weitzhandler17-Jan-12 19:56 
GeneralRe: My vote of 2 Pin
Gil Fink23-Jan-12 1:11
Gil Fink23-Jan-12 1:11 
GeneralRe: My vote of 2 Pin
Shimmy Weitzhandler23-Jan-12 1:24
Shimmy Weitzhandler23-Jan-12 1:24 
GeneralRe: My vote of 2 Pin
Gil Fink23-Jan-12 1:47
Gil Fink23-Jan-12 1:47 
QuestionUsing Database.SetInitializer in a WPF application? Pin
Clayer5-Dec-11 11:38
Clayer5-Dec-11 11:38 
Hi!
Thanks for your article. I'm using Entity Framework and "Code First" in a WPF application and it works great except that I find it impossible to get the application to start the GUI when I run the Database.SetInitializer in my WPF application. I'll get a VerificationException when the GUI is generated from the XAML code. I have tried using Database.SetInitializer in the constructor and in the WPF Load event, as I need the database from start for Data Bindings in the GUI.

Have you succeed using Database.SetInitializer in a WPF application?
I can't find any information about this subject on the Internet. All articles, examples and tutorials regarding Entity Framework "Code First" are based on ASP applications.

Any advise?

Thanks!
/ Kim, Devion AB, Sweden
AnswerRe: Using Database.SetInitializer in a WPF application? Pin
Gil Fink6-Dec-11 2:06
Gil Fink6-Dec-11 2:06 
GeneralRe: Using Database.SetInitializer in a WPF application? Pin
Clayer6-Dec-11 10:49
Clayer6-Dec-11 10:49 
GeneralGood article Pin
JV999931-May-11 20:10
professionalJV999931-May-11 20:10 
GeneralRe: Good article Pin
Gil Fink3-Jun-11 3:48
Gil Fink3-Jun-11 3:48 

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

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