none
Seed data from sql files using EF 4.3 migration RRS feed

  • Question

  • I'm trying to insert some sample data (Seed data) using EF 4.3 Migration.


    The problem is I want to seed data from sql files. But when I run Update-Database cmdlet, I got error like 

    Could not find a part of the path 'c:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\DatabaseScripts\'
    

    Is there anyway to set the working directory to current project folder when I run Update-Database from my powershell console.

    Finally, here is the code for seeding data.

    internal sealed class Configuration : DbMigrationsConfiguration<DbContext>
        {
            public Configuration()
            {
                AutomaticMigrationsEnabled = true;
            }
    
            protected override void Seed(DbContext context)
            {
                //  This method will be called after migrating to the latest version.
    
                //  You can use the DbSet<T>.AddOrUpdate() helper extension method 
                //  to avoid creating duplicate seed data. E.g.
                //
                //    context.People.AddOrUpdate(
                //      p => p.FullName,
                //      new Person { FullName = "Andrew Peters" },
                //      new Person { FullName = "Brice Lambson" },
                //      new Person { FullName = "Rowan Miller" }
                //    );
                //
    
                var dirDatabaseScripts = Path.Combine(Directory.GetCurrentDirectory(), "DatabaseScripts");
                foreach (var filePath in Directory.EnumerateFiles(dirDatabaseScripts, "*.sql")) {
                    context.Database.ExecuteSqlCommand(File.ReadAllText(filePath));
                }
            }
        }


    Thanks in Advance.



    http://www.thinkwith.net

    Wednesday, February 22, 2012 3:00 AM

Answers

  • I tried with XML file and it works. I set the "Copy To Output Directory" property of that XML file to "Always".

    Here is my config file.

     protected override void Seed(CompanyDbContext context)
            {
                var xml = XElement.Load(AppDomain.CurrentDomain.BaseDirectory + "/SeedsData.xml");
                var people = xml.Descendants("People");
                foreach (var person in people.Descendants("Person")) {
                    context.People.Add(new Person() { FullName = person.Value});
                }
               
            }

    XML

    <?xml version="1.0" encoding="utf-8" ?>
    <Company>
      <People>
        <Person>
          <FullName>Elena</FullName>
        </Person>
        <Person>
          <FullName>Tifa</FullName>
        </Person>
      </People>
    </Company>
    

    I will check with SQL file. 


    Michael Sync: blog: http://michaelsync.net

    Wednesday, February 22, 2012 3:49 AM
  • Using SQL works for me as well. 

    SQL File

    INSERT INTO People (FullName) VALUES ('Elena')
     
    INSERT INTO People (FullName) VALUES ('Tifa')

    Config

    protected override void Seed(CompanyDbContext context)
            {            
                foreach (var filePath in Directory.EnumerateFiles(AppDomain.CurrentDomain.BaseDirectory, "*.sql")) {
                    context.Database.ExecuteSqlCommand(File.ReadAllText(filePath));
                }
     
                //var xml = XElement.Load(AppDomain.CurrentDomain.BaseDirectory + "/SeedsData.xml");
                //var people = xml.Descendants("People");
                //foreach (var person in people.Descendants("Person")) {
                //    context.People.Add(new Person() { FullName = person.Value});
                //}
               
            }


    Michael Sync: blog: http://michaelsync.net

    Wednesday, February 22, 2012 3:56 AM

All replies

  • I tried with XML file and it works. I set the "Copy To Output Directory" property of that XML file to "Always".

    Here is my config file.

     protected override void Seed(CompanyDbContext context)
            {
                var xml = XElement.Load(AppDomain.CurrentDomain.BaseDirectory + "/SeedsData.xml");
                var people = xml.Descendants("People");
                foreach (var person in people.Descendants("Person")) {
                    context.People.Add(new Person() { FullName = person.Value});
                }
               
            }

    XML

    <?xml version="1.0" encoding="utf-8" ?>
    <Company>
      <People>
        <Person>
          <FullName>Elena</FullName>
        </Person>
        <Person>
          <FullName>Tifa</FullName>
        </Person>
      </People>
    </Company>
    

    I will check with SQL file. 


    Michael Sync: blog: http://michaelsync.net

    Wednesday, February 22, 2012 3:49 AM
  • Using SQL works for me as well. 

    SQL File

    INSERT INTO People (FullName) VALUES ('Elena')
     
    INSERT INTO People (FullName) VALUES ('Tifa')

    Config

    protected override void Seed(CompanyDbContext context)
            {            
                foreach (var filePath in Directory.EnumerateFiles(AppDomain.CurrentDomain.BaseDirectory, "*.sql")) {
                    context.Database.ExecuteSqlCommand(File.ReadAllText(filePath));
                }
     
                //var xml = XElement.Load(AppDomain.CurrentDomain.BaseDirectory + "/SeedsData.xml");
                //var people = xml.Descendants("People");
                //foreach (var person in people.Descendants("Person")) {
                //    context.People.Add(new Person() { FullName = person.Value});
                //}
               
            }


    Michael Sync: blog: http://michaelsync.net

    Wednesday, February 22, 2012 3:56 AM