APIExample4 shows how to use the TMRQL query language from your own code. TMRQL is simply SQL applied to a defined set of relational views of your topic map data. This makes TMRQL easy to use for anyone already familiar with SQL and also has the advantage of extensive support in tools an in code from the .NET Framework.

The code in APIExample4 performs the following steps:

The code for importing the XTM file is contained in the method ImportXTMFile() - this is similar to the code already seen in APIExample3 for XTM import.

The query for the direct children of William I is done in the method QueryChildren(). In this query, we want to find all topics associated with the topic for William I where the association type is "child-of" and William I plays the role "parent". William I, "child-of" and "parent" are all topics in the topic map an need to be found in the database to perform this query. In this example, this is done as part of th query itself, using the topic subject identifiers and looking up the topic's unique identifiers using SQL sub-selects. The actual query string used is slightly obscured by the use of string constants for the base URIs of the subject identifiers, so is reproduced here in its complete form except for the string {topicmap ID} which is replaced with the ObjectID of the ITopicMap object passed into the QueryChildren() method:

SELECT r2p, dbo.tm_displayName(r2p) FROM tm_assoc2 WHERE
  topicmap = {topicmap ID} AND 
  r1p in (SELECT topic_id from tm_si where subj_id='http://www.example.com/psi/uk-monarchs/william-i') AND 
  association_type in (SELECT topic_id from tm_si where subj_id='http://www.example.com/psi/family/child-of') AND
  r1t in (SELECT topic_id from tm_si where subj_id='http://www.example.com/psi/family/parent')

The principal selection is done using the tm_assoc2 view which presents pairs of topics that are in the same association, giving the association type and the role type played by each topic. Each of the topics William I, "child-of" and "parent" are found using a lookup in the tm_si view which maps a topic's subject identifier to its unique object identifier. In the SELECT part of the query, the tm_displayName function is used to select one name from the topic specified in the function parameter. The query is executed using the ExecuteQuery() method of the ITopicMapSystem interface, not on the ITopicMap interface. Although this query specifically restricts itself to a single topic map, but you can write queries that span multiple topic maps in the same database (although you will then need to be responsible for working out which results came from which topic map!). The exection and procesing of the results set is shown in the code snippet below.

ITMCoreDataReader dr = tm.TopicMapSystem.ExecuteQuery(query);
System.Console.WriteLine("Children of William I:");
try 
{
  while (dr.Read()) 
  {
    int id = dr.GetInt32(0);
    string name = dr.GetString(1);
    System.Console.WriteLine("  {0} - {1}", id, name);
  }
} 
finally 
{
  dr.Close();
}

The ExecuteQuery() method returns an instance of the ITMCoreDataReader interface which extends the standard SQL DataReader implementation with additional methods that can return full TMAPI objects from object identifiers in the results set. As with a normal DataReader, you can call the Read() method to retrieve results row by row until the method returns false (at which point you have reached the end of the results set). You can then use the various GetXXX() methods to retrieve individual items from the row - in this case the unique object identifier is retrieved as a 32-bit integer, and the topic name is retrieved as a string.

The method QueryDescendents() prints a tree-view of all descendants of William I. This list is found by following the "child-of" associations (from "parent" role to "child" role") starting from William I. We call this traversal of a specific type of association in a specific direction (from one role type to another role type) as transitive closure. TMRQL provides two functions for retrieving this transitive closure. The tm_tc function is provided for backwards compatibility, but we recommend you use the tm_tc2 function which returns a table listing each path in the transitive closure as a pair of topics - the "from" topic and the "to" topic. If you think of the transitive closure like a tree, the "from" topic is the parent node and the "to" topic is the child node.

This method also shows a different way of constructing a query that requires topic identifiers. Rather than querying for the identifiers within the query string itself, the topics are first located using the Index APIs to lookup the topics one by one. This can be useful when you write a lot of queries that use the same topics over and over again. The code for the QueryDescendants() method is shown below.

string familyPsiBase = "http://www.example.com/psi/family/";
string monarchyPsiBase = "http://www.example.com/psi/uk-monarchs/";
ITopic childOf = tm.TopicsIndex.GetTopicBySubjectIdentifier(familyPsiBase + "child-of");
ITopic child = tm.TopicsIndex.GetTopicBySubjectIdentifier(familyPsiBase + "child");
ITopic parent = tm.TopicsIndex.GetTopicBySubjectIdentifier(familyPsiBase + "parent");
ITopic william = tm.TopicsIndex.GetTopicBySubjectIdentifier(monarchyPsiBase + "william-i");
string query = String.Format(
  "SELECT from_id, to_id, dbo.tm_displayName(to_id) as child_name from tm_tc2({0},{1},{2},{3})",
  william.ObjectID, childOf.ObjectID, parent.ObjectID, child.ObjectID);
try 
{
  ITMCoreDataReader dr = tm.TopicMapSystem.ExecuteQuery(query);
  System.Data.DataTable tbl = dr.GetDataTable();
  System.Console.WriteLine("William I:");
  ListChildren(tbl, Int32.Parse(william.ObjectID), 2);
} 
catch (Exception ex) 
{
  System.Console.WriteLine("ERROR: Caught error in processing query.");
  System.Console.WriteLine(ex.ToString());
}

The first 6 lines of the code are used to find the topics that are required to execute the tm_tc2 function. This function requires the identifiers of the starting topic for the closure, the type of the association to be traversed, the type of role played by the "from" topic at each link in the path and the type of role played by the "to" topic at each link. Because these topics are located first using the Index API, the query string can be much simpler - requiring only that the ObjectID of each topic be inserted into the correct place in the query. This code example differs from the previous query in another way too. Rather than reading the results set row by row, the code makes use of the GetDataTable() method provided by the ITMCoreDataReader interface. This method reads all the rows from the results set into a local System.Data.DataTable instance and then closes the DataReader. For large results sets, this could potentially require a lot of memory, but it has several advantages. Firstly it means that you do not have to explicitly close the DataReader as this is done by the code that constructs the DataTable. Secondly, the DataTable interface provides a number of methods that allow you to filter and select from the results data without executing further queries against the database. This second feature is used by the method ListChildren() which is a recursive method used to construct the tree view. Each time the ListChildren() method is invoked, it selects from the DataTable all those rows where the "from" topic identifier matches the identifier specified in the input parameter. Then for each row it prints the id and name of the "to" topic (the child) and then calls itself with the child topic identifier as the parameter to print the list of the child's children and so on. This code is shown in the snippet below.

private void ListChildren(System.Data.DataTable descendantsTable, int parentId, int indent)
{
  System.Data.DataRow[] children = descendantsTable.Select("from_id=" + parentId, "child_name");
  foreach(System.Data.DataRow childRow in children)
  {
    int childId = (int)childRow[1];
    string childName = childRow[2] as String;
    System.Console.WriteLine("{0}{1} - {2}", new String(' ', indent), childId, childName);
    ListChildren(descendantsTable, childId, indent + 2);
  }
}