Using the Requirements Authoring Starter Kit (Part 2 of 2)

Microsoft Office 2003 Technical Articles

Microsoft Corporation

May 2006

Applies to:
    Microsoft Office Word 2003
    Microsoft Visual Studio 2005 Tools for the Microsoft Office System
    Microsoft SQL Server 2005
    Microsoft Windows SharePoint Services
    Microsoft Visual Studio 2005 Team System

Summary: See code examples that show how the Requirements Authoring Starter Kit (RASK) can help software development teams collect, interpret, distribute, and synchronize project requirements in a structured Word document using Microsoft Visual Studio 2005 Tools for the Microsoft Office System. (36 printed pages)

Download Office2003RASK2.exe.

Introduction
Code Examples for RASK Features
Establishing Requirement Dependencies
Installing and Setting Up RASK Solutions
Detailed Instructions for Installing and Setting Up RASK Solutions
Troubleshooting RASK
Conclusion
Additional Resources

Introduction

Requirements Authoring Starter Kit (Part 1 of 2) defines the problem that the Requirements Authoring Starter Kit (RASK) solves, and describes the RASK software requirements and architecture. After describing each RASK feature, part 1 discusses how to install, set up, and troubleshoot RASK.

Part 2 introduces the code that enables each RASK feature. You can use this information as a starting place for exploring the code in more detail or for planning extensions to RASK.

The following list shows the contents of the two Solutions that RASK provides:

The RequirementAuthoringStarterKitMaster Solution

RequirementAuthoring
Implements the task pane user interface that appears in the Word documents. This project was specifically created as a Visual Studio 2005 Tools for Office project type. It references several Microsoft Office namespaces. The UI is defined in the RequirementsAuthoringPane namespace. The event handler code is specific to Word and is stored in the Word document template file RequirementsAuthoring.dot.
RequirementAuthoringBL
Implements the RASK business layer, which is code that implements the functionality exposed in the UI. The primary class it defines is the RequirementTemplateManager class, which in turn has methods for each feature. Additionally, OutlookManager implements an interface to Outlook through that application's object model.
RequirementAuthoringSupport
Implements various helper classes for RASK.

The RequirementAuthoringStarterKitSupport Solution

RequirementAuthoringStarterKitDAL
Implements a data access layer to interact with the SQL Server data store using the Web service exposed by SQL Server. RequirementAuthoringStarterKitDAL contains a Requirement class to represent a single requirement and a Project Requirement class to represent the entire set of requirements in a project. Saving to and loading from the database is handled by the RMProject, RMRequirement and RMRequirementDepedency classes, all of which derive from a common RMBase class.
RequirementAuthoringTeamFoundation
Implements a data access layer to interact with Visual Studio Team Foundation Server.
RequirementAuthoringSupport
Implements various helper classes for RASK.

Code Examples for RASK Features

The following code examples introduce each RASK feature described in Part 1 of this article. From these examples, you can explore the details of the code on your own. Creating a New Project

To create a new project in RASK, you must select an established Visual Studio Team Foundation Server project from a list in the task pane. RASK then RASK prepares the document accordingly. In the code example, we examine how the list is populated, and what happens when a users selects an item.

This list is populated by the InitializeProjects method of the RequirementsAuthoringPane class. This instantiates a RequirementsTemplateManager from the RequirementsAuthoringBL project. It also calls its GetTeamFoundationProjects method. This creates an instance of ProjectManager from the RequirementAuthoringTeamFoundation project. It also calls its GetProjects method. GetProjects method performs the work to interface with Visual Studio Team Foundation Server to get the project list.

When the list is populated, the toolStripMenuItemCreateTemplate_Click event handler implements the Actions button in the UI. This calls OnCreateTemplate, which in turn calls the taskPane_OnCreate template, located in RequirementsAuthoring.dot. This calls DisplayProject, which stores the project name, ID, and other information from the task pane in the document. To do so, DisplayProject uses an XML schema that is bound to the Word document. The following code example shows how RASK establishes a reference to the appropriate node, and then populates it from the task pane:

Word.XMLNode nodeProjectName =
   rootNode.ChildNodes.Add(
   SchemaNode.ProjectName.ToString(),
   (string)_namespaceURI,
   ref range);

   nodeProjectName.Range.Text = taskPane.ProjectName;

Adding Requirements

To add requirements, you create appropriate nodes in the document's XML structure that map to new areas in the document. Because the process for adding requirements is similar for all requirement types, we develop one example, the Scenario type, for you to follow.

From the UI, toolStripMenuItemAddScenario handles the event and calls OnAddScenario. This leads to the taskPane_OnAddScenario event handler in RequirementsAuthoring.dot. This calls AddTopLevelRequirement, which creates a new, globally unique identifier (GUID) to act as the requirement ID. AddTopLevelRequirement uses Word bookmarks to locate the end of the requirements section in the document. It then calls the AddRequirement method. Because the AddRequirement method is overloaded into several versions, it can add requirements, such as different types and child levels..

The AddRequirement method first determines what level of requirement you are adding by determining the level of the XML node in the document:

if (rootNode.BaseName ==
    SchemaNode.Project.ToString())
{
    style = "Heading 1";
    detailStyle = "Normal 1";
    typeStyle = "Type 1";
}
else if (rootNode.ParentNode.BaseName ==
    SchemaNode.Project.ToString())
{
    style = "Heading 2";
    detailStyle = "Normal 2";
    typeStyle = "Type 2";
    isTop = false;
}
else if (rootNode.ParentNode.ParentNode.BaseName ==
    SchemaNode.Project.ToString())
{
    style = "Heading 3";
    detailStyle = "Normal 3";
    typeStyle = "Type 3";
    isTop = false;
}
else
{
    MessageBox.Show("Unable to create child requirement. " + 
        "Requirements are limited up to 3 levels only.");
    return;
}

Although this code arbitrarily sets a limit of three levels, you can extend that to any number of levels.

