Today, I’d like to present another real-life example for aspect-oriented programming and post-build reflection. The problem to be solved in a nutshell could be described as follows: How to store date / time values including timezone offset efficiently in an SQLite database?
Let’s look into the architecture in detail:
In a current project, I’m using a local SQLite database from within a .NET application (UWP Windows Store App). Fortunately, there exists the SQLite.Net-PCL library which makes basic access to the database from .NET code possible without manually writing SQL statements, assisted by SQLite-Net Extensions which builds upon the former and acts as (although very simple) OR mapper to the SQLite database.
The core problem lies within the limited range of data types supported by SQLite: If we omit null values, SQLite only understands text, numbers (integers and decimal numbers), and blob data. What about DateTime
values, as commonly known from .NET? Well, by default SQLite.Net-PCL converts DateTime
objects to .NET ticks (a tick corresponding to 100 nanoseconds) and stores this timestamp as integer value. If desired, SQLite.Net-PCL can be forced to store DateTimes as string (following ISO8601 format “YYYY-MM-DD HH:MM:SS.SSS”) by setting the storeDateTimeAsTicks
parameter to false while creating the SQLiteConnection
. (According to SQLite documentation, internal database functions can also handle date / time values as decimal numbers, but since the .NET helper library doesn’t utilize this functionality and it won’t help us in storing timezone offsets, we ignore it for the moment.)
The requirements:
Whenever the user may enter date / time values into the app, they are also asked for timezone information. The reason for this is that the full date / time information is then sent to a backend service to be reviewed by back-office personnel, and these users must see the timezone offset in addition to each date / time value. This means, we need to store timezone information in addition to the date and time values in the database.
The obvious solution is to use .NET DateTimeOffset
objects within data model classes (which will be serialized to SQLite tables), as this simplifies date / time handling within the app since DateTimeOffset
objects already contain the necessary timezone offset. Unfortunately, when writing DateTimeOffset
objects to the database, SQLite.Net-PCL automatically converts them to UTC and stores them as ticks (or as ISO8601 string, as discussed above). This means, although the offset information is available throughout the app, it is lost as soon as a dataset it serialized to the database.
Potential solutions:
After some hours of brainstorming, I came up with the following approaches:
- Manually convert
DateTimeOffset
tostring
properties and store these, or - extract the DateTime and offset (as
TimeSpan
date type) parts of theDateTimeOffset
, and store these in two separate columns - Store the
DateTime
andTimeSpan
(offset) parts of theDateTimeOffset
in two columns of a separate table, and maintain references to this separate table - Use SQLiteNetExtensions’
TextBlob
attribute to serialize theDateTimeOffset
to a single text column - Use
string
properties on data models (which will be converted to SQLite tables), and convert them toDateTimeOffset
objects whenever they are used (e.g., in ViewModels) through AutoMapper or a similar library
Unfortunately, none of these is feasible for most real-life applications:
- For #1 and #2, we’d need to add additional properties to the data model, flag the original
DateTimeOffset
properties with the SQLite.NET[Ignore]
attribute, and handle manual conversion (in both directions) – the effort for these approaches rises with the number of data model classes / SQLite tables. - For #3, we’d need to define a custom data type (in order to specify how the
DateTime
andTimeSpan
parts should be stored), and cannot use the default .NETDateTimeOffset
type - For #4, we’d need to make sure that only SQLiteNetExtensions’ extension methods are used for DB insert / update, and we’d still need an additional
string
property on all the data model classes. - #5 looks most promising, but (as well as #1 and #4, by the way) it still stores values as string, which means we cannot perform mathematical operations (e.g. comparisons in order to sort data sets) on DateTime columns…
What are we gonna do?
Alright, let’s talk about approach #6! First, we’re going to define a .NET attribute that can be used to flag those DateTimeOffset
objects in data model classes that should be stored including timezone offset, instead of converting them to UTC timestamps. In addition, we’ll need a separate process (in its simplest form, this is a .NET console application) that scans a given .NET assembly’s code, looks for properties flagged with the above-mentioned attribute, and adds some magic to the respective SQLite table class… For details, read on in part #2 of this tutorial!