Communities

Writing
Writing
Codidact Meta
Codidact Meta
The Great Outdoors
The Great Outdoors
Photography & Video
Photography & Video
Scientific Speculation
Scientific Speculation
Cooking
Cooking
Electrical Engineering
Electrical Engineering
Judaism
Judaism
Languages & Linguistics
Languages & Linguistics
Software Development
Software Development
Mathematics
Mathematics
Christianity
Christianity
Code Golf
Code Golf
Music
Music
Physics
Physics

Dashboard
Notifications
Mark all as read
Q&A

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

+3
−0

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.

Why does this post require moderator attention?
You might want to add some details to your flag.
Why should this post be closed?

0 comments

0 answers

Sign up to answer this question »