c# - Dapper:字符串未被识别为有效的日期时间

Question

我正在使用 Dapper 访问 sqlite 数据库并获取“字符串未被识别为有效的日期时间。”。如有任何帮助，我们将不胜感激。

// Create table schema
CREATE TABLE "mytable" ( "field1"   TEXT, "field2"  TEXT, "field3"  TEXT, 
                         "field4"   TEXT, "field5"  TEXT, 
                         "field6"   bit, 
                         "field7"   TEXT, "field8"  TEXT, "field9"  TEXT,
                         "field10"  TEXT, 
                         "field11"  DateTime )

查询代码:

var result= sqliteConnection.Query<TestItem>("Select * from mytable");

插入数据的查询:

INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', '', 'some value', 'some value', '1', '84', 'ae35e1e1-dd4c-4e49-a76c-d577f417bf9a', 'some value', 'HOME.aspx', 
                             '2020/01/20 17:38');
INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', 'asdf', 'some value', 'some value', '1', '32', 'a1cd1b8f-95f6-4b03-8d54-f904c21749ac', 'HOME.aspx', 'HOME.aspx', 
                              '2020/01/20 17:38');
INSERT INTO "main"."mytable" ("field1", "field2", "field3", "field4", "field5", "field6", "field7", "field8", "field9", "field10", "field11") VALUES ('750eb223-2993-4d85-9d4f-3e8689e9baa7', 'some value', 'some value', 'some value', 'some value', '1', '99', 'b9e63bfd-c73e-4e9a-b3e7-30ae49d8a002', 'CALLSS.aspx', 'CALLSS.aspx', 
                              '2020/01/20 17:38');

错误消息:

Error parsing column 10 (field11=HOME.aspx - String)

内部异常消息:

String was not recognized as a valid DateTime.

堆栈跟踪:

at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3609
   at Dapper.SqlMapper.<QueryImpl>d__138`1.MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1100
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723
   at Tzunami.LinkResolver.DatabaseMigration.Models.DBMigrator.<MigrateDeploymentListItemAsync>d__5.MoveNext() in C:\Users\surendra\source\repos\Tzunami.LinkResolver.MigrationTool\Tzunami.LinkResolver.DatabaseMigration\Models\DBMigrator.cs:line 77

映射项目类别:

public class TestItem
{
  public string Field1 { get; set; }
  public string Field2 { get; set; }
  public string Field3 { get; set; }
  public string Field4 { get; set; }
  public string Field5 { get; set; }
  public string Field6 { get; set; }
  public string Field7 { get; set; }
  public string Field8 { get; set; }
  public string Field9 { get; set; }
  public string Field10 { get; set; }
  public string Field11 { get; set; }
}

数据库架构和条目可以从 DB SQLite 浏览器中的以下示例中看到

Answer 1

首先:请记住，正如 Sqlite 文档中提到的，没有 Datatypes 因为 DateTime 它以某种方式存储为文本或一些数字，因此最好保存日期在数据库创建期间作为纯文本。此处的文档:https://www.sqlite.org/datatype3.html

其次:预计日期时间采用 ISO8601 格式，即“YYYY-MM-DD HH:MM:SS.SSS”格式，但作为表上的数据，它不能是“YYYY-MM-DD HH:MM”解析为 DateTime。

解决方法:

由于您无法更改数据库架构和数据，connection.ExecuteReader 在这些情况下工作得很好，因此请使用 connection.ExecuteReader 并以字符串形式获取列值使用 reader.GetString() 方法。