0

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.

Luctus
  • 1
  • 2
  • the value you meant is collected here: `"time" => $"'{reader.GetTimeSpan(i)}'"` – weirdgyn Mar 17 '23 at 09:28
  • Yes @weirdgyn . – Luctus Mar 17 '23 at 16:10
  • have you checked [TimeSpan](https://learn.microsoft.com/it-it/dotnet/api/system.timespan?view=net-8.0) structure .. does it match [MySQL TIME](https://dev.mysql.com/doc/refman/8.0/en/time.html) ? – weirdgyn Mar 17 '23 at 16:13
  • maybe [this](https://stackoverflow.com/questions/46061862/calculating-time-difference-in-c-sharp-and-saving-to-mysql) question can help – weirdgyn Mar 17 '23 at 16:17
  • @weirdgyn I'll look into it. I think i tried something similar to this – Luctus Mar 20 '23 at 12:06

0 Answers0