How can I run a series of sql scripts with EF 4.3 Migrations?
I'm trying to do something like this in a method Seed
:
foreach (string sqlFile in Directory.GetFiles(Path.Combine(Directory.GetCurrentDirectory(), @"SqlScripts")))
{
string sqlText = File.OpenText(sqlFile).ReadToEnd();
context.Database.ExecuteSqlCommand(sqlText);
}
When I run Update-Database, I get the error:
Could not find a part of the path 'C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\SqlScripts'.
So the explicitly updated database starts from the VS bin directory, not from the project directory. Without hard-coding the project path (multiple developers work on it) how do I get the assembly path containing DbContext
?
source to share
I wanted to do something like this, but I always found Seed to be a little lackluster considering the Migrations point is the database version, while the Seed command ignores version control - so it can easily shoot in the foot. The preferred result is data movement in Migrations. So here we go:
( Full source on GitHub with several other Migrations commands .)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.IO;
using System.Text.RegularExpressions;
public abstract class ExpandedDbMigration
: System.Data.Entity.Migrations.DbMigration
{
public void SqlFile(string path)
{
var cleanAppDir = new Regex(@"\\bin.+");
var dir = AppDomain.CurrentDomain.BaseDirectory;
dir = cleanAppDir.Replace(dir, "") + @"\";
var sql = File.ReadAllLines(dir + path);
string[] ignore = new string[]
{
"GO", // Migrations doesn't support GO
"/*", // Migrations might not support comments
"print" // Migrations might not support print
};
foreach (var line in sql)
{
if (ignore.Any(ig => line.StartsWith(ig)))
continue;
Sql(line);
}
}
}
AppDomain
... gets you the correct directory for your model project, instead of pointing to Visual Studio like other methods.
Regex cleans up what is returned if it is run from the bin folder.
ReadAllLines reads in your Sql script; in this case, it is stored in \ Sql \ blah.sql, but you can put it somewhere else.
The foreach / ignore function prevents commands like "GO" from running, which will log out when used in Migrations, and often quit tools like Sql Server Management Studio Generate Scripts.
Finally, foreach unloads each line from Migrations.
Using:
using Brass9.Data.Entity.Migrations;
public partial class FillZips : ExpandedDbMigration
{
public override void Up()
{
SqlFile(@"Migrations\Sql\2013-08-15 FillTable.sql");
}
Notice the change in inheritance from DbMigration to ExpandedDbMigration.
Replace the argument to SqlFile with whatever is the path to the sql file inside your migration enabled project.
source to share