private DataTable ConvertDataTable(DataTable dataTable, BulkCopyInfo bulkCopyInfo)
{
var columns = dataTable.Columns.Cast <DataColumn>();
if (!columns.Any(item => item.DataType == typeof(TimeSpan) ||
item.DataType == typeof(byte[]) ||
item.DataType == typeof(decimal)))
{
return(dataTable);
}
Dictionary <int, Type> changedColumnTypes = new Dictionary <int, Type>();
Dictionary <(int RowIndex, int ColumnIndex), object> changedValues = new Dictionary <(int RowIndex, int ColumnIndex), object>();
DataTable dtChanged = dataTable.Clone();
int rowIndex = 0;
Func <DataColumn, TableColumn> getTableColumn = (column) =>
{
return(bulkCopyInfo.Columns.FirstOrDefault(item => item.Name == column.ColumnName));
};
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
object value = row[i];
if (value != null)
{
Type type = value.GetType();
if (type != typeof(DBNull))
{
if (type == typeof(TimeSpan))
{
TimeSpan ts = TimeSpan.Parse(value.ToString());
if (ts.Days > 0)
{
TableColumn tableColumn = getTableColumn(dataTable.Columns[i]);
string dataType = tableColumn.DataType.ToLower();
Type columnType = null;
if (dataType.Contains("datetime"))
{
DateTime dateTime = this.MinDateTime.AddSeconds(ts.TotalSeconds);
columnType = typeof(DateTime);
changedValues.Add((rowIndex, i), dateTime);
}
else if (DataTypeHelper.IsCharType(dataType))
{
columnType = typeof(string);
changedValues.Add((rowIndex, i), ts.ToString());
}
if (columnType != null && !changedColumnTypes.ContainsKey(i))
{
changedColumnTypes.Add(i, columnType);
}
}
}
else if (type == typeof(byte[]))
{
TableColumn tableColumn = getTableColumn(dataTable.Columns[i]);
if (tableColumn.DataType.ToLower() == "uniqueidentifier")
{
changedValues.Add((rowIndex, i), ValueHelper.ConvertGuidBytesToString(value as byte[], this.DatabaseType, tableColumn.DataType, tableColumn.MaxLength, true));
if (!changedColumnTypes.ContainsKey(i))
{
changedColumnTypes.Add(i, typeof(Guid));
}
}
}
else if (type == typeof(decimal))
{
TableColumn tableColumn = getTableColumn(dataTable.Columns[i]);
string dataType = tableColumn.DataType.ToLower();
Type columnType = null;
if (dataType == "bigint")
{
columnType = typeof(Int64);
}
else if (dataType == "int")
{
columnType = typeof(Int32);
if ((decimal)value > Int32.MaxValue)
{
columnType = typeof(Int64);
}
}
else if (dataType == "smallint")
{
columnType = typeof(Int16);
if ((decimal)value > Int16.MaxValue)
{
columnType = typeof(Int32);
}
}
if (columnType != null && !changedColumnTypes.ContainsKey(i))
{
changedColumnTypes.Add(i, columnType);
}
}
}
}
}
rowIndex++;
}
if (changedColumnTypes.Count == 0)
{
return(dataTable);
}
for (int i = 0; i < dtChanged.Columns.Count; i++)
{
if (changedColumnTypes.ContainsKey(i))
{
dtChanged.Columns[i].DataType = changedColumnTypes[i];
}
}
rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
DataRow r = dtChanged.NewRow();
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var value = row[i];
if (changedValues.ContainsKey((rowIndex, i)))
{
r[i] = changedValues[(rowIndex, i)];
}
private object ParseValue(TableColumn column, object value, bool bytesAsString = false)
{
if (value != null)
{
Type type = value.GetType();
bool needQuotated = false;
string strValue = "";
if (type == typeof(DBNull))
{
return("NULL");
}
else if (type == typeof(Byte[]))
{
if (((Byte[])value).Length == 16) //GUID
{
string str = ValueHelper.ConvertGuidBytesToString((Byte[])value, this.databaseType, column.DataType, column.MaxLength, bytesAsString);
if (!string.IsNullOrEmpty(str))
{
needQuotated = true;
strValue = str;
}
else
{
return(value);
}
}
else
{
return(value);
}
}
bool oracleSemicolon = false;
switch (type.Name)
{
case nameof(Guid):
needQuotated = true;
if (this.databaseType == DatabaseType.Oracle && column.DataType.ToLower() == "raw" && column.MaxLength == 16)
{
strValue = StringHelper.GuidToRaw(value.ToString());
}
else
{
strValue = value.ToString();
}
break;
case nameof(String):
needQuotated = true;
strValue = value.ToString();
if (this.databaseType == DatabaseType.Oracle)
{
if (strValue.Contains(";"))
{
oracleSemicolon = true;
}
}
break;
case nameof(DateTime):
case nameof(DateTimeOffset):
case nameof(MySql.Data.Types.MySqlDateTime):
if (this.databaseType == DatabaseType.Oracle)
{
if (type.Name == nameof(MySql.Data.Types.MySqlDateTime))
{
DateTime dateTime = ((MySql.Data.Types.MySqlDateTime)value).GetDateTime();
strValue = this.GetOracleDatetimeConvertString(dateTime);
}
else if (type.Name == nameof(DateTime))
{
DateTime dateTime = Convert.ToDateTime(value);
strValue = this.GetOracleDatetimeConvertString(dateTime);
}
else if (type.Name == nameof(DateTimeOffset))
{
DateTimeOffset dtOffset = DateTimeOffset.Parse(value.ToString());
int millisecondLength = dtOffset.Millisecond.ToString().Length;
string strMillisecond = millisecondLength == 0 ? "" : $".{"f".PadLeft(millisecondLength, 'f')}";
string format = $"yyyy-MM-dd HH:mm:ss{strMillisecond}";
string strDtOffset = dtOffset.ToString(format) + $"{dtOffset.Offset.Hours}:{dtOffset.Offset.Minutes}";
strValue = $@"TO_TIMESTAMP_TZ('{strDtOffset}','yyyy-MM-dd HH24:MI:ssxff TZH:TZM')";
}
}
else if (this.databaseType == DatabaseType.MySql)
{
if (type.Name == nameof(DateTimeOffset))
{
DateTimeOffset dtOffset = DateTimeOffset.Parse(value.ToString());
strValue = $"'{dtOffset.DateTime.Add(dtOffset.Offset).ToString("yyyy-MM-dd HH:mm:ss.ffffff")}'";
}
}
if (string.IsNullOrEmpty(strValue))
{
needQuotated = true;
strValue = value.ToString();
}
break;
case nameof(Boolean):
strValue = value.ToString() == "True" ? "1" : "0";
break;
case nameof(TimeSpan):
if (this.databaseType == DatabaseType.Oracle)
{
return(value);
}
else
{
needQuotated = true;
if (column.DataType.IndexOf("datetime", StringComparison.OrdinalIgnoreCase) >= 0)
{
DateTime dateTime = this.dbInterpreter.MinDateTime.AddSeconds(TimeSpan.Parse(value.ToString()).TotalSeconds);
strValue = dateTime.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
strValue = value.ToString();
}
}
break;
case "SqlHierarchyId":
case "SqlGeography":
case "SqlGeometry":
needQuotated = true;
strValue = value.ToString();
break;
default:
if (string.IsNullOrEmpty(strValue))
{
strValue = value.ToString();
}
break;
}
if (needQuotated)
{
strValue = $"{this.dbInterpreter.UnicodeInsertChar}'{ValueHelper.TransferSingleQuotation(strValue)}'";
if (oracleSemicolon)
{
strValue = strValue.Replace(";", $"'{OracleInterpreter.CONNECT_CHAR}{OracleInterpreter.SEMICOLON_FUNC}{OracleInterpreter.CONNECT_CHAR}'");
}
return(strValue);
}
else
{
return(strValue);
}
}
else
{
return(null);
}
}
