CODEWITHSUNDEEP
.netasp.netsql-server
Manish
Manish

Reputation: 6286

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

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..

Upvotes: 1

Views: 1064

Answers (5)

djangofan
djangofan

Reputation: 29679

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.

Upvotes: 0

Mike J
Mike J

Reputation: 3159

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.

Upvotes: 0

Giulio Vian
Giulio Vian

Reputation: 8343

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.

Upvotes: 0

V1K1ng
V1K1ng

Reputation:

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

Upvotes: 0

Gregory Mostizky
Gregory Mostizky

Reputation: 7261

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

Upvotes: 1

Related Questions