SQLite中SQLite DateTime处理 [英] SQLite DateTime handling in SQLProvider

问题描述

如何使用SQLite通过

LinqPad将其视为Table2中的DateTime这个查询在那里工作：

  var dt2 = new System.DateTime（2016,8,30,0,0,0， DateTimeKind.Local）; 
 Table2 
 .Where（r => r.Date1 == dt2）.Dump（）; 
  

解决方案

DateTime类型与以下版本一起工作， SQLProvider 1.0.31和SQLite 1.0.102：

  #if INTERACTIVE 
 #I @.. \packages \SQLProvider.1.0.31\lib
 #rFSharp.Data.SqlProvider.dll
 #I @.. \packages\System.Data.SQLite.Core.1.0 .102.0\lib\\\
et46
 #rSystem.Data.SQLite.dll
 #I @.. \packages\System.Data.SQLite.Linq.1.0.102.0 \lib \\\
et46
 #rSystem.Data.SQLite.Linq.dll
 #endif 
 
打开系统
打开FSharp.Data.Sql 
 //打开System.Data.SQLite 
 //打开System.Data.SQLite.Linq 
 
 [< Literal>] 
 let connectionString =数据源=+ @C：\tmp\databaseFile.db3
 [< Literal>] 
 let resolutionPath = __SOURCE_DIRECTORY__ + @.. \..\packages\System。 Data.SQLite.Core.1.0.102.0\lib\\\
et46
 
 type sql = SqlDataProvider& lt; 
 Common.DatabaseProviderTypes.SQLITE，
 connectionString，
 ResolutionPath = resolutionPath，
 CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL> 
 
 let ctx = sql.GetDataContext（）
 
 let table2 = ctx.Main.Table2 // DateTime 
 let table3 = ctx.Main.Table3 // Text 
  

How to use DateTime with SQLite via the SQLProvider type-provider?

SQLite doesn't really have a date and time datatype (see Data types) and stores dates as text. I can pass a date string and query it, and get back a string. So this works, where Date1 in table3 was stored as a string:

query {                                         
    for r in table3 do
        select (r.Date1)                                
        } |> Seq.toList

val it : string list =
  ["2016/06/09 0:00:00"; "2016/06/05 0:00:00"; "2016/06/04 0:00:00";
   "2016/06/12 0:00:00"; "2016/06/10 0:00:00"; "2016/06/06 0:00:00";

It is also possible to store Date1 as a DateTime, and in another table I have it as such. That is even though SQLite doesn't understand DateTime, I can create a column with the DateTime data type, and I can store a DateTime value in it. I can extract this value in C# (or LinqPad) for example. But when I try to access it via the type provider (the same type provider that let me store the DateTime value), it gives the following error:

System.FormatException: String was not recognized as a valid DateTime.

>    at System.DateTimeParse.ParseExactMultiple(String s, String[] formats, DateTimeFormatInfo dtfi, DateTimeStyles style)
   at System.Data.SQLite.SQLiteConvert.ToDateTime(String dateText, SQLiteDateFormats format, DateTimeKind kind, String formatString)
   at System.Data.SQLite.SQLite3.GetDateTime(SQLiteStatement stmt, Int32 index)
   at System.Data.SQLite.SQLite3.GetValue(SQLiteStatement stmt, SQLiteConnectionFlags flags, Int32 index, SQLiteType typ)
   at System.Data.SQLite.SQLiteDataReader.GetValue(Int32 i)
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime[e-mail](IEnumerable`1& next)
   at Microsoft.FSharp.Core.CompilerServices.GeneratedSequenceBase`1.MoveNextImpl()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at Microsoft.FSharp.Collections.SeqModule.ToArray[T](IEnumerable`1 source)
   at FSharp.Data.Sql.Runtime.QueryImplementation.executeQuery(ISqlDataContext dc, ISqlProvider provider, SqlExp sqlExp, List`1 ti)
   at FSharp.Data.Sql.Runtime.QueryImplementation.SqlQueryable`1.System-Collections-Generic-IEnumerable`1-GetEnumerator()
   at Microsoft.FSharp.Collections.SeqModule.ToList[T](IEnumerable`1 source)
   at <StartupCode$FSI_0075>.$FSI_0075.main@()

The difference between the working (type provider) query in table3 and the one with the error in table2 is the data type of the column:

LinqPad correctly sees it as a DateTime in Table2 and this query works there:

var dt2 = new System.DateTime(2016,8,30,0,0,0,DateTimeKind.Local);
Table2
    .Where(r => r.Date1 == dt2).Dump();

解决方案

DateTime type is working as intended with the following versions with SQLProvider 1.0.31 and SQLite 1.0.102:

#if INTERACTIVE
#I @"..\packages\SQLProvider.1.0.31\lib"
#r "FSharp.Data.SqlProvider.dll" 
#I @"..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"
#r "System.Data.SQLite.dll"
#I @"..\packages\System.Data.SQLite.Linq.1.0.102.0\lib\net46"
#r "System.Data.SQLite.Linq.dll"
#endif

open System
open FSharp.Data.Sql
//open System.Data.SQLite
//open System.Data.SQLite.Linq

[<Literal>]
let connectionString = "Data Source="+ @"C:\tmp\databaseFile.db3"
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"..\..\packages\System.Data.SQLite.Core.1.0.102.0\lib\net46"

type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext() 

let table2 = ctx.Main.Table2   //DateTime
let table3 = ctx.Main.Table3   //Text

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