I am currently working on a method, which is supposed to retrieve data from a MySQL Database and build an Insert-Statement with the retrieved data. So far I was able to get the values of all columns, except for one which type is time. I have been stuck on this problem for too long... Here is my Method. I always appreciate some good criticism.
public override bool CopyData(DbMigrator target)
{
try
{
if (this is MySqlMigrator)//MYSQL-DATACOPY
{
Func<Dictionary<string, byte[]>, int, byte[], string> StoreBytes = (Dict, x, obj) => { Dict.Add($"@Data{x}", obj); return $"@Data{x}"; };
Func<Dictionary<string, string>, int, string, string> StoreDateTimes = (Dict, x, date) => { Dict.Add($"@Data{x}", date); return $"@Data{x}"; };
var charTypes = new HashSet<string> { "nchar", "char", "varchar", "longtext", "nvarchar", "text", "tinytext", "mediumtext", "ntext", "geometry", "set", "enum" };
var binaryTypes = new HashSet<string> { "geography", "image", "binary", "varbinary", "mediumblob", "blob", "tinyblob", "longblob" };
foreach (var t in Structure)
{
using MySqlDataReader reader = new MySqlCommand($"SELECT t.* FROM `{t.Schema}`.`{t.Name}` t", (MySqlConnection)Connection).ExecuteReader();
while (reader.Read())
{
var colValueStr = "";
Dictionary<string, byte[]> ByteParams = new();
Dictionary<string, string> DateParams = new();
for (var i = 0; i < reader.FieldCount; i++)
{
var type = reader.GetDataTypeName(i).ToLower();
object result;
if (reader.IsDBNull(i)) { result = "NULL"; }
else if (charTypes.Contains(type)) { result = $"'{reader.GetValue(i)}'"; }
else if (binaryTypes.Contains(type)) { result = StoreBytes(ByteParams, i, GetBinary(reader, i)); }
else
result = type switch
{
"bit" => reader.GetInt64(i),
"boolean" => reader.GetBoolean(i) ? 1 : 0,
"tinyint" => reader.GetInt64(i),
"smallint" => reader.GetInt64(i),
"year" => reader.GetInt16(i),
"int" => reader.GetInt64(i),
"mediumint" => reader.GetValue(i),
"float" => reader.GetFloat(i).ToString().Replace(",", "."),
"bigint" => reader.GetInt64(i),
"double" => reader.GetDouble(i).ToString().Replace(",", "."),
"real" => reader.GetDouble(i).ToString().Replace(",", "."),
"decimal" => reader.GetDecimal(i).ToString().Replace(",", "."),
"numeric" => reader.GetDecimal(i).ToString().Replace(",", "."),
"fixed" => reader.GetDecimal(i).ToString().Replace(",", "."),
"serial" => reader.GetDecimal(i),
"date" => $"'{reader.GetDateTime(i):yyyy-MM-dd}'",
"datetime" => $"'{reader.GetDateTime(i):yyyy-MM-dd hh:mm:ss.fff}'",
"timestamp" => $"'{reader.GetDateTime(i):yyyy-MM-dd hh:mm:ss.fffffff}'",
"datetimeoffset" => reader.GetDateTime(i),
"time" => $"'{reader.GetTimeSpan(i)}'",
_ => throw new NotImplementedException(),
};
colValueStr += $"{result}, ";
}
if (!InsertDataRow(target, t, colValueStr.TrimEnd(',', ' '), ByteParams, DateParams)) return false;
}
}
}
else { target.CopyData(target); }
return true;
}
catch (Exception e)
{
LastError = $"Error while executing Copying Process: {e.Message}";
return false;
}
}
I tried to obtain the value through the MySqlDataReaders GetTimeSpan-Method. The expected value can be between '-838:59:59.000000' and '838:59:59.000000'. I tried to get this value '-88:34:22' from my database. But i got '-3.16:34:22' instead.