To extend the number of levels

Modify the above code to add blocks for additional levels by adding an additional ParentNode reference in the if condition for each new level.
Change the value of the _requirementLevels constant in the file DocumentConstants.cs under the RequirementsAuthoring\DocumentSupport folder.
Add Heading x, Normal x, and Type x styles to the Word document template, where x is the number indicating the new level.

The following code example then adds XML nodes for the new requirement:

Word.XMLNode nodeRequirement =
rootNode.ChildNodes.Add(
SchemaNode.Requirement.ToString(),
(string)_namespaceURI,
ref range);

Then this example subsequently inserts a table into the document body for the new requirement:

Word.Table tableRequirement =
Tables.Add(
nodeRequirement.Range,
3, 1,
ref _missingValue,
ref _missingValue);

The requirement is a bookmark in Word:

start = nodeRequirement.Range.Start - 1;
end = nodeRequirement.Range.End + 1;
range = Range(ref start, ref end);
Bookmarks.Add(generatedID, ref range);

Finally, the requirement is added to the tree view in the task pane:

taskPane.AddTreeViewItem(
parentRequirementID,
      (requirementName.Trim()!=String.Empty)?requirementName:"<Unspecified>",
requirementID,
typeID,
Convert.ToBoolean(needsReview.ToString().ToLower()), 
unsavedRequirement, 
teamFoundationWorkItemID);

Saving Requirements to SQL Server

The SaveToSQLServer2005 method in RequirementsAuthoringPane implements saving requirements. RASK relies heavily on RMRequirement and RMProject objects from the RequirementAuthoringStarterKitDAL project to perform the save operation.

To save information to the database, RASK gets information from the Word document's XML schema and calls the appropriate objects in the data access layer. Although RASK saves the project level, requirement level, and dependency information in separate operations, the operations are similar. Therefore, we develop one example, the project level, for you to follow.

First, iterate through the XML nodes in the document:

