/// <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; } } } }