Teaching SQLite how to handle DateTimeOffset values #4

After settings up the basic structure of our post-build task in the previous blog post, let’s bring it two life! This post focuses on the cure steps of our CIL manipulation: Analyzing object structure, declaring new properties, and changing existing ones.

Reading properties and attributes:

Remember that we already defined a FindFlaggedProperties extension method? In this step, we’ll fill it with business logic, such that it actually scans all properties within a given class, and find those that

  • match a certain data type (DateTimeOffset),
  • are flagged with the custom attribute [DateTimeOffsetSerialize], and
  • are not flagged with the SQLite [Ignore] attribute!

The simplest way of comparing types (for both data types and attributes) is by using full names including namespaces, which keeps the code short and readable:

internal static IEnumerable<PropertyDefinition> FindFlaggedProperties(this TypeDefinition type)
	foreach (var property in type.Properties.Where(p =>
		p.HasCustomAttributes &&
		p.CustomAttributes.Any(a => a.AttributeType.FullName.Equals("SQLite.Net.DateTimeOffset.Attributes.DateTimeOffsetSerializeAttribute")) &&
		!p.CustomAttributes.Any(a => a.AttributeType.FullName.Equals("SQLite.Net.Attributes.IgnoreAttribute"))))
		if (!property.PropertyType.FullName.Equals("System.DateTimeOffset"))
			throw new Exception("The DateTimeOffsetSerialize attribute may only be used on properties of type DateTimeOffset!");

		yield return property;

Note that we’re checking for each property’s data type, and if it doesn’t match simply throw an exception. When we’ll convert our simple console application to an integrated post-build task in a later step, there will be the chance to enhance this error handling strategy.

Manipulating properties and fields:

This isn’t going to be as straightforward. Let’s walk through the contents of the internal static void RebuildProperty(this TypeDefinition type, PropertyDefinition property)) method step by step:

First, a fair amount of setup code is needed. Since the aim of our post-build task is to convert DateTimeOffset values to string, we need to define the format for this conversion:

// Define date / time format
var serializeFormat = "yyyy-MM-dd HH:mm:ss zzzz";

Handling data types in post-build reflection procedures is not as intuitive as one might imagine. In the previously discussed FindFlaggedProperties method, we were able to avoid direct calls to a data type by comparing the name of the DateTimeOffset type. Unfortunately, this time we will need references to the actual string and void data types instead of just their names (the reason for this is that we’re about to generate properties, fields, and getter / setter methods, so we’ll need to declare the data types of the fields and properties and the return types of the methods). The good news is, once you’ve understood that references to those data types are necessary, they are quite easy to load: Since both string and void are basic types provided by the .NET framework core, they are available in every .NET assembly, so we can just scan our main module and load them from there, using the Import method:

// Resolve basic data types
var stringType = type.Module.Import(typeof(string));
var voidType = type.Module.Import(typeof(void));

Setup isn’t over yet – since we’re about to generate properties, fields, methods, and attributes, I think it’s a good idea to determine naming of all these artifacts as soon as possible. We will need unique names for the following artifacts:

  • a duplicate property that will hold the serialized date / time value as string – let’s assign this one the same name as the original DateTimeOffset property with some suffix
  • a backing field for this property – the naming of this field is defined by CIL conventions as <property-name>k_BackingField
  • a getter and a setter method for this property – even these names are determined by convention as get_property-name and set_property-name
  • the newly created property will be flagged with the SQLite [Column] attribute (otherwise the SQL column would appear including the above-mentioned suffix), so we pass the original property’s name to the [Column] attribute (in addition, we need to check whether the original property already has the [Column] attribute set: in this case, this attribute should be transferred to the new property, to ensure that this one will show the column name desired by the user)
// Define member names
var propertyNameOriginal = property.Name;
var propertyNameDuplicate = $"{propertyNameOriginal}_Serialized";
var rnd = new Random();
while (type.Properties.Any(p => p.Name.Equals(propertyNameDuplicate)))
	// Don't accidentally use the name of an existing property
	var suffix = rnd.Next(1000, 9999);
	propertyNameDuplicate = $"{propertyNameOriginal}_Serialized_{suffix}";
