Create script for new Database using API

flemingho

Hi Brian,

I had asked this question in version 4 where you gave me a code snippet that worked like a charm.

The initial question was..." Basically I have a starting database (internally) where our customers can create new databases from. So, I'm hoping I can use the API to generate a script which I can deploy and execute on the client side that will create the same database structure (with initial records), under different database names. "


I'm now trying to convert the same utility using version 5, but there seems to be some changes in the API that has "obseleted" some function(s) e.g. SQLProvider.GetDatabase().

I was wondering if you could be so kind again to give me a code snippet that does the same thing, please. The help file in version 5 is a tad less detailed than version 4.

Thanks,

Fleming

Brian Donahue wrote:

Hello Fleming,

In code, you can register one database using Database.Register, then leave the second database as a new, blank database object. Then compare the two to generate a synchronization script. You would still need to append the CREATE DATABASE... command to the top of the script but that's not hard to do at all!

[c#]
using System;
using RedGate.SQL.Shared;
using RedGate.SQLPackager.Engine;
using System.IO;

namespace ConsoleApplication1
{
        class Class1
        {
                static void Main()
                {
                        string serverName=".";
                        string databaseName="WidgetDev";

                        //get the execution block representing the schema
                        RedGate.SQLCompare.Engine.Database database=new RedGate.SQLCompare.Engine.Database();
                        database.Register(new RedGate.SQLCompare.Engine.ConnectionProperties(serverName, databaseName), RedGate.SQLCompare.Engine.Options.Default);
                        
                        //compare it with a null database to get a creation script
                        RedGate.SQLCompare.Engine.Differences schema=database.CompareWith(null, RedGate.SQLCompare.Engine.Options.Default);

                        //select all the differences

                        foreach (RedGate.SQLCompare.Engine.Difference difference in schema)
                        {
                                difference.Selected=true;
                        }

                        RedGate.SQLCompare.Engine.Work work=new RedGate.SQLCompare.Engine.Work();
                        
                        //get the script
                        work.BuildFromDifferences(schema, RedGate.SQLCompare.Engine.Options.Default, true);
                        ExecutionBlock schemaBlock=work.ExecutionBlock;

                        //now get the data script

                        RedGate.SQLDataCompare.Engine.SqlProvider provider=new RedGate.SQLDataCompare.Engine.SqlProvider();
                        RedGate.SQLDataCompare.Engine.Database database2=provider.GetDatabase(new RedGate.SQLDataCompare.Engine.SqlConnectionProperties(serverName, databaseName));
                        RedGate.SQLDataCompare.Engine.ComparisonSession session=new RedGate.SQLDataCompare.Engine.ComparisonSession();

                        RedGate.SQLDataCompare.Engine.TableComparisonSettings tablesToCompare=new RedGate.SQLDataCompare.Engine.TableComparisonSettings();

                        foreach (RedGate.SQLDataCompare.Engine.Table table in database2.Tables)
                        {
                                //since we are always inserting data there is no need to define a primary key
                                tablesToCompare.Add(new RedGate.SQLDataCompare.Engine.TableComparisonSetting(table.FullyQualifiedName, table.Fields, null));
                        }

                        session.CompareDatabases(database2, null, tablesToCompare);
                        ExecutionBlock dataBlock=provider.GetMigrationSQL(session, true);
                }
        }
}

Brian Donahue

Hello Fleming,

Can you please have a look at the SQL Compare code snippets that are installed in the SQL Bundle 5\SQL Toolkit sample files folder? There are examples in there that should give you the right idea. Little has changed in the SQL Compare Engine aside from the end bit that runs the SQL script.