How do I modify an existing database table (add/remove columns) with PetaPOCO (Umbraco 6, MVC)

Question

I have an Umbraco CMS application with some custom functionality for which I use PetaPOCO to store data in my database. I created my POCO and an Umbraco event that fires on application startup to create the table if it does not exist:

public class RegisterEvents : ApplicationEventHandler
{
    //This happens everytime the Umbraco Application starts
    protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
    {
        //Get the Umbraco Database context
        var db = applicationContext.DatabaseContext.Database;

        //Check if the DB table does NOT exist
        if (!db.TableExist("MyTable"))
        {
            //Create DB table - and set overwrite to false
            db.CreateTable<MyPetaPOCO>(false);
        }
    }
}

How do I modify the existing database (I want to add a column) without direct access to the database? I need to use code because the host doesn't provide access yet. I think I should be able to do this in this ApplicationStarted override event but I do not know how.

Edit

Should I use somethingl Fluent Migrator

Answer 1

If you are using PetaPoco, you could use the db.Execute("alter table ...") but then you would need to have sufficient access rights to execute a DDL statement like that.

Also I would run this within a PetaPoco transaction too, as it's good practice.

Finally, if you were running this at Application_Start (which is fine) you would need to perform a check to ensure that the column didn't already exist.