EF 6代码优先使用自定义存储过程

I´m creating a MVC 5 app with a Code-First approach, but I also created some stored procedures on the SQL Server database, is there a way to also generate these stored procedures in c# when the database is created, maybe by executing a sql script, if so where should I do this?

我正在使用Code-First方法创建一个MVC 5应用程序，但我还在SQL Server数据库上创建了一些存储过程，有没有办法在创建数据库时在c＃中生成这些存储过程，可能是通过执行sql脚本，如果是这样我应该在哪里这样做？

3 个解决方案

#1

Late answer, but maybe someone will get an answer for such question

迟到的答案，但也许有人会得到这个问题的答案

I had plenty of views and functions and stored procedures to deal with in my project, and the solution I used was as follows:

我有很多视图，函数和存储过程来处理我的项目，我使用的解决方案如下：

Create an sql file in your project to drop all views and functions and procedures you have if they were exists,call it drop.sql for example.
在项目中创建一个sql文件，以删除它们存在时的所有视图和函数以及过程，例如将其命名为drop.sql。
Create for each view and function and stored procedure a separate sql file in your project.
为每个视图和函数以及存储过程创建项目中的单独sql文件。
Mark all the sql file as Embedded Resource, by right click on the file then property then Build Action , choose Embedded Resource

YourFile.sql => right click => Properties => Build Action, choose Embedded Resource

YourFile.sql =>右键单击=> Properties => Build Action，选择Embedded Resource
In the migration seed function, use the ExecuteSqlCommand, you need a method to allow you to read these files and below all the code required.
在迁移种子函数中，使用ExecuteSqlCommand，您需要一种方法来允许您读取这些文件以及所需的所有代码。

drop.sql structure

drop.sql结构

-- your views
if object_id('dbo.[YourViewName1]') is not null
    drop view dbo.[YourViewName1]
if object_id('dbo.[YourViewName2]') is not null
    drop view dbo.[YourViewName2]
-- your functions 
if object_id('dbo.[Function1]') is not null
    drop function dbo.[Function1]
if object_id('dbo.[Function2]') is not null
    drop function dbo.[Function2]
-- your procedures
if object_id('dbo.[Procedure1]') is not null
    drop procedure dbo.[Procedure1]
if object_id('dbo.[Procedure2]') is not null
    drop procedure dbo.[Procedure2]

view.sql or function.sql or procedure.sql structure

view.sql或function.sql或procedure.sql结构

create view View1 
as
  select Field1,Field2,...Fieldn
  from Table1
  inner join Table2 on Id1 = FId2  
  inner join TableN on IdI = IdJ

Migration seed method

迁移种子方法

I assumed that you created all the sql files inside Sql folder inside Migrations folder in your project

我假设您在项目的Migrations文件夹中的Sql文件夹中创建了所有sql文件

protected override void Seed(YourContext context)
{
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.drop.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.view1.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.view2.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.function1.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.function2.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.procedure1.sql"));
    context.Database
            .ExecuteSqlCommand(Load("YourProject.Migrations.Sql.procedure2.sql"));
}

Finally the Load method

最后是Load方法

private static string Load(string name)
{
    var assembly = Assembly.GetExecutingAssembly();

    using (Stream stream = assembly.GetManifestResourceStream(name))
    using (StreamReader reader = new StreamReader(stream))
    {
        string result = reader.ReadToEnd();
        return result;
    }
}

The benefit of this solution is it will run each time, and you will make sure that if there was any problem ( for example ,after a while you changed a field name or you removed a table that was used inside a view or function or procedure without remembering that you have to update your procedures, you will get error and you can fix if you enabled automatic migrations).

此解决方案的好处是它每次都会运行，并且您将确保如果有任何问题（例如，一段时间后您更改了字段名称或删除了在视图或函数或过程中使用的表如果不记得必须更新程序，则会出现错误，如果启用了自动迁移，则可以修复。

Hope this will help you

希望对你有帮助

#2

I would use code migrations.

我会使用代码迁移。

From your Nuget Package Manager you can set up a blank migration by typing

在您的Nuget Package Manager中，您可以通过键入来设置空白迁移

add-migration AddMyStoredProcedure

This should generate an empty class like so

这应该生成一个像这样的空类

public partial class AddMyStoredProcedure : DbMigration
{
    public override void Up()
    {
    }

    public override void Down()
    {
    }
}

All you need to do is add your stored procedure like so (remember to drop the stored procedure in the Down method in case you need to roll back the migration in the future).

您需要做的就是添加您的存储过程（请记住在Down方法中删除存储过程，以防您以后需要回滚迁移）。

    public partial class AddMyStoredProcedure : DbMigration
{
    public override void Up()
    {
        Sql(@"
            CREATE PROCEDURE dbo.GetMyAddress
            AS
            SELECT * FROM Person.Address");
    }

    public override void Down()
    {
        Sql("DROP PROCEDURE dbo.GetMyAddress");
    }
}

Finally update your database

最后更新您的数据库

update-database

#3

You will probably need to use Migrations to handle it. A good looking solution can be found https://*.com/a/15171900/119262. using resources but I am sure you could just read the text out of your .sql files in the same manner if you didnt want to go down the resource path.

您可能需要使用迁移来处理它。可以找到一个好看的解决方案https://*.com/a/15171900/119262。使用资源，但我相信如果你不想沿着资源路径走，你可以用同样的方式读取.sql文件中的文本。

#1