EF 6 code-first with custom stored procedure

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?

回答1:

I would use code migrations.

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

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

    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

回答2:

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.
Create for each view and function and stored procedure a separate sql file in your project.
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
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.

drop.sql structure

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

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

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

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

回答3:

You will probably need to use Migrations to handle it. A good looking solution can be found https://stackoverflow.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.