foreach (Word.XMLNode node in Globals.ThisDocument.XMLNodes)
{

Find the one containing project information:

if (node.BaseName == SchemaNode.Project.ToString())
{

Instantiate an RMProject object, and then call its Insert method to save to the database:

try
{
RMProject project = new RMProject();
project.Insert(
_projectID,
_projectName,
Environment.UserName,
ref created,
_teamFoundationProjectID);

The Insert method relies on the native XML Web Services feature of SQL Server to communicate with the database. Most of the code to communicate with the database is automatically generated by Visual Studio, within the RequirementAuthoringStarterKitDAL project when the Web reference is added to the project. For more information about how this feature works, see the documentation for Visual Studio or SQL Server.

The loop continues to identify nodes containing requirements and performs similar tasks to save them.

Synchronizing Requirements with Visual Studio Team Foundation Server

The SyncRequirementstoTeamFoundation method in RequirementsAuthoringPane implements synchronizing requirements. RASK relies on a RequirementTemplateManager object from the RequirementAuthoringBL project to synchronize requirements.

As the SyncRequirementstoTeamFoundation method iterates through the XML nodes in the document looking for requirements, It does so recursively to get to all nodes and child nodes in the tree. The following code performs the synchronization for each requirement:

RequirementTemplateManager bll = new RequirementTemplateManager(
   Config.GetTeamFoundationConnectionString());

if (teamFoundationWorkID > 0)
{
   bll.UpdateTeamFoundationRequirement(teamFoundationWorkID,
      _projectName,
      requirementName,
      String.Empty);

Generating a Consolidated Requirements Document

This feature works exactly like creating a new project: it uses DisplayProject to populate the document with project-level information. In addition, it takes calls DisplayRequirements to fill the document with requirements from the data store. Because the path from the UI to this code is straightforward, we focus on DisplayRequirements, which is in RequirementsAuthoring.dot.

First, it calls on the GetRequirements method of the task pane:

string content = taskPane.GetRequirements(taskPane.ProjectID);

This method uses a RMRequirement object from the RequirementAuthoringStarterKitDAL project to get the requirement from the database:

RMRequirement requirement = new RMRequirement();
retVal = requirement.SelectXMLByProjectID(projectID);

Next, the code interprets the results as an XML stream and iterates on a node-by-node basis:

TextReader tr = new StringReader(content);
if (content != "<Requirements />" &&
    content != String.Empty)
{
   taskPane.IsTreeNodeClick = true;
   XPathDocument xpathDoc = new XPathDocument(tr);
   XPathNavigator xpathNav = xpathDoc.CreateNavigator();
   XPathNodeIterator nodes =
      xpathNav.Select(
      "/*[local-name()='Requirements']/*[local-name()='Requirement']");
   if (nodes != null)
   {
      while (nodes.MoveNext())
      {

Code not shown reads the information from the nodes, and then calls AddRequirement to insert the requirement text into the document:

AddRequirement(
range,
Convert.ToInt32(nodeRequirementTypeID),
new Guid(nodeRequirementID),
requirementName,
requirementDetail,
needsReview,
Guid.Empty,
false,
Convert.ToInt32(nodeTeamFoundationWorkItemID));

For more information on AddRequirement, see Adding Requirements elsewhere in this article.

Reusing Requirement Content

To allow you to reuse requirement content, RASK implements search. Search occurs at the database level, rather than in the text of the document. The Search method of the RequirementsAuthoringPane instantiates a RequirementTemplateManager from the RequirementAuthoringStartKitDAL project. The Search method then calls its SearchRequirement method to perform the search and return the results as a DataSet object, as shown in this example:

RequirementTemplateManager bll = new RequirementTemplateManager(
   Config.GetTeamFoundationConnectionString());
DataSet ds = bll.SearchRequirement(textBoxKeyword.Text, nameOnly);

The full-text indexing of XML data feature of SQL Server makes the search possible.

SearchRequirement calls either SelectByRequirementName or SelectByRequirementNameRequirementDetail depending on which option the user selected. The DataSet is then rendered into a list box in the UI, with the help of a Requirement List Item object from the RequirementAuthoringSupport project.

foreach (DataRow row in ds.Tables[0].Rows)
{
item = new RequirementListItem(
   row["RequirementName"].ToString(),
   row["RequirementID"].ToString(),
   WorkItemType)Convert.ToInt32(row["RequirementTypeID"]));
listBoxRequirements.Items.Add(item);
}

Establishing Requirement Dependencies

RASK implements dependencies with the help of the RequirementDependency class in the RequirementAuthoringSupport project. A dependency consists of a pair of RequirementID GUIDS.

The AddDependency method of the RequirementAuthoringPane controls the process that the user initiates. The AddDependency method gets the requirement IDs from the two tree views in the UI, and then instantiates a pair of RequirementDependency objects to define both directions of the relationship:

RequirementDependency newDependency = new RequirementDependency(
   requirementTagItem.RequirementID,
   dependencyTagItem.RequirementID);

RequirementDependency newOppositDependency = new RequirementDependency(
   dependencyTagItem.RequirementID,
   requirementTagItem.RequirementID);

After error checking to ensure that no requirement is linked to itself, RASK adds the dependencies to a collection that each requirement maintains using its Dependencies property:

requirementTagItem.Dependencies.Add(newDependency);
dependencyNode.StateImageIndex = (int)NodeIcon.Dependency;
requirementNode.Tag = requirementTagItem;

if (nodes.Length > 0)
{
   RequirementNodeItem tempItem =
      (RequirementNodeItem)nodes[0].Tag;
   tempItem.Dependencies.Add(newOppositDependency);
   nodes[0].Tag = tempItem;
}

Flagging Requirements Needing Review

With requirement dependencies established, RASK can automatically notify users of requirements that need review when a related requirement changes. To do this, RASK looks for changes to requirements since the last time you generated a consolidated requirements document. If RASK finds changes, it flags related requirements in the Project Summary tree view with an icon (see figure 13).

Sending Outlook Task Requests

The OutlookManager class in the RequirementAuthoringBL project implements this feature. Specifically, the CreateTaskRequest method uses the Outlook object model to create a new task and assign it accordingly:

TaskItem taskItem = (TaskItem)_outlook.CreateItem(
      OlItemType.olTaskItem);
   try
   {
      taskItem.Assign();
      Recipient recipient = taskItem.Recipients.Add(
         toEmail);
      recipient.Resolve();

Using the Resolve method is necessary to validate the e-mail address.

The code continues by filling out the properties of the task, then calls the Save method, to save the request into the user's task list, and the Send method, to send the request to the recipient:

taskItem.Save();
if (displayForm)
      {
         taskItem.Display(true);
      }
         else
      {
         taskItem.Send();
      }

Code Examples for RASK Features Using T-SQL

Transaction-SQL (T-SQL) is an extended form of SQL that adds declared variables, transaction control, error and exception handling, and row processing to SQL's existing functions. The following sections provide code examples for RASK that use T-SQL.

Retrieving a Project from the RASK Data Store Using T-SQL

When T-SQL retrieves a project from the RASK data store, it passes it from SQL Server to RASK assemblies as XML. Although the XML format is self-documenting, in brief, it consists of a root-level <Project> element, which can contain up to three levels of nested <Requirement> elements. The following code examples shows the XML retrieved from SQL Server:

<Project ProjectID="" TeamFoundationProjectID=""> 
   <ProjectName> 
   </ProjectName> 
   <Requirements> 
      <Requirement RequirementID="" TeamFoundationWorkItemID=
         "" RequirementTypeID="" NeedsReview="" Ordinal=""> 
      <RequirementName> 
      </RequirementName> 
      <RequirementDetail> 
      </RequirementDetail> 
      <Requirement RequirementID="" TeamFoundationWorkItemID=
         "" RequirementTypeID="" NeedsReview="" Ordinal=""> 
      <RequirementName> 
      </RequirementName> 
      <RequirementDetail> 
      </RequirementDetail> 
      <Requirement RequirementID="" TeamFoundationWorkItemID=
         "" RequirementTypeID="" NeedsReview="" Ordinal=""> 
      <RequirementName> 
      </RequirementName> 
      <RequirementDetail> 
      </RequirementDetail> 
      </Requirement> 
      </Requirement> 
   </Requirement> 
   </Requirements> 
</Project>

The following code example shows the prototype for the stored procedure that T-SQL uses to retrieve a specific RASK project. RASK creates a GUID named @ProjectID to uniquely identify a project within the database:

CREATE PROC [ProjectSelectXMLByProjectID]
   @ProjectID UNIQUEIDENTIFIER

T-SQL uses FOR XML PAGE to retrieve the XML by project and format it according to the previously specified XML representation. The following pseudo code shows the general layout of the T-SQL code. T-SQL uses each FOR XML PAGE to specify a level of XML element nesting:

SELECT 
   SELECT 
      SELECT 
         SELECT 
            FROM [RequirementMaster] AS RL3
            WHERE 
         FROM [RequirementMaster] AS RL2
      AS XML)
   FROM [RequirementMaster] AS RL1
   FOR XML PATH('Requirement')
FROM [ProjectMaster] AS P
FOR XML PATH ('Project')

The following example shows the entire T-SQL code for the ProjectSelectXMLByProjectID stored procedure call:

CREATE PROC [ProjectSelectXMLByProjectID]
   @ProjectID UNIQUEIDENTIFIER

AS

SELECT 
   [ProjectID] AS "@ProjectID",
   [TeamFoundationProjectID] AS "@TeamFoundationProjectID",
   [ProjectName],
   CAST(
   (
   SELECT 
      [RequirementID] AS "@RequirementID",
      [TeamFoundationWorkItemID] AS "@TeamFoundationWorkItemID",
      [RequirementTypeID] AS "@RequirementTypeID",
      [NeedsReview] AS "@NeedsReview",
      [Ordinal] AS "@Ordinal",
      [RequirementName],
      CAST(RequirementDetail AS XML),
      CAST(
      (
         SELECT 
         [RequirementID] AS "@RequirementID",
         [TeamFoundationWorkItemID] AS "@TeamFoundationWorkItemID",
         [RequirementTypeID] AS "@RequirementTypeID",
         [NeedsReview] AS "@NeedsReview",
         [Ordinal] AS "@Ordinal",
         [RequirementName],
         CAST(RequirementDetail AS XML),
         CAST(
         (
            SELECT 
            [RequirementID] AS "@RequirementID",
            [TeamFoundationWorkItemID] AS "@TeamFoundationWorkItemID",
            [RequirementTypeID] AS "@RequirementTypeID",
            [NeedsReview] AS "@NeedsReview",
            [Ordinal] AS "@Ordinal",
            [RequirementName],
            CAST(RequirementDetail AS XML)
            FROM [RequirementMaster] AS RL3
            WHERE 
               (RL3.[ProjectID] = P.[ProjectID]) AND
               (RL3.[ParentRequirementID] = RL2.[RequirementID])
            FOR XML PATH('Requirement')
            )
         AS XML)
         FROM [RequirementMaster] AS RL2
         WHERE 
            (RL2.[ProjectID] = P.[ProjectID]) AND
            (RL2.ParentRequirementID = RL1.[RequirementID])
         FOR XML PATH('Requirement')
         )
      AS XML)
   FROM [RequirementMaster] AS RL1
   WHERE 
      (RL1.[ProjectID] = P.[ProjectID]) AND
      (RL1.ParentRequirementID = dbo.RequirementRootParentID())
   FOR XML PATH('Requirement')
   )
AS XML)
FROM [ProjectMaster] AS P
WHERE
   ([ProjectID] = @ProjectID)
ORDER BY [ProjectName]
FOR XML PATH ('Project')

Retrieving a Dependencies for a Requirement Using T-SQL

The following code example shows the stored procedure that T-SQL uses to retrieve dependencies for a requirement. The procedure uses FOR XML PAGE. In a dependency, is one requirement is linked to another T-SQL uses the RequirementSelectXMLByRequirementIDDependency to retrieve the dependent requirements for a specific requirement. The following examples show the self-documenting XML format used to retrieve dependencies for a specific requirement:

<Requirements> 
   <Requirement RequirementID="" TeamFoundationWorkItemID=
      "" RequirementTypeID="" 
   NeedsReview="" Ordinal=""> 
   <RequirementName> 
   </RequirementName> 
   <RequirementDetail> 
   </RequirementDetail> 
   </Requirement> 
</Requirements>

The RequirementSelectXMLByRequirementIDDependency stored procedure is an inner and an outer FOR XML PATH. The following code shows the entire stored procedure:

CREATE PROC [RequirementSelectXMLByRequirementIDDependency]
   @RequirementID UNIQUEIDENTIFIER

AS 

SELECT CAST(
(
   SELECT 
   [RequirementID] AS "@RequirementID",
   [TeamFoundationWorkItemID] AS "@TeamFoundationWorkItemID",
   [RequirementTypeID] AS "@RequirementTypeID",
   [NeedsReview] AS "@NeedsReview",
   [Ordinal] AS "@Ordinal",
   [RequirementName],
   CAST([RequirementDetail] AS XML)
   FROM [RequirementMaster]
   WHERE 
   ([RequirementID] IN (
      SELECT RequirementIDDestination
      FROM RequirementDependency
      WHERE (RequirementIDSource = @RequirementID))
   )
   ORDER BY [ProjectID], [ParentRequirementID], [Ordinal]
   FOR XML PATH('Requirement')
)
AS XML)
FOR XML PATH('Requirements')

Updating Requirements to be Reviewed Using T-SQL

When requirements must be reviewed, T-SQL updates the XML and an OPENXML statement in SQL Server. The following code shows the XML used to update requirements in conjunction with the RequirementModifiedUpdate stored procedure:

<ModifiedRequirements> 
   <Items> 
   <ModifiedRequirement RequirementID="" /> 
   </Items> 
</ModifiedRequirements>

The RequirementModifiedUpdate stored procedure uses OPENXLM as shown in the following process and examples:

RequirementModifiedUpdate passes in XML by means of a stored procedure parameter of type XML:
```
CREATE PROCEDURE [dbo].[RequirementModifiedUpdate]
   @XmlDocument XML
```
RequirementModifiedUpdate uses the OPENXML statement in conjunction with a document handle of type integer that is associated with an XML document using the sp_xml_preparedocument stored procedure:
```
DECLARE @docHandle INT

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument
```

OPENXML uses the previously setup up document handle.

T-SQL uses the OPENXML statement for the RequirementModifiedUpdate stored procedure to populate a temporary table with the requirements to update:

CREATE TABLE #TempRequirementDependency
(
   RequirementID UNIQUEIDENTIFIER
)

INSERT #TempRequirementDependency
SELECT * 
FROM OPENXML(@docHandle, N'//ModifiedRequirement')
WITH #TempRequirementDependency

When OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:
```
EXEC sp_xml_removedocument @docHandle 
```

Finally, the RequirementModifiedUpdate stored procedure updates the requirements using an UPDATE statement, and then cleans up the temporary table:

UPDATE [Requirement]
SET [NeedsReview] = 1
WHERE 
   [RequirementID] IN
   (
      SELECT [RequirementIDDestination]
      FROM [RequirementDependency] AS RD
      INNER JOIN #TempRequirementDependency AS TD
      ON (RD.[RequirementIDSource] = TD.[RequirementID])
   )

DROP TABLE #TempRequirementDependency

The following code shows the entire RequirementModifiedUpdate stored procedure:

CREATE PROCEDURE [dbo].[RequirementModifiedUpdate]
   @XmlDocument XML

DECLARE @docHandle INT

CREATE TABLE #TempRequirementDependency
(
   RequirementID UNIQUEIDENTIFIER
)

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument

INSERT #TempRequirementDependency
SELECT * 
FROM OPENXML(@docHandle, N'//ModifiedRequirement')
WITH #TempRequirementDependency

EXEC sp_xml_removedocument @docHandle 

UPDATE [Requirement]
SET [NeedsReview] = 1
WHERE 
   [RequirementID] IN
   (
   SELECT [RequirementIDDestination]
   FROM [RequirementDependency] AS RD
   INNER JOIN #TempRequirementDependency AS TD
      ON (RD.[RequirementIDSource] = TD.[RequirementID])
   )

DROP TABLE #TempRequirementDependency

Updating and Deleting Requirements Using T-SQL

You update and delete requirements using OPENXML in conjunction with the RequirementModifiedDelete stored procedure. The following code shows the XML used by this stored procedure:

<ModifiedRequirements> 
   <Items> 
   <ModifiedRequirement RequirementID="" /> 
   </Items> 
</ModifiedRequirements>

The RequirementModifiedDelete stored procedure uses OPENXLM as shown in the following process and examples:

RequirementModifiedUpdate passes in XML by means of a stored procedure parameter of type XML:
```
CREATE PROCEDURE [dbo].[RequirementModifiedDelete]
   @XmlDocument XML
```
RequirementModifiedUpdate uses the OPENXML statement in conjunction with a document handle of type integer that is associated with an XML document using the sp_xml_preparedocument stored procedure:
```
DECLARE @docHandle INT

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument
```

OPENXML uses the previously setup up document handle.

T-SQL uses the OPENXML statement for the RequirementModifiedDelete stored procedure to populate a temporary table with the requirements to update and delete:

CREATE TABLE #TempRequirement
(
   RequirementID UNIQUEIDENTIFIER
)

INSERT #TempRequirement
SELECT * 
FROM OPENXML(@docHandle, N'//ModifiedRequirement')
WITH #TempRequirementDependency

EXEC sp_xml_removedocument @docHandle

When OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:
```
EXEC sp_xml_removedocument @docHandle 
```

After T-SQL stores the data in the @TempRequirement temporary table, it can place the data into its hierarchical form using another temporary table, #ParentChildRequirement, and a series of SELECT statements that are integrated with UNION statements:

CREATE TABLE #ParentChildRequirement
(
   RequirementID UNIQUEIDENTIFIER,
   ParentRequirementID UNIQUEIDENTIFIER,
   Level INT
)

INSERT #ParentChildRequirement
SELECT 
   RL3.[RequirementID], 
   RL3.[ParentRequirementID],
   3 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON
      (RL1.[RequirementID] = TR.[RequirementID])
      INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])
   INNER JOIN [Requirement] AS RL2
      ON 
      RL2.[ParentRequirementID] = RL1.[RequirementID])
   INNER JOIN [Requirement] AS RL3
      ON 
      (RL3.[ParentRequirementID] = RL2.[RequirementID])

UNION

SELECT 
   RL2.[RequirementID],
   RL2.[ParentRequirementID],
   2 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON 
      (RL1.[RequirementID] = TR.[RequirementID])
   INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])
   INNER JOIN [Requirement] AS RL2
      ON 
      (RL2.[ParentRequirementID] = RL1.[RequirementID])

UNION ALL

SELECT 
   RL1.[RequirementID],
   RL1.[ParentRequirementID],
   1 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON 
       (RL1.[RequirementID] = TR.[RequirementID])
   INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])

