When executing a synchronization script created by the SQL Comparison SDK, it may be desired that some ad-hoc queries be intermixed with the SQL produced by the Red Gate APIs.

Because the BlockExecutor class can only run SQL code by converting ExecutionBlocks to SQL code and submitting them to the SQL Server, custom SQL cannot be introduced into the query stream. It is possible, however, to break an ExecutionBlock into individual query batches and running them using the .NET Framework's ADO .NET methods.

In the following C# example, a SQL query SET LANGUAGE us_english needs to be submitted before the synchronization produced by the SQL Data Compare Engine. First, a connection is made to the server using the connection properties of the second database. Then a transaction is created. The custom SQL query is run first, then each batch of SQL from the ExecutionBlock in order. Finally, the transaction is committed. If any errors occur during the execution of the SQL script, then the error is written to the console and the transaction will be rolled back.

using System;
using RedGate.SQLCompare.Engine;
using System.IO;
using RedGate.Shared.SQL.ExecutionBlock;
using System.Data.SqlClient;
namespace CompareTwoDatabases
{
        /// <summary>
        /// Demonstration that compares the widgetStaging database to the widgetProduction database
        /// and synchronizes them using the ADO .NET libraries
        /// </summary>
        class Program
        {
                static void Main(string[] args)
                {
                        using (Database widgetStaging = new Database(),
                        widgetProduction = new Database())
                        {
                                // Retrieve the schema information for the two databases
                                widgetStaging.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
                                widgetProduction.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
                                // Compare widgetStaging to widgetProduction. Comparing in this order makes WidgetProduction the second database
                                Differences stagingVsProduction = widgetStaging.CompareWith(widgetProduction, Options.Default);
                                // Select the differences to include in the synchronization. In this case, we're using all differences.
                                foreach (Difference difference in stagingVsProduction)
                                {
                                        difference.Selected = true;
                                }
                                Work work = new Work();
                                // Create the migration without the transactional bits --
                                //we will let ADO .NET manage the transactions using SqlTransaction
                                // The script is to be run on widgetProduction so the runOnTwo parameter is true
                                work.BuildFromDifferences(stagingVsProduction, Options.Default | Options.NoSQLPlumbing,true);
                                // We can now access the messages and warnings
                                Console.WriteLine("Messages:");
                                foreach (Message message in work.Messages)
                                {
                                        Console.WriteLine(message.Text);
                                }
                                Console.WriteLine("Warnings:");
                                foreach (Message message in work.Warnings)
                                {
                                        Console.WriteLine(message.Text);
                                }
                                // Disposing the execution block when it's not needed any more is important to ensure
                                // that all the temporary files are cleaned up
                                using (ExecutionBlock block = work.ExecutionBlock)
                                {
                                        // Display the SQL used to synchronize
                                        Console.WriteLine("SQL to synchronize:");
                                        Console.WriteLine(block.GetString());
                                        // Make a connection string from the second database connection properties (runOnTwo)
                                        System.Data.SqlClient.SqlConnection conn = newSystem.Data.SqlClient.SqlConnection("Data Source=" + widgetProduction.ConnectionProperties.ServerName +";Initial Catalog=" + widgetProduction.ConnectionProperties.DatabaseName + ";Integrated Security=SSPI");
                                        System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
                                        conn.Open();
                                        SqlTransaction trans = conn.BeginTransaction("MyTransaction");
                                        cmd.Transaction = trans;
//Run the first command
cmd.CommandText = "SET LANGUAGE us_english";
cmd.ExecuteNonQuery();
                                        //Run batches
                                        for (int i = 0; i < block.batchcount;="">
                                        {
                                                Batch b = block.GetBatch(i);
                                                try
                                                {
                                                        if (!b.Marker) //Do not run blocks that are simply "GO"
                                                        {
                                                                cmd.CommandText = b.Contents;
                                                                cmd.ExecuteNonQuery();
                                                        }
                                                }
                                                catch (System.Data.SqlClient.SqlException se)
                                                {
                                                        Console.WriteLine("Execute SQL failed: " + se.Message);
                                                        trans.Rollback();
                                                }
                                        }
                                        trans.Commit();
                                }
                                Console.WriteLine("Press [Enter]");
                                Console.ReadLine();
                        }
                }
        }
}