public void SplitSqlStatements_BlockComment_MultiLine_Test() { // Arrange var target = new SqlTextExtractor(new[] { ";" }, new Db2DatabaseServer()); const string text = "/*SELECT * FROM TABLE3;\r\nSELECT * FROM TEST;\r\n */SELECT ';',* FROM TABLE1;"; // Act var actual = target.SplitSqlStatements(text); // Assert Assert.AreEqual(1, actual.Count); Assert.AreEqual("SELECT ';',* FROM TABLE1", actual[0]); }
public void SplitSqlStatements_MultiTerminator_Test() { // Arrange var target = new SqlTextExtractor(new[] { ";" }, new Db2DatabaseServer()); const string text = " SELECT * FROM TABLE1;; \r\n SELECT * FROM TABLE2"; // Act var actual = target.SplitSqlStatements(text); // Assert Assert.AreEqual(2, actual.Count); Assert.AreEqual("SELECT * FROM TABLE1", actual[0]); Assert.AreEqual("SELECT * FROM TABLE2", actual[1]); }
private async Task RunSqlFilesAsync() { // Get files to run var sqlFiles = _ugFiles.Rows.Select(x => (SqlFileDetails)x.ListObject).ToList(); if (sqlFiles.Count == 0) { throw new Exception("No files found to run"); } // Get options var useTransaction = ((StateButtonTool)_utm.Tools["Use Database Transaction"]).Checked; var continueOnError = ((StateButtonTool)_utm.Tools["Continue on Error"]).Checked; var runEntireFile = ((StateButtonTool)_utm.Tools["Run Each File as Single Statement"]).Checked; // Disable buttons foreach (var tool in _utm.Tools.Cast <ToolBase>().Where(x => x.Key != "Stop")) { tool.SharedProps.Enabled = false; } _utm.Tools["Stop"].SharedProps.Enabled = true; // Clear output _teResults.DoEditAction(new SelectWholeDocument()); _teResults.DoEditAction(new Delete()); // Remove highlight ranges _errorRanges.Clear(); _queryRanges.Clear(); // Reset all files foreach (var sqlFile in sqlFiles) { sqlFile.Status = "Pending"; sqlFile.ElapsedTime = TimeSpan.Zero; } // Setup cancellation _cancellationTokenSource = new CancellationTokenSource(); try { using (var connection = await _databaseConnection.CreateNewConnectionAsync().WithCancellation(_cancellationTokenSource.Token)) { connection.OpenIfRequired(); IDbTransaction transaction = null; if (useTransaction) { transaction = connection.BeginTransaction(); } var stopwatch = new Stopwatch(); foreach (var sqlFile in sqlFiles) { // Start the timer stopwatch.Restart(); // Read all text for file var sqlFileText = File.ReadAllText(sqlFile.FileName); // Split file text into individual SQL statements List <string> sqlStatements = new List <string>(); if (runEntireFile) { sqlStatements.Add(sqlFileText); } else { var sqlSplitter = new SqlTextExtractor(_databaseConnection.DatabaseServer.SqlTerminators, _databaseConnection.DatabaseServer); sqlStatements.AddRange(sqlSplitter.SplitSqlStatements(sqlFileText)); } // Run each statement sqlFile.Status = "Running"; foreach (var sqlStatement in sqlStatements) { await Task.Run(() => Thread.Sleep(3000)); try { _cancellationTokenSource.Token.ThrowIfCancellationRequested(); var sqlFirstKeyword = SqlHelper.GetFirstKeyword(sqlStatement, _databaseConnection.DatabaseServer .BlockCommentRegex, _databaseConnection.DatabaseServer .LineCommentRegex); var sqlType = SqlHelper.GetSqlType(sqlStatement, sqlFirstKeyword); Task <SqlQueryResult> queryTask; if (sqlType == SqlType.Query) { // Execute SELECT query _log.Debug("Running query ..."); queryTask = connection.ExecuteQueryKeepAliveAsync(transaction, sqlStatement, 100, _cancellationTokenSource.Token); var results = await queryTask.WithCancellation(_cancellationTokenSource.Token); _log.Debug("Query complete."); SetResults(results.Result, sqlStatement); _log.Debug("Results bound."); } else { // Execute DML or DDL query _log.Debug("Running non-query - it will use a transaction ..."); queryTask = connection.ExecuteNonQueryTransactionAsync(transaction, sqlStatement, _cancellationTokenSource .Token); var results = await queryTask.WithCancellation(_cancellationTokenSource.Token); _log.Debug("Non-query complete."); var resultsTable = new DataTable(); resultsTable.Columns.Add("Results", typeof(string)); if (sqlType == SqlType.Dml && results.RowsAffected >= 0) { // Ex: Inserted x rows resultsTable.Rows.Add(string.Format("{0}{1}D {2} row{3}", sqlFirstKeyword.Trim().ToUpper(), sqlFirstKeyword.ToUpper() .Trim() .EndsWith("E") ? string.Empty : "E", results.RowsAffected.ToString("#,0"), results.RowsAffected != 1 ? "s" : string.Empty)); } else if (sqlType == SqlType.Dml) { resultsTable.Columns.Add("Results", typeof(int)); resultsTable.Rows.Add(string.Format("{0} affected {1} row{2}", sqlFirstKeyword.Trim().ToUpper(), results.RowsAffected.ToString("#,0"), results.RowsAffected > 1 ? "s" : string.Empty)); } else { resultsTable.Rows.Add(string.Format("{0} successful", sqlFirstKeyword.Trim().ToUpper())); } SetResults(resultsTable, sqlStatement); _log.Debug("Results bound."); } // Set status sqlFile.Status = "Success"; } catch (OperationCanceledException) { sqlFile.Status = "Cancelled"; throw; } catch (Exception ex) { _log.Error("Error running query:"); _log.Error(sqlStatement); _log.Error(ex.Message, ex); SetResults(GetErrorMessageTable(ex), sqlStatement, true); sqlFile.Status = "Failed"; // If we're not goingto continue, throw OperationCanceledException s it gets caught in the outside scope and doesn't report errors if (!continueOnError) { throw new OperationCanceledException(); } } } // end foreach sql stamenet // Stop the timer stopwatch.Stop(); // Set elapsed time sqlFile.ElapsedTime = stopwatch.Elapsed; } // end foreach sql file if (transaction != null) { transaction.Commit(); } } } catch (OperationCanceledException) { _log.Debug("Operation cancelled."); } finally { MarkScriptMessages(_queryHighlightGroups, _queryRanges, Color.LightSkyBlue); MarkScriptMessages(_errorHighlightGroups, _errorRanges, Color.LightCoral); // Enable buttons foreach (var tool in _utm.Tools.Cast <ToolBase>().Where(x => x.Key != "Stop")) { tool.SharedProps.Enabled = true; } _utm.Tools["Stop"].SharedProps.Enabled = false; _utm.Tools["Run"].SharedProps.Enabled = _databaseConnection.IsConnected; } }