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