Call a custom query through a transformation ?

Francis Poirier asked on April 11, 2017 21:44

Hello,

Is it possible to call a Custom Query defined in a custom table entity inside a transformation ?

I have a BizForm and I need to fetch data based on the user input, process that data and display a result using a transformation viewer.

Thanks for your help!

Recent Answers


Rui Wang answered on April 11, 2017 22:27

Instead use custom query in the transformation, why don't you use a repeater with custom query web part, which you can get data first with the query and maybe the where condition based on the form then apply transformation? (be sure to sanitize the user input before pass it to SQL call)

0 votesVote for this answer Mark as a Correct answer

Francis Poirier answered on April 11, 2017 22:29

That make sens but how do I access data inside the transformation ? I don't want to list the data, only for processing.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on April 11, 2017 22:34 (last edited on April 11, 2017 22:37)

Sure, assuming you are using ASCX transformation and doing it over custom table where one of columns contains a query that you need to run. You can do in your transformation smth like this:

<script runat="server">
  string QueryName = "";

  protected override void OnInit(EventArgs e) 
  {   
    // Your Regular C# code
    QueryName = Eval("ColumnWithQueryName"); 
    var query = new CMS.DataEngine.DataQuery(QueryName);
    DataSet ds = query.Result; // your result set - the query will be run on each iteration
    .....       
  }
</script>
<%#QueryName%>

But from what I understand you need do it more likely you need an AJAX call to a web service based on a user input. Because repeater/transformation run on the server, when a user filling out a form the html (i.e. the output of your repeater with transformation) is already rendered to the client side.

0 votesVote for this answer Mark as a Correct answer

Rui Wang answered on April 11, 2017 22:35

Is the data you are trying to display from the custom table part of the form or post back?

For example, a example I can think of is: There is a drop down, based on what's been selected in that drop down, I need to use custom query to select a list of values from a custom table and generate a second drop or check box options.

Is that similar to what you are trying to do or you are looking for something like. Based on user's choice from a drop down, on the post back, display a list of data from a custom table.

0 votesVote for this answer Mark as a Correct answer

Francis Poirier answered on April 11, 2017 22:42

Definitely on the post back. I will try Peter's solution but I am not quite sure what is a ColumnWithQueryName. I thought a query was link to a custom table not one of its columns...

0 votesVote for this answer Mark as a Correct answer

Rui Wang answered on April 11, 2017 22:45

If it's one post back, how do you plan to get the "value" for what the visitor selected in the form?

0 votesVote for this answer Mark as a Correct answer

Francis Poirier answered on April 11, 2017 22:48

I would pass it through the query string of the redirect url.

0 votesVote for this answer Mark as a Correct answer

Rui Wang answered on April 11, 2017 22:53 (last edited on April 11, 2017 22:55)

In that case, use a Repeater with custom query web part on the page. And select your custom query like this one:

SELECT Fields FROM YYYYY WHERE ##WHERE##

Then in the property of the repeater WHERE add: FieldName = '{%QueryString.FieldName|(handlesqlinjection)true%}'

This repeater will get the data you need. Then you only need to transform the data as normal repeater. The above query also takes care of SQL injection problem. (https://devnet.kentico.com/articles/security-avoiding-sql-injection-(sqli))

0 votesVote for this answer Mark as a Correct answer

Francis Poirier answered on April 11, 2017 22:55

Thank you !

0 votesVote for this answer Mark as a Correct answer

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