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 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");
}
}
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 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));
}
}
}
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;
}
