I'm using Jesper to pull some data from a database that isn't the umbraco default database. And so far I have it working however i think it could work better.
Right now I loop through a set of nodes and for each node I hit the DB looking for a product with a specific SKU. However if there are 100 nodes, thats 100 hits to the DB... which I'm pretty sure is bad. I would rather only perform one hit against the DB, but I'm not sure how to do that. I could use some help with writing the SQL.
So this is my code.
<xsl:for-each select="$currentPage/* [@isDoc and string(umbracoNaviHide) != '1' and name()= 'Product']"> <xsl:sort select="@sortOrder" order="ascending"/>
<xsl:variable name="sqlexp"> SELECT [sku] ,[cThumbnail] ,[master_page] ,[descriptionLong] ,[details] ,[idProduct] FROM [FlinnSci_Demo].[dbo].[LIST_ALL_PRODUCTS] WHERE sku = '<xsl:value-of select="@nodeName"/>' </xsl:variable>
As you can see from my code, it loops through and writes a SQL statement for every node. Can anyone help me with a better way to do this?
Writing an SQL Script
I'm using Jesper to pull some data from a database that isn't the umbraco default database. And so far I have it working however i think it could work better.
Right now I loop through a set of nodes and for each node I hit the DB looking for a product with a specific SKU. However if there are 100 nodes, thats 100 hits to the DB... which I'm pretty sure is bad. I would rather only perform one hit against the DB, but I'm not sure how to do that. I could use some help with writing the SQL.
So this is my code.
As you can see from my code, it loops through and writes a SQL statement for every node. Can anyone help me with a better way to do this?
Hi Chuck ,
why dont you use a similar method but instead calling sql statement each time add to sql statement and call it once
ie
SELECT *
FROM suppliers
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');
not sure how this would respond if you had a large number of products to call
Hi,
You might want to consider moving the database access into a .net project and returning XML back to your xslt and then doing the filter there?
Rich
hi Chuck, i think Rich is correct, this doesn't seem to be something that should be done within xslt.
how are you then executing that sql statement? (can you post that code). then an alternative method can be suggested..
Here is the exact copy of my xslt code.
is working on a reply...