Teaching SQLite how to handle DateTimeOffset values #2

What do we need to get started with our automatic DateTimeOffset-to-SQLite serialization project? Let’s begin with the simplest part: Obviously, we’ll need a custom attribute to flag those DateTimeOffset properties our solution shall handle.

Create a new Class Library Project, containing one single class. I decided to call it DateTimeOffsetSerializeAttribute – of course, the exact naming is up to you, but remember that (since it is a custom attribute) it should follow common naming conventions and end with ...Attribute. In addition, the class needs to be derived from System.Attribute. Since the custom attribute will be used to flag properties, we should annotate it with the [AttributeUsage(AttributeTargets.Property)] attribute – this ensures that our attribute may only be used on properties, not on classes, fields, methods, etc. To be exact, our attribute will be used to flag properties of type DateTimeOffset – unfortunately, there is no way to further limit the usage of custom attributes, but keep this goal in mind, we’ll come back to it in the second part of our solution!

That’s it! In its simplest form, our custom attribute looks as follow:

[AttributeUsage(AttributeTargets.Property)]
public class DateTimeOffsetSerializeAttribute : Attribute
{
	public DateTimeOffsetSerializeAttribute() { }
}

Before moving on, why not taking advantage of this newly created attribute, and apply it to a sample project? Open any project that uses SQLite.NET and add a reference to the Class Library that contains our custom attribute. Add a property of type DateTimeOffset to any data model class that is used as SQLite table, and flag the newly created property with the [DateTimeOffsetSerialize] attribute!

Of course, the attribute won’t have any effect yet, but it will help us in analyzing the necessary requirements for part 2 of our solution. For this, we will need a .NET decompiler. I prefer using ILSpy, but there are other products that will do as well. Build your SQLite project, load it into the decompiler (in case you’re using ILSpy, make sure decompilation into C# or VB is turned off and pure IL code is shown, by selecting IL from the drop-down in the toolbar), and open the data model that contains the flagged DateTimeOffset property.

At this point, some theoretical background: The .NET compiler does not create machine code directly. Instead, when compiling C# or VB.NET code, it is translated into CIL (Common Intermediate Language) code which is interpreted by a virtual machine at runtime. This has the advantage that compiled .NET assemblies can be read, converted back into C# or VB code, and even manipulated – and that’s exactly what we’ll do: For the moment, let’s focus on just reading and analyzing the CIL code representing the sample DateTimeOffset property. In a later step, we will manipulate CIL code and modify this property.

The CIL code that belongs to the sample DateTimeOffset property (in my case, this property is called TestProperty), consists of four parts:

    • The property declaration (this also contains the reference to the DateTimeOffsetSerializeAttribute the property is flagged with):
      .property instance valuetype [System.Runtime]System.DateTimeOffset TestProperty()
      {
      	.custom instance void [SQLite.Net.DateTimeOffset.Attributes]SQLite.Net.DateTimeOffset.Attributes.DateTimeOffsetSerializeAttribute::.ctor() = (
      		01 00 00 00
      	)
      	.get instance valuetype [System.Runtime]System.DateTimeOffset Workheld.Core.Data.Models.Case::get_TestProperty()
      	.set instance void Workheld.Core.Data.Models.Case::set_TestProperty(valuetype [System.Runtime]System.DateTimeOffset)
      }
      
    • A backing field:
      .field private valuetype [System.Runtime]System.DateTimeOffset '<TestProperty>k__BackingField'
      
    • A getter method:
      .method public hidebysig specialname 
      	instance valuetype [System.Runtime]System.DateTimeOffset get_TestProperty () cil managed 
      {
      	.custom instance void [System.Runtime]System.Runtime.CompilerServices.CompilerGeneratedAttribute::.ctor() = (
      		01 00 00 00
      	)
      	// Method begins at RVA 0x1c34f
      	// Code size 7 (0x7)
      	.maxstack 8
      
      	IL_0000: ldarg.0
      	IL_0001: ldfld valuetype [System.Runtime]System.DateTimeOffset Workheld.Core.Data.Models.Case::'<TestProperty>k__BackingField'
      	IL_0006: ret
      } // end of method Case::get_TestProperty
      
    • A setter method:
      .method public hidebysig specialname 
      	instance void set_TestProperty (
      		valuetype [System.Runtime]System.DateTimeOffset 'value'
      	) cil managed 
      {
      	.custom instance void [System.Runtime]System.Runtime.CompilerServices.CompilerGeneratedAttribute::.ctor() = (
      		01 00 00 00
      	)
      	// Method begins at RVA 0x1c357
      	// Code size 8 (0x8)
      	.maxstack 8
      
      	IL_0000: ldarg.0
      	IL_0001: ldarg.1
      	IL_0002: stfld valuetype [System.Runtime]System.DateTimeOffset Workheld.Core.Data.Models.Case::'<TestProperty>k__BackingField'
      	IL_0007: ret
      } // end of method Case::set_TestProperty
      

Don’t be confused by the complicated syntax – the most important thing is the content of both the getter and setter method, and these are actually quite simple: The getter method loads the value of the backing field and returns it, and the setter method loads the passed parameter, sets it as the the backing field’s content, and returns. (For detailed information about these instructions, consult the complete list of CIL instructions.)

Our plan is to manipulate the assembly such that, for each flagged property, an additional property of type string is declared which holds the DateTimeOffset value in ISO8601 format, and the original DateTimeOffset property’s getter and setter methods are adapted in order to actually read from / write to the string property’s value. Although this might sound easy at first glance, there are several issues to be taken into account – if you are interested in how we’re gonne solve this challenge, stay tuned for part #3 of this tutorial!