Code First Migrations and Stored Procedures

asp.net-mvc code-first entity-framework sql-server

Question

I have just created a database and done my first migration (just a simple table add). Now I want to add some stored procedures which I have just added by writing the sql and executing it in Management Studio. But I would like to include these stored procedures if possible in a migration so that they are saved and I can run an Up or Down method against them. Is this possible and if so what syntax needs to be used? Or will I just have to add/edit/remove them using Management Studio?

1
29
1/3/2013 12:59:36 PM

Popular Answer

I've done this like so...

In the current migration class -

public partial class MyMigration : DbMigration
{
    public override void Up()
    {
        ... other table creation logic

        // This command executes the SQL you have written
        // to create the stored procedures
        Sql(InstallScript);

        // or, to alter stored procedures
        Sql(AlterScript);
    }

    public override void Down()
    {
        ... other table removal logic

        // This command executes the SQL you have written
        // to drop the stored procedures
        Sql(UninstallScript);

        // or, to rollback stored procedures
        Sql(RollbackScript);
    }

    private const string InstallScript = @"
        CREATE PROCEDURE [dbo].[MyProcedure]
        ... SP logic here ...
    ";

    private const string UninstallScript = @"
        DROP PROCEDURE [dbo].[MyProcedure];
    ";

    // or for alters
    private const string AlterScript = @"
        ALTER PROCEDURE [dbo].[AnotherProcedure]
        ... Newer SP logic here ...
    ";

    private const string RollbackScript = @"
        ALTER PROCEDURE [dbo].[AnotherProcedure]
        ... Previous / Old SP logic here ...
    ";
}
26
6/5/2014 2:38:25 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow