/// <summary>
/// Executes the script.
/// </summary>
/// <remarks>
/// Use script.Execute(transaction) to do the work. We will also pull the
/// status of our script exection from here.
/// </remarks>
/// <param name="scriptName">Name of the script.</param>
/// <param name="transaction">The current transaction.</param>
/// <param name="dbUserName">Name of the DB owner.</param>
public static void ExecuteScript(string scriptName, SqlTransaction transaction, string dbUserName)
{
SqlScriptRunner scriptRunner = new SqlScriptRunner(UnpackEmbeddedScript(scriptName), Encoding.UTF8);
if(!string.IsNullOrEmpty(dbUserName))
scriptRunner.TemplateParameters.SetValue("dbUser", dbUserName);
scriptRunner.Execute(transaction);
}
/// <summary>
/// Executes create / alter scripts against the database
/// </summary>
/// <param name="SqlScript"></param>
public void RunScript(string SqlScript)
{
// gets the server connection
string connectionString = _database.Parent.ConnectionContext.ConnectionString;
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString);
// specify the database
builder.InitialCatalog = _database.Name;
// remove comments
Regex re = new Regex(@"\/\*.+\*\/", RegexOptions.Multiline);
SqlScript = re.Replace(SqlScript, "");
// create a script runner (SubText) object
SqlScriptRunner runner = new SqlScriptRunner(SqlScript);
// connect to the database and run the scripts
using (SqlConnection cn = new SqlConnection(builder.ConnectionString))
{
cn.Open();
using (SqlTransaction transaction = cn.BeginTransaction())
{
runner.Execute(transaction);
transaction.Commit();
}
}
runner = null;
}
public void RunTearDownFile()
{
SqlScriptRunner runner = new SqlScriptRunner(GetCommandText(TearDownFile));
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlTransaction trans = conn.BeginTransaction())
{
runner.Execute(trans);
trans.Commit();
}
}
}
public void TestScriptCollectionsExpansionWithChanges()
{
Stream stream = UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScript.txt");
var scriptRunner = new SqlScriptRunner(stream, Encoding.UTF8);
Assert.AreEqual(5, scriptRunner.TemplateParameters.Count,
"Not the expected number of template parameters. Make sure it merges correctly.");
string expectedDefault =
UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScriptExpectedChanges.txt",
Encoding.UTF8);
scriptRunner.TemplateParameters["subtext_db_name"].Value = "SubtextDB";
scriptRunner.TemplateParameters["dottext_db_name"].Value = "dbDotText";
scriptRunner.TemplateParameters["dotTextDbUser"].Value = "haacked";
scriptRunner.TemplateParameters["someOtherTemplate"].Value = "NotABlogId";
string expected = expectedDefault.Trim();
string result = scriptRunner.ScriptCollection.ExpandedScriptText.Trim();
expected = expected.Replace("" + (char)13, ""); //Ugly hack! I know. I'll Explain later.
result = result.Replace("" + ((char)13), ""); //Ugly hack! I know. I'll Explain later.
UnitTestHelper.AssertStringsEqualCharacterByCharacter(expected, result);
}
public void TestScriptCollectionsDefaultExpansion()
{
Stream stream = UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScript.txt");
var scriptRunner = new SqlScriptRunner(stream, Encoding.UTF8);
Assert.AreEqual(5, scriptRunner.TemplateParameters.Count,
"Not the expected number of template parameters. Make sure it merges correctly.");
string expectedDefault =
UnitTestHelper.UnpackEmbeddedResource("Scripting.TestTemplateSqlScriptExpectedDefault.txt",
Encoding.UTF8);
Assert.AreEqual(expectedDefault, scriptRunner.ScriptCollection.ExpandedScriptText);
}
/// <summary>
/// Executes the script.
/// </summary>
/// <remarks>
/// Use script.Execute(transaction) to do the work. We will also pull the
/// status of our script exection from here.
/// </remarks>
/// <param name="scripts">The collection of scripts to execute.</param>
/// <param name="transaction">The current transaction.</param>
/// <param name="dbUserName">Name of the DB owner.</param>
public static void ExecuteScript(ScriptCollection scripts, SqlTransaction transaction, string dbUserName)
{
SqlScriptRunner scriptRunner = new SqlScriptRunner(scripts);
if (!string.IsNullOrEmpty(dbUserName))
scriptRunner.TemplateParameters.SetValue("dbUser", dbUserName);
scriptRunner.Execute(transaction);
}
private ActivityResult RunSqlScript( InstallData installData, string sqlScriptFile, int consoleMessageReportFrequency, int progressbarEventFrequency, int percentOfStep, int startPercent )
{
ActivityResult result = new ActivityResult();
result.Success = true;
SqlConnection conn = null;
try
{
string sql = System.IO.File.ReadAllText( sqlScriptFile );
string connectionString = String.Format( "user id={0};password={1};server={2};Initial Catalog={3};connection timeout=10", installData.ConnectionString.Username, installData.ConnectionString.Password, installData.ConnectionString.Server, installData.ConnectionString.Database );
conn = new SqlConnection( connectionString );
conn.Open();
SqlScriptRunner runner = new SqlScriptRunner( sql );
int sqlScriptCount = runner.ScriptCollection.Count;
int scriptsRun = 0;
int scriptNextConsolePercentile = consoleMessageReportFrequency;
int scriptNextProgressbarPercentile = progressbarEventFrequency;
if ( consoleMessageReportFrequency != 0 )
{
this.SendConsoleMessage( String.Format("There are {0} scripts to run.", sqlScriptCount) );
}
using ( SqlTransaction transaction = conn.BeginTransaction() )
{
foreach ( var script in runner.ScriptCollection )
{
script.Execute( transaction );
scriptsRun++;
// calculate current percentile
int currentPercentile = (int)(((double)scriptsRun / (double)sqlScriptCount) * 100);
// update console messages
if ( consoleMessageReportFrequency != 0 )
{
if ( sqlScriptCount == scriptsRun )
{
this.SendConsoleMessage( "100% of scripts run" );
}
else if ( currentPercentile >= scriptNextConsolePercentile )
{
this.SendConsoleMessage( currentPercentile + "% of scripts run" );
scriptNextConsolePercentile = currentPercentile + consoleMessageReportFrequency;
}
}
// update progress bar
if ( progressbarEventFrequency != 0 )
{
if ( sqlScriptCount == scriptsRun )
{
this.UpdateProgressBar( (int)(100 * (percentOfStep * .01)) + startPercent );
}
else if ( currentPercentile >= scriptNextProgressbarPercentile )
{
this.UpdateProgressBar( (int)(currentPercentile * (percentOfStep * .01)) + startPercent );
scriptNextProgressbarPercentile = currentPercentile + progressbarEventFrequency;
}
}
}
transaction.Commit();
}
}
catch ( Exception ex )
{
result.Success = false;
result.Message = ex.Message;
}
finally
{
conn.Close();
conn.Dispose();
}
return result;
}
/// <summary>
/// Provides the import information as provided by the user back
/// into the import provider.
/// The control passed in should be the same as that provided in
/// <see cref="GatherImportInformation"/>, but with user values
/// supplied within it.
/// </summary>
/// <param name="populatedControl">Populated control.</param>
public override void Import(Control populatedControl)
{
string dotTextConnectionString;
GetConnectionStringsFromControl(populatedControl, out dotTextConnectionString);
using(SqlConnection connection = new SqlConnection(Config.ConnectionString.ToString()))
{
connection.Open();
using(SqlTransaction transaction = connection.BeginTransaction())
{
try
{
//Set up script parameters...
ConnectionString subtextConnection = Config.ConnectionString;
ConnectionString dotTextConnection = ConnectionString.Parse(dotTextConnectionString);
Stream stream = ScriptHelper.UnpackEmbeddedScript("ImportDotText095.sql");
SqlScriptRunner runner = new SqlScriptRunner(stream, Encoding.UTF8);
runner.TemplateParameters["subtext_db_name"].Value = subtextConnection.Database;
runner.TemplateParameters["dottext_db_name"].Value = dotTextConnection.Database;
if(!dotTextConnection.TrustedConnection)
{
// We need to determine if we should be using the username we got from
// the dotTextConnection, or use the default template value (dbo).
if (DoesTableExist("blog_Config", dotTextConnection.UserId, dotTextConnection))
{
// we have the correct user for the path to the dotText tables
runner.TemplateParameters["dotTextDbUser"].Value = dotTextConnection.UserId;
}
}
runner.Execute(transaction);
transaction.Commit();
}
catch(Exception)
{
transaction.Rollback();
throw;
}
}
}
}
