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

    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.

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

  • Anthony pj 40 posts 63 karma points
    Oct 28, 2011 @ 10:57
    Anthony pj
    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

  • Rich Green 2246 posts 4008 karma points
    Oct 28, 2011 @ 11:40
    Rich Green
    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

  • andrew shearer 510 posts 659 karma points
    Nov 04, 2011 @ 05:38
    andrew shearer
    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..

  • Chuck 71 posts 69 karma points
    Nov 04, 2011 @ 15:03
    Chuck
    0

    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>
Please Sign in or register to post replies

Write your reply to:

Draft