Display Values From Database


XsltDb provides mdo:sql extension that allows you read and write data from/to database.

The followng sample loads and display list of modules installed into your DotNetNuke instance:


<ul>
  <xsl:for-each select="mdo:sql('SELECT * FROM dnn_DesktopModules', 'mod')//mod">
    <li>{{FriendlyName}}</li>
  </xsl:for-each>
</ul>
As you see "dnn_" prefix is hardcoded in SQL query so the code above is not portable. On another DNN instance it can cause error. We can use {databaseOwner} and {objectQualifier} meta tags to build more portable queries.
Another issue here is that we select all fields (*) but use only one (FriendlyName). So it is better to use exact list of columns. In some cases it can be significant performance issue.

The folowing sample is more accurate:


<!-- SQL query definition. As you see DNN meta tags are used to build right object names.-->
<xsl:variable name="sql">
   SELECT FriendlyName, Description
   FROM {databaseOwner}[{objectQualifier}DesktopModules];
</xsl:variable>

<!-- Execute query and build XML naming each row as "module" -->
<xsl:variable name="modules" select="mdo:sql($sql, 'mod')"/>

<table border="1">
  <tr><td>Friendly Name</td><td>Module Description</td></tr>
  <!-- enumerate modules and build a 2-column table -->
  <xsl:for-each select="$modules//mod">
    <tr>
      <td>{{FriendlyName}}</td>
      <td>{{Description}}</td>
    </tr>
</xsl:for-each>
</table>
You may want to review XML that mdo:sql returns. Do it as follows:


<!-- Full form -->
<xsl:value-of 
  select="mdo:html(mdo:sql('SELECT top 2 * FROM {databaseOwner}[{objectQualifier}DesktopModules]', 'mod'))"
  disable-output-escaping="yes"/>

<!--  Short form {h{...}}, that is equivalent of the full form -->
{h{mdo:html(mdo:sql('SELECT top 2 * FROM {databaseOwner}[{objectQualifier}DesktopModules]', 'mod'))}}


The result will be as follows:


<root> 
  <mod> 
    <DesktopModuleID>10</DesktopModuleID> 
    <FriendlyName>Users And Roles</FriendlyName> 
    <Description>
       Administrators can manage the security roles defined for their portal.
       The module allows you to add new security roles, modify existing security roles,
       delete security roles, and manage the users assigned to security roles.
    </Description> 
    <IsPremium>true</IsPremium> 
    <IsAdmin>false</IsAdmin> 
    <BusinessControllerClass /> 
    <FolderName>Admin/Security</FolderName> 
    <ModuleName>Security</ModuleName> 
    <SupportedFeatures>0</SupportedFeatures> 
    <PackageID>17</PackageID> 
    <LastModifiedByUserID>-1</LastModifiedByUserID> 
    <LastModifiedOnDate>2011-01-07T13:29:47.003+03:00</LastModifiedOnDate> 
  </mod> 
  <mod> 
    <DesktopModuleID>11</DesktopModuleID> 
    <FriendlyName>Tabs</FriendlyName> 
    <Description>
       Administrators can manage the Tabs within the portal. This module allows you to create a new tab,
       modify an existing tab, delete tabs, change the tab order, and change the hierarchical tab level.
    </Description> 
    <IsPremium>false</IsPremium> 
    <IsAdmin>false</IsAdmin> 
    <BusinessControllerClass /> 
    <FolderName>Admin/Tabs</FolderName> 
    <ModuleName>Tabs</ModuleName> 
    <SupportedFeatures>0</SupportedFeatures> 
    <PackageID>21</PackageID> 
  </mod> 
</root>

Last edited Jan 30, 2011 at 2:51 PM by findy, version 2

Comments

No comments yet.