我正在使用Dapper访问sqlite数据库并获取“字符串未识别为有效的DateTime。”。任何帮助表示赞赏。
// 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; }
}
首先:请记住,如Sqlite文档中所述,由于以文本或某些数字形式存储Datatypes
,DateTime
因此没有任何内容,因此最好在DB创建期间将日期保存为纯文本。此处的文档:https : //www.sqlite.org/datatype3.html
其次:预期DateTime为ISO8601格式,即“ YYYY-MM-DD HH:MM:SS.SSS”格式,但作为表上的数据为“ YYYY-MM-DD HH:MM”,则不能解析为DateTime
。
解决方法:
由于您无法更改数据库架构和数据,connection.ExecuteReader
因此在这些情况下效果很好,因此请使用connection.ExecuteReader
和使用列reader.GetString()
方法作为字符串来获取列值。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句