C# 更新 mySQLDateTime 数据表

Question

我在使用数据表更新 mySQL 数据库时遇到问题。我可以使用 INSERT 语句来完成此操作，但是当我插入一行并出现错误“无法在日期列中存储 <...>”时，表会分配失败。我有数百万条记录要插入，我认为这种方式可能会快点。其实我不在乎时间，只在乎日期。

            MySqlConnection con = new MySqlConnection();
        con.ConnectionString = string.Format(@"server={0};userid={1};password={2};database={3};AllowZeroDatetime=True", srvr, user, pass, db);
        MySqlCommand cmnd = new MySqlCommand();
        cmnd.Connection = con;
        con.Open();
        cmnd.CommandText = "DROP TABLE IF EXISTS dateTest";
        cmnd.ExecuteNonQuery();
        cmnd.CommandText = "CREATE TABLE dateTest (date DATE, dateTime DATETIME)";
        cmnd.ExecuteNonQuery();

        string myDate = "2014-04-19";
        string myDateTime = "2014-04-20 00:00:00";

        //this code works
        cmnd.CommandText = string.Format("INSERT INTO dateTest(date, dateTime) VALUES('{0}', '{1}')", myDate, myDateTime);
        cmnd.ExecuteNonQuery();

        MySqlDataAdapter da = new MySqlDataAdapter("SELECT * from dateTest", con);
        MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
        DataTable tbl = new DataTable();
        da.Fill(tbl);
        foreach (DataRow row1 in tbl.Rows)
        {
            Debug.WriteLine(string.Format("{0} : {1}", row1["date"], row1["dateTime"]));
            //returns: 4/19/2014 : 4/20/2014 12:00:00 AM
        }
        DataRow row2 = tbl.NewRow();
        row2["date"] = myDate; //Errors here: Couldn't store <2014-04-19> in date Column.  Expected type is MySqlDateTime.
        row2["dateTime"] = myDateTime; //Also errors here: Couldn't store <2014-04-20 00:00:00> in dateTime Column.  Expected type is MySqlDateTime.
        tbl.Rows.Add(row2);
        da.Update(tbl);

Answer 1

这是我第一次尝试回答问题。希望这有帮助。

我认为你必须先将日期转换为DateTime，然后才能将其存储到mysql中。

string myDateTime = "2014-04-20 00:00:00";
DateTime myDateTimeValue = DateTime.Parse(myDateTime);

然后

row2["dateTime"] = myDateTimeValue;

我还没试过。希望它有效