Create script for new Database using API

fleminghoflemingho Posts: 10
edited August 23, 2006 7:16AM in SQL Compare Previous Versions
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

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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);
                    }
            }
    }
    
  • 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 DonahueBrian Donahue Posts: 6,590 New member
    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.
  • 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



    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);
                    }
            }
    }
    
Sign In or Register to comment.