/// <summary> /// Reads the value of a cell from the file wrapper, checks to see if it null using /// <paramref name="isNullFunc"/>, and converts it to the proper output type using /// <paramref name="convertFunc"/>. /// </summary> /// <param name="offset">Offset into the file to read from</param> /// <param name="rowId">Internal ID of the row to set on all cells in this row</param> /// <param name="convertFunc">Function to use to convert the buffer to the target type</param> /// <param name="isNullFunc"> /// If provided, this function will be used to determine if the value is null /// </param> /// <param name="toStringFunc">Optional function to use to convert the object to a string.</param> /// <typeparam name="T">The expected type of the cell. Used to keep the code honest</typeparam> /// <returns>The object, a display value, and the length of the value + its length</returns> private FileStreamReadResult ReadCellHelper <T>(long offset, long rowId, Func <int, T> convertFunc, Func <int, bool> isNullFunc = null, Func <T, string> toStringFunc = null) { LengthResult length = ReadLength(offset); DbCellValue result = new DbCellValue { RowId = rowId }; if (isNullFunc == null ? length.ValueLength == 0 : isNullFunc(length.TotalLength)) { result.RawObject = null; result.DisplayValue = SR.QueryServiceCellNull; result.IsNull = true; } else { AssureBufferLength(length.ValueLength); fileStream.Read(buffer, 0, length.ValueLength); T resultObject = convertFunc(length.ValueLength); result.RawObject = resultObject; result.DisplayValue = toStringFunc == null?result.RawObject.ToString() : toStringFunc(resultObject); result.IsNull = false; } return(new FileStreamReadResult(result, length.TotalLength)); }
public static void VerifyJsonMatchesData(DbCellValue[][] data, ColumnInfo[] columns, string filePath) { // ... Upon deserialization to an array of dictionaries Assert.True(File.Exists(filePath), "Expected file to have been written"); string output = File.ReadAllText(filePath); Dictionary<string, object>[] outputObject = JsonConvert.DeserializeObject<Dictionary<string, object>[]>(output); // ... There should be 2 items in the array, // ... The item should have three fields, and three values, assigned appropriately // ... The deserialized values should match the display value Assert.Equal(data.Length, outputObject.Length); for (int rowIndex = 0; rowIndex < outputObject.Length; rowIndex++) { Dictionary<string,object> item = outputObject[rowIndex]; Assert.Equal(columns.Length, item.Count); for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++) { var key = columns[columnIndex].Name; Assert.True(item.ContainsKey(key)); DbCellValue value = data[rowIndex][columnIndex]; object expectedValue = GetJsonExpectedValue(value, columns[columnIndex]); Assert.Equal(expectedValue, item[key]); } } }
public static void VerifyXmlMatchesData(DbCellValue[][] data, ColumnInfo[] columns, string filePath) { // ... Upon deserialization to an array of dictionaries Assert.True(File.Exists(filePath), "Expected file to have been written"); string output = File.ReadAllText(filePath); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(output); // ... There should be 2 items in the array, // ... The item should have three fields, and three values, assigned appropriately // ... The deserialized values should match the display value string xpath = "data/row"; var rows = xmlDoc.SelectNodes(xpath); Assert.Equal(data.Length, rows.Count); for (int rowIndex = 0; rowIndex < rows.Count; rowIndex++) { var rowValue = rows.Item(rowIndex); var xmlCols = rowValue.ChildNodes.Cast<XmlNode>().ToArray(); Assert.Equal(columns.Length, xmlCols.Length); for (int columnIndex = 0; columnIndex < columns.Length; columnIndex++) { var columnName = columns[columnIndex].Name; var xmlColumn = xmlCols.FirstOrDefault(x => x.Name == columnName); Assert.NotNull(xmlColumn); DbCellValue value = data[rowIndex][columnIndex]; object expectedValue = GetXmlExpectedValue(value); Assert.Equal(expectedValue, xmlColumn.InnerText); } } }
/// <summary> /// Constructs a new EditCell based on a DbCellValue /// </summary> /// <param name="dbCellValue">The DbCellValue that will be enhanced</param> /// <param name="isDirty">Whether or not the edit cell is dirty</param> public EditCell(DbCellValue dbCellValue, bool isDirty) { Validate.IsNotNull(nameof(dbCellValue), dbCellValue); dbCellValue.CopyTo(this); IsDirty = isDirty; }
public async Task GetEditRow() { // Setup: Create a row delete var columns = Common.GetColumns(false); var rs = await Common.GetResultSet(columns, false); var etm = Common.GetStandardMetadata(columns); RowDelete rd = new RowDelete(0, rs, etm); // If: I attempt to get an edit row DbCellValue[] cells = rs.GetRow(0).ToArray(); EditRow er = rd.GetEditRow(cells); // Then: // ... The state should be dirty Assert.True(er.IsDirty); Assert.Equal(EditRow.EditRowState.DirtyDelete, er.State); // ... The ID should be the same as the one provided Assert.Equal(0, er.Id); // ... The row should match the cells that were given and should be dirty Assert.Equal(cells.Length, er.Cells.Length); for (int i = 0; i < cells.Length; i++) { DbCellValue originalCell = cells[i]; EditCell outputCell = er.Cells[i]; Assert.Equal(originalCell.DisplayValue, outputCell.DisplayValue); Assert.Equal(originalCell.IsNull, outputCell.IsNull); Assert.True(outputCell.IsDirty); // Note: No real need to check the RawObject property } }
/// <summary> /// Generates a WHERE clause that uses the key columns of the table to uniquely identity /// the row that will be updated. /// </summary> /// <param name="parameterize"> /// Whether or not to generate a parameterized where clause. If <c>true</c> verbatim values /// will be replaced with paremeters (like @Param12). The parameters must be added to the /// SqlCommand used to execute the commit. /// </param> /// <returns>A <see cref="WhereClause"/> object</returns> protected WhereClause GetWhereClause(bool parameterize) { WhereClause output = new WhereClause(); if (!AssociatedObjectMetadata.KeyColumns.Any()) { throw new InvalidOperationException(SR.EditDataColumnNoKeyColumns); } IList <DbCellValue> row = AssociatedResultSet.GetRow(RowId); foreach (EditColumnMetadata col in AssociatedObjectMetadata.KeyColumns) { // Put together a clause for the value of the cell DbCellValue cellData = row[col.Ordinal]; string cellDataClause; if (cellData.IsNull) { cellDataClause = "IS NULL"; } else { if (cellData.RawObject is byte[] || col.DbColumn.DataTypeName.Equals("TEXT", StringComparison.OrdinalIgnoreCase) || col.DbColumn.DataTypeName.Equals("NTEXT", StringComparison.OrdinalIgnoreCase)) { // Special cases for byte[] and TEXT/NTEXT types cellDataClause = "IS NOT NULL"; } else { // General case is to just use the value from the cell if (parameterize) { // Add a parameter and parameterized clause component // NOTE: We include the row ID to make sure the parameter is unique if // we execute multiple row edits at once. string paramName = $"@Param{RowId}{col.Ordinal}"; cellDataClause = $"= {paramName}"; SqlParameter parameter = new SqlParameter(paramName, col.DbColumn.SqlDbType) { Value = cellData.RawObject }; output.Parameters.Add(parameter); } else { // Add the clause component with the formatted value cellDataClause = $"= {ToSqlScript.FormatValue(cellData, col.DbColumn)}"; } } } string completeComponent = $"({col.EscapedName} {cellDataClause})"; output.ClauseComponents.Add(completeComponent); } return(output); }
/// <summary> /// Generates a subset of the rows from the result set /// </summary> /// <param name="startRow">The starting row of the results</param> /// <param name="rowCount">How many rows to retrieve</param> /// <returns>A subset of results</returns> public Task <ResultSetSubset> GetSubset(long startRow, int rowCount) { // Sanity check to make sure that results read has started if (!hasStartedRead) { throw new InvalidOperationException(SR.QueryServiceResultSetNotRead); } // Sanity check to make sure that the row and the row count are within bounds if (startRow < 0 || startRow >= RowCount) { throw new ArgumentOutOfRangeException(nameof(startRow), SR.QueryServiceResultSetStartRowOutOfRange); } if (rowCount <= 0) { throw new ArgumentOutOfRangeException(nameof(rowCount), SR.QueryServiceResultSetRowCountOutOfRange); } return(Task.Factory.StartNew(() => { DbCellValue[][] rows; using (IFileStreamReader fileStreamReader = fileStreamFactory.GetReader(outputFileName)) { // If result set is 'for xml' or 'for json', // Concatenate all the rows together into one row if (isSingleColumnXmlJsonResultSet) { // Iterate over all the rows and process them into a list of string builders // ReSharper disable once AccessToDisposedClosure The lambda is used immediately in string.Join call IEnumerable <string> rowValues = fileOffsets.Select(rowOffset => fileStreamReader.ReadRow(rowOffset, 0, Columns)[0].DisplayValue); string singleString = string.Join(string.Empty, rowValues); DbCellValue cellValue = new DbCellValue { DisplayValue = singleString, IsNull = false, RawObject = singleString, RowId = 0 }; rows = new[] { new[] { cellValue } }; } else { // Figure out which rows we need to read back IEnumerable <long> rowOffsets = fileOffsets.LongSkip(startRow).Take(rowCount); // Iterate over the rows we need and process them into output // ReSharper disable once AccessToDisposedClosure The lambda is used immediately in .ToArray call rows = rowOffsets.Select((offset, id) => fileStreamReader.ReadRow(offset, id, Columns).ToArray()).ToArray(); } } // Retrieve the subset of the results as per the request return new ResultSetSubset { Rows = rows, RowCount = rows.Length }; })); }
public SaveAsExcelFileStreamWriterHelperTests() { _stream = new MemoryStream(); using (var helper = new SaveAsExcelFileStreamWriterHelper(_stream, true)) using (var sheet = helper.AddSheet()) { DbCellValue value = new DbCellValue(); sheet.AddRow(); value.IsNull = true; sheet.AddCell(value); value.IsNull = false; value.RawObject = ""; sheet.AddCell(value); value.RawObject = "test string"; sheet.AddCell(value); value.RawObject = 3; sheet.AddCell(value); value.RawObject = 3.5; sheet.AddCell(value); value.RawObject = false; sheet.AddCell(value); value.RawObject = true; sheet.AddCell(value); sheet.AddRow(); value.RawObject = new DateTime(1900, 2, 28); sheet.AddCell(value); value.RawObject = new DateTime(1900, 3, 1); sheet.AddCell(value); value.RawObject = new DateTime(1900, 3, 1, 15, 00, 00); sheet.AddCell(value); value.RawObject = new DateTime(1, 1, 1, 15, 00, 00); sheet.AddCell(value); value.RawObject = new TimeSpan(15, 00, 00); sheet.AddCell(value); value.RawObject = new TimeSpan(24, 00, 00); sheet.AddCell(value); } }
public void StringTypeTest(string datatype) { // Setup: Build a column and cell for the string type column DbColumn column = new FormatterTestDbColumn(datatype); DbCellValue cell = new DbCellValue { RawObject = "test string" }; // If: I attempt to format a string type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should match the output string Assert.Equal("N'test string'", output); }
public void IntegerNumericTest(string dataType) { // Setup: Build a column and cell for the integer type column DbColumn column = new FormatterTestDbColumn(dataType); DbCellValue cell = new DbCellValue { RawObject = (long)123 }; // If: I attempt to format an integer type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should be able to be converted back into a long Assert.Equal(cell.RawObject, long.Parse(output)); }
public void FloatTest() { // Setup: Build a column and cell for the approx numeric type column DbColumn column = new FormatterTestDbColumn("REAL"); DbCellValue cell = new DbCellValue { RawObject = (float)3.14159 }; // If: I attempt to format a approx numeric type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should be able to be converted back into a double Assert.Equal(cell.RawObject, float.Parse(output)); }
[InlineData("'", "N''''")] // Test with escaped character public void StringFormattingTest(string input, string expectedOutput) { // Setup: Build a column and cell for the string type column // NOTE: We're using VARCHAR because it's very general purpose. DbColumn column = new FormatterTestDbColumn("VARCHAR"); DbCellValue cell = new DbCellValue { RawObject = input }; // If: I attempt to format a string type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should be quoted and escaped properly Assert.Equal(expectedOutput, output); }
public void CopyToValid() { // If: I copy a DbCellValue to another DbCellValue DbCellValue source = new DbCellValue { DisplayValue = "qqq", IsNull = true, RawObject = 12 }; DbCellValue dest = new DbCellValue(); source.CopyTo(dest); // Then: The source values should be in the dest Assert.Equal(source.DisplayValue, dest.DisplayValue); Assert.Equal(source.IsNull, dest.IsNull); Assert.Equal(source.RawObject, dest.RawObject); }
public void ConstructValid() { // If: I construct a new DbCellValue DbCellValue dbc = new DbCellValue { DisplayValue = "qqq", IsNull = true, RawObject = 12 }; // Then: It should have the values I specified in it Assert.Equal("qqq", dbc.DisplayValue); Assert.Equal(12, dbc.RawObject); Assert.True(dbc.IsNull); }
public void DecimalTest(string dataType, string regex, int?precision, int?scale) { // Setup: Build a column and cell for the decimal type column DbColumn column = new FormatterTestDbColumn(dataType, precision, scale); DbCellValue cell = new DbCellValue { RawObject = 123.45m }; // If: I attempt to format a decimal type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: It should match a something like CAST(123.45 AS MONEY) Regex castRegex = new Regex($@"CAST\([\d\.]+ AS {regex}", RegexOptions.IgnoreCase); Assert.True(castRegex.IsMatch(output)); }
public void GuidTest() { // Setup: Build a column and cell for the string type column DbColumn column = new FormatterTestDbColumn("UNIQUEIDENTIFIER"); DbCellValue cell = new DbCellValue { RawObject = Guid.NewGuid() }; // If: I attempt to format a string type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should match the output string Regex regex = new Regex(@"N'[0-9A-F]{8}(-[0-9A-F]{4}){3}-[0-9A-F]{12}'", RegexOptions.IgnoreCase); Assert.True(regex.IsMatch(output)); }
private static object GetJsonExpectedValue(DbCellValue value, ColumnInfo column) { if (value.IsNull) { return null; } else if (column.DataTypeName == "Int") { return Int64.Parse(value.DisplayValue.ToLower()); } else if (column.DataTypeName == "Bit") { return Boolean.Parse(value.DisplayValue.ToLower()); } return value.DisplayValue; }
public void BinaryTest(string datatype) { // Setup: Build a column and cell for the string type column DbColumn column = new FormatterTestDbColumn(datatype); DbCellValue cell = new DbCellValue { RawObject = new byte[] { 0x42, 0x45, 0x4e, 0x49, 0x53, 0x43, 0x4f, 0x4f, 0x4c } }; // If: I attempt to format a string type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should match the output string Regex regex = new Regex("0x[0-9A-F]+", RegexOptions.IgnoreCase); Assert.True(regex.IsMatch(output)); }
public void TimeTest() { // Setup: Build a column and cell for the time type column DbColumn column = new FormatterTestDbColumn("TIME"); DbCellValue cell = new DbCellValue { RawObject = TimeSpan.FromHours(12) }; // If: I attempt to format a time type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should be able to be converted back into a timespan Regex dateTimeRegex = new Regex("N'(.*)'"); TimeSpan outputDateTime; Assert.True(TimeSpan.TryParse(dateTimeRegex.Match(output).Groups[1].Value, out outputDateTime)); }
public void DateTimeOffsetTest() { // Setup: Build a column and cell for the datetime offset type column DbColumn column = new FormatterTestDbColumn("DATETIMEOFFSET"); DbCellValue cell = new DbCellValue { RawObject = DateTimeOffset.Now }; // If: I attempt to format a datetime offset type column string output = SqlScriptFormatter.FormatValue(cell, column); // Then: The output string should be able to be converted back into a datetime offset Regex dateTimeRegex = new Regex("N'(.*)'"); DateTimeOffset outputDateTime; Assert.True(DateTimeOffset.TryParse(dateTimeRegex.Match(output).Groups[1].Value, out outputDateTime)); }
/// <summary> /// Write a object cell /// </summary> /// The program will try to output number/datetime, otherwise, call the ToString /// <param name="o"></param> public void AddCell(DbCellValue dbCellValue) { object o = dbCellValue.RawObject; if (dbCellValue.IsNull || o == null) { AddCellEmpty(); return; } switch (Type.GetTypeCode(o.GetType())) { case TypeCode.Boolean: AddCell((bool)o); break; case TypeCode.Byte: case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.Single: case TypeCode.Double: case TypeCode.Decimal: AddCellBoxedNumber(o); break; case TypeCode.DateTime: AddCell((DateTime)o); break; case TypeCode.String: AddCell((string)o); break; default: if (o is TimeSpan) //TimeSpan doesn't have TypeCode { AddCell((TimeSpan)o); break; } AddCell(dbCellValue.DisplayValue); break; } }
public async Task GetEditRow() { // Setup: Create a row update with a cell set var columns = Common.GetColumns(false); var rs = await Common.GetResultSet(columns, false); var etm = Common.GetStandardMetadata(columns); RowUpdate ru = new RowUpdate(0, rs, etm); ru.SetCell(0, "foo"); // If: I attempt to get an edit row DbCellValue[] cells = rs.GetRow(0).ToArray(); EditRow er = ru.GetEditRow(cells); // Then: // ... The state should be dirty Assert.True(er.IsDirty); Assert.Equal(EditRow.EditRowState.DirtyUpdate, er.State); // ... The ID should be the same as the one provided Assert.Equal(0, er.Id); // ... The row should match the cells that were given, except for the updated cell Assert.Equal(cells.Length, er.Cells.Length); for (int i = 1; i < cells.Length; i++) { DbCellValue originalCell = cells[i]; DbCellValue outputCell = er.Cells[i]; Assert.Equal(originalCell.DisplayValue, outputCell.DisplayValue); Assert.Equal(originalCell.IsNull, outputCell.IsNull); // Note: No real need to check the RawObject property } // ... The updated cell should match what it was set to and be dirty EditCell newCell = er.Cells[0]; Assert.Equal("foo", newCell.DisplayValue); Assert.False(newCell.IsNull); Assert.True(newCell.IsDirty); }
/// <summary> /// Generates a edit row that represents a row pending insertion /// </summary> /// <param name="cachedRow">Original, cached cell contents. (Should be null in this case)</param> /// <returns>EditRow of pending update</returns> public override EditRow GetEditRow(DbCellValue[] cachedRow) { // Iterate over the new cells. If they are null, generate a blank value EditCell[] editCells = newCells.Select(cell => { DbCellValue dbCell = cell == null ? new DbCellValue { DisplayValue = string.Empty, IsNull = false, RawObject = null } : cell.AsDbCellValue; return(new EditCell(dbCell, true)); }) .ToArray(); return(new EditRow { Id = RowId, Cells = editCells, State = EditRow.EditRowState.DirtyInsert }); }
/// <summary> /// Reads the value of a cell from the file wrapper, checks to see if it null using /// <paramref name="isNullFunc"/>, and converts it to the proper output type using /// <paramref name="convertFunc"/>. /// </summary> /// <param name="offset">Offset into the file to read from</param> /// <param name="rowId">Internal ID of the row to set on all cells in this row</param> /// <param name="convertFunc">Function to use to convert the buffer to the target type</param> /// <param name="isNullFunc"> /// If provided, this function will be used to determine if the value is null /// </param> /// <param name="toStringFunc">Optional function to use to convert the object to a string.</param> /// <param name="setInvariantCultureDisplayValue">Optional parameter indicates whether the culture invariant display value should be provided.</param> /// <typeparam name="T">The expected type of the cell. Used to keep the code honest</typeparam> /// <returns>The object, a display value, and the length of the value + its length</returns> private FileStreamReadResult ReadCellHelper <T>(long offset, long rowId, Func <int, T> convertFunc, Func <int, bool> isNullFunc = null, Func <T, string> toStringFunc = null, bool setInvariantCultureDisplayValue = false) { LengthResult length = ReadLength(offset); DbCellValue result = new DbCellValue { RowId = rowId }; if (isNullFunc == null ? length.ValueLength == 0 : isNullFunc(length.TotalLength)) { result.RawObject = null; result.DisplayValue = SR.QueryServiceCellNull; result.IsNull = true; } else { AssureBufferLength(length.ValueLength); fileStream.Read(buffer, 0, length.ValueLength); T resultObject = convertFunc(length.ValueLength); result.RawObject = resultObject; result.DisplayValue = toStringFunc == null?result.RawObject.ToString() : toStringFunc(resultObject); if (setInvariantCultureDisplayValue) { string icDisplayValue = string.Format(System.Globalization.CultureInfo.InvariantCulture, "{0}", result.RawObject); // Only set the value when it is different from the DisplayValue to reduce the size of the result // if (icDisplayValue != result.DisplayValue) { result.InvariantCultureDisplayValue = icDisplayValue; } } result.IsNull = false; } return(new FileStreamReadResult(result, length.TotalLength)); }
public void ConstructValid() { // Setup: Create a DbCellValue to copy the values from DbCellValue source = new DbCellValue { DisplayValue = "qqq", IsNull = true, RawObject = 12 }; // If: I construct an EditCell with a valid DbCellValue EditCell ec = new EditCell(source, true); // Then: // ... The values I provided in the DbCellValue should be present Assert.Equal(source.DisplayValue, ec.DisplayValue); Assert.Equal(source.IsNull, ec.IsNull); Assert.Equal(source.RawObject, ec.RawObject); // ... The is dirty value I set should be present Assert.True(ec.IsDirty); }
private EditCell GetEditCell(CellUpdate cell, int index) { DbCellValue dbCell; if (cell == null) { // Cell hasn't been provided by user yet, attempt to use the default value dbCell = new DbCellValue { DisplayValue = DefaultValues[index] ?? string.Empty, IsNull = false, // TODO: This doesn't properly consider null defaults RawObject = null, RowId = RowId }; } else { // Cell has been provided by user, so use that dbCell = cell.AsDbCellValue; } return(new EditCell(dbCell, isDirty: true)); }
public void AsDbCellValue(bool isNull) { // Setup: Create a cell update var value = isNull ? "NULL" : "foo"; var col = GetWrapper <string>("NTEXT"); CellUpdate cu = new CellUpdate(col, value); // If: I convert it to a DbCellvalue DbCellValue dbc = cu.AsDbCellValue; // Then: // ... It should not be null Assert.NotNull(dbc); // ... The display value should be the same as the value we supplied Assert.Equal(value, dbc.DisplayValue); // ... The null-ness of the value should be the same as what we supplied Assert.Equal(isNull, dbc.IsNull); // ... We don't care *too* much about the raw value, but we'll check it anyhow Assert.Equal(isNull ? (object)DBNull.Value : value, dbc.RawObject); }
private static string GetXmlExpectedValue(DbCellValue d) { return d.IsNull ? "" : d.DisplayValue; }
/// <summary> /// Constructs a new FileStreamReadResult /// </summary> /// <param name="value">The value of the result, ready for consumption by a client</param> /// <param name="totalLength">The number of bytes for the used to store the value's length and value</param>s public FileStreamReadResult(DbCellValue value, int totalLength) { Value = value; TotalLength = totalLength; }
/// <summary> /// Converts a cell value into a string for SQL script /// </summary> /// <param name="value">The cell to convert</param> /// <param name="column">The column metadata for the cell to insert</param> /// <returns>String version of the cell value for use in SQL scripts</returns> public static string FormatValue(DbCellValue value, DbColumn column) { Validate.IsNotNull(nameof(value), value); return(FormatValue(value.RawObject, column)); }