External Database Table Connection in ASCX Transformation

Fahlgren Mortine asked on June 7, 2016 19:28

I have what is likely a unique (and hopefully temporary) situation in which I need to make a call to an non-Kentico SQL table from an ASCX transformation that loops through a product list. The scenario is this: I need to compare the full path of each product that is output by the transformation (along with other fields) to a list of predefined paths which are specified in this external table in order to get an external product ID. To connect to the database, I need to decode the connection string (via an added class) from the web.config. I'm not sure any of this is possible (let alone reasonable). I can't even get a full product path inside a C# script block (the first step) that would be necessary for the SQL statement, let alone connect to the external table. Thoughts?

Kentico 8.2.48, ASPX-templates

Recent Answers


Pedro Costa answered on June 7, 2016 20:32

That is a performance nightmare, suppose you have 100 products, would you really want to make 100 unique SQL queries to the other database? Not a very good idea, your site will drag, and your stakeholder won't be happy ;)

I would preload this data, either to new fields in existing kentico page types, or even to a custom table that maps as close as possible the external database.

On each application start, you load the external database to your kentico database, you could also use cache dependencies -https://docs.kentico.com/display/K8/Setting+cache+dependencies- to make sure this data is refreshed on cache clearing for example.

Or even, create a scheduled task to update the data on a recurrence, https://docs.kentico.com/display/K8/Scheduling+custom+tasks

Then on the repeater (depending on how you store this data) you can either join with the custom table, or use the field in the custom table.

Best of luck, let me know if you need clarification on any of the points above to get you started.

Cheers, P.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on June 7, 2016 20:39 (last edited on June 7, 2016 20:40)

Not ideal really to make this call but if you must. You should be able to connect to another database (assuming the conn string is in the web.config) and execute a query like so:

CMS.DataEngine.GeneralConnection cn = CMS.DataEngine.ConnectionHelper.GetConnection("yourConnectionStringName");
System.Data.DataSet ds = cn.ExecuteQuery(your, parameters, here);

You should be able to get a path using one of the following:

Eval("NodeAliasPath")

or

GetDocumentUrl()
0 votesVote for this answer Mark as a Correct answer

Fahlgren Mortine answered on June 7, 2016 20:39

I agree, Pedro, it's a terrible solution, but it's a temporary one for no more than about 10 products at a time. The external table will very likely be integrated at a point in the future, so I'm trying not to overspend on my effort to come up with a workable solution in the meantime. Thank you for your thoughts.

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on June 7, 2016 21:03

What if you create your own datasource webpart...that would solve both issues, and performance wouldn't be terrible, assuming your query is optimized. Maybe clone a datasource that exists and use the other connection string as Brenden suggested to connect to. You could also build in caching as well to make sure you aren't calling that query every single request.

0 votesVote for this answer Mark as a Correct answer

David Pearson answered on June 8, 2016 19:58

Would a Product Repository Class that makes a linq to sql data class call to the external database in the App_Code directory be a solution?

0 votesVote for this answer Mark as a Correct answer

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