Shawn Weisfeld

I find when I talk to myself nobody listens. - Shawn Weisfeld
posts - 356, comments - 173, trackbacks - 34

My Links

News

The views expressed in this blog are mine and mine alone, not that of my employer, Microsoft, or anyone else’s. No warrantee is given for the quality of any material on this site.

Archives

Post Categories

Entity Framework Code First: Executing SQL files on database creation

 

Many times you want to seed data into your DB but don’t want to put all that seed data into your code files, or perhaps you want to add stored procedures, triggers or make other database changes after code first creates your database. While I would argue that the latter impacts one of the “goals” of EF Code First (i.e. centralizing your business logic in one spot), I can see where it might have value. So lets see how we can achieve this mission.

Lets start by creating our data objects. I am going to create a simple object “Tweet”:

image

 

Great now Lets wire it up to our DbContext:

image

 

You will see that my “SetInitializer” creates a special ContextInitializer. I borrowed this idea from Julie Lerman’s blog post on seeding data (http://thedatafarm.com/blog/data-access/seeding-a-database-with-ef4-code-first/). Lets take a look at the code in that guy:

image

 

As you can see we inherit from the “CreateDatabaseIfNotExists<T>” object. This means that EF Code First will only touch database schema if the database is totally dropped. Preventing any unintended accidental database modifications. In the seed method you see that I query the file system for all files that end in .sql, sort them alphabetically (so we can control the order that they are applied to our new database). Then one by one execute them against the context. Great now I can write seed files, stored procedures, triggers or whatever else I want in standard .sql files, and Code First will take care of applying them when it creates the database.

Here is an example of some .sql files for our little Twitter clone:

 image

Nothing all that interesting, except for the naming convention. By placing the numbers in front I can control the order that they get executed against the database.

 

One last note, I think using EF Code First to “generate” your database for dev/test/qa environments is a great idea. This allows all development and testing to work against a “known”, “common” database state that is easily re-creatable. I am not quite sold on using it to generate the db in production. IMHO when it comes time to migrate to production you can use one of the many database schema diff tools (VS Data Dude, Red Gate, etc.) and prepare delta scripts to “Alter” the production DB to match the test/qa server, and tightly control what gets moved to production and when.

Print | posted on Friday, July 15, 2011 8:33 AM |

Feedback

Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

One other thing you can do. Create a table with one row and column that holds the database version number. As you execute each script, save the number of that script in the database itself. Only run the scripts after the current version. This lets you automatically deploy migrations with the code.

One big drawback to this approach is that the code will have to have permissions to run DDL. This violates the law of least privilege, and opens up a bigger security hole should SQL injection or buffer overruns be a problem in the code.
7/15/2011 10:03 AM | Michael Perry
Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

so does that mean one file can only contains one sql command?
8/24/2011 2:04 AM | newb
Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

Nice, I was looking for a way to execute some random SQL after the DB has been created. I also plan on using code first DB generation for a production project (initial DB gen only) we'll see how that goes. For data generation I actually use code rather than SQL files though, this way it's easy to generate a lot of data using for(;;) loops, this way I can get a better handle on how the application might perform against large quantities of data.

Shane
9/20/2011 11:51 PM | Shane Holder
Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

NEWB - yes only one SQL Statement per file the way it is currently configured.
9/21/2011 7:34 AM | shawnweisfeld
Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

It's quite easy to update this to support multiple commands per file using the standard "GO" batch separator:

static IEnumerable<string> ReadAllCommands(string path)
{
StringBuilder sb = null;
foreach (string line in File.ReadLines(path))
{
if (string.Equals(line, "GO", StringComparison.OrdinalIgnoreCase))
{
if (null != sb && 0 != sb.Length)
{
string item = sb.ToString();
if (!string.IsNullOrWhiteSpace(item)) yield return item;
sb = null;
}
}
else
{
if (null == sb) sb = new StringBuilder();
sb.AppendLine(line);
}
}
if (null != sb && 0 != sb.Length)
{
string item = sb.ToString();
if (!string.IsNullOrWhiteSpace(item)) yield return item;
}
}

var sqlCommands = Directory
.EnumerateFiles(AppDomain.CurrentDomain.BaseDirectory, "*.sql")
.OrderBy(x => x)
.SelectMany(ReadAllCommands);

foreach (string command in sqlCommands)
{
context.Database.ExecuteSqlCommand(command);
}
11/21/2011 11:51 AM | Richard
Gravatar

# re: Entity Framework Code First: Executing SQL files on database creation

Thanks for posting this. Very Helpful
2/15/2012 5:15 PM | Ben

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 6 and 7 and type the answer here:

Powered by: