Installing a Schema

Installing a Schema

Creating the Database

Make sure that the database you want exists. You can skip this step if you know your database is already there.

SchemaInstaller.CreateDatabase(connectionString);

Load a Schema

Load your schema from a file:

SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Load("Beer.sql");

You can also embed your SQL into your assembly, then load the SQL from the assembly resources:

schema.Load(Assembly.GetExecutingAssembly());

Creating an installer

Once you have a populated SchemaObjectCollection, you are ready to install your schema into the database.

First, get a SchemaInstaller and give it a connection string to the server you want to connect to.

using (SqlConnection connection = new SqlConnection("Database=.;Initial Catalog=InsightTest;Integrated Security=true"))
{
    connection.Open();
    SchemaInstaller installer = new SchemaInstaller(connection);

    // Follows below

Installing the Schema

One line to do this:

    installer.Install("BeerGarten", schema);

}

This will install the objects. If the schema collection "BeerGarten" was already created, then Insight will calculate the differences and apply the changes to the database.

You can uninstall a schema by referencing the name:

installer.Uninstall("BeerGarten");

Multiple Schema Collections

You can install more than one schema collection into the same database. Insight will manage them separately. That way you can have two different projects/executables in the same database, and use Insight to automatically manage the two schemas, including the cross-dependencies.

You can also install other objects into the database that aren't registered with Insight. Insight will respect them and attempt to maintain them. For example, your ops team may add some stored procedures for monitoring or copying data. Insight will not add or remove them. If you touch one of the dependencies, Insight will automatically script the dependency and keep the stored procedure around.

Long Upgrades

If your upgrade takes a while, you may want to increase the CommandTimeout on the SchemaInstaller. It defaults to 30 seconds per command, but yours may take longer:

SchemaInstaller installer = new SchemaInstaller(connection);
installer.CommandTimeout = 60 * 60	// 1 hour = 60 minutes * 60 seconds