The stored procedure is ready to handle update and delete operations.
Because UPDATE and DELETE are separate statements, T-SQL creates a transaction and a TRY/CATCH statement in SQL Server:
```
BEGIN TRY
   BEGIN TRANSACTION
```

Updating requirements is simply an update generated from the temporary table created to contain the hierarchy of requirements:

UPDATE [Requirement]
SET [NeedsReview] = 1
FROM [Requirement] AS R
   INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])

T-SQL performs delete operations at the lowest level of requirements, followed by the middle layer of requirements, and then the top level of requirements; this sequencing avoids issues with dependencies between levels:

DELETE [Requirement]
FROM [Requirement] AS R
   INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 3

DELETE [Requirement]
FROM [Requirement] AS R
   INNER JOIN #ParentChildRequirement AS PR
   ON
   (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 2

DELETE [Requirement]
FROM [Requirement] AS R
   INNER JOIN #ParentChildRequirement AS PR
   ON
      (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 1

After T-SQL processes the UPDATE and DELETE statements, it drops the temporary tables:
```
DROP TABLE #TempRequirement
DROP TABLE #ParentChildRequirement
```

The CATCH clause of the TRY/CATCH statement is used to detect an error and rollback the transaction used to wrap the update and delete statements:

END TRY

BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(400)
   DECLARE @ErrorSeverity INT
   DECLARE @ErrorState INT

   SELECT @ErrorMessage = ERROR_MESSAGE()
   SELECT @ErrorSeverity = ERROR_SEVERITY()
   SELECT   @ErrorState = ERROR_STATE()
   IF @ErrorState = 0
      BEGIN
         SET @ErrorState = 1
      END

   IF (XACT_STATE()) != 0
      BEGIN
         ROLLBACK TRANSACTION
      END

   RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )

   RETURN
END CATCH

After the CATCH clause is processed the stored procedure can commit the update and delete actions:
```
COMMIT TRANSACTION
```

Each level of the document requires new nesting of SELECT calls. With some restructuring of the data, you can use Common Table Expressions (CTE) or an APPLY statement in SQL Server. Although you can make the code generic, this adds a layer of complexity to the logic. A CTE makes handling hierarchies elegant, but it applies only to a single statement. Using temporary tables allows T-SQL to handle and apply hierarchies to four T-SQL statements, one UPDATE statement and three DELETE statements.

The following code shows the entire RequirementModifiedDelete stored procedure:

CREATE PROCEDURE [dbo].[RequirementModifiedDelete]
   @XmlDocument XML

AS

DECLARE @docHandle INT

EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument

CREATE TABLE #TempRequirement
(
   RequirementID UNIQUEIDENTIFIER
)

CREATE TABLE #ParentChildRequirement
(
   RequirementID UNIQUEIDENTIFIER,
   ParentRequirementID UNIQUEIDENTIFIER,
   Level INT
)

INSERT #TempRequirement
SELECT * 
FROM OPENXML(@docHandle, N'//ModifiedRequirement')
WITH #TempRequirementDependency

EXEC sp_xml_removedocument @docHandle 

INSERT #ParentChildRequirement
SELECT 
   RL3.[RequirementID], 
   RL3.[ParentRequirementID],
   3 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON 
      (RL1.[RequirementID] = TR.[RequirementID])
   INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])
   INNER JOIN [Requirement] AS RL2
      ON 
      (RL2.[ParentRequirementID] = RL1.[RequirementID])
   INNER JOIN [Requirement] AS RL3
      ON 
      (RL3.[ParentRequirementID] = RL2.[RequirementID])

