Loading a SQL Schema

Loading a SQL Schema

Insight supports installing most types of SQL objects. It groups a set of SQL objects into a SchemaObjectCollection, which you can then install into a database.

• A Database contains
  • Multiple SchemaObjectCollections which contain
    • SQL objects (tables, procedures, etc.)

The first thing you need to do is to create a SchemaObjectCollection. While you are there, add some SQL to it.

SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Add("CREATE TABLE Beer ([ID] [int] IDENTITY, [Name] [varchar](128))");
schema.Add(@"
    CREATE PROC InsertBeer (@Name [varchar](128)) AS 
        INSERT INTO Beer (Name) OUTPUT Inserted.ID VALUES (@Name)
");

You can use the Add method to add any SQL snippet to the collection. Insight will automatically detect the type and handle it appropriately.

You can also use the Load method to add a set of SQL snippets to the collection. In this case, each SQL object must be separated by the GO keyword.

So assume you have Beer.sql:

CREATE PROC InsertBeer (@Name [varchar](128)) AS 
    INSERT INTO Beer (Name) OUTPUT Inserted.ID VALUES (@Name)
GO
CREATE TABLE Beer ([ID] [int] IDENTITY, [Name] [varchar](128))
GO

Then to load that SQL into your collection, you just need:

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

You can also store your SQL as Embedded Resource files in your executable. Follow these steps in Visual Studio:

1. Add Beer.sql to your project.
2. In Solution Explorer, right click on Beer.sql and choose Properties.
3. Change the Build Action to "Embedded Resource".

Then you can load Beer.sql (and any other *.sql Embedded Resources) with a single line:

SchemaObjectCollection schema = new SchemaObjectCollection();
schema.Load(System.Reflection.Assembly.GetExecutingAssembly());

Now you have a SchemaObjectCollection of Schema Objects.

There is also an option to filter the embedded resources:

schema.Load(
    System.Reflection.Assembly.GetExecutingAssembly(), 
    resourceName => resourceName.StartsWith("MyProject.Schema1"));