/// <summary> /// Get the strings for csv file for different data types. /// </summary> /// <param name="type">DatabaseType</param> /// <param name="cellValue">string</param> /// <param name="col">ReportColumn</param> /// <returns>string</returns> public static string GetCsvCellValue(DatabaseType type, string cellValue, ReportColumn col) { //convert cell value to its particular type object value = DatabaseTypeHelper.ConvertFromString(type, cellValue); if (cellValue == null) { return(""); } if (type is BoolType) { return(GetBooleanCellValue(( bool )value)); } if (type is AutoIncrementType) { return(DatabaseTypeHelper.ConvertToString(type, value, col.AutoNumberDisplayPattern)); } if (type is DateType || type is DateTimeType) { return(cellValue); } if (type is TimeType) { return((( DateTime )value).ToUniversalTime( ).TimeOfDay.ToString( )); } if (type is StructureLevelsType) { return(GetStructureLevelCellValue(cellValue, false)); } return(value.ToString( )); }
public void ConvertToString_String_Null() { const string originalData = null; string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.StringType, originalData); string convertedData = stringData; Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_Int32() { const int originalData = 476685; string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.Int32Type, originalData); Int32 convertedData = Int32.Parse(stringData); Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_Decimal() { const decimal originalData = 8722387463.4562M; string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DecimalType, originalData); Decimal convertedData = Decimal.Parse(stringData); Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_Guid() { Guid originalData = Guid.NewGuid(); string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.GuidType, originalData); var convertedData = new Guid(stringData); Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_Time() { var originalData = new TimeSpan(10, 34, 45); string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.TimeType, originalData); TimeSpan convertedData = TimeSpan.Parse(stringData); Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_DateTime() { var originalData = new DateTime(2011, 5, 23, 5, 4, 3); string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DateTimeType, originalData); DateTime convertedData = DateTime.Parse(stringData); Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertToString_Xml() { const string originalData = "<originalXml></originalXml>"; string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.XmlType, originalData); string convertedData = stringData; Assert.AreEqual(originalData, convertedData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Xml() { const string originalData = "<xml></xml>"; object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.XmlType, originalData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.XmlType, objectData); Assert.AreEqual(originalData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_String_Null() { const string originalData = null; object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.StringType, originalData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.StringType, objectData); Assert.AreEqual(originalData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_DateTime() { var originalData = new DateTime(2011, 4, 4, 4, 6, 7, DateTimeKind.Utc); string originalStringData = originalData.ToString(DateTimeType.DateTimeFormatString); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DateTimeType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DateTimeType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Currency() { const decimal originalData = 74845739.345M; string originalStringData = originalData.ToString(CultureInfo.InvariantCulture); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.CurrencyType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.CurrencyType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Bool() { const bool originalData = true; string originalStringData = originalData.ToString(); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.BoolType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BoolType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Time() { var originalData = new TimeSpan(13, 4, 13); string originalStringData = originalData.ToString("c"); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.TimeType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.TimeType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Int32() { const int originalData = 12387352; string originalStringData = originalData.ToString(CultureInfo.InvariantCulture); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.Int32Type, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.Int32Type, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Guid() { Guid originalData = Guid.NewGuid(); string originalStringData = originalData.ToString("B"); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.GuidType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.GuidType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Decimal() { const decimal originalData = 83673838.3335M; string originalStringData = originalData.ToString(CultureInfo.InvariantCulture); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.DecimalType, originalStringData); object convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.DecimalType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertToString_Bool() { string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BoolType, true); bool convertedData = bool.Parse(stringData); Assert.AreEqual(true, convertedData, "The converted data is invalid."); stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BoolType, false); convertedData = bool.Parse(stringData); Assert.AreEqual(false, convertedData, "The converted data is invalid."); }
public void ConvertFromString_ConvertToString_Binary() { var originalData = new byte[] { 1, 2, 3, 4, 0x8 }; string originalStringData = Convert.ToBase64String(originalData); object objectData = DatabaseTypeHelper.ConvertFromString(DatabaseType.BinaryType, originalStringData); string convertedStringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BinaryType, objectData); Assert.AreEqual(originalStringData, convertedStringData, "The converted data is invalid."); }
public void ConvertToString_Binary() { var originalData = new byte[] { 1, 2, 3, 4, 5, 0xF }; string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BinaryType, originalData); byte[] convertedData = Convert.FromBase64String(stringData); Assert.AreEqual(originalData.Length, convertedData.Length, "The length of the converted data is invalid."); for (int i = 0; i < originalData.Length; i++) { Assert.AreEqual(originalData[i], convertedData[i], "The converted data is invalid."); } }
/// <summary> /// Logic for setting a value object /// </summary> /// <param name="value"></param> private void SetValueString(object value) { if (value == null) { ValueString = null; return; } if (value is string) { var strValue = (string)value; if (Type is DateType) { if (strValue == DateType.DefaultValueToday) { ValueString = strValue; return; } } if (Type is DateTimeType) { if (strValue == DateType.DefaultValueToday || strValue == DateTimeType.DefaultValueNow) { ValueString = strValue; return; } } if (strValue == "") { // Work around issue in DatabaseTypeHelper.ConvertFromString whereby empty strings get round-tripped as nulls // Can't easily be corrected in ConvertFromString as some code depends on that behavior. ValueString = ""; return; } } ValueString = DatabaseTypeHelper.ConvertToString(Type, value, _defaultDateTimeKind); }
//DatabaseType.BinaryType //DatabaseType.StructureLevelsType private static void TestType <T>(DatabaseType type, T value, string sValue, bool testDatabaseTypeHelper = true, bool testTypeOverrides = true, bool testFromString = true, bool testToString = true) where T : IComparable { if (testDatabaseTypeHelper) { if (testFromString) { // Test DatabaseTypeHelper.ConvertFromString object actualValue = DatabaseTypeHelper.ConvertFromString(type, sValue); Assert.IsTrue(value.CompareTo(actualValue) == 0, "DatabaseTypeHelper.ConvertFromString"); } if (testToString) { // Test DatabaseTypeHelper.ConvertToString string actualString = DatabaseTypeHelper.ConvertToString(type, value); Assert.AreEqual(sValue, actualString, "DatabaseTypeHelper.ConvertToString"); } } if (testTypeOverrides) { if (testFromString) { // Test type.ConvertFromString object actualValue2 = type.ConvertFromString(sValue); Assert.IsTrue(value.CompareTo(actualValue2) == 0, "DatabaseTypeHelper.ConvertFromString"); } if (testToString) { // Test type.ConvertToString string actualString2 = type.ConvertToString(value); Assert.AreEqual(sValue, actualString2, "DatabaseTypeHelper.ConvertToString"); } } }
/// <summary> /// Converts the data table from one format to another format. /// </summary> /// <param name="queryResult">The query result.</param> /// <param name="sourceQuery">The source query.</param> /// <param name="reportObject">The report object.</param> /// <returns>An in-memory copy of the converted data table.</returns> public static DbDataTable ConvertTo(QueryResult queryResult, StructuredQuery sourceQuery, ReportObject reportObject) { DbDataTable dbTable = null; DataTable dataTable = queryResult.DataTable; if (queryResult.DataTable != null) { var columns = new List <DbDataColumn>( ); // Convert the columns var columnDictionary = new Dictionary <DataColumn, SelectColumn>( ); for (int i = 0; i < queryResult.Columns.Count; i++) { ResultColumn resultColumn = queryResult.Columns[i]; DataColumn dataColumn = queryResult.DataTable.Columns[i]; var dbColumn = new DbDataColumn( ); SelectColumn selectColumn = null; // Check for a column id property if (dataColumn.ExtendedProperties.ContainsKey("ColumnId")) { var columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"]; selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId); if (!columnDictionary.ContainsKey(dataColumn)) { columnDictionary.Add(dataColumn, selectColumn); } } // Have not found the column yet fallback to using the ordinal if (selectColumn == null) { selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal]; } // Determine column type if (resultColumn.ColumnType != null) { if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern") && !string.IsNullOrEmpty(( string )dataColumn.ExtendedProperties["DisplayPattern"])) { dbColumn.Type = new AutoIncrementType( ); } else { dbColumn.Type = resultColumn.ColumnType; } } else { dbColumn.Type = GetColumnDatabaseType(sourceQuery, selectColumn); if (dbColumn.Type is UnknownType) { dbColumn.Type = DatabaseType.ConvertFromType(dataColumn.DataType); } } dbColumn.Id = selectColumn.ColumnId; dbColumn.Name = string.IsNullOrEmpty(selectColumn.DisplayName) ? selectColumn.ColumnName : selectColumn.DisplayName; dbColumn.ColumnName = selectColumn.ColumnName; dbColumn.IsHidden = selectColumn.IsHidden; columns.Add(dbColumn); } var rows = new List <DbDataRow>( ); // Convert the rows foreach (DataRow dataRow in dataTable.Rows) { var row = new DbDataRow( ); // Convert each field foreach (DataColumn dataColumn in dataTable.Columns) { object obj = dataRow[dataColumn]; if (!Convert.IsDBNull(obj)) { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, dataRow[dataColumn])); } else { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null); } } rows.Add(row); } dbTable = new DbDataTable { TableName = dataTable.TableName, Columns = columns, Rows = rows }; if (dataTable.ExtendedProperties.ContainsKey("Id")) { dbTable.Id = ( Guid )dataTable.ExtendedProperties["Id"]; } if (dataTable.ExtendedProperties.ContainsKey("Name")) { dbTable.Name = ( string )dataTable.ExtendedProperties["Name"]; } } else if (queryResult.AggregateDataTable != null) { var columns = new List <DbDataColumn>( ); // Convert the columns for (int i = 0; i < queryResult.AggregateColumns.Count; i++) { ResultColumn resultColumn = queryResult.AggregateColumns[i]; DataColumn dataColumn = queryResult.AggregateDataTable.Columns[i]; var dbColumn = new DbDataColumn( ); SelectColumn selectColumn = null; // Check for a column id property if (dataColumn.ExtendedProperties.ContainsKey("ColumnId")) { var columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"]; selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId); } // Have not found the column yet fallback to using the ordinal if (selectColumn == null) { selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal]; } // Determine column type if (resultColumn.ColumnType != null) { if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern") && !string.IsNullOrEmpty(( string )dataColumn.ExtendedProperties["DisplayPattern"])) { dbColumn.Type = new AutoIncrementType( ); } else { dbColumn.Type = resultColumn.ColumnType; } } else { dbColumn.Type = GetColumnDatabaseType(sourceQuery, selectColumn); if (dbColumn.Type is UnknownType) { dbColumn.Type = DatabaseType.ConvertFromType(dataColumn.DataType); } } dbColumn.Id = selectColumn.ColumnId; dbColumn.Name = string.IsNullOrEmpty(selectColumn.DisplayName) ? selectColumn.ColumnName : selectColumn.DisplayName; dbColumn.ColumnName = resultColumn.AggregateColumn.AggregateMethod == AggregateMethod.List ? selectColumn.ColumnName : resultColumn.AggregateColumn.AggregateMethod.ToString( ); dbColumn.IsHidden = selectColumn.IsHidden; columns.Add(dbColumn); } var rows = new List <DbDataRow>( ); // Convert the rows foreach (DataRow dataRow in dataTable.Rows) { var row = new DbDataRow( ); // Convert each field foreach (DataColumn dataColumn in dataTable.Columns) { object obj = dataRow[dataColumn]; if (!Convert.IsDBNull(obj)) { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, dataRow[dataColumn])); } else { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null); } } rows.Add(row); } dbTable = new DbDataTable { TableName = dataTable.TableName, Columns = columns, Rows = rows }; if (dataTable.ExtendedProperties.ContainsKey("Id")) { dbTable.Id = ( Guid )dataTable.ExtendedProperties["Id"]; } if (dataTable.ExtendedProperties.ContainsKey("Name")) { dbTable.Name = ( string )dataTable.ExtendedProperties["Name"]; } } return(dbTable); }
public static DbDataTable ConvertTo(QueryResult queryResult, List <ResultColumn> queryResultColumns, DataTable dataTable, DataTable aggregateDataTable, StructuredQuery sourceQuery, ClientAggregate clientAggregate, ReportObject reportObject) { var columns = new List <DbDataColumn>( ); var databaseTypeDictionary = new Dictionary <Guid, DatabaseType>( ); for (int i = 0; i < queryResultColumns.Count; i++) { queryResult.Columns[i] = queryResultColumns[i]; ResultColumn resultColumn = queryResultColumns[i]; DataColumn dataColumn = dataTable.Columns[i]; var dbColumn = new DbDataColumn( ); SelectColumn selectColumn = null; Guid columnId = Guid.Empty; // Check for a column id property if (dataColumn.ExtendedProperties.ContainsKey("ColumnId")) { columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"]; selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId); if (!databaseTypeDictionary.ContainsKey(columnId)) { databaseTypeDictionary.Add(columnId, resultColumn.ColumnType); } } // Have not found the column yet fallback to using the ordinal if (selectColumn == null) { selectColumn = sourceQuery.SelectColumns[dataColumn.Ordinal]; } //if current column in groupedcolumn, same as columntype //otherwise, use string type bool existAggregatedColumn = clientAggregate.AggregatedColumns.Any(gc => gc.ReportColumnId == columnId); if (existAggregatedColumn) { dbColumn.Type = new StringType( ); } else { // Determine column type if (resultColumn.ColumnType != null) { if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern") && !string.IsNullOrEmpty(( string )dataColumn.ExtendedProperties["DisplayPattern"])) { dbColumn.Type = new AutoIncrementType( ); } else { dbColumn.Type = resultColumn.ColumnType; } } else { dbColumn.Type = GetColumnDatabaseType(sourceQuery, selectColumn); if (dbColumn.Type is UnknownType) { dbColumn.Type = DatabaseType.ConvertFromType(dataColumn.DataType); } } } dbColumn.Id = selectColumn.ColumnId; dbColumn.Name = string.IsNullOrEmpty(selectColumn.DisplayName) ? selectColumn.ColumnName : selectColumn.DisplayName; dbColumn.ColumnName = selectColumn.ColumnName; dbColumn.IsHidden = selectColumn.IsHidden; columns.Add(dbColumn); } var rows = new List <DbDataRow>( ); // Convert the rows int rowIndex = 0; foreach (DataRow aggregateDataRow in aggregateDataTable.Rows) { bool isGrandTotalRow = rowIndex == (aggregateDataTable.Rows.Count - 1); rowIndex++; var row = new DbDataRow( ); // Convert each field foreach (DataColumn dataColumn in dataTable.Columns) { Guid columnId = Guid.Empty; DatabaseType databaseType = DatabaseType.StringType; DatabaseType selectColumnType = DatabaseType.StringType; // Check for a column id property if (dataColumn.ExtendedProperties.ContainsKey("ColumnId")) { columnId = ( Guid )dataColumn.ExtendedProperties["ColumnId"]; SelectColumn selectColumn = sourceQuery.SelectColumns.FirstOrDefault(c => c.ColumnId == columnId); databaseType = databaseTypeDictionary.ContainsKey(columnId) ? databaseTypeDictionary[columnId] : DatabaseType.StringType; var expression = selectColumn.Expression as EntityExpression; if (expression != null) { selectColumnType = GetSelectColumnCastType(expression, databaseType); } } bool existGroupedColumn = clientAggregate.GroupedColumns != null && clientAggregate.GroupedColumns.Any(gc => gc.ReportColumnId == columnId); bool existAggregatedColumn = clientAggregate.AggregatedColumns.Any(gc => gc.ReportColumnId == columnId); //not existing in GroupedColumn or AggregatedColumn if (!existGroupedColumn && !existAggregatedColumn) { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null); } else if (existGroupedColumn) { List <DataColumn> aggregatedDataColumns = GetAggregatedDataColumns(aggregateDataTable.Columns, columnId); DataColumn aggregatedDataColumn = aggregatedDataColumns.Count > 0 ? aggregatedDataColumns[0] : null; object obj = aggregateDataRow[aggregatedDataColumn]; if (!Convert.IsDBNull(obj)) { string displayPattern = null; if (dataColumn.ExtendedProperties.ContainsKey("DisplayPattern")) { displayPattern = ( string )dataColumn.ExtendedProperties["DisplayPattern"]; } string additionStringformating = GetAddtionalFormatString(columnId, reportObject); string cellValue = DatabaseTypeHelper.ConvertToString(columns[dataColumn.Ordinal].Type, aggregateDataRow[aggregatedDataColumn], displayPattern); if (!string.IsNullOrEmpty(additionStringformating)) { cellValue = string.Format(additionStringformating, aggregateDataRow[aggregatedDataColumn]); } row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(cellValue); } else { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null); } } else if (existAggregatedColumn) { List <DataColumn> aggregatedDataColumns = GetAggregatedDataColumns(aggregateDataTable.Columns, columnId); var aggregatedValues = new List <string>( ); foreach (DataColumn aggregatedDataColumn in aggregatedDataColumns) { string columnName = aggregatedDataColumn.ColumnName; columnName = columnName.Split(' ')[0]; int groupedColumnCount = clientAggregate.GroupedColumns != null ? clientAggregate.GroupedColumns.Count : 0; int columnIndex = aggregateDataTable.Columns.IndexOf(aggregatedDataColumn); int aggregateColumnIndex = columnIndex - groupedColumnCount; ReportAggregateField reportAggregateField = clientAggregate.AggregatedColumns[aggregateColumnIndex]; AggregateMethod aggregateMethod = reportAggregateField.AggregateMethod; if (clientAggregate.AggregatedColumns[aggregateColumnIndex] != null) { if (aggregateMethod == AggregateMethod.Count || aggregateMethod == AggregateMethod.CountUniqueItems || aggregateMethod == AggregateMethod.CountUniqueNotBlanks || aggregateMethod == AggregateMethod.CountWithValues) { databaseType = DatabaseType.Int32Type; } } string displayPattern = DatabaseTypeHelper.GetDisplayFormatString(databaseType); string additionStringformating = GetAddtionalFormatString(columnId, reportObject); bool doFormat = !(aggregateMethod == AggregateMethod.Count || aggregateMethod == AggregateMethod.CountUniqueItems || aggregateMethod == AggregateMethod.CountUniqueNotBlanks || aggregateMethod == AggregateMethod.CountWithValues); if (string.IsNullOrEmpty(additionStringformating)) { if (doFormat) { if (selectColumnType is CurrencyType) { //displayPattern = "{0:c3}"; } else if (selectColumnType is DecimalType) { displayPattern = "{0:N3}"; } } } else { displayPattern = additionStringformating; } string aggregateValue = string.Empty; if (doFormat) { if (selectColumnType is DateTimeType) // convert to local time { DateTime tempDate; if (DateTime.TryParse(aggregateDataRow[aggregatedDataColumn].ToString( ), out tempDate)) { tempDate = TimeZoneHelper.ConvertToLocalTime(tempDate, sourceQuery.TimeZoneName); aggregateValue = string.Format(displayPattern, tempDate); } } else { aggregateValue = string.Format(displayPattern, aggregateDataRow[aggregatedDataColumn]); } } else { aggregateValue = aggregateDataRow[aggregatedDataColumn].ToString( ); } if (selectColumnType is ChoiceRelationshipType && (aggregateMethod == AggregateMethod.Max || aggregateMethod == AggregateMethod.Min)) { aggregateValue = UpdateChoiceFieldXmlValue(aggregateValue); } if (doFormat && selectColumnType is CurrencyType && !aggregateValue.StartsWith("$")) { aggregateValue = string.Format("{0:c2}", aggregateDataRow[aggregatedDataColumn]); } if (columnName == "Count") { columnName = "Count"; } else if (columnName == "CountWithValues") { columnName = "Count with values"; } else if (columnName == "CountUniqueItems") { columnName = "Count unique"; } if (reportAggregateField != null && ((isGrandTotalRow && reportAggregateField.ShowGrandTotals) || (!isGrandTotalRow && reportAggregateField.ShowSubTotals))) { aggregatedValues.Add(string.Format("{0}: {1}", columnName, aggregateValue)); } } row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(string.Join("\r", aggregatedValues)); } else { row.FieldsByOrdinal[dataColumn.Ordinal] = new DbDataField(null); } } rows.Add(row); } var dbTable = new DbDataTable { TableName = dataTable.TableName, Columns = columns, Rows = rows }; if (dataTable.ExtendedProperties.ContainsKey("Id")) { dbTable.Id = ( Guid )dataTable.ExtendedProperties["Id"]; } if (dataTable.ExtendedProperties.ContainsKey("Name")) { dbTable.Name = ( string )dataTable.ExtendedProperties["Name"]; } return(dbTable); }
public void ConvertToString_Null() { string stringData = DatabaseTypeHelper.ConvertToString(DatabaseType.BinaryType, null); Assert.AreEqual(null, stringData, "The converted data is invalid."); }
/// <summary> /// Generate CSV document byte stream /// </summary> /// <param name="queryResult">QueryResult</param> /// <returns>byte[]</returns> public static byte[] CreateCsvDocument(QueryResult queryResult) { var currentCulure = Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; try { var sb = new StringBuilder( ); int headerIndx = 1; foreach (ResultColumn col in queryResult.Columns) { //add separator if (!col.IsHidden) { sb.Append(col.DisplayName); if (headerIndx != queryResult.Columns.Count) { sb.Append(','); } } headerIndx++; } //append new line sb.Append("\r\n"); foreach (DataRow row in queryResult.DataTable.Rows) { int colIndx = 0; bool first = true; foreach (ResultColumn col in queryResult.Columns) { if (!col.IsHidden) { if (first) { first = false; } else { sb.Append(','); } object cellValue = row[colIndx]; string csvValue; if (cellValue == null || cellValue is DBNull) { csvValue = string.Empty; } else if (col.ColumnType is StructureLevelsType) { csvValue = GetStructureLevelCellValue(( string )cellValue, false); } else if (col.ColumnType is ChoiceRelationshipType || col.ColumnType is InlineRelationshipType) { csvValue = DatabaseTypeHelper.GetEntityXmlName(( string )cellValue); } else if (col.ColumnType is AutoIncrementType) { string displayPattern = null; if (row.Table.Columns[colIndx].ExtendedProperties.ContainsKey("DisplayPattern")) { displayPattern = ( string )row.Table.Columns[colIndx].ExtendedProperties["DisplayPattern"]; } var intStringVal = cellValue as string; int temp; if (intStringVal != null) { temp = int.Parse(intStringVal); } else { temp = ( int )cellValue; } csvValue = DatabaseTypeHelper.ConvertToString(col.ColumnType, temp, displayPattern); } else if (col.ColumnType is BoolType) { csvValue = GetBooleanCellValue(( bool )cellValue); } else if (col.ColumnType is DateType) { csvValue = (( DateTime )cellValue).ToString("d/MM/yyyy"); } else if (col.ColumnType is TimeType) { csvValue = (( DateTime )cellValue).ToString("h:mm tt"); } else if (col.ColumnType is DateTimeType) { csvValue = (( DateTime )cellValue).ToString("d/MM/yyyy h:mm tt"); } else { //The cell value is string type. bool isRelationshipValue = col.RequestColumn.Expression is AggregateExpression; // Use ConvertToString, which is the network format, because it is invariant. csvValue = isRelationshipValue ? DatabaseTypeHelper.GetEntityXmlName(cellValue.ToString( )) : DatabaseTypeHelper.ConvertToString(col.ColumnType, cellValue); csvValue = csvValue.Replace("\r", string.Empty).Replace("\n", string.Empty); } sb.Append(CsvEscape(csvValue)); } colIndx++; } //append new line sb.Append("\r\n"); } byte[] bytesInStream = Encoding.UTF8.GetBytes(sb.ToString( )); return(bytesInStream); } finally { Thread.CurrentThread.CurrentCulture = currentCulure; } }
/// <summary> /// Get the formatted cell value for the different data types. /// </summary> public static string GetFormattedCellValue(DatabaseType type, string cellValue, ReportColumn col) { string result; //convert cell value to its particular type object value = DatabaseTypeHelper.ConvertFromString(type, cellValue); if (cellValue == null) { return(""); } if (type is BoolType) { result = GetBooleanCellValue(( bool )value); return(result); } if (type is AutoIncrementType) { result = DatabaseTypeHelper.ConvertToString(type, value, col.AutoNumberDisplayPattern); return(result); } if (type is DateType) { return((( DateTime )value).ToString("d/MM/yyyy")); } if (type is TimeType) { return((( DateTime )value).ToUniversalTime( ).ToString("h:mm tt")); } if (type is DateTimeType) { return((( DateTime )value).ToString("d/MM/yyyy h:mm tt")); } if (type is Int32Type) { return(Convert.ToInt32(value).ToString("#,##0")); } if (type is DecimalType) { long? decimalPlaces = col.DecimalPlaces; NumberFormatInfo numberFormatInfo = new CultureInfo("en-US", false).NumberFormat; numberFormatInfo.NumberDecimalDigits = decimalPlaces != null?Convert.ToInt32(decimalPlaces) : 3; decimal temp = Convert.ToDecimal(value); return(temp.ToString("N", numberFormatInfo)); } if (type is CurrencyType) { long? decimalPlaces = col.DecimalPlaces; NumberFormatInfo numberFormatInfo = new CultureInfo("en-US", false).NumberFormat; numberFormatInfo.CurrencyDecimalDigits = decimalPlaces != null?Convert.ToInt32(decimalPlaces) : 2; numberFormatInfo.CurrencySymbol = "$"; numberFormatInfo.CurrencyNegativePattern = 1; decimal temp = Convert.ToDecimal(value); return(temp.ToString("C", numberFormatInfo)); } if (type is StructureLevelsType) { return(GetStructureLevelCellValue(cellValue, true)); } result = value.ToString( ); return(result); }
public void Test_Unknown_ToString_Empty() { string result = DatabaseTypeHelper.ConvertToString(DatabaseType.UnknownType, ""); Assert.IsNull(result); }