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

Answer 2

namespace QuickProject.Migrations
{
    using System;
    using System.Data.Entity.Migrations;


public partial class CreateStoredProcedure_GellAllAgents : DbMigration
{
    public override void Up()
    {
        CreateStoredProcedure("dbo.GellAllAgents", c => new
        {
            DisplayLength = c.Int(10),
            DisplayStart = c.Int(0),
            UserName = c.String(maxLength: 255, defaultValueSql: "NULL"),
            FullName = c.String(maxLength: 255, defaultValueSql: "NULL"),
            PhoneNumber = c.String(maxLength: 255, defaultValueSql: "NULL"),
            LocationDescription = c.String(maxLength: 255, defaultValueSql: "NULL"),
            AgentStatusId = c.Int(defaultValueSql: "NULL"),
            AgentTypeId = c.Int(defaultValueSql: "NULL")
        }, StoredProcedureBody);
    }

    public override void Down()
    {
        DropStoredProcedure("dbo.GellAllAgents");
    }


    private const string StoredProcedureBody = @"
Declare @FirstRec int, @LastRec int
Set @FirstRec = @DisplayStart;
Set @LastRec = @DisplayStart + @DisplayLength;

With CTE_AspNetUsers as
(
     Select ROW_NUMBER() over (order by AspNetUsers.Id) as RowNum,
         COUNT(*) over() as TotalCount, AspNetUsers.Id, AspNetUsers.FullName, AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption as LocationDescription, Cities.Name as LocationCity, AgentStatus.Name as AgentStatusName, AgentTypes.Name as AgentTypeName
         from AspNetUsers
     join Locations on AspNetUsers.LocationId = Locations.id
     join Cities on Locations.CityId = Cities.Id
     join AgentStatus on AspNetUsers.AgentStatusId = AgentStatus.Id
     join AgentTypes on AspNetUsers.AgentTypeId = AgentTypes.Id
     where (Discriminator = 'Agent' 
         and (@UserName is null or UserName like '%' + @UserName + '%')
         and (@FullName is null or FullName like '%' + @FullName + '%')
         and (@PhoneNumber is null or PhoneNumber like '%' + @PhoneNumber + '%')
         and (@LocationDescription is null or  @LocationDescription like '%' + (select Cities.Name from Cities where Locations.CityId = Cities.Id) + '%' or  @LocationDescription like '%' + Desciption + '%')
         and (@AgentStatusId is null or AgentStatusId = @AgentStatusId)
         and (@AgentTypeId is null or AgentTypeId = @AgentTypeId)
     )
     group by AspNetUsers.Id, AspNetUsers.FullName,AspNetUsers.UserName, AspNetUsers.PhoneNumber, Locations.Desciption, Cities.Name, AgentStatus.Name, AgentTypes.Name
)
Select *
from CTE_AspNetUsers
where RowNum > @FirstRec and RowNum <= @LastRec
";
}
}

Result, When you view/modify the SP in SQL server, that's why it shows "ALTER PROCEDURE"

Answer 3

I am using EF6 and the DbMigration class provides methods to Create/Alter/Delete stored procedures

• Create a new stored procedure

  public partial class MyFirstMigration : DbMigration
  {
      public override void Up()
      {
          // Create a new store procedure
          CreateStoredProcedure("dbo.DequeueMessages"
          // These are stored procedure parameters
          , c => new{                
              MessageCount = c.Int()
          },
          // Here is the stored procedure body
          @"
          SET NOCOUNT ON;
          SELECT TOP (@MessageCount)
              *
          FROM
              dbo.MyTable;
          ");
      }
  
      public override void Down()
      {
          // Delete the stored procedure
          DropStoredProcedure("dbo.DequeueMessages");                
      }
  }
  

• Modify a stored procedure

  public partial class MySecondMigration : DbMigration
  {
      public override void Up()
      {
          // Modify an existing stored procedure
          AlterStoredProcedure("dbo.DequeueMessages"
          // These are new stored procedure parameters
          , c => new{                
              MessageCount = c.Int(),
              StatusId = c.Int()
          },
          // Here is the new stored procedure body
          @"
          SET NOCOUNT ON;
          SELECT TOP (@MessageCount)
              *
          FROM
              dbo.MyTable
          WHERE
              StatusId = @StatusId;
          ");
      }
  
      public override void Down()
      {
          // Rollback to the previous stored procedure
          // Modify an existing stored procedure
          AlterStoredProcedure("dbo.DequeueMessages"
          // These are old stored procedure parameters
          , c => new{                
              MessageCount = c.Int()
          },
          // Here is the old stored procedure body
          @"
          SET NOCOUNT ON;
          SELECT TOP (@MessageCount)
              *
          FROM
              dbo.MyTable;
          ");              
      }
  }