How to manage views and stored procedures in an ASP.NET Core project?

I am slowly modernizing an older ASP.NET Core Web API and one of the steps involved migrating from database first to code first.

Now, all schema changes and seeding is covered by migrations which are automatically run at API startup. However, the service also uses several dozens of views and stored procedures which are currently manually deployed.

The official documentation suggests using migrations that include the object body (example):

migrationBuilder.Sql(@"
EXEC ('CREATE PROCEDURE getFullName
    @LastName nvarchar(50),
    @FirstName nvarchar(50)
AS
    RETURN @LastName + @FirstName;')");

This is ugly and quite error-prone (requires copy-pasting typically long object body in a static string).

I would like also to automate this and my first iteration of this is to store them in a special folder within the API service. All the content is included in the release package and reaches the server. The application runs all these objects after the normal migrations are applied.

Pros: objects are stored in a clear format, with intelliSense (can connect to a database from the IDE) and all changes are stored in Git (are committed along with other changes).

Cons: all objects are run at each application startup. No transaction is created when running each script (relevant for custom scripts that execute multiple changes at once).

This works fine for now, as application startups are very rare (IIS hosting with always running), but this is not OK in the long run.

One solution that crosses my mind is to use migration and explicitly run the objects that were changed (e.g. use a function that takes the path and will run similarly to the aforementioned example):

public partial class FixesSomething: Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        CustomRunner.Run("relative_path_to_change_1.sql");
        CustomRunner.Run("relative_path_to_change_2.sql");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // no coming back!
    }
}

However, I would like not to reinvent the wheel and perhaps the community can suggest a better approach for this.

posted almost 4 years ago

CC BY-SA 4.0

Alexei‭

5003 reputation 112 101 690 487