database query within transformation in portal page site

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

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?

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(); }

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 <p>? The latter could be caused by the Eval boolean parameter or the CMSHTMLEncodeEval web.config key, see Reference - Transformation methods Data loading.