UNION

SELECT 
   RL2.[RequirementID],
   RL2.[ParentRequirementID],
   2 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON 
      (RL1.[RequirementID] = TR.[RequirementID])
   INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])
   INNER JOIN [Requirement] AS RL2
      ON 
      (RL2.[ParentRequirementID] = RL1.[RequirementID])

UNION ALL

SELECT 
   RL1.[RequirementID],
   RL1.[ParentRequirementID],
   1 AS Level
FROM [Requirement] AS RL1
   INNER JOIN #TempRequirement AS TR
      ON 
      (RL1.[RequirementID] = TR.[RequirementID])
   INNER JOIN RequirementDependency AS RD
      ON 
      (RL1.[RequirementID] = RD.[RequirementIDDestination])

BEGIN TRY
   BEGIN TRANSACTION

   UPDATE [Requirement]
   SET [NeedsReview] = 1
   FROM [Requirement] AS R
      INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])

DELETE [Requirement]
FROM [Requirement] AS R
   INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 3

   DELETE [Requirement]
   FROM [Requirement] AS R
      INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 2

   DELETE [Requirement]
   FROM [Requirement] AS R
    INNER JOIN #ParentChildRequirement AS PR
      ON
      (R.[RequirementID] = PR.[RequirementID])
   WHERE PR.[Level] = 1

   DROP TABLE #TempRequirement
   DROP TABLE #ParentChildRequirement
