public void AnonymousColumnsAreHandledCorrectly()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List <SQLColumn>()
{
new SQLColumn()
{
ColumnName = "", DataType = "varchar"
},
new SQLColumn()
{
ColumnName = "", DataType = "int"
}
};
result.data = new List <object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public string anonymousProperty { get; set; }
public int anonymousProperty_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
public void handle_duplicate_column_names()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List <SQLColumn>()
{
new SQLColumn()
{
ColumnName = "testa", DataType = "int"
},
new SQLColumn()
{
ColumnName = "testa", DataType = "varchar"
}
};
result.data = new List <object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public int testa { get; set; }
public string testa_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
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 void EscapedNamesLeadingToACollisionAreHandledCorrectly()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List <SQLColumn>()
{
new SQLColumn()
{
ColumnName = "test_1", DataType = "varchar"
},
new SQLColumn()
{
ColumnName = "test?1", DataType = "varchar"
}
};
result.data = new List <object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public string test_1 { get; set; }
public string test_1_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
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";
});
}
}
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 void RenderXmlSpreadsheet(SqlRunParameters srp)
{
if (srp.worksheetIsValid)
{
TryToSaveSpreadsheet(srp);
}
else
{
drawExceptions(srp);
}
}
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();
}
});
}
public void RenderingTimeField_RendersCorrectly()
{
var frs = new FlexResultSet();
var dt = new List <SQLColumn>()
{
SchemaScriptingTests.FakeColumn("ColIntNotNull", "MyStuff", 32, "int", false, 255, 255),
SchemaScriptingTests.FakeColumn("ColIntNull", "MyStuff", 32, "int", true, 255, 255),
SchemaScriptingTests.FakeColumn("ColTimeOfDay", "MyStuff", 32, "time", true, 255, 255)
};
var result = new FlexResult()
{
schema = dt,
data = new List <object[]>()
{
new object[] { 99, 111, new TimeSpan(0, 00, 00, 00, 001) },
new object[] { 42, null, new TimeSpan(23, 59, 30) },
}
};
frs.results.Add(result);
var tempFileName = Guid.NewGuid().ToString() + ".txt";
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), "", SqlRunParameters.TO_XML_SPREADSHEET, tempFileName);
XmlSpreadsheetRenderer.renderAsXMLSpreadsheet(frs, srp);
var xmlSpreadsheetContent = srp.getOutputStreamAsString(tempFileName);
Assert.IsTrue(xmlSpreadsheetContent.Length > 1000, "expected more than 1000 characters of output");
Assert.IsTrue(xmlSpreadsheetContent.Contains(">99<"));
Assert.IsTrue(xmlSpreadsheetContent.Contains(">111<"));
Assert.IsTrue(xmlSpreadsheetContent.Contains(">1899-12-31T00:00:00.001<"));
Assert.IsTrue(xmlSpreadsheetContent.Contains(">42<"));
Assert.IsTrue(xmlSpreadsheetContent.Contains(">1899-12-31T23:59:30.000<"));
XmlDocument doc = null;
Assert.DoesNotThrow(() => {
doc = new XmlDocument();
doc.LoadXml(xmlSpreadsheetContent);
}, "expected no exception");
Assert.IsNotNull(doc, "expected valid XML");
}
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();
}
}
public void EscapedNamesLeadingToACollisionAreHandledCorrectly()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List<SQLColumn>() {
new SQLColumn() { ColumnName = "test_1", DataType = "varchar" },
new SQLColumn() { ColumnName = "test?1", DataType = "varchar" }
};
result.data = new List<object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public string test_1 { get; set; }
public string test_1_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
public void AnonymousColumnsAreHandledCorrectly()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List<SQLColumn>() {
new SQLColumn() { ColumnName = "", DataType = "varchar" },
new SQLColumn() { ColumnName = "", DataType = "int" }
};
result.data = new List<object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public string anonymousProperty { get; set; }
public int anonymousProperty_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
public void NullabilityWorksCorrectly()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List <SQLColumn>()
{
new SQLColumn()
{
ColumnName = "a", DataType = "varchar", AllowNulls = true
},
new SQLColumn()
{
ColumnName = "b", DataType = "int", AllowNulls = true
},
new SQLColumn()
{
ColumnName = "c", DataType = "smalldatetime", AllowNulls = true
}
};
result.data = new List <object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public string a { get; set; }
public int? b { get; set; }
public DateTime? c { get; set; }
}
";
Assert.AreEqual(expected, srp.resultsText.ToString());
}
public void handle_duplicate_column_names()
{
var resultSet = new FlexResultSet();
var result = new FlexResult();
result.schema = new List<SQLColumn>() {
new SQLColumn() { ColumnName = "testa", DataType = "int" },
new SQLColumn() { ColumnName = "testa", DataType = "varchar" }
};
result.data = new List<object[]>();
resultSet.results.Add(result);
var srp = new SqlRunParameters(new SqlConnectionStringBuilder(), SqlRunParameters.TO_CSHARP, "");
CSharpRenderer.renderAsCSharp(resultSet, srp);
var expected = @"public class Result0
{
public int testa { get; set; }
public string testa_2 { get; set; }
}
";
Assert.AreEqual(srp.resultsText.ToString(), expected);
}
