SPManager.NET 1.0.5

SPManager

SPManager is a .NET 9 library for versioning and managing SQL Server stored procedures with change detection, migration management, and a web dashboard.

Features

• Versioning: Track stored procedure changes with full history tracking in the database
• Change Detection: Compare stored procedures in a folder to those in a SQL Server database to detect additions, modifications, and deletions
• Migration Management: Apply and rollback stored procedures with complete history
• Dashboard Interface: View and manage stored procedures through a simple web dashboard
• Automatic Startup Application: Optionally apply all detected changes automatically on application startup

Installation

Install the NuGet package:

dotnet add package SPManager

Quick Start

1. Create a Folder for Stored Procedures

Create a folder in your project to store your stored procedure SQL files:

/StoredProcedures
    ├── usp_GetUser.sql
    ├── usp_UpdateUser.sql
    └── usp_DeleteUser.sql

2. Register Services

In your Program.cs or Startup.cs file, register SPManager services:

// Add SPManager services
builder.Services.AddSPManager(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
           .UseStoredProceduresFolder("StoredProcedures")
           .AutoApplyChangesOnStartup(true); // Optional: automatically apply changes on startup
});

3. Add the Dashboard

In your middleware configuration:

// Add SPManager dashboard
app.UseSPManagerDashboard(); // The dashboard is always available at "/spmanager"

4. Access the Dashboard

Navigate to /spmanager in your browser to access the dashboard.

Usage

Stored Procedure File Format

Create SQL files following standard naming conventions:

For example:

• usp_GetUser.sql
• usp_GetEmployee.sql

The content of each file should be a valid SQL script that creates or alters a stored procedure.

Dashboard Features

The dashboard provides three main tabs:

1. Stored Procedures: View all stored procedures found in your configured folder
2. Changes: View detected changes between folder and database
3. History: View all stored procedures applied to the database with their version history

Actions available:

• Apply a specific stored procedure
• Apply all detected changes
• Roll back to a previous version

Programmatic Usage

You can also use SPManager services directly in your code:

public class YourService
{
    private readonly ISPChangeDetector _changeDetector;
    private readonly ISPFileScanner _fileScanner;
    private readonly ISPDatabaseManager _databaseManager;

    public YourService(
        ISPChangeDetector changeDetector,
        ISPFileScanner fileScanner,
        ISPDatabaseManager databaseManager)
    {
        _changeDetector = changeDetector;
        _fileScanner = fileScanner;
        _databaseManager = databaseManager;
    }

    public async Task DoSomethingAsync()
    {
        // Get all stored procedures from folder
        var procedures = await _fileScanner.ScanFolderAsync();

        // Detect changes
        var changes = await _changeDetector.DetectChangesAsync();

        // Apply all changes
        await _changeDetector.ApplyAllChangesAsync();

        // Get stored procedure history
        var history = await _databaseManager.GetStoredProcedureHistoryAsync("usp_GetUser");

        // Apply a specific stored procedure
        var procedure = await _fileScanner.GetStoredProcedureAsync("usp_GetUser");
        if (procedure != null)
        {
            await _databaseManager.ApplyStoredProcedureAsync(procedure);
        }

        // Roll back to a previous version
        await _databaseManager.RollbackToVersionAsync("usp_GetUser", 1);
    }
}

License

This library is licensed under the MIT License.