database query within transformation in portal page site

Ryan Enos asked on January 27, 2023 21:34

Hi, we have a need to perform a database query within a transformation. The query is being run directly against the database using CMS.DataEngine.ConnectionHelper.ExecuteQuery(). The fields we are trying to return contain html tags but the results we're getting have all the tags removed - we are only receiving plain text. Is this default behavior for the ExecuteQuery method and if so is there a way to return the entire contents of the fields unchanged. I understand the aspects of sql injection and such but in this case this is not an issue. If we use a repeater bound to a SQL data source webpart with the same query we can get the data complete with the tags so I know that it is possible. Thanks for any advice.

Recent Answers


Marcel Vriend answered on January 28, 2023 15:56

Have you considered writing a custom transformation? That way you are in full control and can let the Kentico API do the data retrieval.

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on January 31, 2023 08:55

Hi Ryan,

Are you using text or ASCX transformation? Could you paste a code snippet that queries the database and processes the result? From what I can see ExecuteQuery should return you a raw result. Maybe it's something else clearing it?

Have you tried running the same code in a custom web part or something? Does it give you the same result?

0 votesVote for this answer Mark as a Correct answer

Ryan Enos answered on January 31, 2023 15:15 (last edited on January 31, 2023 21:49)

First, Marcel, thank you for responding. I have not had a chance to try the custom transformation yet as described in the article.

Second, Dmitry, here is a code snippet showing the approach I'm using to try to retrieve the DB data. This is an .ascx transformation. I've simplified some of the variable names for readability. I cannot find any documentation which states that this should strip html tags for safety or otherwise. Thanks for your input and advice.

var qdparams = new CMS.DataEngine.QueryDataParameters();

qdparams.Add("@pkgid",packageID);

qdparams.Add("@pkgname",packageName);

qdparams.Add("@packagedate",packageDate);

string querytext="select * from wsc_PackageDetails where package_id=@pkgid and PackageName=@pkgname and start_datetime>@packagedate and not (DATEDIFF(D,package_date_start,start_datetime)=0 and status='WTL')";

System.Data.DataSet ds = CMS.DataEngine.ConnectionHelper.ExecuteQuery(querytext,qdparams,CMS.DataEngine.QueryTypeEnum.SQLQuery);

System.Data.DataTable dt = ds.Tables[0];

foreach (System.Data.DataRow row in dt.Rows){ string fieldData = row["fieldName"].ToString(); }

0 votesVote for this answer Mark as a Correct answer

Marcel Vriend answered on February 7, 2023 16:34 (last edited on February 7, 2023 16:34)

Sorry, I'm not familiar with ASCX transformations. But your code snippet does not remove HTML tags.

Are the HTML tags really removed from your output or are they HTML encoded, e.g. <p> becomes &lt;p&gt;? The latter could be caused by the Eval boolean parameter or the CMSHTMLEncodeEval web.config key, see Reference - Transformation methods Data loading.

1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.