C# runner

AliaSQL.Core provides a way to run AliaSQL from C# code. There are 4 methods to facilitate this.

This is currently in beta and can be downloaded via Nuget at https://www.nuget.org/packages/AliaSQL.Core/

To update or create the database use the UpdateDatabase method.

 AliaSqlResult UpdateDatabase(string connectionString, RequestedDatabaseAction action, string scriptDirectory = "");

• Runs AliaSQL against a database
• Default action is Update but it can be set to other AliaSQL actions
• Default script directory is ~/App_Data/scripts/ but it can bet set to any physical path
• If database does not exist it will be created
• Script directory path must exist
• Returns an object with a success boolean and a result string

Example usage:

  var result = new DbUpdater().UpdateDatabase(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString);

To get a list of pending scripts use the PendingChanges method.

 List<string> PendingChanges(string connectionString, string scriptDirectory = "");

• Gets list of SQL scripts that have not been ran against the target database

• Default script directory is ~/App_Data/scripts/ but it can bet set to any physical path

• Script directory path must exist
• Returns a list of string with names of pending sql scripts

Example usage:

 var result = new DbUpdater().PendingChanges(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

To get a list of pending test data scripts use the PendingTestData method.

 List<string> PendingTestData(string connectionString, string scriptDirectory = "");

• Gets list of SQL test data scripts that have not been ran against the target database

• Default script directory is ~/App_Data/scripts/ but it can bet set to any physical path

• Script directory path must exist
• Returns a list of string with names of pending test data sql scripts

Example usage:

 var result = new DbUpdater().PendingTestData(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

To see if the target database exists use the DatabaseExists method.

 bool DatabaseExists(string connectionString);

• Returns a boolean if the target database exists

Example usage:

    if (!new DbUpdater().DatabaseExists(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString)){
 //something
 }

To get the current database revision number use the DatabaseVersion method.

 int DatabaseVersion(string connectionString);

• Returns database version representing the number of scripts that have been ran against the database

Example usage:

    var version = DbUpdater().DatabaseVersion(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString)){

Recommendations

Add a post build step to your website project to copy your AliaSQL scripts to the website App_Data folder. Here is an example of the post build script:

 md "$(SolutionDir)Website\App_Data"
 md "$(SolutionDir)Website\App_Data\scripts"
 xcopy /y /e "$(SolutionDir)Demo\scripts\*.*" "$(SolutionDir)Demo.Website\App_Data\scripts"

If you intend to use this in Azure there are some additional hoops because App_Data is in a different location. I discovered a way to handle this if anyone is interested. Submit an issue and I will explain it in detail.

It is possible to these methods in a control panel section of your application and decide when to run the scripts. You may want to run this on App_Start to create the initial database using something like this:

  if (!new DbUpdater().DatabaseExists(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString))
        {
            var logger = LogManager.GetLogger("DatabaseMigrations");
            var result = new DbUpdater().UpdateDatabase(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString);
            if (result.Success)
            {
                logger.Info(result.Result);
            }
            else
            {
                logger.Error(result.Result);
            }

            //you may not want to run test data if the db is empty
            var testdataresult = new DbUpdater().UpdateDatabase(ConfigurationManager.ConnectionStrings["DemoConnectionString"].ConnectionString, RequestedDatabaseAction.TestData);
            if (testdataresult.Success)
            {
                logger.Info(testdataresult.Result);
            }
            else
            {
                logger.Error(testdataresult.Result);
            }
        }

There is a full working demo of all of this in the AliaSQL Demo repository at https://github.com/ericdc1/AliaSQL-Demo