There comes a time when you create document types and you need to insert some SQL to populate a field. I have a Kentico Best Practice tip for you!
Add SQL to a document type field
As we develop in Kentico, we are always dealing with Kentico Document Types. Whether they are Custom Document Types or the default ones provided by Kentico.
There will be times while working with them, when you need to add some SQL query to populate a field. This field would be a Kentico Form Control, such as a drop down list, check boxes, radio buttons etc...
A good example of such data to populate a drop down is from a Kentico Custom Table. So in simple cases it would look something like this.
It's ok, but we can do better
So what you can see is ok for a one off, but as soon as sites get larger, you could be doing this on other fields. You could even use the same SQL query on another field on another document type. Now this should ring alarm bells and make you think what if we add the SQL query in both places and have to make a change? This can get messy.
How do we get round this?
What I would recommend, is to decide where this query is from. If it is going to be populated from a custom table or a different doc type, then what you should do is add a query to that object and then use a macro to call that query.
So for example, add the same query to the custom table.
This would mean the query is held in one single place and you know where to access them if a change needs to take place. The other plus side to this is that the query is kept in a standardised place for any team member to know where to look if a change is required.
Awesome, but how do we use it?
What you now need is a Macro provided by Kentico. This macro would get the SQL and render it into where it is called. Here is an example of this.
The Macro parameter is the 'CustomTableCodeNamePrefix'.'QueryName'. I would strongly recommend this approach when dealing with queries that are used in the form controls.
So there you have it, that is my Kentico Best Practice tip for you. It enables you to have a common area to manage all your queries. It can be worked on by multiple users, as they will know this is the place to look to amend the query. And the best part of this, is that you can call that query in different document types or custom tables and use it!