C# SQLite ExecuteReader --> DataTable.Load --> FormatException (DateTime)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10287487/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite ExecuteReader --> DataTable.Load --> FormatException (DateTime)
提问by masterchris_99
I tried to read out a sample Northwind sqlite database but get an error on some tables with a datetime. Is this a problem of the database or of my System.Data.SQLite? The Exception is somethin like: "the string is not a valid DateTime"
我试图读出一个示例 Northwind sqlite 数据库，但在某些带有日期时间的表上出现错误。这是数据库的问题还是我的 System.Data.SQLite 的问题？异常类似于：“字符串不是有效的日期时间”
http://system.data.sqlite.org/
http://system.data.sqlite.org/
Of cource I can read out the data by myself with the correct converting of datetime but this is not as performant as reading it out via a simple dt.Load()
当然，我可以通过正确转换日期时间自己读出数据，但这不如通过简单的 dt.Load()
SQLiteCommand dbCommand = myConnector.CreateCommand();
dbCommand.CommandText = "SELECT * FROM " + tablename;
SQLiteDataReader executeReader = dbCommand.ExecuteReader(CommandBehavior.SingleResult);
DataTable dt = new DataTable();
dt.Load(executeReader); // <-- FormatException
" bei System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)\r\n bei System.DateTime.ParseExact(String s, String[] formats, IFormatProvider provider, DateTimeStyles style)\r\n bei System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConvert.cs:Zeile 322."
" bei System.DateTimeParse.ParseExactMultiple(String s, String[] 格式, DateTimeFormatInfo dtfi, DateTimeStyles 样式)\r\n bei System.DateTime.ParseExact(String s, String[] 格式, IFormatProvider provider, DateTimeStyles 样式)\r\ n bei System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind) in c:\dev\sqlite\dotnet\System.Data.SQLite\SQLiteConvert.cs:Zeile 322。”
I need a good help to improve the current code.
我需要一个很好的帮助来改进当前的代码。
采纳答案by Leandro
Yep, I faced the same problem, and I've found a solution.
是的，我遇到了同样的问题，我已经找到了解决方案。
First of all you need to know why it happens.
首先你需要知道它为什么会发生。
http://www.sqlite.org/datatype3.html
http://www.sqlite.org/datatype3.html
**
**
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
SQLite 没有专门用于存储日期和/或时间的存储类。相反，SQLite 的内置日期和时间函数能够将日期和时间存储为 TEXT、REAL 或 INTEGER 值：
**
**
TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.
应用程序可以选择以任何这些格式存储日期和时间，并使用内置的日期和时间函数在格式之间自由转换。
For more date/time functions you can see this page: http://www.sqlite.org/lang_datefunc.html
有关更多日期/时间函数，您可以查看此页面：http: //www.sqlite.org/lang_datefunc.html
SOLUTION:
解决方案：
Is just use a datetime function to convert your field into a valid .NET datatable format. You can use anyone from the above link, in my case I choose:
只是使用 datetime 函数将您的字段转换为有效的 .NET 数据表格式。您可以使用上述链接中的任何人，就我而言，我选择：
SELECT datetime(field) as field FROM table
TIP 1If your query is on a view, and you can't touch your db, create a different view or something like, you are in a little trouble because the problem is on the db side as I explain before. So the first solution is replace the column of a datatable before load (a little dirty work). Or you can use a Unix stamp on a text field, or any desired timestamp. But I think the best solution is create your query on your application from your view:
提示 1如果您的查询在视图上，并且您无法触摸您的数据库、创建不同的视图或类似的东西，那么您会遇到一些麻烦，因为正如我之前解释的那样，问题出在数据库方面。所以第一个解决方案是在加载之前替换数据表的列（有点脏工作）。或者您可以在文本字段或任何所需的时间戳上使用 Unix 戳记。但我认为最好的解决方案是从您的视图中创建对您的应用程序的查询：
SELECT datetime(field) as field FROM view
TIP 2
提示 2
You can try also with the SQLite Administrator and play with datetime functions before to check the format.
您也可以尝试使用 SQLite 管理员并在检查格式之前使用日期时间函数。
TIP 3
提示 3
Be aware of your culture globalization also, you can find a problem if you change the culture. Try to your soft work with only one and display it as you want, not as the culture wants. In my country for eg, we use as common this format: dd/MM/yy and is a pain, like the dot on decimal numbers (we use a comma)
也要注意你的文化全球化，如果你改变文化，你会发现问题。尝试只用一个软工作，并按照你的意愿展示它，而不是按照文化的要求。例如，在我的国家，我们通常使用这种格式：dd/MM/yy 并且很麻烦，就像十进制数字上的点（我们使用逗号）
TIP 4
提示 4
If you are working with a wizard-defined connection on some report or entity or something like, be sure to check your desired conversion format. For example I use the most common and non problematic way: ISO8601
如果您在某个报表或实体或类似的东西上使用向导定义的连接，请务必检查所需的转换格式。例如我使用最常见且没有问题的方式：ISO8601
回答by mgnoonan
See this publicationabout SQLite and DateTime storage, under the heading of Storing DateTimes.
回答by banging
Did you try with SQLiteDataAdapterlike so..
你有没有SQLiteDataAdapter像这样尝试过..
SQLiteDataAdapter da = new SQLiteDataAdapter(dbCommand);
da.Fill(dt);
Chances are it behaves the same but it doesn't hurt to try ;)
有可能它的行为相同，但尝试并没有什么坏处；)
