none
Create Database + Execute SQL Scripts for setup data?

    Question

  • 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.

    Thanks

    Ray

    Friday, January 28, 2011 8:21 PM

All replies

  • Hi Ray,

    This post shows our suggested approach; http://romiller.com/2010/07/31/ef-ctp4-tips-tricks-running-additional-ddl/

    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
    ~Rowan

    Tuesday, February 01, 2011 6:49 PM
    Moderator
  • 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.

    Thanks.

     

     

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

    Wednesday, February 02, 2011 6:18 PM
  • http://weblogs.asp.net/scottgu/archive/2010/07/16/code-first-development-with-entity-framework-4.aspx

    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)
        {
          context.Database.SqlCommand(File.ReadAllText("MyCustomScript.sql"));
        }
      }
    }
    

     

    ~Rowan

    Friday, February 04, 2011 9:33 PM
    Moderator