无法将带有注册00:00:00值的MySQL.DateTime转换为System.DateTime [英] Unable to convert MySQL.DateTime to System.DateTime with 0000-00-00 00:00:00 values

问题描述

我正在从事C#项目，但遇到了问题.该程序允许用户连接到MySQL数据库并从每个选定的表中检索信息，并将数据写到文件中.问题是因为我不知道该架构将是什么样，或者它将包含什么值.

I am working on a C# project and I am facing an issue. The program allows the user to connect to a MySQL database and retrieve information from each selected table and write the data out to a file. The problem is because I have no idea what the schema is going to be like or what values its going to contain.

如果时间戳记"列中包含日期0000-00-00 00:00:00，则我将收到转换错误，无论我尝试执行什么操作，它都永远不会起作用.我试过转换为字符串，试过转换为DateTime，但总是收到错误消息.

If the timestamp column contains the date 0000-00-00 00:00:00 I get the conversion error and no matter what I try it never works. I've tried converting to a string I've tried converting to a DateTime but I always get the error.

以下是我当前尝试获取数据的方式:

Below is how I am currently trying to get the data:

using (ConnectMySQLDB db = new ConnectMySQLDB(databaseSettings))
{
   string query = string.Format("SELECT * FROM {0}.{1}", database, table);
   Console.WriteLine("Query: {0}", query);
   using (MySqlCommand cmd = new MySqlCommand(query, db.conn))
   {
      using (MySqlDataReader reader = cmd.ExecuteReader())
      {
         int i = 1;
         while (reader.Read())
         {
            Console.WriteLine("ID: {0}", i);
            fieldsAndValues = new Dictionary<string, string>();
            foreach (ColumnDataTypes fieldAndType in fieldsAndTypes)
            {
               Console.WriteLine("Column: {0} Type: {1}", fieldAndType.field, fieldAndType.dataType);
               string formattedValue = "";
               if (fieldAndType.dataType == "timestamp")
               {
                  DateTime date = DateTime.Parse(reader.GetDateTime(fieldAndType.field).ToString());
                  formattedValue = date.ToString("yyyyMMdd");
               }

               else
               {
                  formattedValue = getDBFormattedValue(reader.GetString(fieldAndType.field), fieldAndType.dataType);
                  fieldsAndValues.Add(fieldAndType.field, formattedValue);
               }
            }
            rows.Add(fieldsAndValues);
            i++;
         }
      }
   }
}

我还在连接器字符串中添加了允许零日期"和将zerozerodate转换为null"选项，如下所示:

I also have added the allow zero date and convertzerodate to null option in the connector string as follows:

connString = "server=" + server + ";uid=" + username + ";pwd=" + password + ";port=" + port + ";Allow Zero Datetime=true;zeroDateTimeBehavior=convertToNull;Convert Zero Datetime=true";

推荐答案

查看

Looking at this documentation, it looks like you're specifying two contradictory options (AllowZeroDateTime=true and ConvertZeroDateTime=true) and one which appears not to be listed (ZeroDateTimeBehavior=ConvertToNull).

我建议，除非您有不想与零"值混合的 DateTime.MinValue 实际数据，否则 just 指定 ConvertZeroDateTime = true 并检测结果是否为 DateTime.MinValue .您绝对不应调用 reader.GetDateTime()，然后将结果转换为字符串，然后再返回到 DateTime -您应该尽可能避免字符串转换，因为它们很容易为您搞砸.

I suggest that unless you have actual data which is DateTime.MinValue which you don't want to mix up with the "zero" value, you just specify ConvertZeroDateTime=true and detect if the result is DateTime.MinValue. You definitely shouldn't call reader.GetDateTime(), then convert the result to a string, and then back to a DateTime - you should avoid string conversions as far as you can, as they can mess things up for you pretty easily.

目前还不清楚您要为这些零"值使用什么字符串值，但是应该可以轻松地使用 DateTime.MinValue 对它们进行特殊大小写.就我个人而言，我实际上试图将数据尽可能地保持其本机"形式，而不是将所有内容都转换为字符串，但这是另一回事.

It's not really clear what string value you want for these "zero" values, but you should be able to special-case them with DateTime.MinValue fairly easily. Personally I'd actually try to keep the data in its "native" form as much as possible rather than converting everything to strings, but that's a different battle.

这篇关于无法将带有注册00:00:00值的MySQL.DateTime转换为System.DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！