SQL Script Generator API Sample

The Script Generator API allows for developers and DBA's to easily create schema creation scripts, run the scripts or save them to a file.

VistaDB database to a SQL Server target

This sample will demonstrate taking an existing VistaDB 4 database and generating the SQL Script files required to create the same schema in SQL Server.  You must still manually create the actual database in SQL Server, the SQL Scripts are only for schema.

Create a script generator

Creating a new ScriptGenerator object which will handle all of the database connections and script generation logic. 

    CornerstoneDB.ScriptGeneratorTool.ScriptGenerator generator =
    new CornerstoneDB.ScriptGeneratorTool.ScriptGenerator();

Set source and target database properties

After creating the ScriptGenerator object you will need to set the required database properties.

In this code we are setting the source as VistaDB 4, but we want SQL Server scripts for the output.

    generator.ConnectionString = @”Data Source = C:\MyDatabase.vdb4”;
    generator.SourceProviderName = “VistaDB”;
    generator.DestinationProviderName = “SqlServer”;

The connection string property specifies the source database the schema will be loaded from, as well as the source provider name being the name of the database provider (in this instance is VistaDB ).

The destination provider name field needs to be set to inform the script generator which database vendor output to target.  It doesn't need a connection string because it never connects to the destination provider.  The destination is used to customize the script for that provider.

Write output sql script

After the ScriptGenerator object has been set up you can get the script two different ways.

Write to a file

    generator.WriteScriptToFile(@“C:\MyScript.txt”);

The first shows an example of how to use the generator to write the sql script to a text file, if the file does not exist the generator will create this file for you.

In memory list of strings

        List<string> script = generator.GetScriptCommands();

You may also get the sql script back as a list of strings, you can use this list to dynamically execute the commands with your own logic to the actual target, or write them to another location.

Samples

Read Migrate data schema using SQL Script Generation blog post for another example of the API usage.

There are some samples included with the trial.  Please remember to check out those samples for more ways to generate SQL Scripts.