/// <summary>
/// Replaces all string representations destined to end up in DateTime or TimeSpan fields in the target database
/// into hard typed objects (using <see cref="DateTimeDecider"/>)
/// </summary>
/// <param name="dt"></param>
protected void ConvertStringDatesToDateTime(DataTable dt)
{
var dict = GetMapping(dt.Columns.Cast <DataColumn>(), out _);
//for each column in the destination
foreach (var kvp in dict)
{
//if the destination column is a date based column
var dataType = kvp.Value.DataType.GetCSharpDataType();
if (dataType == typeof(DateTime) || dataType == typeof(TimeSpan))
{
//if it's already not a string then that's fine (hopefully its a DateTime!)
if (kvp.Key.DataType != typeof(string))
{
continue;
}
//create a new column hard typed to DateTime
var newColumn = dt.Columns.Add(kvp.Key.ColumnName + "_" + Guid.NewGuid().ToString(), dataType);
//guess the DateTime culture based on values in the table
DateTimeDecider.GuessDateFormat(dt.Rows.Cast <DataRow>().Take(500).Select(r => r[kvp.Key] as string));
foreach (DataRow dr in dt.Rows)
{
//parse the value
var val = DateTimeDecider.Parse(dr[kvp.Key] as string) ?? DBNull.Value;
if (dataType == typeof(DateTime))
{
dr[newColumn] = val;
}
else
{
dr[newColumn] = val == DBNull.Value? val:((DateTime)val).TimeOfDay;
}
}
//if the DataColumn is part of the Primary Key of the DataTable (in memory)
//then we need to update the primary key to include the new column not the old one
if (dt.PrimaryKey != null && dt.PrimaryKey.Contains(kvp.Key))
{
dt.PrimaryKey = dt.PrimaryKey.Except(new [] { kvp.Key }).Union(new [] { newColumn }).ToArray();
}
//drop the original column
dt.Columns.Remove(kvp.Key);
//rename the hard typed column to match the old column name
newColumn.ColumnName = kvp.Key.ColumnName;
}
}
}
/// <summary>
/// Replaces all string representations for data types that can be problematic/ambiguous (e.g. DateTime or TimeSpan)
/// into hard typed objects using appropriate decider e.g. <see cref="DateTimeDecider"/>.
/// </summary>
/// <param name="dt"></param>
protected void ConvertStringTypesToHardTypes(DataTable dt)
{
var dict = GetMapping(dt.Columns.Cast <DataColumn>(), out _);
var factory = new TypeDeciderFactory(Culture);
//These are the problematic Types
Dictionary <Type, IDecideTypesForStrings> deciders = factory.Dictionary;
//for each column in the destination
foreach (var kvp in dict)
{
//if the destination column is a problematic type
var dataType = kvp.Value.DataType.GetCSharpDataType();
if (deciders.ContainsKey(dataType))
{
//if it's already not a string then that's fine (hopefully its a legit Type e.g. DateTime!)
if (kvp.Key.DataType != typeof(string))
{
continue;
}
//create a new column hard typed to DateTime
var newColumn = dt.Columns.Add(kvp.Key.ColumnName + "_" + Guid.NewGuid().ToString(), dataType);
var decider = deciders[dataType];
//if it's a DateTime decider then guess DateTime culture based on values in the table
if (decider is DateTimeTypeDecider)
{
//also use this one incase the user has set up explicit stuff on it e.g. Culture/Settings
decider = DateTimeDecider;
DateTimeDecider.GuessDateFormat(dt.Rows.Cast <DataRow>().Take(500).Select(r => r[kvp.Key] as string));
}
foreach (DataRow dr in dt.Rows)
{
try
{
//parse the value
dr[newColumn] = decider.Parse(dr[kvp.Key] as string) ?? DBNull.Value;
}
catch (Exception ex)
{
throw new Exception($"Failed to parse value '{dr[kvp.Key]}' in column '{kvp.Key}'", ex);
}
}
//if the DataColumn is part of the Primary Key of the DataTable (in memory)
//then we need to update the primary key to include the new column not the old one
if (dt.PrimaryKey != null && dt.PrimaryKey.Contains(kvp.Key))
{
dt.PrimaryKey = dt.PrimaryKey.Except(new [] { kvp.Key }).Union(new [] { newColumn }).ToArray();
}
var oldOrdinal = kvp.Key.Ordinal;
//drop the original column
dt.Columns.Remove(kvp.Key);
//rename the hard typed column to match the old column name
newColumn.ColumnName = kvp.Key.ColumnName;
if (oldOrdinal != -1)
{
newColumn.SetOrdinal(oldOrdinal);
}
}
}
}