writing an sql script

Press Ctrl / CMD + C to copy this to your clipboard.

Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at

Chuck 71 posts 69 karma points

Oct 27, 2011 @ 23:01
0

Writing an SQL Script

Extending Umbraco
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?
Copy Link
Anthony pj 40 posts 63 karma points

Oct 28, 2011 @ 10:57

0

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

Copy Link
Rich Green 2246 posts 4008 karma points

Oct 28, 2011 @ 11:40

0

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

Copy Link
andrew shearer 506 posts 653 karma points

Nov 04, 2011 @ 05:38

0

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..

Copy Link

Chuck 71 posts 69 karma points

Nov 04, 2011 @ 15:03

Here is the exact copy of my xslt code.

<xsl:for-each select="$currentPage/* [@isDoc and string(umbracoNaviHide) != '1' and name()= 'Product']">
<xsl:sort select="@sortOrder" order="ascending" data-type="number" />

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

<xsl:variable name="sqlresult" select="jesper.sql:SQLXml($sqlexp)"/>

  <xsl:for-each select="$sqlresult//.">

   <xsl:if test="@sku != ''">
        <xsl:call-template name="buildProduct">
          <xsl:with-param name="sku" select="@sku"/>
          <xsl:with-param name="thumbnail" select="@cThumbnail"/>
          <xsl:with-param name="masterPage" select="@master_page"/>
          <xsl:with-param name="description" select="@descriptionLong"/>
          <xsl:with-param name="details" select="@details"/>
          <xsl:with-param name="productID" select="@idProduct"/>
        </xsl:call-template>
  </xsl:if>
    
</xsl:for-each>  
  
  
</xsl:for-each>

Copy Link

is working on a reply...

Please Sign in or register to post replies

Flag this post as spam?