private static void renderAndCountExceptions(FlexResultSet resultSet, SqlRunParameters srp) { var sb = srp.exceptionsText; if (resultSet == null) { srp.exceptionCount = 0; } else { srp.exceptionCount = resultSet.exceptions.Count; } if (srp.exceptionCount > 0) { sb.Append(String.Format("--There were {0} exception(s) encountered while running the query.\r\n", resultSet.exceptions.Count)); } for (int i = 0; i < srp.exceptionCount; i++) { var ex = resultSet.exceptions[i]; if (ex is SqlResultProcessingException) { sb.Append(String.Format("--Error processing result: \"{0}\".\r\n", ex.Message)); } else if (ex is SqlExecutionException) { sb.Append(String.Format("--Error executing query: \"{0}\".\r\n", ex.Message)); } else { sb.Append(String.Format("--Error: \"{0}\".\r\n", ex.Message)); } } }
public static void renderAsCSharp(FlexResultSet resultSet, SqlRunParameters srp) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; var columnNamesAndCounts = new Dictionary <string, int>(); sb.Append(String.Format(classHeader, generateValidCSharpClassName(i))); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { var s = result.schema[colIndex]; sb.Append(DisambiguateAndRenderCSharpProperty(s, columnNamesAndCounts)); } sb.Append(classFooter); } } srp.resultsText = sb; }
private static void renderSchemaAndData(FlexResultSet resultSet, SqlRunParameters srp) { var sb = srp.resultsText; for (int i = 0; i < resultSet.results.Count; i++) { if (resultSet.results[i].recordsAffected > 0) { sb.AppendLine(String.Format("--Records affected: {0:G}\r\n\r\n", resultSet.results[i].recordsAffected)); } string resultTableName = "#Result" + (i + 1 + srp.completedResultsCount).ToString(); sb.AppendLine(resultSet.ScriptResultAsCreateTable(i, resultTableName)); sb.Append("\r\n"); if (FieldScripting.ResultIsRenderableAsScriptedData(resultSet.results[i])) { sb.AppendLine(FieldScripting.ScriptResultDataAsInsert(resultSet.results[i], resultTableName, FlexResultSet.SQL2008MaxRowsInValuesClause).ToString()); } srp.completedResultsCount += 1; sb.Append("\r\n"); } }
public static void renderAsCSV(FlexResultSet resultSet, SqlRunParameters srp) { int writtenResultSets = 0; for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { writtenResultSets += 1; if (writtenResultSets > 1) { srp.openNewOutputStream(); } int columnCount = result.visibleColumnCount; //do header for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(columnName(result, colIndex)); if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); } } //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; object[] fieldInfo = result.schema.Rows[colIndex].ItemArray; string fieldTypeName = fieldInfo[(int)FieldScripting.FieldInfo.DataType].ToString(); if (fieldData == null || fieldData is DBNull) { //do nothing } else if (fieldTypeName == "bigint" || fieldTypeName == "numeric" || fieldTypeName == "smallint" || fieldTypeName == "decimal" || fieldTypeName == "smallmoney" || fieldTypeName == "int" || fieldTypeName == "tinyint" || fieldTypeName == "float" || fieldTypeName == "real" || fieldTypeName == "money" || fieldTypeName == "bit") { //todo: may be bug in German culture where they use , as the decimal separator. srp.WriteToStream(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)); } else if (fieldTypeName == "date" || fieldTypeName == "datetime2" || fieldTypeName == "time" || fieldTypeName == "datetime" || fieldTypeName == "smalldatetime") { srp.WriteToStream(escapeForCSV(String.Format("{0}.{1}", ((DateTime)fieldData).ToString("s"), ((DateTime)fieldData).ToString("fff") ))); } else if (fieldTypeName == "binary" || fieldTypeName == "rowversion" || fieldTypeName == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(escapeForCSV(FieldScripting.formatBinary(d, d.Length))); } else if (fieldTypeName == "varbinary" || fieldTypeName == "image") { srp.WriteToStream(escapeForCSV(FieldScripting.formatVarbinary(fieldData))); } else { srp.WriteToStream(escapeForCSV(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))); } if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); }; } } srp.worksheetIsValid = true; } } }
public static FlexResultSet AnalyzeResultWithRollback(SqlConnection openConnection, SqlRunParameters srp, BackgroundWorker bw = null) { FlexResultSet resultSet = new FlexResultSet(); throwExceptionIfConnectionIsNotOpen(openConnection); SqlTransaction transaction = openConnection.BeginTransaction("Tran"); SqlDataReader reader = null; try { srp.command = new SqlCommand(srp.sqlToRun, openConnection, transaction); var cmd = srp.command; cmd.CommandTimeout = 0; // wait forever. //todo: this is a bad way of doing this. Need to abstract further. bw.ReportProgress(5, "Running query..."); reader = executeSQL(resultSet, cmd, reader); int progress = 50; bw.ReportProgress(progress, "Processing results..."); do { FlexResult result = new FlexResult(); if (reader != null) { try { result.recordsAffected = reader.RecordsAffected; processSchemaInfo(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); processData(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } } foreach (Exception ex in result.exceptions) { resultSet.exceptions.Add(ex); } resultSet.results.Add(result); } while (reader != null && reader.NextResult()); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } finally { cleanupReader(reader); rollbackTransaction(transaction); } return resultSet; }
private void TryToSaveSpreadsheet(SqlRunParameters srp) { string fileName = ""; try { fileName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "TSqlFlex" + DateTime.Now.ToString("_yyyyMMddTHHmmss") + ".xml"); } catch (Exception ex) { string error = "Exception when attempting to find personal documents folder for current user. Will not save file."; logger.Log(error + " " + ex.Message); logger.Log(ex.StackTrace); MessageBox.Show(error + " " + ex.Message); fileName = ""; } if (fileName != "") { srp.saveOutputStreamTo(srp.outputFiles[0], fileName); this.lastExportedFilePath = fileName; InvokeFireAndForgetOnFormThread(() => { txtOutput.Text = "--Results written to \"" + fileName + "\".\r\n--You can open this file in Excel.\r\n\r\n"; }); } }
public static void renderAsXMLSpreadsheet(FlexResultSet resultSet, SqlRunParameters srp) { //todo: refactor this and FlexResultSet to to share code and have test coverage. srp.WriteToStream(Utils.GetResourceByName("TSqlFlex.Core.Resources.XMLSpreadsheetTemplateHeader.txt")); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; srp.WriteToStream(String.Format("<Worksheet ss:Name=\"Sheet{0}\">", i + 1)); srp.WriteToStream(String.Format("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">", columnCount, result.data.Count + 1 /* include header row */) ); //do header srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", columnName(result, colIndex))); } srp.WriteToStream("</Row>\r\n"); //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; SQLColumn fieldInfo = result.schema[colIndex]; if (fieldData == null || fieldData is DBNull) { srp.WriteToStream("<Cell/>"); } else if (fieldInfo.DataType == "bigint" || fieldInfo.DataType == "numeric" || fieldInfo.DataType == "smallint" || fieldInfo.DataType == "decimal" || fieldInfo.DataType == "smallmoney" || fieldInfo.DataType == "int" || fieldInfo.DataType == "tinyint" || fieldInfo.DataType == "float" || fieldInfo.DataType == "real" || fieldInfo.DataType == "money" || fieldInfo.DataType == "bit") { srp.WriteToStream(String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } else if (fieldInfo.DataType == "date" || fieldInfo.DataType == "datetime2" || fieldInfo.DataType == "time" || fieldInfo.DataType == "datetime" || fieldInfo.DataType == "smalldatetime") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}.{1}</Data></Cell>\r\n", escapeForXML(((DateTime)fieldData).ToString("s")), escapeForXML(((DateTime)fieldData).ToString("fff")) )); } else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatBinary(d,d.Length)))); } else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatVarbinary(fieldData)))); } else { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } } srp.WriteToStream("</Row>\r\n"); } srp.WriteToStream("</Table></Worksheet>\r\n"); srp.worksheetIsValid = true; } } srp.WriteToStream("</Workbook>\r\n"); }
public static void renderAsCSV(FlexResultSet resultSet, SqlRunParameters srp) { int writtenResultSets = 0; for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { writtenResultSets += 1; if (writtenResultSets > 1) { srp.openNewOutputStream(); } int columnCount = result.visibleColumnCount; //do header for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(columnName(result, colIndex)); if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); } } //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; SQLColumn fieldInfo = result.schema[colIndex]; if (fieldData == null || fieldData is DBNull) { //do nothing } else if (numberLikeDataTypesForCSV.Contains(fieldInfo.DataType)) { //todo: may be bug in German culture where they use , as the decimal separator. srp.WriteToStream(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)); } else if (dateLikeDataTypesForCSV.Contains(fieldInfo.DataType)) { srp.WriteToStream(escapeForCSV(String.Format("{0}.{1}", ((DateTime)fieldData).ToString("s"), ((DateTime)fieldData).ToString("fff") ))); } else if (fieldInfo.DataType == "binary" || fieldInfo.DataType == "rowversion" || fieldInfo.DataType == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(escapeForCSV(FieldScripting.formatBinary(d, d.Length))); } else if (fieldInfo.DataType == "varbinary" || fieldInfo.DataType == "image") { srp.WriteToStream(escapeForCSV(FieldScripting.formatVarbinary(fieldData))); } else { srp.WriteToStream(escapeForCSV(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false))); } if (colIndex + 1 < columnCount) { srp.WriteToStream(","); } else { srp.WriteToStream("\r\n"); }; } } srp.worksheetIsValid = true; } } }
private void RenderCsv(SqlRunParameters srp) { if (srp.worksheetIsValid) { TryToSaveCSVs(srp); } else { drawExceptions(srp); } }
private void drawExceptions(SqlRunParameters srp, string header = "") { InvokeFireAndForgetOnFormThread(() => { if (header == "") { txtOutput.Text = srp.exceptionsText.ToString(); } else { txtOutput.Text = header + "\r\n\r\n" + srp.exceptionsText.ToString(); } }); }
private void cmdRunNRollback_Click(object sender, EventArgs e) { if (!Utils.IsValidConnectionStringBuilder(connStringBuilder)) { MessageBox.Show("Select the target database in the object tree first."); return; } if (!queryWorker.IsBusy) { lastExportedFilePath = ""; sqlStopwatch = new Stopwatch(); sqlStopwatch.Start(); queryTimer.Enabled = true; setUIState(true); try { var srp = new SqlRunParameters(connStringBuilder, getSqlToRun(), cmbResultsType.SelectedItem.ToString()); srp.completedResultsCount = completedResultsCount; queryWorker.RunWorkerAsync(srp); } catch (Exception ex) { string error = "There was an exception when setting up the query run parameters. " + ex.Message; logger.Log(error + " " + ex.StackTrace); MessageBox.Show(error); sqlStopwatch.Stop(); queryTimer.Enabled = false; setUIState(false); } } }
private static void renderExceptionToSqlRunParameters(string generalDescriptionOfWhenTheErrorOccurred, SqlRunParameters srp, Exception ex) { srp.exceptionsText.Append("--Exception encountered "); srp.exceptionsText.Append(generalDescriptionOfWhenTheErrorOccurred); srp.exceptionsText.Append(".\r\n\r\n/* "); srp.exceptionsText.Append(ex.Message); srp.exceptionsText.Append("\r\n\r\n"); srp.exceptionsText.Append(ex.StackTrace); srp.exceptionsText.Append("\r\n*/"); }
private void RenderInsertStatements(SqlRunParameters srp) { bool success = false; try { if (srp.exceptionsText.Length > 0) { if (srp.resultsText.Length > 0) { txtOutput.Text = srp.exceptionsText.ToString() + "\r\n\r\n" + srp.resultsText.ToString(); success = true; } else { drawExceptions(srp); success = true; } } else { txtOutput.Text = srp.resultsText.ToString(); success = true; } } catch (Exception ex) { srp.resultsText = null; srp.exceptionsText.Append("\r\n\r\n/*\r\n\r\nException while attempting to display results: "); srp.exceptionsText.Append(ex.Message); srp.exceptionsText.Append("\r\n\r\n"); srp.exceptionsText.Append(ex.StackTrace); srp.exceptionsText.Append("\r\n*/"); } if (!success) { GC.Collect(); txtOutput.Text = srp.exceptionsText.ToString(); } }
private void RenderXmlSpreadsheet(SqlRunParameters srp) { if (srp.worksheetIsValid) { TryToSaveSpreadsheet(srp); } else { drawExceptions(srp); } }
public static void renderAsXMLSpreadsheet(FlexResultSet resultSet, SqlRunParameters srp) { //todo: refactor this and FlexResultSet to to share code and have test coverage. srp.WriteToStream(Utils.GetResourceByName("TSqlFlex.Core.Resources.XMLSpreadsheetTemplateHeader.txt")); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; srp.WriteToStream(String.Format("<Worksheet ss:Name=\"Sheet{0}\">", i + 1)); srp.WriteToStream(String.Format("<Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\" x:FullRows=\"1\" ss:DefaultRowHeight=\"15\">", columnCount, result.data.Count + 1 /* include header row */) ); //do header srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s62\"><Data ss:Type=\"String\">{0}</Data></Cell>", columnName(result, colIndex))); } srp.WriteToStream("</Row>\r\n"); //do data rows for (int rowIndex = 0; rowIndex < result.data.Count; rowIndex += 1) { srp.WriteToStream("<Row>"); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { //todo: fix each of these items to work with the actual scripting stuff (requires finishing major refactoring work). object fieldData = result.data[rowIndex][colIndex]; object[] fieldInfo = result.schema.Rows[colIndex].ItemArray; string fieldTypeName = fieldInfo[(int)FieldScripting.FieldInfo.DataType].ToString(); if (fieldData == null || fieldData is DBNull) { srp.WriteToStream("<Cell/>"); } else if (fieldTypeName == "bigint" || fieldTypeName == "numeric" || fieldTypeName == "smallint" || fieldTypeName == "decimal" || fieldTypeName == "smallmoney" || fieldTypeName == "int" || fieldTypeName == "tinyint" || fieldTypeName == "float" || fieldTypeName == "real" || fieldTypeName == "money" || fieldTypeName == "bit") { srp.WriteToStream(String.Format("<Cell><Data ss:Type=\"Number\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } else if (fieldTypeName == "date" || fieldTypeName == "datetime2" || fieldTypeName == "time" || fieldTypeName == "datetime" || fieldTypeName == "smalldatetime") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"DateTime\">{0}.{1}</Data></Cell>\r\n", escapeForXML(((DateTime)fieldData).ToString("s")), escapeForXML(((DateTime)fieldData).ToString("fff")) )); } else if (fieldTypeName == "binary" || fieldTypeName == "rowversion" || fieldTypeName == "timestamp") { byte[] d = (byte[])result.data[rowIndex][colIndex]; srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatBinary(d, d.Length)))); } else if (fieldTypeName == "varbinary" || fieldTypeName == "image") { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.formatVarbinary(fieldData)))); } else { srp.WriteToStream(String.Format("<Cell ss:StyleID=\"s64\"><Data ss:Type=\"String\">{0}</Data></Cell>\r\n", escapeForXML(FieldScripting.valueAsTSQLLiteral(fieldData, fieldInfo, false)))); } } srp.WriteToStream("</Row>\r\n"); } srp.WriteToStream("</Table></Worksheet>\r\n"); srp.worksheetIsValid = true; } } srp.WriteToStream("</Workbook>\r\n"); }
private void TryToSaveCSVs(SqlRunParameters srp) { txtOutput.Text = ""; string personalFolder = Environment.GetFolderPath(Environment.SpecialFolder.Personal); string timestamp = DateTime.Now.ToString("_yyyyMMddTHHmmss"); for (int csvIndex = 0; csvIndex < srp.outputFiles.Count; csvIndex += 1) { string fileName = ""; try { fileName = Path.Combine(personalFolder, "TSqlFlex" + timestamp + (srp.outputFiles.Count > 1 ? "_" + (csvIndex+1).ToString() : "") + ".csv"); } catch (Exception ex) { string error = "Exception when attempting to find personal documents folder for current user. Will not save file."; logger.Log(error + " " + ex.Message); logger.Log(ex.StackTrace); MessageBox.Show(error + " " + ex.Message); fileName = ""; break; } if (fileName != "") { srp.saveOutputStreamTo(srp.outputFiles[csvIndex], fileName); this.lastExportedFilePath = fileName; InvokeFireAndForgetOnFormThread(() => { txtOutput.Text += "--Results written to \"" + fileName + "\".\r\n--You can open this file in your text editor.\r\n\r\n"; }); } } }
public static FlexResultSet AnalyzeResultWithRollback(SqlConnection openConnection, SqlRunParameters srp, BackgroundWorker bw = null) { FlexResultSet resultSet = new FlexResultSet(); throwExceptionIfConnectionIsNotOpen(openConnection); SqlTransaction transaction = openConnection.BeginTransaction("Tran"); SqlDataReader reader = null; try { SqlCommand cmd = new SqlCommand(srp.sqlToRun, openConnection, transaction); //todo: this is a bad way of doing this. Need to abstract further. bw.ReportProgress(5, "Running query..."); reader = executeSQL(resultSet, cmd, reader); int progress = 50; bw.ReportProgress(progress, "Processing results..."); do { FlexResult result = new FlexResult(); if (reader != null) { try { result.recordsAffected = reader.RecordsAffected; processSchemaInfo(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); processData(reader, result); if (progress < 90) { progress += 5; } bw.ReportProgress(progress, "Processing results..."); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } } foreach (Exception ex in result.exceptions) { resultSet.exceptions.Add(ex); } resultSet.results.Add(result); } while (reader != null && reader.NextResult()); } catch (Exception ex) { resultSet.exceptions.Add(new SqlResultProcessingException(ex)); } finally { cleanupReader(reader); rollbackTransaction(transaction); } return(resultSet); }
public static void renderAsCSharp(FlexResultSet resultSet, SqlRunParameters srp) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < resultSet.results.Count; i++) { FlexResult result = resultSet.results[i]; if (result.schema != null && result.data != null) { int columnCount = result.visibleColumnCount; var columnNamesAndCounts = new Dictionary<string, int>(); sb.Append(String.Format(classHeader, generateValidCSharpClassName(i))); for (int colIndex = 0; colIndex < columnCount; colIndex += 1) { var s = result.schema[colIndex]; sb.Append(DisambiguateAndRenderCSharpProperty(s, columnNamesAndCounts)); } sb.Append(classFooter); } } srp.resultsText = sb; }