Create script for new Database using API

flemingho

Hi,

Is there an API code example that I can use, to create a script for a new database (ie. non-upgrade script).

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 can't use the template packager because I writing my own custom utility that will be executed on the customer site, as it includes other operations that I require.

Thanks

Fleming

Brian Donahue

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);
                }
        }
}

flemingho

Great, thanks, Brian.

Just as a suggestion, but this would be a good code example that needs an appropriate section title/name for itself in the next version of SQLBundle.

Or the CompareWith method needs to have the use of "null" documented.

Thanks, again.

Brian Donahue

The above code was already dutifully stolen from the Toolkit help file... it's just that this particular example is part of the Packager API documentation.

flemingho

Thanks.

flemingho

Hi Brian,

I had asked this question in version 4 where you gave me a code snippet that worked like a charm. I'm trying to convert the same utility using version 5, but there seems to be some changes in the API that has "obseleted" some functions 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

Please see http://www.red-gate.com/MessageBoard/vi ... hp?p=10600