I have been working to write a SQL installer for my application and haven't been given a lot of time to work on it therefore its a bit dirty. My UI thread collects values from the form, plugs them into a series of SQL scritps and then one by one kicks them of "asynchronously" using SMO. Here is a sample of the calling method on the UI thread:
private static bool BeginScriptExecution(Dictionary<string, string[]> scripts)
{
try
{
foreach (var script in scripts)
{
if (script.Key.Length > 0)
{
SqlConnection conn = new SqlConnection();
conn = ReplaceDatabaseName(GetConnectionString(), script.Value[0]);
if (TestSqlConnection())
{
if (ConfigurationManager.AppSettings["useSMO"] == "1")
{
if (!RunDatabaseCommandsSmo(conn, script.Key, script.Value[1]).Result)
{
throw new Exception("Script failed to run. Error in SMO functions.");
}
}
else
{
if (!RunDatabaseCommandsNonSmo(conn, script.Key, script.Value[1]))
{
throw new Exception("Script failed to run. Non-SMO related failure.");
}
}
}
else
{
throw new Exception("Connection not available. Script failed to run");
}
}
}
return true;
}
catch (Exception ex)
{
log.Error(ex.ToString());
return false;
}
}
Here is my async method to run the command using SMO:
public static async Task<bool> RunDatabaseCommandsSmo(SqlConnection connectionString, string scriptText, string scriptName)
{
Helper.UpdateProgressLabel(scriptName);
bool isSuccess = false;
try
{
ServerConnection srvCon = new ServerConnection(connectionString);
Server server = new Server(srvCon);
//script = scriptText;
if (scriptName.Contains("Creating Database")||scriptName.Contains("Building objects"))
{
try
{
isSuccess = await Task.Run(() => RunCommand(scriptText, server))
.ConfigureAwait(continueOnCapturedContext:false);
return isSuccess;
}
catch(Exception ex)
{
log.Error("Cannot create database");
log.Error(ex.StackTrace.ToString());
return false;
}
}
else
{
try
{
//server.ConnectionContext.ExecuteNonQuery(scriptText);
isSuccess = await Task.Run(() => RunTransaction(scriptText, server, srvCon))
.ConfigureAwait(continueOnCapturedContext: false);
return isSuccess;
}
catch (Exception ex)
{
log.Error(string.Format("Error writing transaction from script {0}. Installation halted - check inner exception.", scriptName));
log.Error(ex.ToString());
log.Error(ex.StackTrace.ToString());
return false;
}
}
}
catch (Exception ex)
{
log.Error(string.Format("Error writing transaction from script {0}. Installation halted - check inner exception.", scriptName));
log.Error(ex.StackTrace.ToString());
return false;
}
}
Here is the code running the transactions:
static bool RunCommand(string script, Server server)
{
try
{
server.ConnectionContext.ExecuteNonQuery(script);
return true;
}
catch(Exception ex)
{
log.Error(ex.ToString());
return false;
}
}
static bool RunTransaction(string script, Server server, ServerConnection srvCon)
{
try
{
srvCon.BeginTransaction();
server.ConnectionContext.ExecuteNonQuery(script);
srvCon.CommitTransaction();
return true;
}
catch (Exception ex)
{
srvCon.RollBackTransaction();
log.Error(ex.ToString());
return false;
}
}
Before I started moving towards async I was running all on the UI thread and the form would give me "Not Responding" until the script returned completed. Once I moved to this style of async the app is a bit more responsive and I no longer get not responding, but I am not sure of this method and whether or not it is correct use of async. Can someone let me know how I can alter my code to make this work correctly? There are 4 scripts that run and must run in specific order meaning script 2 cannot run until script 1 returns.
A good way to use asynchronous tasks is using a background worker,
take a look at the code example here: Background Worker