var backingFieldName = $"<{propertyNameDuplicate}>k_BackingField";
var getMethodName = $"get_{propertyNameDuplicate}";
var setMethodName = $"set_{propertyNameDuplicate}";

// Ensure that the new property is stored in a column that is called the same as the original property
// (or as specified in the original property's "Column" attribute, if any)
var columnName = property.Name;
if (property.HasCustomAttributes)
	foreach (var attribute in property.CustomAttributes)
		if (attribute.AttributeType.FullName.Equals("SQLite.Net.Attributes.ColumnAttribute") && attribute.HasConstructorArguments)
			columnName = attribute.ConstructorArguments[0].Value as string;

The next step is to actually declare property, field, and methods, as discussed above. Property and fields declarations are easily done within one line, afterwards the field is declared as the property’s backing fields:

// Create new property including backing field
var duplicateProperty = new PropertyDefinition(propertyNameDuplicate, PropertyAttributes.None, stringType);
var backingField = new FieldDefinition(backingFieldName, FieldAttributes.Private, stringType);

Declarating new methods is similary straightforward, however the necessary code is a bit longer since we immediately fill our methods bodies with CIL instructions. All existing CIL instructions are available as static fields via the Mono.Cecil.Cil.OpCodes class, and they can be appended to a method body using an IL processor’s Emit() method – note that some instructions require additional arguments:

// Create getter method for new property
var getMethod = new MethodDefinition(getMethodName, MethodAttributes.Public | MethodAttributes.HideBySig | MethodAttributes.SpecialName, stringType);
getMethod.Body = new MethodBody(getMethod);
var processor = getMethod.Body.GetILProcessor();
processor.Emit(OpCodes.Ldfld, backingField);
duplicateProperty.GetMethod = getMethod;

// Create setter method for new property
var setMethod = new MethodDefinition(setMethodName, MethodAttributes.Public | MethodAttributes.HideBySig | MethodAttributes.SpecialName, voidType);
setMethod.Parameters.Add(new ParameterDefinition("value", ParameterAttributes.None, stringType));
setMethod.Body = new MethodBody(setMethod);
processor = setMethod.Body.GetILProcessor();
processor.Emit(OpCodes.Stfld, backingField);
duplicateProperty.SetMethod = setMethod;

Manipulating the original property’s getter and setter methods is done in a similar way: We remove all their CIL instructions, and emit a new instruction set that converts string to DateTimeOffset (or vice versa), and points to the newly created property’s getter or setter method.

// Adapt existing property's getter method
var parseExactMethod = type.Module.FindDateTimeOffsetParseExactMethod();
if (parseExactMethod == null) return false;
var invariantCultureGetterMethod = type.Module.FindInvariantCultureGetterMethod();
if (invariantCultureGetterMethod == null) return false;

processor = property.GetMethod.Body.GetILProcessor();
processor.Emit(OpCodes.Call, getMethod);
processor.Emit(OpCodes.Ldstr, serializeFormat);
processor.Emit(OpCodes.Call, invariantCultureGetterMethod);
processor.Emit(OpCodes.Call, parseExactMethod);

// Adapt existing property's setter method
var toStringMethod = type.Module.FindDateTimeOffsetToStringMethod();
if (toStringMethod == null) return false;

processor = property.SetMethod.Body.GetILProcessor();
processor.Emit(OpCodes.Ldarga_S, property.SetMethod.Parameters[0]);
processor.Emit(OpCodes.Ldstr, serializeFormat);
processor.Emit(OpCodes.Call, toStringMethod);
processor.Emit(OpCodes.Call, setMethod);

