Why would you roll your own database adapter instead of using third party ORM tools? Today I’m going to share an example.
Sometimes, because of the way data is stored in a database, it is easier to work with the data using SQL techniques, or maybe more efficient, than trying to do the same work in another layer. This is even more important when there are a limited number of situations needing the particular work being accomplished.
Recently I was returning two sets of the same data to be included in a JSON object for a web site. The JSON consisted of two sets of data, basically key value pairs. The first set was in one language, and the second set may be in the same language, or translated into something completely different. Both sets needed to be in the same order, and have the same key (Property Name).
That’s not really all that hard to do, if you have a static class representing all the properties you need, or if the JSON can be an array of key value pairs. In my situation, they want a set of properties for a class, and they want to add new properties (ie. Key value pairs) without having to change the code. In our case, the data is stored in an SQL table as a key value pair. Now, how do we dynamically build the JSON from a dynamic set that includes two different languages. Maybe a little JSON might help explain the problem. Here is a highly truncated JSON object with two sets, both in English. Set2 could be something completely different.
{
"set1": {
"lang": "English",
"prop1": "System",
"prop3": "ID"
},
"set2": {
"lang": "English",
"prop1": "System",
"prop3": "ID"
}
}
The easiest way to create this kind of JSON is to use a class that has properties for lang, prop1 and prop3. Once an instance of the class is created you could use a JSON serializer to create JSON right from the class. However, in Dot Net it isn’t as straight forward to have a dynamic class, which is what I would need when the end users need to add prop4 to the set.
Because I am under as short deadline, one very easy way to generate this object is to return the data from my database, already formatted the way I need it in JSON. I can get “lang” from the key column in my database record, and “English” from the value column in the same database record. Now all I need to do is format it with the JSON. Sure, I could do all this in Dot Net. But I have other complicated things that have to happen to do translations. Also, for each set, I am doing a union or virtual creation of some placeholders that need to appear as a single list of properties for Set1 and Set2.
All of these gymnastics are easy to do in SQL. So, in this case, I chose to use SQL to return to me two data tables, and then build my JSON object from the results directly using a StringBuilder. Problem solved.
I like to put this kind of work on the client whenever possible, allowing things to scale. However, I found that doing the work on the client actually resulted in more work on the database server in order to get the information the way it needed to make things work correctly.
So, for me a rule of thumb is that if work can be done on the client without creating more load on the database server, then do the work on the client. Otherwise, consider doing the work in the database.
Cheers,
Ben