Create script for new Database using API

• 
  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 March 14, 2006 13:00

  - 0 +
• 
  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.

  flemingho March 14, 2006 15:13

  - 0 +
• 
  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.

  Brian Donahue March 15, 2006 04:46

  - 0 +
• 
  Thanks.

  flemingho March 15, 2006 09:59

  - 0 +
• 
  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);
                  }
          }
  }
  

  flemingho August 16, 2006 13:41

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

  Brian Donahue August 23, 2006 07:16

  - 0 +

Add comment

Please sign in to leave a comment.