END TRY

BEGIN CATCH
   DECLARE @ErrorMessage NVARCHAR(400)
   DECLARE @ErrorSeverity INT
   DECLARE @ErrorState INT

   SELECT @ErrorMessage = ERROR_MESSAGE()
   SELECT @ErrorSeverity = ERROR_SEVERITY()
   SELECT @ErrorState = ERROR_STATE()
   IF @ErrorState = 0
   BEGIN
      SET @ErrorState = 1
   END

   IF (XACT_STATE()) != 0
   BEGIN
      ROLLBACK TRANSACTION
   END

   RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState )

   RETURN
END CATCH

COMMIT TRANSACTION

GO

IF EXISTS( 
   SELECT * 
   FROM DBO.SYSOBJECTS 
   WHERE ID = OBJECT_ID(N'[DBO].[RequirementDependencyDelete]') AND  
      OBJECTPROPERTY(ID, N'IsProcedure') = 1) 
DROP PROCEDURE [DBO].[RequirementDependencyDelete]

GO

CREATE PROC [RequirementDependencyDelete]
   @RequirementIDSource UNIQUEIDENTIFIER,
   @RequirementIDDestination UNIQUEIDENTIFIER

AS

DELETE FROM [RequirementDependency]
WHERE
   (([RequirementIDSource] = @RequirementIDSource) AND
   ([RequirementIDDestination] = @RequirementIDDestination)) OR
   (([RequirementIDSource] = @RequirementIDDestination) AND
   ([RequirementIDDestination] = @RequirementIDSource))

Exposing Stored Procedures as Web Services Using T-SQL

RASK uses the CREATE ENDPOINT statement in SQL Server to expose stored procedures as Web services. T-SQL uses the WEBMETHOD argument to specify each stored procedure exposed as a Web method. The following code shows a portion of the T-SQL code used to define the Web service:

CREATE ENDPOINT RequirementsAuthoring
STATE = STARTED
AS HTTP (
   SITE = '*', 
   PATH = '/sql/RequirementsAuthoring', 
   AUTHENTICATION = (INTEGRATED),
   PORTS=(CLEAR)   
)   
FOR SOAP (
   WEBMETHOD 'ProjectSelectAll' 
   (
   name='RequirementsAuthoring.dbo.ProjectSelectAll', 
   schema=STANDARD
   ),

-- Remainder of Web service exposed stored procedures list here

   WSDL = DEFAULT,
   DATABASE = 'RequirementsAuthoring',
   BATCHES=ENABLED,
   NAMESPACE = 'http://tempui.org/'
)

Installing and Setting Up RASK Solutions

These instructions apply to developers needing to install the source code to modify RASK.

Quick Start: Installing and Setting Up RASK Solutions

You can follow these brief instructions to get started quickly or see Detailed Instructions for Installing RASK Source Code for more information.

To install the source code for the RASK Solutions

Extract all code to a folder.
For example, you might extract the code to C:\RASK\
At the Visual Studio command prompt, run Createdb.bat to create the database.
Open the RequirementAuthoringStarterKitSupport Solution.
Open the Web Reference in the DAL project, and then refresh it.
Be sure the subfolder is not read-only.
Set the Solution to Release mode.
Build the Solution.
At the Visual Studio command prompt, run Register.bat to register the DAL and TFS assemblies.
Close the Solution.
Open the RequirementAuthoringStarterKitMaster Solution.

