Db-Status-Provider

Introduction

This is a very simple extension that can be used to keep track of the state of your application's database. I use this method for my other open source project sBlog.Net!

Installation

To install DbStatusProvider, run the following command in the Package Manager Console

PM> Install-Package DbStatusProvider

Or, see below (Usage).

Samples

A sample project is located at https://github.com/karthik25/db-status-provider

Usage

Select the ASP.Net MVC project that should contain the DbStatusProvider nuget package. Right click, select "Manage NuGet Packages...".

Select the "Online" option in the left menu, enter "DbStatusProvider" in the search box.

In the results select "DbStatusProvider", click on "Install". Click on "Close".

The following changes will happen to your project.

• References to "DbStatusProvider.dll" and "WebActivatorEx.dll"
• DbStatusProvider.cs to App_Start folder
• _DbStatus.cshtml partial view to Views\Shared folder
• The following element to web.config

<dbStatusUpdater contextType="" scriptsBase="" scriptsPrefix="" />

The first step would be to create the data context that could be used to get a list of scripts ran / add a script that was successfully ran.

namespace SampleMvcApp.Contexts
{
    // Implementation not shown
    public class SchemaContext : ISchemaContext
    {
        public void Dispose()
        {
            throw new NotImplementedException();
        }

        public IList<ISchemaVersion> GetScriptsInstalled()
        {
            throw new NotImplementedException();
        }

        public void AddScript(ISchemaVersion schemaVersion)
        {
            throw new NotImplementedException();
        }
    }
}

Lets now update the web.config file as shown below. I guess it's already evident what the first attribute is for. The 2nd attribute specifies the base folder for the scripts ("~/Sql") and the 3rd attribute specifies the prefix of the scripts relevant to this application, "sc" in this case.

<dbStatusUpdater contextType="SampleMvcApp.Contexts.SchemaContext" scriptsBase="~/Sql" scriptsPrefix="sc" />

The next step would be to create the folder that would contain the schema change files. Say "Sql" within the root folder. Start creating the scripts in the format:

scXXYYZZ[.sql]

"sc" is the prefix defined in the web.config file. Here XX, YY, ZZ are 2 digit numbers following an order. A valid example would be "sc010001.sql", where XX = 01, YY = 00 and ZZ = 01. [.sql] is the extension of the and it could pretty much be anything.

That's it you are all set! When the application starts the extension is going to use the context defined and the folder to "see" if any scripts are pending. An application variable is created to hold the results of this operation and it could be used like this.

public class HomeController : Controller
{
    public ActionResult Index()
    {
        var setupStatus = App_Start.DbStatusProvider.GetStatus();
        return View(setupStatus);
    }
}

Now, you could use the partial provided to display the status, as shown below:

@model DbStatusProvider.Objects.SetupStatus

@Html.Partial("_DbStatus", Model)

That's it! It's "kind of" functional now! But to finalize, you could extend the application further by providing a button, based on the setup status:

@model DbStatusProvider.Objects.SetupStatus

@Html.Partial("_DbStatus", Model)

@using (Html.BeginForm("Submit", "Home"))
{
    <input type="submit" value="Finalize" />
}

Then on "submit" you could run the scripts to be run and redirect them to a different page!

[HttpPost]
public ActionResult Submit()
{
    var setupStatus = App_Start.DbStatusProvider.GetStatus();
    if (setupStatus.StatusCode == SetupStatusCode.HasUpdates)
    {
    var scripts = setupStatus.FullPathsOfScripts;

    foreach( var script in scripts)
    {
        // Run the script

        // Call the data context's AddScript method to update
    }

    // Unset the application variable
    App_Start.DbStatusProvider.ClearStatus();   

    // You could also reset the status
    // var newStatus = App_Start.DbStatusProvider.ResetStatus();

    // Redirect
    }
    return View();
}