External Database Table Connection in ASCX Transformation

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.

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

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.

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.

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?