How to create a installer to implement a database schema change?

Question

hi all,

I am creating a project which has its own database schema. The other projects which will use the DLL created by my project will have the same schema in their databse that the my DLL requires. But the problem comes if there is a need to change the schema for me, it's not a good option to say this to every client that make these all changes in your database.

So, I want to create a installer which will do this automatically for them... Any suggestions, ideas appreciated..

Answer 1

Check out this tool -> http://www.liquibase.org/ It allows automation of database migration scripts

Answer 2

I would generate a change srcipt on every change in database schema with checking if the change has not already been applied and ship it with my DLL or with an install application which will update the DLL and run all the change scripts

Answer 3

You may use WiX to create the installer: GAC'ing your assembly is just an option and you may execute SQL Scripts against the database.

Answer 4

This solution uses a Setup Project within Visual Studio 2008 and makes use of the builtin Installer class found in the .NET framework. Other links can be found here and here.

Here's a basic outline of what to do:

1. Add an Installer class instance to the project that will be installed to the client's PC
2. Add a setup project that will install the application to the client's PC
3. Define actions within the setup project that will be raised at certain points during the installation
4. Override relevant methods on the Installer class to get database connection details from the end user during application installation
5. Preserve the database connection settings (in case of an uninstall)
6. Run your custom method/class/executable within the context of the installer and do what must be done

Here's a step-by-step guide:

1. Add Installer class: an Installer class must be housed inside the application to be installed. In Visual Studio's Solution Explorer, select the application to be installed and click the menu items "Project->Add New Item->Installer Class". This will add a default installer class called "Installer1".
2. In Visual Studio's Solution Explorer, select the Setup project and right click it. Select the "View->Custom actions" to display the custom actions to be executed during installation.
3. In the "Custom actions" window, add custom actions for "Install" and "Uninstall"
4. Go to the Installer class ("Installer1") and override OnBeforeInstall.

Here's pseudocode of how things would work:

[Installer1.cs]

protected override void OnBeforeInstall(IDictionary savedState)
{
    base.OnBeforeInstall(savedState);

    MessageBox.Show("OnBeforeInstall: " + GetProperties(savedState));

    using (ConnectionDialog d = new ConnectionDialog())
    {
     d.ShowDialog();

     savedState["database"] = d.Database;
     savedState["user"] = d.User;
     savedState["password"] = d.Password;
     savedState["integrated"] = d.Integrated;
    }
}

The IDictionary instance passed in by the installer is a collection of key/value pairs which users can populate with their relevant data. This same information is passed into other methods such as OnBeforeUninstall which the developer can use to detach the database or rollback changes or whatever else.

Answer 5

If your installer can call a simple batch file you can create a batch file that does simply the following:

1. Uses osql to query a table for schema version number. In this case lets say the table has "3" stored in it.
2. Use osql again to run the next version update, a file called 4.sql
3. Use osql to update the version number
4. Then, if 5.sql exists do the next one, etc.