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?

Matronymic

possible dupe #7668130 – Stepaniestepbrother

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

Appointment

What about if you are ALTERing the procedure because it was created in a previous migration and then you need to go down? You wouldn't just DROP the procedure it would have to go back to it's original state which is whatever the procedure looked like before... – Sec

Answer 2

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

Translate

Answer 3

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"

Woodcock

Answer 4

I will try to provide a different perspective because having SQL code within C# strings is not very appealing and one should expect to change such scripts within a tool that provides intellisense (e.g. SSMS).

The following solution is implemented within a ASP.NET Core 2.0 Web API project.

1. Maintain procedures in the development database using any convenient tool

2. Generate procedures scripts:

   public class ProcedureItemMetadata
   {
       /// <summary>
       /// SQL server side object identifier
       /// </summary>
       [Key]
       public int ObjectId { get; set; }
   
       /// <summary>
       /// schema name
       /// </summary>
       public string SchemaName { get; set; }
   
       /// <summary>
       /// procedure name
       /// </summary>
       public string Name { get; set; }
   
       /// <summary>
       /// procedure body
       /// </summary>
       public string Definition { get; set; }
   }
   
   
   public string GetProceduresScript()
   {
      var query = Context.ProcedureItemMetadata.AsNoTracking().FromSql(@"
         SELECT ao.object_id as ObjectId, SCHEMA_NAME(ao.schema_id) as SchemaName, ao.name, sm.definition
         FROM sys.all_objects ao 
         JOIN sys.sql_modules sm ON sm.object_id = ao.object_id
         WHERE ao.type = 'P'
            and execute_as_principal_id IS NULL
         order by 1;");
   
         var list = query.ToList();
         string text = string.Join($" {Base.Constants.General.ScriptGeneratorSeparator}\n", list.Select(p => p.Definition));
   
         // replace create with create or alter
         string replaced = Regex.Replace(text,
            @"(?<create>CREATE\s+PROCEDURE\s+)",
            "CREATE OR ALTER PROCEDURE ", 
            RegexOptions.IgnoreCase);
   
         return replaced;
   }
   

This is a manual process, but allows to obtain procedures whenever their development is ready. Also, it can easily be extended to other types of objects (e.g. views).

3. Create a folder within solution to hold scripts to be run at application startup (e.g. _SQL)

4. Copy generated script within the folder (e.g. all_procedures.sql)

One advantage of storing scripts like this is that the IDE might automatically validate the syntax + highlight stuff etc.

5. Create "seed" code to automatically run when application starts

   private static void EnsureSqlObjects(CustomContext context)
   {
       string path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "_Sql");
       foreach (var file in Directory.GetFiles(path, "*.sql"))
       {
           string fileText = File.ReadAllText(file);
           // escaping { } for those rare cases when sql code contains {..}
           // as ExecuteSqlCommand tries to replace them with params values
           fileText = fileText.Replace("{", "{{");
           fileText = fileText.Replace("}", "}}");
   
           // splitting objects (cannot run more than one DDL in a command)
           string[] ddlParts = fileText.Split(Base.Constants.General.ScriptGeneratorSeparator, StringSplitOptions.RemoveEmptyEntries);
           foreach (string ddl in ddlParts)
           {
               context.Database.ExecuteSqlCommand(ddl);
           }
       }
   }
   

This approach allows for any idempotent scripts that are not easily maintained through migrations to be managed.

Junno