How can I retrieve the correct value of a MySQL Time Column in C# using the MySqlDataReader

53 Views Asked by At

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.

0

There are 0 best solutions below