SQLite 没有正确存储小数 [英] SQLite not storing decimals correctly
问题描述
我有一个带有名为 tbl_invent 的表的 sqlite 数据库，在表单加载时它用表中的内容填充 datagridview.问题是我的字段名称 cost 和 sale_price 有小数，当表单加载时它只显示数字而不显示小数.
示例:
表=1.75，DGV=1.00
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) 处理 MyBase.Load连接()Dim da As New SQLiteDataAdapter("select * from tbl_Invent", connection)Dim ds 作为新数据集da.Fill(ds, "tbl_Invent")DataGridView1.DataSource = dsDataGridView1.DataMember = "tbl_Invent"DataGridView1.Columns(6).ValueType = GetType(Single)DataGridView1.Columns(6).DefaultCellStyle.Format = "N2"DataGridView1.Columns(7).ValueType = GetType(Single)DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"连接.关闭()da.Dispose()结束子我已经检查了字段类型它是正确的整数"，我也尝试了GetType(Single)"和GetType(Decimal)"，但还是一样.任何人都可以指出我正确的方向吗?谢谢.
<小时>来自评论:
SQLite 中没有其他类型.SQLite中也只有Text"、Integer"、Real"和Blob"，它说整数可以有小数.
您没有指明您使用的是哪个 DB 提供程序，但是标准提供程序(来自 SQLite 开发人员)会看到 Integer 和 map将数据转换为不允许小数的 NET Int32 类型.Real 会像 Decimal 一样保存小数.
SQLite 中没有其他类型.只有Text"、Integer"、Real"和Blob"
这是真的，但它适用于 SQLite 数据库，而不是数据库提供程序.标准的 DB Provider 被巧妙地编写为能够将 4 种基本类型转换为各种 NET 类型，从而使实际的存储类型/格式成为实现细节.
<小时>提供程序代码包括许多步骤、查找表、子系统、字典和执行转换的方法.甚至还有一种方法可以定义自定义类型名称.以下是对工作原理的概括解释.
SQLite NET 提供程序识别的列类型名称
字节、SByte
INT8, INTEGER8, TINYSINT (SByte)UINT8、UNSIGNEDINTEGER8、TINYINT(字节)
积分(短、长、有符号、无符号等)
BIGINT、BIGUINT、计数器、身份、INT、INT16、INT32、INT64、INTEGER、INTEGER16、INTEGER32、INTEGER64、长、SMALLINT、SMALLUINT、UINT、UINT16、UINT32、UINT64、ULGER、UNSIGNED4E64UNSIGNED4E>
布尔值
BIT、BOOL、BOOLEAN、逻辑、YESNO
文本/字符串
CHAR, CLOB, LONGCHAR, LONGTEXT, LONGVARCHAR, MEMO, NCHAR, NOTE, NTEXT, NVARCHAR, STRING, TEXT, VARCHAR, VARCHAR2
数字
双，浮动，真实；单(单)
十进制
货币、十进制、货币、数字、数字
BLOB
二进制、BLOB、通用、图像、OLEOBJECT、RAW、VARBINARY
日期/时间
日期、日期时间、小日期、时间、时间戳
GUID
GUID，唯一标识符
来源:SQLiteConvert.cs 中的 SQLiteDbTypeMap(版本 1.0.103；2016 年 9 月).
本质上，DBProvider以适当的SQLite类型存储数据，但是当它读回时，它使用您在表定义中使用的类型来转换数据返回到 NET 类型.SQLite 提供程序包括一个大型的 SQLiteConvert 类来为您完成所有转换.
我无法在野外找到这个文档，尽管它似乎是 SQLite 爱好者的常识.大多数站点只是重新格式化 SQLite 站点内容.它可能记录在帮助文件中，但我的主题没有内容.给定列表，很容易意外使用有效名称并发现它有效.
该列表包含了其他 DB 使用的最常用符号，以及一些 NET 类型.例如，Boolean 可以定义为 BIT、BOOL、BOOLEAN、LOGICAL 或 YESNO.因此，此表定义合法且功能齐全:
创建表 LiteColTypes (Id 整数主键自增，名称文本，项目日期日期时间，Char3 CHAR (3),UINT32 UINT32,值 INT16，VarChar5 VARCHAR (5),G代码GUID，价格十进制，ItemImg 图像，活动布尔，不活跃 是 否);需要注意一些事项和一些有用的 DateTime 选项.
工作原理
列表来自此代码:
//////构建并返回一个包含数据库列类型的映射///被这个提供者识别.///</总结>///<返回>///包含由此识别的数据库列类型的映射///提供者.///</returns>私有静态 SQLiteDbTypeMap GetSQLiteDbTypeMap(){返回新的 SQLiteDbTypeMap(new SQLiteDbTypeMapping[] {new SQLiteDbTypeMapping("BIGINT", DbType.Int64, false),new SQLiteDbTypeMapping("BINARY", DbType.Binary, false),new SQLiteDbTypeMapping("BIT", DbType.Boolean, true),new SQLiteDbTypeMapping("BLOB", DbType.Binary, true),new SQLiteDbTypeMapping("BOOL", DbType.Boolean, false),new SQLiteDbTypeMapping("BOOLEAN", DbType.Boolean, false),...new SQLiteDbTypeMapping("GUID", DbType.Guid, false),new SQLiteDbTypeMapping("IMAGE", DbType.Binary, false)... (还有很多) 保留 XML 注释是因为它具有启发性和权威性:
<块引用>构建并返回包含数据库此提供程序识别的列类型的映射.(强调我的).
DbType 对流程至关重要.
读取数据
上面的 SQLiteDbTypeMap 将它识别的许多列名称与 DbType 相关联，该DbType 用于确定要返回的 NET 数据类型.该列表足够全面，可以为您转换除 1 或 2 种类型之外的所有类型.
例如，注意GUID和IMAG*都存储为BLOB，但是GUID 类型名称与不同的 DbType 相关联，这允许 那个 BLOB 以不同于 IMAGE BLOB 的方式返回.
您还可以通过连接对象指定类型.空间和范围不允许解释，虽然有点乏味，但它允许您为自定义类型名称提供数据类型.
存储数据
在存储数据时，您不必担心它应该如何存储.DB Provider 将使用传递的 DbType 来查找要使用的 SQLite 类型(Affinity").如果您使用 AddWithValue 或(过时的)Add(object,object) 重载，DBProvider 会猜测类型.它很擅长猜测，但不要那样做.
所以，不需要这种转换:
cmd.Parameters.Add("@g", DbType.Binary).Value = myGuid.ToByteArray();使用与任何其他数据库相同类型的代码:
'//为 c# 添加尾随分号cmd.Parameters.Add("@n", DbType.String).Value = "Ziggy"cmd.Parameters.Add("@dt", DbType.DateTime).Value = DateTime.Nowcmd.Parameters.Add("@c3", DbType.StringFixedLength, 3).Value = "XYZ123" '//见注释cmd.Parameters.Add("@u", DbType.UInt16).Value = 3cmd.Parameters.Add("@g", DbType.Guid).Value = myGuidcmd.Parameters.Add("@p", DbType.Decimal).Value = 3.14D'//'ToByteArray()' 是转换的扩展方法cmd.Parameters.Add("@img", DbType.Binary).Value = myImg.ToByteArray()cmd.Parameters.Add("@act", DbType.Boolean).Value = True注意事项:
- 使用描述传递的数据的
DbType，而不是您认为应该如何保存它(例如DbType.Guid，而不是Binary用于Guid).提供商将执行大多数转换. - 没有
DbType.Image，所以需要一个字节数组转换. - 为
Char()/VarChar()字段指定大小不会限制保存的字符数.这似乎是一个错误，因为保存比定义多的字符会阻止加载该行. UInt16反之:尝试传递超出范围的值，例如 UInt16 的 -5，将导致溢出异常.但是对于已经存储的这样的值，它会返回65531.- 列的大小/精度参数(例如
Decimal(9,2))似乎无关紧要.内部表提供固定的精度和大小. - 对于日期，传递日期并指明
DbType.DateTime.永远不需要传递特定格式的字符串.提供者知道事情.(请参阅下面的日期时间选项.) - 要仅保存日期，请仅传递日期:
.Value = DateTime.Now.Date.
两个不同的查找表用于保存和读取数据，它们的共同点是 DbType 这就是为什么它很重要.使用正确的方法可以确保数据可以往返.避免使用 AddWithValue.
演示/结果
来自 UI 浏览器的数据视图
加载数据没有什么特殊要求:
//Dim SQL = "SELECT * FROM LiteColTypes" ' for VB字符串 SQL = "SELECT * FROM LiteColTypes";...dbCon.Open();Dim dt As New DataTable();dt.Load(cmd.ExecuteReader());dgv.DataSource = dt;
DataGridView 中的相同数据
DGV 正确识别并显示 GUID、图像和布尔列.每个DataColumn的数据类型都符合预期:
名称 --->System.String (maxLen = 2147483647)项目日期 --->系统日期时间字符 3 --->System.String (maxLen = 3)UINT16 --->System.UInt16VarChar5 --->System.String (maxLen = 5)G代码--->系统向导价格 --->系统.十进制ItemImg --->System.Byte[]主动 --->系统布尔值不活跃 --->系统布尔值请注意，Guid 和 Image 项目都存储为 BLOB，但返回的方式不同.Active (BOOL) 和 NotActive (YESNO) 使用不同的类型名称但返回相同的数据类型.一切都按预期工作.
日期时间问题"和选项
TIME 作为列类型名称并不像预期的那样工作.它不会解析 DateTime.Now.TimeofDay (Timespan) 到它.该表将 TIME 映射到 DbType.DateTime.
请勿使用 DbType.DateTime2 或 .DateTimeOffset.这些在转换器查找中丢失，因此数据以无效格式(版本 1.0.103.0)存储为文本.
UTC、种类和标志
SQLite NET Provider 不只支持一种日期格式.保存为 UTC 时，数据包含一个指示符.但是，无论是保存为 Local 还是 UTC，Kind 总是返回为Unspecified.对此的部分补救措施是将 datetimekind 添加到您的连接字符串中:
`...;datetimekind=UTC;``...;datetimekind=本地;`这将为所有DateTime值返回设置Kind，但不转换值.
解决这个的方法是使用(相对)新的BindDateTimeWithKind连接标志.这将转换日期以匹配保存时连接的DateTimeKind:
Private LiteConnStr = "Data Source='C:Tempdemo.db';Version=3;DateTimeKind=Utc;"...Dim dt As New DateTime(2011, 2, 11, 11, 22, 33, 444, DateTimeKind.Local)使用 dbCon = New SQLiteConnection(LiteConnStr)dbCon.Flags = SQLiteConnectionFlags.Default 或SQLiteConnectionFlags.BindDateTimeWithKind...cmd.Parameters.Add("@dt", DbType.DateTime).Value = dt' == 2011-02-11 17:22:33.444Z 注意小时尽管传递了本地日期，BindDateTimeWithKind 会导致它被保存为 UTC 以匹配连接.由于DateTimeKind=Utc;"返回UTC日期连接设置.
请注意，DateTimeKind 对日期read起作用，而 BindDateTimeWithKind 在保存日期时起作用.就个人而言，他们似乎会使事情变得更糟；一起整个数据库成为基于UTC(或本地)的日期统一保存和读取为相同的Kind——你不需要做任何事情.
ConnectionFlags 手动使用可能很乏味，在连接字符串中指定它们:
connx = "...;datetimekind=Utc;flags='Default, BindDateTimeWithKind';"限制/问题
统一的 Kind 处理适用于 DbDataReader，至少适用于 Dapper.但是当使用 DataTable 时，日期的 Kind 保持未指定.这显然是由于 DataColumn 中的 DateTimeMode 属性，并且可能是 Microsoft 的设计决定不假设列中的所有日期始终相同 Kind.这也体现在其他数据库中.
当使用 UTC 或本地连接时，提供程序将单独保留未指定(这也适用于查询中的日期).所以不应该有任何不需要的额外转换:在 DataTable 中读取和伪装"为 Unspecified 的 UTC 日期不会在更新中再次转换.
刻度格式
与传统智慧"相反，日期并不总是并且仅保存为文本；为了节省一点空间，您可以保存刻度值.由于这些不能有时区指示器，Kind 相关选项可能非常有用.要启用 Ticks，请使用 DateTimeFormat 连接字符串选项:
Private LiteConnStr = "...;datetimekind=Utc;DateTimeFormat=Ticks;..."'例如:634939900800000000其他 DateTimeFormat 选项包括 CurrentCulture、ISO8601(默认)、JulianDay 和 UnixEpoch.无需更改列类型名称即可使用这些格式之一.仍然是日期，SQLite Provider 根据连接标志处理实现细节.
用户界面浏览器
许多 SQLite UI 浏览器似乎只知道四种规范类型.也许这是有意为之，但这限制了它们对 NET 开发人员的用处，并隐藏了 NET 提供者的功能.
SQLiteStudio(版本:3.1.0)提供了更多，但它似乎不知道完整列表，因为缺少一些非常有用的(例如 GUID、IMAGE、SINGLE、整数变体).
它确实允许您输入您想要的任何类型名称，因此获利！
总结(tl;dr)
- NET 提供程序通过支持各种列的类型名称为 SQLite 添加了功能，
- 每个支持的名称都与一个
DBType相关联，它决定了实际的返回数据类型 - 在保存数据时使用正确的
DbType可确保数据往返 - NET Provider 将为您执行大部分转换
DateTimeKind和BindDateTimeWithKind选项允许自动、统一的时区存储日期
最重要的是，NET 提供程序使实际存储成为实现细节.
I have a sqlite DB with a table called tbl_invent, on form load it fills the datagridview with what is in the table. The problem is I have field names cost and sell_price which have decimals, and when the form loads it only shows the number not the decimal.
sample:
Table=1.75, DGV=1.00
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
connect()
Dim da As New SQLiteDataAdapter("select * from tbl_Invent", connection)
Dim ds As New DataSet
da.Fill(ds, "tbl_Invent")
DataGridView1.DataSource = ds
DataGridView1.DataMember = "tbl_Invent"
DataGridView1.Columns(6).ValueType = GetType(Single)
DataGridView1.Columns(6).DefaultCellStyle.Format = "N2"
DataGridView1.Columns(7).ValueType = GetType(Single)
DataGridView1.Columns(7).DefaultCellStyle.Format = "N2"
connection.Close()
da.Dispose()
End Sub
i already check the field type it's correct "Integer", i also tried the "GetType(Single)" and "GetType(Decimal)" but still the same. any one could point me in the right direction? thank you.
From comments:
there is no other type in SQLite. there is only "Text", "Integer", "Real" and "Blob" also in SQLite it says integer can have decimals.
You did not indicate which DB provider you were using, but the standard provider (from the SQLite devs) will see Integer and map the data to the NET Int32 type which doesn't allow decimals. Real would save fractionals as would Decimal.
there is no other type in SQLite. there is only "Text", "Integer", "Real" and "Blob"
That's true but it applies to the SQLite DB, not the DB Provider. The standard DB Provider is cleverly written to be able to convert the 4 basic types to a variety of NET types such that the actual storage type/format becomes an implementation detail.
The provider code includes a number of steps, look-up tables, sub systems, dictionaries and methods to perform conversions. There is even a way to define custom type names. The following is a generalized explanation of the workings.
Column Type Names Recognized by the SQLite NET Provider
Byte, SByte
INT8, INTEGER8, TINYSINT (SByte)
UINT8, UNSIGNEDINTEGER8, TINYINT (Byte)
Integral (short, long, signed, unsigned etc)
BIGINT, BIGUINT, COUNTER, IDENTITY, INT, INT16, INT32, INT64, INTEGER, INTEGER16, INTEGER32, INTEGER64, LONG, SMALLINT, SMALLUINT, UINT, UINT16, UINT32, UINT64, ULONG, UNSIGNEDINTEGER, UNSIGNEDINTEGER16, UNSIGNEDINTEGER32, UNSIGNEDINTEGER64
Boolean
BIT, BOOL, BOOLEAN, LOGICAL, YESNO
Text/String
CHAR, CLOB, LONGCHAR, LONGTEXT, LONGVARCHAR, MEMO, NCHAR, NOTE, NTEXT, NVARCHAR, STRING, TEXT, VARCHAR, VARCHAR2
Numeric
DOUBLE, FLOAT, REAL; SINGLE (Single)
Decimal
CURRENCY, DECIMAL, MONEY, NUMBER, NUMERIC
BLOB
BINARY, BLOB, GENERAL, IMAGE, OLEOBJECT, RAW, VARBINARY
Date/Time
DATE, DATETIME, SMALLDATE, TIME, TIMESTAMP
GUID
GUID, UNIQUEIDENTIFIER
Source: SQLiteDbTypeMap in SQLiteConvert.cs (version 1.0.103; September, 2016).
In essence, the DBProvider stores the data in the appropriate SQLite type, but when it is read back it uses the type you used in the table definition to convert the data back to a NET type. The SQLite provider includes a large SQLiteConvert class to do all the conversions for you.
I cannot find this documented in the wild, though it seems to be common knowledge to SQLite devotees. Most sites just reformat the SQLite site content. It might be documented in the help file, but mine has topics with no content. Given the list, it is easy to accidentally use a valid name and discover it works.
The list incorporates the most common notations used by other DBs, plus a few NET types. For example, Boolean can be defined as BIT, BOOL, BOOLEAN, LOGICAL or YESNO. As a result, this table definition is legal and fully functional:
CREATE TABLE LiteColTypes (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
Name TEXT,
ItemDate DATETIME,
Char3 CHAR (3),
UINT32 UINT32,
Value INT16,
VarChar5 VARCHAR (5),
GCode GUID,
Price DECIMAL,
ItemImg IMAGE,
Active BOOL,
NotActive YESNO
);
There are a few things to be aware of and some useful DateTime options.
How It Works
The list comes from this code:
/// <summary>
/// Builds and returns a map containing the database column types
/// recognized by this provider.
/// </summary>
/// <returns>
/// A map containing the database column types recognized by this
/// provider.
/// </returns>
private static SQLiteDbTypeMap GetSQLiteDbTypeMap()
{
return new SQLiteDbTypeMap(new SQLiteDbTypeMapping[] {
new SQLiteDbTypeMapping("BIGINT", DbType.Int64, false),
new SQLiteDbTypeMapping("BINARY", DbType.Binary, false),
new SQLiteDbTypeMapping("BIT", DbType.Boolean, true),
new SQLiteDbTypeMapping("BLOB", DbType.Binary, true),
new SQLiteDbTypeMapping("BOOL", DbType.Boolean, false),
new SQLiteDbTypeMapping("BOOLEAN", DbType.Boolean, false),
...
new SQLiteDbTypeMapping("GUID", DbType.Guid, false),
new SQLiteDbTypeMapping("IMAGE", DbType.Binary, false)
... (many more)
The XML comment was retained because it is illuminating and authoritative:
Builds and returns a map containing the database column types recognized by this provider. (emphasis mine).
The DbType is crucial to the process.
Reading Data
The above SQLiteDbTypeMap associates those many, many column names it recognizes to a DbType which is used to determine the NET data type to return. The list is comprehensive enough that it can convert all but 1 or 2 types for you.
For example, note that GUID and IMAG* are both stored as BLOB, but the GUID type name is associated with a different DbType which allows that BLOB to be returned differently than an IMAGE BLOB.
You can also specify types via the connection object. Space and scope does not permit an explanation, but while a bit tedious, it allows you to provide the data type for custom type names.
Storing Data
When storing data, you need not fret about how it should be stored. The DB Provider will use the DbType passed to look up SQLite type to use (Affinity"). If you use AddWithValue or the (obsolete) Add(object, object) overload, the DBProvider will guess at the type. It's pretty good at guessing, but dont do that.
So, this conversion is not needed:
cmd.Parameters.Add("@g", DbType.Binary).Value = myGuid.ToByteArray();
Use the same sort of code you would/should with any other database:
' // add trailing semicolons for c#
cmd.Parameters.Add("@n", DbType.String).Value = "Ziggy"
cmd.Parameters.Add("@dt", DbType.DateTime).Value = DateTime.Now
cmd.Parameters.Add("@c3", DbType.StringFixedLength, 3).Value = "XYZ123" '// see notes
cmd.Parameters.Add("@u", DbType.UInt16).Value = 3
cmd.Parameters.Add("@g", DbType.Guid).Value = myGuid
cmd.Parameters.Add("@p", DbType.Decimal).Value = 3.14D
'// 'ToByteArray()' is an extension method to convert
cmd.Parameters.Add("@img", DbType.Binary).Value = myImg.ToByteArray()
cmd.Parameters.Add("@act", DbType.Boolean).Value = True
Notes:
- Use the
DbTypewhich describes the data passed, not how you think it should be saved ( e.g.DbType.Guid, notBinaryfor aGuid). The provider will perform most conversions. - There is no
DbType.Imageso a byte array conversion is needed. - Specifying a size for a
Char()/VarChar()field does not limit the number of characters saved. This seems like a bug because saving more characters than defined can prevent the row from loading. - A
UInt16works in reverse: trying to pass an out of range value, such as -5 for a UInt16, will result in anOverflow Exception. But it will return65531for such a value already stored. - Size/precision parameters such as
Decimal(9,2)for a column doesn't seem to matter. An internal table provides fixed precision and sizes. - For dates, pass dates and indicate
DbType.DateTime. There is no need to pass strings of a particular format ever. The provider Knows Things. (See DateTime Options below.) - To save the Date only, pass only the date:
.Value = DateTime.Now.Date.
Two Different look-up tables are used for saving versus reading data, the one thing they have in common is the DbType which is why it is important. Using the correct one assures that data can make the round trip. Avoid using AddWithValue.
Demo / Results
Data view from a UI Browser
Nothing special is required to load data:
// Dim SQL = "SELECT * FROM LiteColTypes" ' for VB
string SQL = "SELECT * FROM LiteColTypes";
...
dbCon.Open();
Dim dt As New DataTable();
dt.Load(cmd.ExecuteReader());
dgv.DataSource = dt;
Same data in a DataGridView
A DGV correctly identifies and displays the GUID, Image and Boolean columns. The data types of each DataColumn are as expected:
Name ---> System.String (maxLen = 2147483647) ItemDate ---> System.DateTime Char3 ---> System.String (maxLen = 3) UINT16 ---> System.UInt16 VarChar5 ---> System.String (maxLen = 5) GCode ---> System.Guid Price ---> System.Decimal ItemImg ---> System.Byte[] Active ---> System.Boolean NotActive ---> System.Boolean
Note that Guid and Image items were both stored as BLOB but are returned differently. Active (BOOL) and NotActive (YESNO) used different type names but return the same data type. Everything works as desired.
DateTime "Issues" and Options
TIME as a column type name doesn't quite work as expected. It does not parse DateTime.Now.TimeofDay (Timespan) to it. The table maps TIME to DbType.DateTime.
Do not use DbType.DateTime2 or .DateTimeOffset. These are missing in converter look-ups so data is stored as Text in an invalid format (version 1.0.103.0).
UTC, Kind and Flags
The SQLite NET Provider does not support just one date format. When saving as UTC, the data includes an indicator. But, whether saved as Local or UTC, the Kind always returns as Unspecified. Part of the remedy for this is to add datetimekind to your connection string:
`...;datetimekind=Utc;`
`...;datetimekind=Local;`
This will set the Kind for all DateTime values returned but without converting the value.
The remedy for this is to use the (relatively) new BindDateTimeWithKind connection flag. This will convert dates to match the DateTimeKind of the connection when saved:
Private LiteConnStr = "Data Source='C:Tempdemo.db';Version=3;DateTimeKind=Utc;"
...
Dim dt As New DateTime(2011, 2, 11, 11, 22, 33, 444, DateTimeKind.Local)
Using dbCon = New SQLiteConnection(LiteConnStr)
dbCon.Flags = SQLiteConnectionFlags.Default Or
SQLiteConnectionFlags.BindDateTimeWithKind
...
cmd.Parameters.Add("@dt", DbType.DateTime).Value = dt
' == 2011-02-11 17:22:33.444Z note the hour
Though a local date was passed, BindDateTimeWithKind results in it being saved as UTC to match the connection. A UTC date is returned due to the "DateTimeKind=Utc;" connection setting.
Note that DateTimeKind works on dates read while BindDateTimeWithKind acts when saving dates. Individually they can seem to make things worse; together the entire database becomes UTC based (or Local) with dates uniformly saved and read as the same Kind -- you need not do anything.
ConnectionFlags can be tedious to work with manually, to specify them in the connection string:
connx = "...;datetimekind=Utc;flags='Default, BindDateTimeWithKind';"
Limitation / Issue
The uniform Kind treatment works well with a DbDataReader and at least with Dapper. But when using a DataTable, the Kind for dates remains Unspecified. This is apparently due to the DateTimeMode property in DataColumn and probably a design decision by Microsoft not to assume all dates in a column will always be the same Kind. This manifests in other DBs as well.
When using a UTC or Local connection, the provider leaves Unspecified alone (this applies to dates in queries as well). So there should not be any undesired extra conversions: a UTC date read and 'disguised' as Unspecified in a DataTable isn't converted again in updates.
Ticks Format
Contrary to 'conventional wisdom' dates are not always and only saved as TEXT; to save a little space, you can save the tick value. Since these cannot have a Time Zone indicator, the Kind related options can be very useful. To enable Ticks, use the DateTimeFormat connection string option:
Private LiteConnStr = "...;datetimekind=Utc;DateTimeFormat=Ticks;..."
'e.g: 634939900800000000
Other DateTimeFormat options include CurrentCulture, ISO8601 (the default), JulianDay and UnixEpoch. There is no need to change the column type name to use one of these formats. It is still a date, the SQLite Provider handles the implementation details based on the connection flags.
UI Browsers
Many SQLite UI Browsers seem to only know about the four canonical types. Perhaps this is intentional, but this limits their usefulness for NET developers and hides the capabilities of the NET provider.
SQLiteStudio (version: 3.1.0) offers a few more, but it doesn't seem to be aware of the complete list, because some very useful ones are missing (e.g. GUID, IMAGE, SINGLE, integer variants).
It does allow you to enter any type name you want, so Profit!
Summary (tl;dr)
- The NET Provider adds functionality to SQLite by supporting a wide variety of type names for columns,
- Each supported name is associated with a
DBTypewhich determines the actual return data type - Using the correct
DbTypewhen saving data assures the data makes the round trip - The NET Provider will perform most conversions for you
- The
DateTimeKindandBindDateTimeWithKindoptions allow for automatic, uniform TimeZone storage of dates
Above all, the NET provider makes the actual storage an implementation detail.
这篇关于SQLite 没有正确存储小数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
