Walkthrough: Creating a Custom Query

Top  Previous  Next

This example demonstrated how to create a new query and include the results from within a page template. The query we are going to define returns the page link and default name of all pages of a specified topic type.

1. Define the SQL

This query requires three parameters:

1.The identity of the occurrence type topic used for marking page links: @PageLinkOccurrenceTypeId.
2.The name of the topic map: @TopicMapName.
3.The subject identifier of the topic type that we want to find the instances of: @TopicTypeSi.

CREATE PROCEDURE dbo.GetPagesOfType

(

  @PageLinkOccurrenceTypeId int,

  @tmId int,

  @TopicTypeSi varchar(255)

)

AS

BEGIN

  SELECT

    dbo.tm_displayName(tm_directInstanceOf.topic_id) as PageName,

    tm_occurResource.occur_resource AS PageLink

  FROM

    tm_occurResource INNER JOIN

    tm_si INNER JOIN

    tm_directInstanceOf ON tm_si.topic_id = tm_directInstanceOf.type_id ON tm_occurResource.topic_id = tm_directInstanceOf.topic_id

  WHERE

    (tm_si.subj_id = @TopicTypeSi) AND

    (tm_occurResource.occur_type = @PageLinkOccurrenceTypeId) AND

    (tm_si.topicmap = @tmId)

END

GO

This query returns the page links and names of all pages which are connected to the page specified. Once this query has been loaded in to SQL server, ensure that the relevant permissions defined on the query to allow TMService to execute it.

2. Add the query to TMService

Having written and tested the query using the SQL server tools. The query can be added to the TMService web.config configuration file as follows:

<query name="EPiDemo_GetPagesOfType" topicMapParam="@tmId">

  <tmrql>

    EXEC GetPagesOfType @PageLinkOccurrenceTypeId,@tmId,@TopicTypeSi

  </tmrql>

  <param name="@PageLinkOccurrenceTypeId" type="oid"/>

  <param name="@TopicTypeSi" type="string"/>

</query>

When TMService executes the query it will convert the table result from the SQL query into XML in the following format:

<TopicMapWebServiceQueryResult xmlns="http://www.networkedplanet.com/2005/01/topicmap/querydata/">

  <tmdata>

    <column1name>row1col1Value</column1name>

    <column2name>row1col2Value</column2name>

       ...

    <columnNname>row1colNValue</columnNname>

  </tmdata>

  <tmdata>

    <column1name>row2col1Value</column1name>

    <column2name>row2col2Value</column2name>

       ...

    <columnNname>row2colNValue</columnNname>

  </tmdata>d

    ...

  <tmdata>

    <column1name>rowNcol1Value</column1name>

    <column2name>rowNcol2Value</column2name>

       ...

    <columnNname>rowNcolNValue</columnNname>

  </tmdata>

</TopicMapWebServiceQueryResult>

For this query the column names are fixed in the query to be PageLink and PageName, so the results supplied by TMService would resemble the following XML:

<TopicMapWebServiceQueryResult xmlns="http://www.networkedplanet.com/2005/01/topicmap/querydata/">

  <tmdata>

    <PageLink>URL to page</PageLink>

    <PageName>Default Page Name</PageName>

  </tmdata>

  <tmdata>

    <PageLink>URL to page</PageLink>

    <PageName>Default Page Name</PageName>

  </tmdata>

...

</TopicMapWebServiceQueryResult>

 

3. Invoke the Query from within the ASPX/ASCX code behind

Once the query has been added to the TMService configuration then it should be executable from any application through the TMService service endpoint. Typically data is loaded and bound within the Load phase of an ASP.NET control, the following code demonstrates how to execute the query and load it in to an ADO.NET DataSet.

TMServiceClient client = EPiTMServiceHelper.GetServiceEndPoint();

XmlNode resultsXml = client.Query(EPiConfig.NP_TM_TopicMapName, "EPiDemo_GetPagesOfType",

    new string[]

    {

      EPiConfig.NP_TM_PageLinkOccurrenceTypeId,

      EPiConfig.NP_TM_TopicMapName,

      "http://psi.networkedplanet.com/types/Person"

    });

DataSet resultsSet = new DataSet("QueryResults");

resultsSet.ReadXml(new StringReader(resultsXml.OuterXml));

 

4. Bind the DataSet to a Repeater control

Once the DataSet has been created it is easy to bind it to a repeater. Declare the following in the page template ASPX file:

<asp:repeater Runat="server" ID="myRepeater">

  <headertemplate>

    <ul>

  </headertemplate>

  <footertemplate>

    </ul>

  </footertemplate>

  <itemtemplate>

    <li><a href='<%# DataBinder.Eval(Container.DataItem, "PageLink") %>'>

      <%# DataBinder.Eval(Container.DataItem, "PageName") %></a>

    </li>

   </itemtemplate>

</asp:repeater>

Finally, in the code-behind for the page you can bind the resultsSet data set to the DataSource member of the repeater control.

this.myRepeater.DataSource = resultsSet;

this.myRepeater.DataBind();

When the page is loaded, the repeater will render a list of all the instances of the specified topic type, in this case, people, to the page as an HTML unordered list.