Knowledge Base

Executing your own SQL queries together with SDK synchronization

Category: How do I?
Date: 03 Feb 2011
Product: SQL Comparison SDK
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.

1. [C#]
2. using System;
3. using RedGate.SQLCompare.Engine;
4. using System.IO;
5. using RedGate.Shared.SQL.ExecutionBlock;
6. using System.Data.SqlClient;
7. namespace CompareTwoDatabases
8. {
9.         /// <summary>
10.         /// Demonstration that compares the WidgetStaging database to the WidgetProduction database
11.         /// and synchronizes them using the ADO .NET libraries
12.         /// </summary>
13.         class Program
14.         {
15.                 static void Main(string[] args)
16.                 {
17.                         using (Database widgetStaging = new Database(),
18.                         widgetProduction = new Database())
19.                         {
20.                                 // Retrieve the schema information for the two databases
21.                                 widgetStaging.Register(new ConnectionProperties(".", "WidgetStaging"), Options.Default);
22.                                 widgetProduction.Register(new ConnectionProperties(".", "WidgetProduction"), Options.Default);
23.  
24.                                 // Compare widgetStaging to widgetProduction. Comparing in this order makes WidgetProduction the second database
25.                                 Differences stagingVsProduction = widgetStaging.CompareWith(widgetProduction, Options.Default);
26.  
27.                                 // Select the differences to include in the synchronization. In this case, we're using all differences.
28.                                 foreach (Difference difference in stagingVsProduction)
29.                                 {
30.                                         difference.Selected = true;
31.                                 }
32.  
33.                                 Work work = new Work();
34.  
35.                                 // Create the migration without the transactional bits --
36.                                 //we will let ADO .NET manage the transactions using SqlTransaction
37.                                 // The script is to be run on WidgetProduction so the runOnTwo parameter is true
38.                                 work.BuildFromDifferences(stagingVsProduction, Options.Default | Options.NoSQLPlumbing, true);
39.  
40.                                 // We can now access the messages and warnings
41.                                 Console.WriteLine("Messages:");
42.  
43.                                 foreach (Message message in work.Messages)
44.                                 {
45.                                         Console.WriteLine(message.Text);
46.                                 }
47.  
48.                                 Console.WriteLine("Warnings:");
49.  
50.                                 foreach (Message message in work.Warnings)
51.                                 {
52.                                         Console.WriteLine(message.Text);
53.                                 }
54.  
55.                                 // Disposing the execution block when it's not needed any more is important to ensure
56.                                 // that all the temporary files are cleaned up
57.                                 using (ExecutionBlock block = work.ExecutionBlock)
58.                                 {
59.                                         // Display the SQL used to synchronize
60.                                         Console.WriteLine("SQL to synchronize:");
61.                                         Console.WriteLine(block.GetString());
62.                                         // Make a connection string from the second database connection properties (runOnTwo)
63.                                         System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("Data Source=" + widgetProduction.ConnectionProperties.ServerName + ";Initial Catalog=" + widgetProduction.ConnectionProperties.DatabaseName + ";Integrated Security=SSPI");
64.                                         System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
65.                                         conn.Open();
66.                                         SqlTransaction trans = conn.BeginTransaction("MyTransaction");
67.                                         cmd.Transaction = trans;
68. //Run the first command
69. cmd.CommandText = "SET LANGUAGE us_english";
70. cmd.ExecuteNonQuery();
71.  
72.                                         //Run batches
73.                                         for (int i = 0; i < block.batchcount;="">
74.                                         {
75.                                                 Batch b = block.GetBatch(i);
76.                                                 try
77.                                                 {
78.                                                         if (!b.Marker) //Do not run blocks that are simply "GO"
79.                                                         {
80.                                                                 cmd.CommandText = b.Contents;
81.                                                                 cmd.ExecuteNonQuery();
82.                                                         }
83.                                                 }
84.                                                 catch (System.Data.SqlClient.SqlException se)
85.                                                 {
86.                                                         Console.WriteLine("Execute SQL failed: " + se.Message);
87.                                                         trans.Rollback();
88.                                                 }
89.                                         }
90.                                         trans.Commit();
91.                                 }
92.  
93.                                 Console.WriteLine("Press [Enter]");
94.                                 Console.ReadLine();
95.                         }
96.                 }
97.         }
98. }
99. [/C#]