I know what you are thinking… ‘attributes?? who needs them?!’.

I have to be honest here… it took me a while to warm up to attributes too. When they were first introduced in 2009, I was like… ‘meh, I can get that done without attributes…’. That feeling remained with me for the next 4 years until that one fateful day… *music taking you to the past* 😉

On that day, I really needed a breakthrough. I was one of the early adopters of microservices development using Delphi and I wanted to get out of Datasnap. Don’t get me wrong. Datasnap is good at what it does. I just needed something more light weight. So, I was toying with several frameworks such as core web broker, mORMot, Delphi MVC (MARS and RAD Server were not invented yet). Finally, decided to go Delphi MVC route.

As soon as I started writing REST APIs in DMVC, I realised very quickly that I was repeating a lot of code over and over again because most tables required the same CRUD facilities having more or less the same SQL barring the field names and table names.

As any self respecting lazy programmer would have thought, I thought… ‘Why am I writing this over and over again?! Borrrrring!’. So I decided to come up with a plan to automate SQL generation… but the question was… how?? If only there was some way to neatly tie up the Delphi class fields to the database fields, I could probably write a method to do what I needed. Hmmm… what could have helped me?

And then it hit me — ATTRIBUTES! Or as I like to call them… ATTRI-BEAUTES 😉 !!

Attributes gave me the perfect way to tie database information with the class fields. Here’s how…

The plan was to link each field of the object with its corresponding field name in the table at design time. Attributes gave me the perfect way of doing it. So I went ahead and created a custom attribute class as follows

The FieldName property will store the actual name of the field in the database table while the FieldType will store the kind of field it is. The FieldType property will come in handy when we are creating the SQL, you will see.

Now, that I had the above class created, I moved on to defining the table through a Delphi class. This is a sample of the business object class would take the attributes:

The above class is declared as replica of the table is in the database. We ‘decorated’ the properties with attributes that tie them up with the table field names and we also put in the data type of the field type. As mentioned earlier, you will see that this will come in handy when we are generating the SQL.

Next, we can write a method that accepts an instance of the above class and generates the Insert SQL string. But how?

RTTI to the rescue!

Thanks to RTTI, we can access ‘decorated’ custom attributes for instance of the class. That information gives us everything we need to create the SQL string. Here’s a crude but very functional method for doing just that.

The above code packs a punch, right? But if you understand it, it’s really simple. The above method takes in 2 params. One the instance of the object and the other the table name to which this instance maps.

I will not go into the nitty-gritty of how RTTIContext works as that’s out of the scope of this blog. However, assuming that you know how it works, I will explain the key statements (the ones that use our custom attributes) to drive the point home.

RTTIContext gives us access to attributes of a class once the type is set. We did that with ctx.GetType(oInstance.ClassType) statement. Using this we can scan all the attributes of the class. We do that using p.GetAttributes method. Now, because we are only interested in our custom attribute (TDatabaseFieldAttribute), we only want to do something if we see that attribute attached to any property that can use to get the value from.

Enumerating through all attributes (for a property of the class), we just want to use the one that matches and once we get it, we can extract the information we had set at design time by casting the attribute to our custom attribute to get the attribute property value using TDatabaseFieldAttribute(a).FieldName to get the field name mapped to the property. Next we use the TDatabaseFieldAttribute(a).FieldType attribute property to see the type of field it is. This helps us how to format the string of the SQL (see, I told you it will come in handy!) for the insert statement. Why? Because different databases may require different kinds of string formatting. The above code is a rather simple example where string field types are quoted, date field types are formatted in yyyy-mm-dd hh:mm:ss and then quoted while integer field types are simply put (without any quotes) into the Insert SQL string.

Finally, we simply concatenate the variables into the SQL string that we want to generate and voila! we have a dynamically generated SQL based on the kind of class we pass to it.

You can take this idea further and create your own Select, Delete, Update SQLs with Where conditions.

Note: You can also add the table name as an attribute to the entire class, in case you don’t want to pass it as a param value to the SQL generator method. Just use RTTIContext to read the attribute of the class itself. 🙂

This is just one way to use attributes. Clearly, there are tonnes of other use cases out there.

Hope you found this article helpful. Cheers!