Saving Contents of BlockExecutor for Later Execution

Forum for users of SQL Toolkit 3,4,and 5

Saving Contents of BlockExecutor for Later Execution

Postby bazza47 » Wed Oct 24, 2007 10:50 pm

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

Code: Select all
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.

Code: Select all
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
bazza47
 
Posts: 3
Joined: Wed Oct 24, 2007 10:27 pm

Postby Michelle Taylor » Mon Oct 29, 2007 12:51 pm

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:

Code: Select all
        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.
Michelle Taylor
 
Posts: 529
Joined: Mon Oct 30, 2006 12:45 pm
Location: Red Gate Software


Return to SQL Toolkit Previous Versions

Who is online

Users browsing this forum: No registered users and 1 guest