从ASP.NET MVC中的现有数据库生成种子代码 [英] Generating seed code from existing database in ASP.NET MVC

问题描述

我想知道是否有人遇到过类似的挑战:

I wondered if anyone has encountered a similar challenge:

我有一个数据库，其中包含一些从Excel文件进​​行ETL(导入和转换)的数据.在我的ASP.NET MVC Web应用程序中，我使用代码优先"方法，并在每次数据库更改时都删除/创建:

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

但是，由于数据库中的数据丢失，因此我不得不再次ETL它，这很烦人.

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

既然如此，数据库将仅在模型更改时被删除，无论如何，我必须调整我的ETL，我知道.但我宁愿更改数据库种子代码.

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.

假设Model和SQL Table都是最新的，有谁知道如何获取数据库的内容并生成种子代码?

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?

我计划使用自动生成的Configuration.cs及其Seed方法，然后使用AddOrUpdate()方法将数据添加到数据库中:这是

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

推荐答案

播种数据的另一种方法是在Up迁移中将其作为sql运行.

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

我有可以读取并运行sql文件的代码

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
    }
}

因此，如果您的ETL为您生成了sql，则可以使用该技术.

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

在Up方法中执行此操作的优点是

The advantages of doing it in the Up method are

1. 这比使用AddOrUpdate更快，因为 AddOrUpdate每次调用数据库获取任何内容时都会查询数据库 已经存在的实体.
2. 您通常从已知状态(例如空表)出发，因此您可能 不需要检查数据是否已经存在.注意，要确保这一点 那么您应该删除Down方法中的数据，以便您可以 一直撕下来，然后又回来.
3. Up方法不会在每次应用程序启动时运行.

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.

Seed方法提供了便利-它具有(！?)优点，它在应用程序每次启动时都运行

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

但是，如果您希望从那里运行sql，请使用ExecuteSqlCommand而不是Sql:

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));
}

参考:

以最佳方式递增种子数据的方法

为数据库部署做准备

数据库初始化程序和迁移种子方法

这篇关于从ASP.NET MVC中的现有数据库生成种子代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！