The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
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 -
public interface IDatabaseInitializer<in TContext> where TContext :
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)
using (new TransactionScope(TransactionScopeOption.Suppress))
dbExists = context.Database.Exists();
"EXEC sp_MSforeachtable @command1 = \"DROP TABLE ?\"");
var dbCreationScript = ((IObjectContextAdapter)
throw new ApplicationException("No database instance")
protected virtual void Seed(Context context)
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:
connectionString="Data Source=Server Name;Initial Catalog=Database Name;
Persist Security Info=True;User ID=Username;
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()
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.