How to access sql file from Seed() in EF?

Question

I have the following:

  string sproc = AppDomain.CurrentDomain.BaseDirectory + "/Stored Procedures/new_message.sql";
  context.Database.ExecuteSqlCommand(sproc);

However the sproc path is invalid for some reason. What is the proper way to access files in your project directory from the Seed Method of EF code-first migrations?

Are you intentionally giving path to `ExecuteSqlCommand` method? — Adil Mammadov, Aug 26 '16 at 05:42
If there were a method where you can pass a filename you can expect the name to be *ExecuteSqlComandFromFile*. But it is not and therefore you had to pass the command itself (read the file content and pass that content to the method) — Sir Rufo, Aug 26 '16 at 06:04
Possible duplicate of [Executing a SQL script stored as a resource](https://stackoverflow.com/questions/1379195/executing-a-sql-script-stored-as-a-resource) — Michael Freidgeim, Dec 02 '17 at 06:21

marc_s · Accepted Answer · 2016-08-27T05:55:04.557

If those .sql files are stored as embedded resources in your assembly, then you can do something like this:

Assembly asy = Assembly.GetExecutingAssembly();

Stream stm = asy.GetManifestResourceStream("yourAssembly.Stored_Procedures.new_message.sql");

if (stm != null)
{
    string sql = new StreamReader(stm).ReadToEnd();
    // now you have the SQL statements which you can execute 
    context.Database.ExecuteSqlCommand(sql);
}

You may use this to determine the name of the resources from Seed() if needed:

Assembly asy = Assembly.GetExecutingAssembly();
string[] names = asy.GetManifestResourceNames();
throw new Exception(string.Join("", names));

How to access sql file from Seed() in EF?

1 Answers1