To set up Team Explorer in Visual Studio Team Foundation Server

If necessary, connect to Visual Studio Team Foundation Server from the Tools menu.
Create a new Team Project.
Edit Workitem.bat to use the name of the Team Project you just created, and then run it at the Visual Studio command prompt.
Set the Solution to Debug mode.
Run the Solution.
OR
Publish the RequirementAuthoring project.
For example, publish the project to C:\RASK\PUBLISH.
In the RequirementAuthoring_1.0.0.X subfolder of the Publish folder, create a shortcut to RequirementAuthoring.dot file.

Detailed Instructions for Installing and Setting Up RASK Solutions

For instructions without installation details, see Quick Start: Installing RASK Source Code.

To install the RequirementAuthoringStarterKitSupport Solution

Setup the RequirementsAuthoring database.
In the RequirementAuthoringStarterKitDAL project, refresh the Web Reference
Build and register the RequirementAuthoringStarterKitDAL assembly.
Build and register the RequirementAuthoringTeamFoundation assembly.
On the Build menu, click Build Solution to build the RequirementAuthoringStarterKitSupport Solution.

You must recompile and reregister the RequirementAuthoringStarterKitDAL andRequirementAuthoringTeamFoundation projects for your changes to take affect in RASK.

To install the RequirementAuthoringStarterKitMaster Solution

Open the RequirementAuthoringStarterKitMaster Solution
If Visual Studio prompts you to enable VBA. Click OK.
In Solution Explorer, select RequirementAuthoring, and then open the App.config file for editing:
Set the TeamFoundataionConnectionString key to the connection string used to access the Visual Studio Team Foundation Server:
```
<add key="TeamFoundationConnectionString" value="http://localhost:8080/"/>
```
Set the WordXSDPath key to the full path of the .xsd file:
RequirementAuthoringStarterKit\RequirementAuthoring\Requirements.xsd.
For example:
```
<add key="WordXSDPath"
   value=
   "C:\RequirementAuthoringStarterKit\RequirementAuthoring\Requirements.xsd"/>
```
Build the RequirementAuthoringStarterKitMaster Solution.
Verify that you have access to the Visual Studio Team Foundation Server and that Visual Studio Team Foundation Server has at least one project.
To set the RequirementAuthoring project as the startup project, right-click the RequirementAuthoring project in Solution Explorer, and then on the Contact menu click, Set as Startup Project .
Create a project to use with RASK, and then import a custom work item into the project.
In Visual Studio, press F5 to run RASK.
Install the custom requirement Workitem.
For information, see Importing a Custom Workitem.
To run RASK without running Visual Studio, click the Publish command for the RequirementAuthoring project, and then run the .dot file from the subfolder in the Publish folder.
To set up Management Studio in SQL Server, open Management Studio, and then log in.
Drop/Delete the RequirementsManager database.
Open these files in the order shown:
1. RequirementsAuthoringDB.sql
  This file creates the RequirementsAuthoring database including its user-defined data types, tables, indexes, and constraints.
2. RequirementsAuthoringCode.sql
  This file creates the stored procedures, triggers, and user-defined functions.
3. RequirementsAuthoringSeed.sql
  This file creates the data used in the database: requirement types, root level projects, and root level requirements.
Click Execute.

To use the SQL Server command-line utility (SqlCmd)

On the taskbar, click Start, and then click Run.
Type cmd, and then click OK.

In the Command Prompt window, type the following syntax:

sqlcmd -E -S "<Server>" -I -i "InputSQLScriptFile"

For example:

sqlcmd -E -S "SOFTAGON-VPC001" -I -i "RequirementsAuthoringDB.sql"

Execute the following scripts in the order shown:
- RequirementsAuthoringDB.sql
- RequirementsAuthoringCode.sql
- RequirementsAuthoringSeed.sql

To compile and register RequirementAuthoringStarterKitDAL

Open the RequirementAuthoringStarterKitSupport Solution.
Set the compilation mode to Release mode.
In Solution Explorer, expand the RequirementAuthoringStarterKitDAL project folder and the Web References folder.
Right-click RequirementsAuthoringWebservice, and then click Update Web Reference to refresh the RequirementsAuthoringWebservice web reference.
At the Visual Studio command prompt, run Register.bat.
Note To locate the command prompt, click Start, and then click All Programs. In the list of programs, click Microsoft Visual Studio 2005, click Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

Running the batch file Register.bat, which is in the Release folder, registers the RequirementAuthoringStarterKitDAL.dll assembly, which places the assembly in the global assembly cache (GAC), as shown in this example:
```
RequirementAuthoringStarterKit\
   dev\
      RequirementAuthoringStarterKit\
      RequirementAuthoringStarterKitDAL\
         bin\
         Release
```
For information about manually registering or unregistering the assembly in GAC, see To register and unregister assemblies in the Global Assembly Cache.
Note Although RASK does not place the unregister task in the project's Pre-build event or the register task in the Post-build event, we encourage you to do so. To comply with security requirement when Word interacts with Web Services, specifically stored procedures and Team Foundation Services, certain assemblies must be placed in the GAC.

To compile and register the RequirementAuthoringTeamFoundation project

