Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Generating seed code from existing database in ASP.NET MVC

I wondered if anyone has encountered a similar challenge:

I have a database with some data that was ETL'ed (imported and transformed) in there from an Excel file. In my ASP.NET MVC web application I'm using Code First approach and dropping/creating every time database changes:

#if DEBUG
  Database.SetInitializer(new DropCreateDatabaseIfModelChanges<MyDataContext>());
#endif

However, since the data in the Database is lost, I have to ETL it again, which is annoying.

Since, the DB will be dropped only on model change, I will have to tweak my ETL anyway, I know that. But I'd rather change my DB seed code.

Does anyone know how to take the contents of the database and generate seed code, assuming that both Models and SQL Tables are up to date?

EDIT 1: I'm planning to use the auto-generated Configuration.cs, and its Seed method, and then use AddOrUpdate() method to add data into the database: Here is Microsoft's Tutorial on migrations (specifically the "Set up the Seed method" section).

like image 396
Arman Bimatov Avatar asked Oct 07 '13 14:10

Arman Bimatov


People also ask

What is seeding in MVC?

Seed data is data that you populate the database with at the time it is created. You use seeding to provide initial values for lookup lists, for demo purposes, proof of concepts etc.

What does reseed in database mean?

The reseed-database command enables you to reseed a passive database copy that is in a failed state and restore the copy (using the latest local Snapshot copy; an archived copy cannot be used) to a healthy state.


1 Answers

Lets say we have a simple database table with 3750 records in it;

| Id   | Age | FullName        |
|------|-----|-----------------|
| 1    | 50  | Michael Jackson |
| 2    | 42  | Elvis Presley   |
| 3    | 48  | Whitney Houston |
| ...  | ... | ...             |
| 3750 | 57  | Prince          |

We want to create this table in our database with using auto-generated Configuration.cs file and its Seed() method.

protected override void Seed(OurDbContainer context)
{
    context.GreatestSingers.AddOrUpdate(
            p => p.Id,
            new GreatestSinger { Id = 1, Age = 50, FullName = "Michael Jackson" },
            new GreatestSinger { Id = 2, Age = 42, FullName = "Elvis Presley" },
            new GreatestSinger { Id = 3, Age = 48, FullName = "Whitney Houston" }
            );
}

This is what you should do. 3750 times!

But you already have this data in your existing database table. So we can use this existing data to create Seed() codes.

With the help of SQL String Concatenation;

SELECT
CONCAT('new GreatestSinger { Id = ', Id ,', Age = ', Age ,', FullName = "', FullName ,'" },') 
FROM GreatestSinger

will give us all the code needed to create 3750 rows of data.

Just copy/paste it into Seed() method. And from Package Manager Console;

Add-Migration SeedDBwithSingersData

Update-Database
like image 76
Emre Bolat Avatar answered Sep 22 '22 03:09

Emre Bolat