private string ConstructIndividualValue(string dataType, string value)
{
switch (dataType)
{
case "BIT":
return(value);
//Numbers
case "INT":
case "TINYINT":
case "SMALLINT":
case "MEDIUMINT":
case "BIGINT":
case "FLOAT":
case "DOUBLE":
case "DECIMAL":
return(string.Format("{0}", value));
//Text
case "CHAR":
case "VARCHAR":
case "BLOB":
case "TEXT":
case "TINYBLOB":
case "TINYTEXT":
case "MEDIUMBLOB":
case "MEDIUMTEXT":
case "LONGBLOB":
case "LONGTEXT":
case "ENUM":
return(string.Format("'{0}'", MySqlHelper.EscapeString(value)));
//Dates/times
case "DATE":
return(String.Format("'{0:yyyy-MM-dd}'", (DateTime)DateTimeDecider.Parse(value)));
case "TIMESTAMP":
case "DATETIME":
return(String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", (DateTime)DateTimeDecider.Parse(value)));
case "TIME":
return(String.Format("'{0:HH:mm:ss}'", (DateTime)DateTimeDecider.Parse(value)));
case "YEAR2":
return(String.Format("'{0:yy}'", value));
case "YEAR4":
return(String.Format("'{0:yyyy}'", value));
//Unknown
default:
// we don't understand the format. to safegaurd the code, just enclose with ''
return(string.Format("'{0}'", MySqlHelper.EscapeString(value)));
}
}
/// <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;
}
}
}
public override int UploadImpl(DataTable dt)
{
//don't run an insert if there are 0 rows
if (dt.Rows.Count == 0)
{
return(0);
}
var syntaxHelper = _server.GetQuerySyntaxHelper();
var tt = syntaxHelper.TypeTranslater;
//if the column name is a reserved keyword e.g. "Comment" we need to give it a new name
Dictionary <DataColumn, string> parameterNames = syntaxHelper.GetParameterNamesFor(dt.Columns.Cast <DataColumn>().ToArray(), c => c.ColumnName);
int affectedRows = 0;
var mapping = GetMapping(dt.Columns.Cast <DataColumn>());
var dateColumns = new HashSet <DataColumn>();
var sql = string.Format("INSERT INTO " + TargetTable.GetFullyQualifiedName() + "({0}) VALUES ({1})",
string.Join(",", mapping.Values.Select(c => '"' + c.GetRuntimeName() + '"')),
string.Join(",", mapping.Keys.Select(c => parameterNames[c]))
);
using (OracleCommand cmd = (OracleCommand)_server.GetCommand(sql, Connection))
{
//send all the data at once
cmd.ArrayBindCount = dt.Rows.Count;
foreach (var kvp in mapping)
{
var p = _server.AddParameterWithValueToCommand(parameterNames[kvp.Key], cmd, DBNull.Value);
p.DbType = tt.GetDbTypeForSQLDBType(kvp.Value.DataType.SQLType);
if (p.DbType == DbType.DateTime)
{
dateColumns.Add(kvp.Key);
}
}
var values = new Dictionary <DataColumn, List <object> >();
foreach (DataColumn c in mapping.Keys)
{
values.Add(c, new List <object>());
}
foreach (DataRow dataRow in dt.Rows)
{
//populate parameters for current row
foreach (var col in mapping.Keys)
{
var val = dataRow[col];
if (val is string && string.IsNullOrWhiteSpace((string)val))
{
val = null;
}
else
if (val == null || val == DBNull.Value)
{
val = null;
}
else if (dateColumns.Contains(col))
{
if (val is string s)
{
val = (DateTime)DateTimeDecider.Parse(s);
}
else
{
val = Convert.ToDateTime(dataRow[col]);
}
}
values[col].Add(val);
}
}
foreach (DataColumn col in mapping.Keys)
{
var param = cmd.Parameters[parameterNames[col]];
param.Value = values[col].ToArray();
}
//send query
affectedRows += cmd.ExecuteNonQuery();
}
return(affectedRows);
}
/// <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);
}
}
}
}
