Execute synchronization SCRIPT via .net sqlClient.SqlCOmmand

Comments

• Eddie D Redgate › Posts: 1,803 Rose Gold 5

  July 21, 2009 12:27PM

  Thank you for your post into the forum.
  
  When executing a synchronization script created by the SQL Compare (or SQL Data Compare or using the 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, 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 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();
  }
  

  I hope the above helps in resolving your issue.
  
  Many Thanks
  Eddie

  Eddie Davis
  Senior Product Support Engineer
  Redgate Software Ltd
  Email: support@red-gate.com

  0 · Share on Twitter