MySQL的DataSet字段MySQLDateTime到DateTime [英] MySQL's fetched DataSet field MySQLDateTime to DateTime

问题描述

查询摘要:数据集(从MySQL中获取)中的datetime列的类型为MySQLDateTime，该空值保留为0000-00-00，当我尝试在SQL Server中批量插入时不兼容.给出此错误

MySql.Data.Types.MySqlConversionException:无法将MySQL日期/时间值转换为System.DateTime

代码如下；

Query summary: the datetime column in a dataset(fetched from MySQL) is of type MySQLDateTime, which holds null values as 0000-00-00, which is incompatible when i try to bulk insert in SQL server....which gives this error

MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

the code is as follows;

MySqlConnection connection = new MySqlConnection(mysqlConnString);
                    connection.Open();
                    MySqlCommand command = connection.CreateCommand();
                    command.CommandText = "SELECT * FROM ` orders`;";
                    MySqlDataAdapter myda = new MySqlDataAdapter(command.CommandText, connection);
                    myda.Fill(ds, sTableName);

上面的作品find和ds包括了罗斯文的订购表的空日期值

the above works find and ds include the northwind''s order table''s empty date values

using (SqlBulkCopy bulkcopy = new SqlBulkCopy(trans.Connection, SqlBulkCopyOptions.Default, trans))
                {
                    bulkcopy.DestinationTableName = "["+sTableName+"]";
                    bulkcopy.WriteToServer(ds.Tables[0]);
                    cConvertor.AddTextOutputtoFile(ds.Tables[0].Rows.Count.ToString() + " row(s) have been added to " + sTableName + ".");   
                }

请告知

Please advise

推荐答案

在进行批量复制之前，您需要检查数据集并用null替换0000-00-00值.

如果创建了SqlBulkCopy类，则此任务应该属于它.

Before doing your bulk copy, you need to check your dataset and replace 0000-00-00 values by null.

If you created the SqlBulkCopy class, this task should belong to it.

在进行批量复制之前，您需要检查数据集并用.

如果创建了SqlBulkCopy类，则此任务应属于她.

Before doing your bulk copy, you need to check your dataset and replace 0000-00-00 values by null.

If you created the SqlBulkCopy class, this task should belong to her.

这篇关于MySQL的DataSet字段MySQLDateTime到DateTime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！