| Author |
Message |
bazza47
Joined: 24 Oct 2007
Posts: 3
|
 Posted: Wed Oct 24, 2007 10:50 pm Post subject: Saving Contents of BlockExecutor for Later Execution |
 |
|
After comparing two database schemas using the SQL Toolkit 6 API, I can save the changes to a file as follows:
| Code: |
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: |
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 |
|
| Back to top |
|
 |
Michelle Taylor
Joined: 30 Oct 2006
Posts: 519
Location: Red Gate Software
|
| Posted: Mon Oct 29, 2007 12:51 pm Post subject: |
|
|
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: |
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. |
|
| Back to top |
|
|
|
|
All times are GMT + 1 Hour
|
| Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
