Saving Contents of BlockExecutor for Later Execution

bazza47

After comparing two database schemas using the SQL Toolkit 6 API, I can save the changes to a file as follows:

string filePath = GetCachedScriptFilePath(targetDatabaseName);
BlockSaver saveBlock = new BlockSaver(filePath, EncodingType.UTF8, work.ExecutionBlock);
saveBlock.SaveToFile();

Is it possible to use the API to load the contents of the file, at some later time, and then apply these changes?

I tried code as follows, to apply the script contents, but the BlockExecutor doesnt like the 'GO's in the script. If I take the GOs out the the BlockExecutor fails because variables are defined more than once etc.

string scriptWithoutGO = String.Empty;
using (StreamReader sr = new StreamReader(cachedScriptFilePath))
{
    string script = sr.ReadToEnd();
    sr.Close();
    //scriptWithoutGO = script.Replace("GO", " ");   // nuke any GO's
    scriptWithoutGO = script;                       // or try with GO's
}

using (RedGate.SQLCompare.Engine.Database dbTarget = new RedGate.SQLCompare.Engine.Database())
{
    RedGate.SQL.Shared.BlockExecutor b = new BlockExecutor();
    using (ExecutionBlock blk = new ExecutionBlock())
    {
        blk.AddBatch(scriptWithoutGO);
        b.ExecuteBlock(blk, dataSource, targetDatabaseName, true, String.Empty, String.Empty);
    }
}

We have a large number of databases we need to sync and storing the changes to a script file for subsequent execution could lead to a reduction in processing time.

Thanks
Colin

Michelle T

The script which is saved using BlockSaver is designed to be run with Management Studio or similar tools.

There are two approaches you could take to saving an execution block:

1) Save the script and then run it 'by hand', i.e. using something like the following:

public static void executeSql(string fileName, SqlConnection connection, bool reportErrors)
        {
            string sqlScript;

            using (StreamReader sr = new StreamReader(fileName))
            {
                sqlScript = sr.ReadToEnd();
            }

            Regex r = new Regex("\\sGO\\s");
            string[] sqlScriptParts = r.Split(sqlScript);

            try
            {
                connection.Open();

                foreach (string sql in sqlScriptParts)
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    try
                    {
                        command.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        if (reportErrors) throw;
                        Console.WriteLine("Error executing command: " + command.CommandText);
                        Console.WriteLine("Error was: " + e);
                        continue;
                    }
                    finally
                    {
                        command.Dispose();
                    }
                }
            }
            finally
            {
                connection.Dispose();
            }
        }

2) Serialize the actual ExecutionBlock object out to disk somehow, and read it back in into an ExecutionBlock object directly. I'm not entirely sure how or if this would work - I can look into it further if the above method won't do.