Code First Migrations and Stored Procedures

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?

Answer 1

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 ...     "; }