SQLite Data Types Mapping

• Jun 21, 2022
• 2 minutes to read

XPO creates these column types when updating the SQLite database schema and expects them in existing databases.

.NET Type	Database Column Type
Boolean	bit
Byte	tinyint
SByte	numeric(3,0)
Char	nchar(1)
Decimal	money
Double	double
Single	float
Int16	smallint
UInt16	numeric(5,0)
Int32	int
UInt32	numeric(10,0)
Int64	numeric(20,0)
UInt64	numeric(20,0)
Guid	char(36)
String	nvarchar
DateTime	datetime
TimeSpan	double
Byte[]	image
Unlimited size string	text

Type Conversion for Int64 and UInt64 Values

XPO converts System.Int64 and System.UInt64 values to decimal type values for SQLite. This can lead to errors. Refer the following topic for more information: Datatypes In SQLite.

To override this behavior, create an SQLiteConnectionProvider class descendant. In this descendant, override the ConvertToDbParameter method as follows:

C#

using DevExpress.Xpo.DB;
using System.Data;

namespace MySqliteExtensions {
    public class CustomSQLiteConnectionProvider : SQLiteConnectionProvider {
        public CustomSQLiteConnectionProvider(IDbConnection connection, AutoCreateOption autoCreateOption) : base(connection, autoCreateOption) {
        }
        protected override object ConvertToDbParameter(object clientValue, TypeCode clientValueTypeCode) {
            switch (clientValueTypeCode) {
                case TypeCode.UInt64:
                    return (UInt64)clientValue;
                case TypeCode.Int64:
                    return (Int64)clientValue;
            }
            return base.ConvertToDbParameter(clientValue, clientValueTypeCode);
        }
    }
}

VB.NET

Imports DevExpress.Xpo.DB
Imports System.Data

Namespace MySqliteExtensions
    Public Class CustomSQLiteConnectionProvider
        Inherits SQLiteConnectionProvider
        Public Sub New(ByVal connection As IDbConnection, ByVal autoCreateOption As AutoCreateOption)
            MyBase.New(connection, autoCreateOption)
        End Sub
        Protected Overrides Function ConvertToDbParameter(ByVal clientValue As Object, ByVal clientValueTypeCode As TypeCode) As Object
            Select Case clientValueTypeCode
                Case TypeCode.UInt64
                    Return CType(clientValue, ULong)
                Case TypeCode.Int64
                    Return CType(clientValue, Long)
            End Select
            Return MyBase.ConvertToDbParameter(clientValue, clientValueTypeCode)
        End Function
    End Class
End Namespace

Use the newly created SQLiteConnectionProvider class descendant as a custom XPO connection provider.

Mapping Properties To Database Column Types Not Supported By Default

If a column type is not listed in the above table but can be converted to any of the listed type by the database, it can be read and modified by XPO. To create a column of a specific type in the database when updating the schema, decorate the property with DbTypeAttribute. If a column type cannot be converted to a supported type, it is possible to support such columns by creating a custom connection provider.