Generating Seed Code from Existing Database in ASP.NET Mvc

Generating seed code from existing database in ASP.NET MVC

Another way of seeding data is to run it as sql in an Up migration.

I have code that will read a sql file and run it

using System;
using System.Data.Entity.Migrations;
using System.IO;

public partial class InsertStandingData : DbMigration
{
    public override void Up()
    {
        var baseDir = AppDomain.CurrentDomain
                               .BaseDirectory
                               .Replace("\\bin", string.Empty) + "\\Data\\Sql Scripts";

        Sql(File.ReadAllText(baseDir + "\\StandingData.sql"));
    }

    public override void Down()
    {
        //Add delete sql here
    }
}

So if your ETL generates sql for you then you could use that technique.

The advantages of doing it in the Up method are

1. It will be quicker than doing it using AddOrUpdate because
   AddOrUpdate queries the database each time it is called to get any
   already existing entity.
2. You are normally going from a known state (e.g. empty tables) so you probably
   don't need to check whether data exists already. NB to ensure this
   then you should delete the data in the Down method so that you can
   tear all the way down and back up again.
3. The Up method does not run every time the application starts.

The Seed method provides convenience - and it has the advantage (!?) that it runs every time the application starts

But if you prefer to run the sql from there use ExecuteSqlCommand instead of Sql:

string baseDir = AppDomain.CurrentDomain.BaseDirectory.Replace("\\bin", string.Empty) 
              + "\\Data\\Sql Scripts";
string path = Path.Combine(baseDir, "StandingData");
foreach (string file in Directory.GetFiles(path, "*.sql"))
{
    context.Database.ExecuteSqlCommand(File.ReadAllText(file));
}

References:

Best way to incrementally seed data

Preparing for database deployment

Database Initializer and Migrations Seed Methods

MVC 5 Seed Users and Roles

Here is example of usual Seed approach:

protected override void Seed(SecurityModule.DataContexts.IdentityDb context)
{
    if (!context.Roles.Any(r => r.Name == "AppAdmin"))
    {
        var store = new RoleStore<IdentityRole>(context);
        var manager = new RoleManager<IdentityRole>(store);
        var role = new IdentityRole { Name = "AppAdmin" };

        manager.Create(role);
    }

    if (!context.Users.Any(u => u.UserName == "founder"))
    {
        var store = new UserStore<ApplicationUser>(context);
        var manager = new UserManager<ApplicationUser>(store);
        var user = new ApplicationUser {UserName = "founder"};

        manager.Create(user, "ChangeItAsap!");
        manager.AddToRole(user.Id, "AppAdmin");
    }
}

I used package-manager "update-database". DB and all tables were created and seeded with data.

How can I get my database to seed using Entity Framework CodeFirst?

This is what my DbContext classes all look like and they seed just fine:

public class MyDbContext : DbContext
{
    public DbSet<MyClass> MyClasses { get; set; }

    protected override void OnModelCreating (DbModelBuilder modelBuilder)
    {
        base.OnModelCreating (modelBuilder);
        modelBuilder.Conventions.Remove<System.Data.Entity.ModelConfiguration.Conventions.PluralizingTableNameConvention> ();

        // Add any configuration or mapping stuff here
    }

    public void Seed (MyDbContext Context)
    {
        #if DEBUG
        // Create my debug (testing) objects here
        var TestMyClass = new MyClass () { ... };
        Context.MyClasses.Add (TestMyClass);
        #endif

        // Normal seeding goes here

        Context.SaveChanges ();
    }

    public class DropCreateIfChangeInitializer : DropCreateDatabaseIfModelChanges<MyDbContext>
    {
        protected override void Seed (MyDbContext context)
        {
            context.Seed (context);

            base.Seed (context);
        }
    }

    public class CreateInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        protected override void Seed (MyDbContext context)
        {
            context.Seed (context);

            base.Seed (context);
        }
    }

    static MyDbContext ()
    {
        #if DEBUG
        Database.SetInitializer<MyDbContext> (new DropCreateIfChangeInitializer ());
        #else
        Database.SetInitializer<MyDbContext> (new CreateInitializer ());
        #endif
    }
}

I have used this pattern a few times and it has worked out very well for me.

How to seed data when using Model First approach?

You can have something like this:

public class MySeedData : DropCreateDatabaseIfModelChanges<YourDataBaseContextClass>
{
    protected override void Seed(YourDataBaseContextClass context)
    {  
       // Create objects here and add them to your context DBSets...

    }
}

public class YourDataBaseContextClass : DbContext
{

}

Then, within Application_Start() you call:

Database.SetInitializer(new MySeedData());

In your case, you could try creating DbSets (using your model first classes) manually and try to plug it using the code above. It's kind of a mix of Model First + Code First.

public class FourthCoffeeWebContext : DbContext
{
    public DbSet<Category> Categories { get; set; }
    public DbSet<Product> Products { get; set; }
}

Adding to this: CreateDatabaseIfNotExists<(Of <(<'TContext>)>)>

EF Code First Generate C# Seed Method from Database

In the package management console, enter the following command:

enable-migrations

It will enable the configuration part of EF and includes a Seed method. If this is not enough there are more options you can explore, like add-migration to add a custom migration.

See this post for details:
http://msdn.microsoft.com/en-us/data/jj554735.aspx or
http://www.asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application

When using a Code Based Migration, you can query for data, remove it and re-insert it any way you like.

---

Related Topics