Database access sample

In this sample I will use mdo:xml extension to show you how to access database from XsltDb.
As I said before Xslt provides restricted access to the database. the restriction is: you can not execute free SQL and you can not access any stored procedure. From within XsltDb you can access only stored procedures, that named with mdo_xslt_

Assume current task is Show users of my portal.
So we need a stored procedure mdo_xslt_users which will select users from our database:

create procedure {databaseOwner}[{objectQualifier}mdo_xslt_users] @PortalID int as
begin
   select * from {databaseOwner}[{objectQualifier}UserPortals] up
   join {databaseOwner}[{objectQualifier}Users] u on u.UserID = up.UserID
   where up.PortalID = @PortalID;
end;
  • How to run this script: Here you can see {databaseOwner} and {objectQualifier} DotNetNuke meta tags. You can execute this script "as is" via DotNetNuke Host/SQL menu (check the "Run As Script" option). Or you can manually replace meta tags by values you input during the installation process and use SQL Management Studio in common way.

XsltDb stored procedures must have PortalID as first parameter as mdo:xml automatically pass PortalID to the stored procedure. PortalID is required in 99% of algorithms to isolate data of particular portal.

Now let's see what it returns. We can use mdo:html extension to output xml to the page:

<xsl:variable name="users" select="mdo:xml('users', 'user')" />
<xsl:value-of select="mdo:html($users)" disable-output-escaping="yes" />
We see:

<root> 
  <user> 
    <UserId>2</UserId> 
    <PortalId>0</PortalId> 
    <UserPortalId>1</UserPortalId> 
    <CreatedDate>2009-10-22T14:16:49.073+04:00</CreatedDate> 
    <Authorised>true</Authorised> 
    <UserID1>2</UserID1> 
    <Username>admin</Username> 
    <FirstName>Administrator</FirstName> 
    <LastName>Account</LastName> 
    <IsSuperUser>false</IsSuperUser> 
    <Email>admin@localhost</Email> 
    <DisplayName>Administrator Account</DisplayName> 
    <UpdatePassword>true</UpdatePassword> 
  </user> 
</root>
  • root is the default root element for the XslDb. All XMLs it produces start with "root" element.
  • user - is entity name, provided in second parameter of mdo:xml.

Now we are able to format list of users using XSLT:

<xsl:for-each select="mdo:xml('users', 'user')//user">
   <xsl:value-of select="DisplayName"/>
   <br />
</xsl:for-each>
To simplify use of very frequently used element xsl:value-of XsltDb provides {{xpath}} syntax:

<xsl:for-each select="mdo:xml('users', 'user')//user">
   {{DisplayName}}<br />
</xsl:for-each>
And XsltDb also provides {h{xpath}} to output html code:

{h{mdo:html(mdo:xml('users', 'user'))}}

Next: Simple user input: button clicks, paging.

Last edited Aug 16, 2010 at 1:19 PM by findy, version 6

Comments

JohnPlex Aug 9, 2010 at 7:52 AM 
That makes sense Thanks much for this exciting module!

findy Jun 18, 2010 at 12:20 PM 
I added "How to run this script" section right under SQL code block.
Also have a look at mdo:sql extension. It allows free sql statement but requires a super user account to mark module as "super module".

JohnPlex May 10, 2010 at 2:55 PM 
Very nicely written! what's the easiest way to add a stored proceedure, say mdo_xslt_users, to my DotNetNuke install?