Kentico: Exposing The SQL Generated By DocumentHelper API

Yesterday, I was frantically trying to debug why some documents weren’t getting returned when using the DocumentHelper.GetDocuments() method. Normally when this happens, I need delve deeper to see what SQL Kentico is generating via the API in order to get a little more information on where the querying could be going wrong. To do this, I perform a little “hacky” approach (purely for debugging) whereby I break the SQL within the API by insert a random character within the OrderBy or Where condition parameters.

Voila! The can see the SQL in the error page.

But it was only yesterday where I was shown a much more elegant solution by simply adding a GetFullQueryText() to your GetDocuments(), which then returns the SQL with all the parameters populated for you:

string debugQuery = DocumentHelper.GetDocuments()
                                  .OnSite(SiteContext.CurrentSiteName)
                                  .Types(DocumentTypeHelper.GetClassNames(TreeProvider.ALL_CLASSNAMES))
                                  .Path("/", PathTypeEnum.Children)
                                  .Culture(LocalizationContext.PreferredCultureCode)
                                  .OrderBy("NodeLevel ASC", "NodeOrder ASC")
                                  .GetFullQueryText();

I can’t believe I did not know this after so many years working on Kentico! How embarrassing...