Executing your own SQL queries together with SDK synchronization

Taken from:

http://www.red-gate.com/supportcenter/Content.aspx?p=SQL%20Comparison%20SDK&c=knowledgebaseSQL_Comparison_SDKKB200809000297.htm

Category: SDK, API & Command line
Date: 15 May 2009
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.

ExecutionBlock block = provider.GetMigrationSQL(session, new SelectionDelegate(this.SyncRecord), true);

…

System.Data.SqlClient.SqlTransaction trans = null;
try
{
// Make a connection string from the second database connection properties
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(“Data Source=”+db2.ConnectionProperties.ServerName+”;Initial Catalog=”+db2.ConnectionProperties.DatabaseName+”;Integrated Security=SSPI”);
System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
conn.Open();
trans = conn.BeginTransaction(“MyTransaction”);
//Run the first command
cmd.Transaction = trans;
cmd.CommandText = “SET LANGUAGE us_english”;
cmd.ExecuteNonQuery();
//Run batches
for (int i = 0; i < block.BatchCount; i++)
{
Batch b = block.GetBatch(i);
if (!b.Marker)
{
cmd.CommandText = b.Contents;
cmd.ExecuteNonQuery();
}
}
trans.Commit();
}
catch (System.Data.SqlClient.SqlException se)
{
Console.WriteLine(“Execute SQL failed: ” + se.Message);
trans.Rollback();
}