实体框架迁移过程中读入数据库（select查询） [英] Read database during an entity framework migration (select query)

问题描述

我知道我可以使用的Sql 方法的迁移过程中更新数据，它可以在纯SQL来表达简单的事情的伟大工程。

我也知道我可以使用种子方式，但感觉就像一个黑客（我想写的代码必须执行一次，当执行迁移）。

在我目前的情况下，我需要从一个列剥离HTML标记，并写在添加了一个新列移民。我已经有一个不正是一个C＃方法。我想要做的，是迭代在C＃中的每一行，并为每一行，将更新相应的HTML剥离文本行的SQL语句。

更一般地，我认为在迁移过程中能够读取C＃中的数据库可以在很多情况下派上用场。移民事务中这样做将是完美的，但我需要检索实体框架内部使用的迁移SQL连接。

到目前为止，我已经找到没有办法执行返回结果的SQL查询。这可能吗？

解决方案

我更喜欢也超过了种子法变换迁移文件中的数据。

更新数据不应该是一个问题，你可以做，如下面的代码行，你也可以通过从C＃中的参数在SQL字符串：

$ b $

  SQL（UPDATE表名SET MyValueInMinutes = -DATEDIFF（分，CURRENTTIME，0） p $ p> 
 
 
 由数据读取问题！在现实中你将永远不会需要处理C＃中的数据改造的探讨，通常你创建的SQL Server的一切（存储过程和函数和SQL stametents），你可以只是在时间点叫他们从迁移文件时，你需要他们或者当数据准备trasformed。但我认为你不是一个数据库程序员，那你为什么试图处理在C＃中的数据。
 
 
 
下面是一个例子，如何来遍历行，我将寻找一个给定的文本，并找到为您免费提供AnyText '+计数器。
  CREATE PROCEDURE sp_FindFreeName 
 @toBeFindName为nvarchar（MAX）OUT 
 AS 
 BEGIN 
 DECLARE @LoopCounter INTEGER 
设置@LoopCounter = 1 
 WHILE EXISTS（SELECT @toBeFindName FROM dbo.MyTable其中name = @toBeFindName）
 BEGIN 
设置@ toBeFindName ='AnyText'，@LoopCounter）
设置@LoopCounter = @LoopCounter + 1 
端
端
  
然后就可以称为形式C＃：
  VAR cSharpName =的String.Empty; 
 SQL（EXEC sp_FindFreeName @toBeFindName = @+ cSharpName +输出）; 
  
其中从C＃给出cSharpName。
 
 
 
第三，为什么这样做，你也可以编写自己的代码首先迁移操作。你必须定义SqlServerMigrationSqlGenerator如罗文博客中所述：
 
 
 
的 https://romiller.com/2013/02/27/ef6-writing-your-own-code-first-migration-operations/  
 
 
 
  结论：的 
 
 
 
如果你真的想在迁移文件，那么你必须使用例如读取数据：SqlDataReader的，你必须同时读取的App.config的连接字符串
 
 
 
在大多数用例可以执行SQL Server内部的一切。刚刚创建自己的数据tranfsormation SQL脚本，并从所需位置执行它们。
 
 
 
您可以使用扩展和SqlServerMigrationSqlGenerator做，在那么一个更清洁的方式DataReader的。
 
I know I can use the Sql method to update data during a migration, and it works great for simple things that can be expressed in pure SQL.

I also know I could use a Seed method but that would feel like a hack (the code I want to write has to be executed once, when the migration is executed).

In my current case, I need to strip HTML tags from a column, and write that to a new column added in the migration. I already have a C# method that does exactly that. What I want to do, is iterate over each row in C#, and generate a SQL statement for each row that will update the row with the corresponding HTML-stripped text.

More generally, I think being able to read the database in C# during a migration could be handy in a lot of situations. Doing so within the migration transaction would be perfect, but for that I need to retrieve the SQL connection that Entity Framework internally uses for the migration.

So far I've found no way to execute a SQL query that returns results. Is this possible?
解决方案
I prefer also to transform the data in migration files over the Seed method.

Updating the data should not be a problem, you can just do as shown in the line code below and  you can also passing the parameters from C# in the SQL string:
Sql("UPDATE TableName SET MyValueInMinutes= -DATEDIFF(MINUTE, CurrentTime, 0)";
The problem by data reading! In reality you will never need to handle the data tranformation in C#, normally you create everything in SQL server (Stored procedure and functions and SQL stametents) and you can just call them from the migration file at the time point when you need them or when the data are ready to be trasformed. But I think you are not a database programmer, that why you are trying to handle the data in C#.

Here is an example how to iterate over the rows and I will search for a given text and find for you a free available 'AnyText'+Counter.
CREATE PROCEDURE sp_FindFreeName  
@toBeFindName nvarchar(MAX) OUT  
AS 
BEGIN 
DECLARE @LoopCounter INTEGER 
SET @LoopCounter = 1 
WHILE EXISTS (SELECT @toBeFindName FROM dbo.MyTable WHERE Name = @toBeFindName) 
  BEGIN 
      SET   @toBeFindName = 'AnyText', @LoopCounter) 
      SET @LoopCounter = @LoopCounter + 1 
  END 
END 
Then you can called form C#:
var cSharpName = string.Empty;
Sql("exec sp_FindFreeName @toBeFindName=@"+ cSharpName +" OUTPUT");
where cSharpName given from C#.

The third why to do that you can also write your own Code First Migration Operations. You have to define your SqlServerMigrationSqlGenerator as described in Rowan blog:

https://romiller.com/2013/02/27/ef6-writing-your-own-code-first-migration-operations/

Conclusion:

If you really want to read the data in the migration files then you have to use for example: SqlDataReader and you have to read also the connection string from App.config.

In most use cases you can do everything inside the SQL Server. Just create your data tranfsormation SQL scripts and execute them from the desired location.

You can use Extensions and SqlServerMigrationSqlGenerator to do that in a cleaner way then the DataReader.

                        
这篇关于实体框架迁移过程中读入数据库（select查询）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！