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