Create Database + Execute SQL Scripts for setup data?


  • I vaguely remember seeing a post but can't find it anymore -- I'm using EF4 CTP5 Code-First.

    In my DbContext after I create the database, I want to also execute a number of SQL scripts. These contain my stored procedures, user functions, and setup data.

    Obviously, for production, we'd have our own build scripts that will automate all of this, but I want individual developers who will be use to pulling down the latest from source control and hitting F5... any recommendations on how to do this?

    Or, should I just 'hard-code' the logic and explicitly? e.g.

               context.Database.SqlCommand("exec myPath\myscript.sql" )??? something like this?

    i.e. something similar to ADO.Net's Command.ExecuteNonQuery? Code sample would be appreciated.



    Friday, January 28, 2011 8:21 PM

All replies

  • Hi Ray,

    This post shows our suggested approach;

    The post uses the CTP4 API surface and there have been a couple of renames in CTP5:

    • Database => System.Data.Entity.Database.DbDatabase
    • RecreateDatabaseIfModelChanges => System.Data.Entity.Database.DropCreateDatabaseIfModelChanges
    • AlwaysRecreateDatabase => System.Data.Entity.Database.DropCreateDatabaseAlways
    • CreateDatabaseIfNotExists => System.Data.Entity.Database.CreateDatabaseIfNotExists

    Tuesday, February 01, 2011 6:49 PM
  • I took a look at it, but it seems more complicated (esp with CTP4/5 naming conventions).

    Do you have a sample that is a little more simple? Something like below? I just don't know how to call something equivalent.




    public static void CreateDatabase(string connectionString)
      MyCatalog context = new MyCatalog(connectionString);
      if (context.Database.Exists())
      context.Database.ExecuteStoreCommand("myscript.sql", "my path location"); 

    Wednesday, February 02, 2011 6:18 PM

    Scroll down to the part where Scott explains how to create an Initializer class. You basically need to override the Seed() method and fill in your data (by creating the objects) or you use DbContext.Database.SqlCommand(). 

    Wednesday, February 02, 2011 6:49 PM
  • Hi,

    Here is a code listing that shows running a custom sql script after the database schema has been created:

    using System.Data.Entity;
    using System.Data.Entity.Database;
    using System.IO;
    namespace ConsoleApplication28
      class Program
        static void Main(string[] args)
          DbDatabase.SetInitializer<PersonContext>(new MyCustomInitializer());
          using (var ctx = new PersonContext())
            // Perform Data Access  
      public class PersonContext : DbContext
        public DbSet<Person> People { get; set; }
      public class Person
        public int PersonId { get; set; }
        public string Name { get; set; }
      public class MyCustomInitializer : DropCreateDatabaseIfModelChanges<PersonContext>
        protected override void Seed(PersonContext context)



    Friday, February 04, 2011 9:33 PM