public void FieldWithCarriageReturn_IsScriptedWithBrackets()
{
string fieldName = "Crazy\rtown";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual("[Crazy\rtown]", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void ReservedWord_IsScriptedWithBrackets()
{
string fieldName = "Drop";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual("[Drop]", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void FieldWithCloseSquareBracket_IsScriptedWithBracketsAndAlsoFurtherEscaped()
{
string fieldName = "test]ing";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual("[test]]ing]", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void FieldWithSpaces_IsScriptedWithBrackets()
{
string fieldName = "What We Should Be Sending";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual("[What We Should Be Sending]", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void FieldWithLineFeed_IsScriptedWithBrackets()
{
string fieldName = "This\nis";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual("[This\nis]", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void NonReservedWord_IsScriptedWithoutBrackets()
{
string fieldName = "TestColumnName";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual(false, TSqlRules.IsReservedWord(fieldName));
Assert.AreEqual("TestColumnName", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void EmptyColumnName_IsScriptedAnonymously()
{
string fieldName = "";
var fieldInfo = SchemaScriptingTests.FakeColumn(fieldName, "test", 32, "int", false, 0, 0);
Assert.AreEqual(false, TSqlRules.IsReservedWord(fieldName));
Assert.AreEqual("anonymousColumn1", FieldScripting.FieldNameOrDefault(fieldInfo, 0));
}
public void SQLVARIANT_DateTimeOffset_Data_ScriptsCorrectly()
{
DateTimeOffset baseData = new DateTimeOffset(new DateTime(2014, 5, 30), new TimeSpan(1, 0, 0));
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sql_variant", false, 0, 0);
Assert.AreEqual("'2014-05-30T00:00:00+01:00'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "sql_variant");
}
public void TIME_HighResolution_Data_ScriptsCorrectly()
{
TimeSpan baseData = new TimeSpan(TimeSpan.TicksPerHour * 2 + TimeSpan.TicksPerMinute * 33 + TimeSpan.TicksPerSecond * 44 + 1234567);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "time", false, 0, 0);
Assert.AreEqual("'02:33:44.1234567'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "time fractional seconds");
Assert.AreEqual("02:33:44.1234567", FieldScripting.formatTime(data, false), "time fractional seconds");
}
public void DATE_Data_ScriptsCorrectly()
{
DateTime baseData = new DateTime(2000, 10, 31);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "date", false, 0, 0);
Assert.AreEqual("'2000-10-31'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "date");
Assert.AreEqual("2000-10-31", FieldScripting.formatDate(data, false), "date");
}
public void SQLVARIANT_Bit_Data_ScriptsCorrectly()
{
bool baseData = false;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sql_variant", false, 0, 0);
Assert.AreEqual("0", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "sql_variant");
}
public void SQLVARIANT_Guid_Data_ScriptsCorrectly()
{
Guid baseData = new Guid("9631B0CA-D86F-4CA2-BFA5-93A9980D050A");
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sql_variant", false, 0, 0);
Assert.AreEqual("'9631B0CA-D86F-4CA2-BFA5-93A9980D050A'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "sql_variant");
}
public void GEOGRAPHY_Data_ScriptsCorrectly()
{
SqlGeography baseData = SqlGeography.STGeomFromText(new SqlChars("LINESTRING(-122.360 47.656, -122.343 47.656 )"), 4326);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sys.junk.geography", false, 0, 0);
Assert.AreEqual("geography::STGeomFromText('LINESTRING (-122.36 47.656, -122.343 47.656)',4326)", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "geography");
Assert.AreEqual("LINESTRING (-122.36 47.656, -122.343 47.656) 4326", FieldScripting.formatGeography(data, false), "geography");
}
public void GEOMETRY_Data_ScriptsCorrectly()
{
SqlGeometry baseData = SqlGeometry.STGeomFromText(new SqlChars("LINESTRING (100 100, 20 180, 180 180)"), 0);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sys.junk.geometry", false, 0, 0);
Assert.AreEqual("geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)',0)", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "geometry");
Assert.AreEqual("LINESTRING (100 100, 20 180, 180 180) 0", FieldScripting.formatGeometry(data, false), "geometry");
}
public void UNIQUEIDENTIFIER_Data_ScriptsCorrectly()
{
Guid baseData = new Guid("9631B0CA-D86F-4CA2-BFA5-93A9980D050A");
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "uniqueidentifier", false, 0, 0);
Assert.AreEqual("'9631B0CA-D86F-4CA2-BFA5-93A9980D050A'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "uniqueidentifier");
Assert.AreEqual("9631B0CA-D86F-4CA2-BFA5-93A9980D050A", FieldScripting.formatGuid(data, false), "uniqueidentifier");
}
public void SQLVARIANT_Binary_Data_ScriptsCorrectly()
{
Int32 baseData = 123456;
byte[] data = BitConverter.GetBytes(baseData);
EnsureByteArrayIsBigEndianLikeSQLServer(data);
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 20, "sql_variant", false, 0, 0);
Assert.AreEqual("0x0001E240", FieldScripting.valueAsTSQLLiteral((object)data, fieldInfo), "sql_variant");
}
public void String_IncludingHighChar_ScriptsCorrectly()
{
var highChar = new char[] { '\0', '†', 'B', 'C' };
string baseData = new string(highChar);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 15, "nvarchar", false, 0, 0);
Assert.AreEqual("0x00864243", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "nvarchar");
}
public void TIMESTAMP_Data_ScriptsCorrectly()
{
Int32 baseData = 123456;
byte[] data = BitConverter.GetBytes(baseData);
EnsureByteArrayIsBigEndianLikeSQLServer(data);
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 8, "timestamp", false, 0, 0);
Assert.AreEqual("0x000000000001E240", FieldScripting.valueAsTSQLLiteral((object)data, fieldInfo), "timestamp");
Assert.AreEqual("0x000000000001E240", FieldScripting.formatTimestamp((object)data), "timestamp");
}
public void ResultSet_WithNoReturnedSchema_ResultsInNoReturnedSchemaComment()
{
FlexResultSet fsr = new FlexResultSet();
var dt = SchemaScriptingTests.FakeSchemaDataTable();
var result = new FlexResult();
fsr.results.Add(result);
Assert.AreEqual("--No schema for result from query.", FieldScripting.ScriptResultDataAsInsert(result, "#result0", FlexResultSet.SQL2008MaxRowsInValuesClause).ToString());
}
public void INT_Data_ScriptsCorrectly()
{
Int32 baseData = 2000123456;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "int", false, 0, 0);
Assert.AreEqual("2000123456", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "positive int");
baseData = -2000123456;
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "int", false, 0, 0);
Assert.AreEqual("-2000123456", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "negative int");
}
public void BIT_Data_ScriptsCorrectly()
{
bool baseData = true;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 1, "bit", false, 0, 0);
Assert.AreEqual("1", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "true should script as 1");
baseData = false;
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 1, "bit", false, 0, 0);
Assert.AreEqual("0", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "false should script as 0");
}
public void TINYINT_Data_ScriptsCorrectly()
{
sbyte baseData = 125;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 8, "tinyint", false, 0, 0);
Assert.AreEqual("125", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "positive tinyint");
baseData = -125;
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 8, "tinyint", false, 0, 0);
Assert.AreEqual("-125", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "negative tinyint");
}
public void BIGINT_Data_ScriptsCorrectly()
{
Int64 baseData = 999999999999;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "bigint", false, 0, 0);
Assert.AreEqual("999999999999", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "positive bigint");
baseData = -999999999999;
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "bigint", false, 0, 0);
Assert.AreEqual("-999999999999", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "negative bigint");
}
public void SMALLINT_Data_ScriptsCorrectly()
{
Int16 baseData = 31000;
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 16, "smallint", false, 0, 0);
Assert.AreEqual("31000", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "positive smallint");
baseData = -31000;
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 16, "smallint", false, 0, 0);
Assert.AreEqual("-31000", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "negative smallint");
}
public void DATETIMEOFFSET_Data_ScriptsCorrectly()
{
DateTimeOffset baseData = new DateTimeOffset(2000, 10, 31, 2, 33, 44, new TimeSpan(3, 0, 0));
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "datetimeoffset", false, 0, 0);
Assert.AreEqual("'2000-10-31T02:33:44+03:00'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "datetimeoffset no fractional seconds");
Assert.AreEqual("2000-10-31T02:33:44+03:00", FieldScripting.formatDatetimeoffset(data, false), "datetimeoffset no fractional seconds");
baseData = baseData.AddTicks(1234567);
data = baseData;
Assert.AreEqual("'2000-10-31T02:33:44.1234567+03:00'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "datetimeoffset fractional seconds");
Assert.AreEqual("2000-10-31T02:33:44.1234567+03:00", FieldScripting.formatDatetimeoffset(data, false), "datetimeoffset fractional seconds");
}
public void SMALLDATETIME_Data_ScriptsCorrectly()
{
DateTime baseData = new DateTime(2000, 10, 31, 2, 33, 0);
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 32, "smalldatetime", false, 0, 0);
Assert.AreEqual("'2000-10-31T02:33:00'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "smalldatetime");
Assert.AreEqual("2000-10-31T02:33:00", FieldScripting.formatSmallDateTime(data, false), "smalldatetime");
baseData = new DateTime(2000, 10, 31, 0, 0, 0);
data = baseData;
Assert.AreEqual("'2000-10-31'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "midnight omits time altogether");
Assert.AreEqual("2000-10-31", FieldScripting.formatSmallDateTime(data, false), "midnight omits time altogether");
}
public void VARBINARY_Data_ScriptsCorrectly()
{
Int32 baseData = 123456;
const int columnSize = 20;
byte[] data = BitConverter.GetBytes(baseData);
EnsureByteArrayIsBigEndianLikeSQLServer(data);
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", columnSize, "varbinary", false, 0, 0);
Assert.AreEqual("0x0001E240", FieldScripting.valueAsTSQLLiteral((object)data, fieldInfo), "varbinary");
Assert.AreEqual("0x0001E240", FieldScripting.formatVarbinary((object)data), "varbinary");
}
public void SQLVARIANT_String_Data_ScriptsCorrectly()
{
string baseData = "sql variant test";
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sql_variant", false, 0, 0);
Assert.AreEqual("N'sql variant test'", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "sql_variant");
baseData = "That's fun! ";
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 64, "sql_variant", false, 0, 0);
Assert.AreEqual("N'That''s fun! '", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "sql_variant escapes string fields");
}
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");
}
public void HIERARCHYID_Data_ScriptsCorrectly()
{
SqlHierarchyId baseData = SqlHierarchyId.Parse("/");
object data = baseData;
var fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 20, "sys.junk.hierarchyid", false, 0, 0);
Assert.AreEqual("0x", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "hierarchyid");
Assert.AreEqual("0x", FieldScripting.formatHierarchyId(data), "hierarchyid");
baseData = SqlHierarchyId.Parse("/1/");
data = baseData;
fieldInfo = SchemaScriptingTests.FakeColumn("test", "test", 20, "sys.junk.hierarchyid", false, 0, 0);
Assert.AreEqual("0x58", FieldScripting.valueAsTSQLLiteral(data, fieldInfo), "hierarchyid");
Assert.AreEqual("0x58", FieldScripting.formatHierarchyId(data), "hierarchyid");
}