Open the RequirementAuthoringStarterKitSupport Solution.
Set the compilation mode to Release mode.
Compile the RequirementAuthoringTeamFoundation project.
At the Visual Studio command prompt, run Register.bat.
Register.bat is in the Release folder. Running this batch file registers the RequirementAuthoringTeamFoundation.dll assembly, which places the assembly in the GAC, as shown in this example:
```
RequirementAuthoringStarterKit\
   dev\
   RequirementAuthoringStarterKit\
      RequirementAuthoringTeamFoundation\
         bin\
Release
```
Compile RequirementAuthoringTeamFoundation Solution.
Reregister the RequirementAuthoringTeamFoundation assembly to update the GAC version of assembly.
To register it, you can use Register.bat.
For information about manually registering or unregistering the assembly in the GAC, see To register and unregister assemblies in the Global Assembly Cache.
Note Developers are encouraged to place the unregister task in a project's Pre-build event and the register task in the Post-build event. This step was not performed in this authoring toolkit because build events tend to go unnoticed and developer need to recognize that in order to get around security issues related to Microsoft Word talking to web services (stored procedures and Team Foundation Services) certain assemblies must be placed in the GAC.

To create a Visual Studio Visual Studio Team Foundation Server project

Display Team Explorer in Visual Studio (View | Team Explorer)
If the Team Explorer window does not display a tree of Server/Servers (root nodes) and team services projects (child nodes) then run Tools | Connect to Team Foundation Server and connect to the Visual Studio Team Foundation Server to be used with RASK.
Right-click the Visual Studio Team Foundation Server in the Team Explorer windows, and then select new Team Project.
Create a project named Testing123, and then click Next, Next, Next and Finish.

To import a custom work item into a Visual Studio Team Foundation Server project

At the Visual Studio command prompt, locate the directory containing the Requirement.xml file, as shown in this example:
```
RequirementAuthoringStarterKit\WorkItem
```

Type the following command syntax:

witimport /f template filename" /t "TEAM FOUNDATION SERVER" /p "PROJECT NAME"

For example:

witimport /f "Requirement.xml" /t "http://localhost:8080" /p "Testing123"

Note RASK uses the custom work item. Each time a project is created, the custom work item must be imported.

To register and unregister assemblies in the Global Assembly Cache

The Global Assembly Cache (GAC) tool, Gacutil.exe, allows you to view and manipulate the contents of the global assembly cache and download cache. Although Shfusion.dll provides similar functionality in the Microsoft .NET Framework, you can use Gacutil.exe from build scripts, makefile files, and batch files. Gacutil.exe is located here: C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin.

To register or unregister an assembly in GAC

On the taskbar in the Visual Studio command prompt, click Start, click All Programs, and then click Microsoft Visual Studio 2005.
Click Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.
From the folder where the assembly to register or unregister is located,
To unregister an assembly, type the following syntax, and then press ENTER:
```
gacutil /u Assembly name
```
For example: gacutil /u RequirementAuthoringTeamFoundation
To register an assembly, type the following syntax, and then press ENTER:
```
gacutil /i Assembly name
```
For example:
```
gacutil /i RequirementAuthoringTeamFoundation.dll
```

Note The path of Gacutil.exe is C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin.

To register assemblies in the .NET Project Reference List

If you cannot find an assembly that you successfully registered in GAC on the .NET Reference tab of the Add Reference window, follow the steps below to locate the assembly:

On the taskbar, click Start, and then click Run.
Type regedit, and then click OK.
Navigate to this path in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\AssemblyFolders
Right-click AssemblyFolders, click New, and then click Key.
Give the key the same name as the assembly, for example, RequirementAuthoringStarterKitDAL.
In the dialog box, double click the (Default) name, and then type the path to the assembly, for example, C:\installdirectory\RASK\GAC\DAL.
""Close the Registry Editor.

To publish the Requirement Authoring Document Template

Before publishing the template, be sure that the RequirementAuthoring project builds successfully.

Right-click the RequirementAuthoring project, and then click Properties.
In the Properties dialog box, click Publish.
Type the path where you want to publish the project, and then click Publish.
After you publish the document template, configure RequirementAuthoring.dll.config for the appropriate Team Foundation connection string and XSD path.

Troubleshooting RASK

The following list describes common problems using RASK and suggests ways to solve them:

Parameter not optional
Exception from HRESULT: 0x8002000F (DISP_E_PARAMNOTOPTIONAL
1. Go to add-in and Templates under Word | Tools, then select the XML tab.
2. Remove the entire XML schema, except ActionsPane.
3. Run RASK.
Operation Timed Out
This error occurs with CTP of SQL Server before September 2005.
1. Start, and then stop, the SQL Server Service.
2. Double-click the SQL Server icon on the taskbar.
3. From the Services drop-down list box, select SQL Server.
4. Click Stop.
5. After the SQL Server Service stops, click Start.
6. Attempt to start and stop SQL Server Service again.
Configuring Code Access Security
1. Go to Admin Tools, and then run Microsoft .NET Framework 2.0 Configuration.
2. In Windows Explorer, click My Computer , Runtime Security Policy, User, Code Groups, All Codes, and then click RASK.
3. Right-click RASK, and then select the properties you want.
4. On the Membership tab, verify that URL Condition Type is selected and that the path leads to the RASK folder where you extracted the file, for example, C:\Softago\RASK\*).
5. Select the Permission Set tab, and then select full trust.
Code Access Security in SharePoint Portal Server
"The customization does not have the required permissions to execute. . ."
1. Go to Admin Tools and run the .NET Framework Configuration
2. In Windows Explorer, click My Computer, Runtime Security Policy, Machine, Code Groups, and then click All Codes.
3. Right click All Code, and then click New.
4. Enter the name and description
5. Go to the Membership tab, be sure that URL Condition Type is selected, and that the URL is points to document on the SharePoint site, for example, http://myserver/*.
6. Select the Permission Set tab, and then select full trust.
7. Click OK.
You might need to wait a few minutes for Windows to refresh the security settings.
Error in sending a task request
Verify that Outlook is running and configured correctly.

Conclusion

The RASK solution illustrates one way you can integrate Microsoft products to create a custom solution.

Requirements Authoring Starter Kit (Part 1 of 2) defines the problem that the Requirements Authoring Starter Kit (RASK) solves, and describes the RASK software requirements and architecture.

Contents