One interesting thing to notice here is that for conversion between string to DateTimeOffset the DateTimeOffset.ParseExact() and DateTimeOffset.ToString() methods are needed. In a similar way to the loading of data types described above, also these methods can not simply be referenced, but need to be imported. You may have noticed the FindDateTimeOffsetParseExactMethod() and FindDateTimeOffsetToStringMethod() references in the code snippet above – these are custom extension method that handle the import. Let me just show one of them to ilustrate the pattern of importing data types, scanning them for a specific method, and returning a reference to this method:

internal static MethodReference FindDateTimeOffsetToStringMethod(this ModuleDefinition module)
	TypeDefinition dateTimeOffsetType;
	dateTimeOffsetType = module.Import(typeof(System.DateTimeOffset)).Resolve();

	var foreignToStringMethod = dateTimeOffsetType.Methods.Single(m =>
		m.Name.Equals("ToString") &&
		m.Parameters.Count == 1 &&
		m.Parameters[0].ParameterType.MetadataType == MetadataType.String);
	var toStringMethod = module.Import(foreignToStringMethod);
	return toStringMethod;

Now, let’s return to our RebuildProperty method, as there are not many things left to do: An important one is to actually register the newly created property (and thus also its backing field and its getter and setter methods) to the SQLite table class that also contains the original DateTimeOffset property:

// Add new property to class

By now it’s time for clean up: First of all, we’ll want to remove the [DateTimeOffsetSerialize] attribute from the original property. This is necessary to ensure that, if the post-build task should somehow be executed twice, no second duplicate property is generated:

// Remove the original "DateTimeOffsetSerialize" attribute (to ensure the property is not rebuilt again on a potential second run)
for (int i = property.CustomAttributes.Count - 1; i >= 0; i--)
	if (property.CustomAttributes[i].AttributeType.FullName.Equals("SQLite.Net.DateTimeOffset.Attributes.DateTimeOffsetSerializeAttribute") ||

As announced before, the newly created property must be flagged with the SQLite [Column("column-name")] attribute:

// Add a "Column" attribute to the new property, to guarantee a human-readable column name in the database
var columnsCtorMethod = type.Module.FindColumnAttributeCtorMethod();
if (columnsCtorMethod == null) return false;
var columnAttribute = new CustomAttribute(columnsCtorMethod);
columnAttribute.ConstructorArguments.Add(new CustomAttributeArgument(type.Module.TypeSystem.String, columnName));

Even this code snippet contains a reference to a custom extension method: FindColumnAttributeCtorMethod() is structured in a similar way as the FindDateTimeOffsetToStringMethod() method introduced above: It loads the SQLite.Net.Attributes.ColumnAttribute type, and returns its constructor method:

internal static MethodReference FindColumnAttributeCtorMethod(this ModuleDefinition module)
	TypeDefinition columnAttributeType;
	columnAttributeType = module.Import(typeof(ColumnAttribute)).Resolve();
	var foreignCtorMethod = columnAttributeType.GetConstructors().Single(m =>
		m.Parameters.Count == 1 &&
		m.Parameters[0].ParameterType.MetadataType == MetadataType.String);
	var ctorMethod = module.Import(foreignCtorMethod);
	return ctorMethod;

Finally, since the new string property shall replace the original DateTimeOffset property in the database, the original one must be flagged with the SQLite [Ignore] property:

// Since the original property shall not be stored in the database, flag it with an "Ignore" attribute
var ignoreCtorMethod = type.Module.FindIgnoreAttributeCtorMethod();
if (ignoreCtorMethod == null) return false;
property.CustomAttributes.Add(new CustomAttribute(ignoreCtorMethod));

Time for testing!

That’s it! Since I did not reproduce all necessary custom extension methods here, you might want to check out the full source code from GitHub. Then, we are ready to run the console program, and check its outcome by loading the sample SQLite assembly back into our CIL decompiler. In addition, you might want to open your SQLite database in some visual editor, and check which columns are available, how they are named, and finally their content.

Even if this should be working fine, there is still room for improvement, which will be covered in the next section of this tutorial.