• RequirementAuthoring   Implements the task pane user interface (UI) that appears in the Microsoft Office Word 2003 documents. This project was specifically created as a Microsoft Visual Studio 2005 Tools for the Microsoft Office System (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 Microsoft Office Outlook through that application's object model.

• RequirementAuthoringSupport   Implements various helper classes for RASK.

• RequirementAuthoringStarterKitDAL   Implements a data access layer to interact with the Microsoft SQL Server data store using the Web service exposed by Microsoft SQL Server. RequirementAuthoringStarterKitDAL contains a Requirement class to represent a single requirement and a ProjectRequirement 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 Microsoft Visual Studio Team Foundation Server.

• RequirementAuthoringSupport   Implements various helper classes for RASK.

To create a project in RASK, you must select an established Visual Studio Team Foundation Server project from a list in the task pane. RASK then 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. The 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;

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

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

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

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

taskPane.AddTreeViewItem(
parentRequirementID,

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

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 Microsoft SQL Server to communicate with the database. Most of the code to communicate with the database is automatically generated by Microsoft 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 Microsoft SQL Server.

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

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);

This feature works exactly like creating a new project: It uses DisplayProject to populate the document with project-level information. In addition, it calls DisplayRequirements to fill the document with requirements from the data store. Because the path from the UI to this code is straight-forward, 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 about AddRequirement, see >Adding Requirements in this article.

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 Microsoft 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);
}

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 in Using the Office Requirements Authoring Starter Kit (Part 1 of 2)).

The OutlookManager class in the RequirementAuthoringBL project implements sending Microsoft Office Outlook task requests. Specifically, the CreateTaskRequest method uses the Outlook object model to create a 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();
      }

When you use T-SQL to retrieve a project from the RASK data store, it passes it from Microsoft 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 example shows the XML retrieved from Microsoft 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 RASK 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

RASK uses T-SQL's FOR XML PATH to retrieve the XML for a project and format it according to the previously specified XML representation. The following pseudo code shows the general layout of the T-SQL code. RASK uses each FOR XML PATH 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')

The following code example shows the stored procedure that RASK uses to retrieve dependencies for a requirement. The procedure uses FOR XML PATH. In a dependency, one requirement is linked to another. RASK uses RequirementSelectXMLByRequirementIDDependency to retrieve the dependent requirements for a specific requirement. The following example shows 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')

When requirements must be reviewed, RASK updates the XML and an OPENXML. 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 OPENXML, as shown in the following process and examples:

1. RequirementModifiedUpdate passes in XML using a stored procedure parameter of type XML:

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

2. 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

3. OPENXML uses the previously set up document handle.

   RASK 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

4. When OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:

   EXEC sp_xml_removedocument @docHandle 

5. 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

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 OPENXML, as shown in the following process and examples:

1. RequirementModifiedUpdate passes in XML using a stored procedure parameter of type XML:

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

2. 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

3. OPENXML uses the previously set up document handle.

   RASK 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 

4. When OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:

   EXEC sp_xml_removedocument @docHandle 

5. After RASK 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])

6. The stored procedure is ready to handle update and delete operations.

   Because UPDATE and DELETE are separate statements, we create a transaction and use the new SQL Server 2005 TRY/CATCH block:

   BEGIN TRY
      BEGIN TRANSACTION

7. 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])

8. RASK 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

9. Once RASK processes the UPDATE and DELETE statements, it drops the temporary tables:

   DROP TABLE #TempRequirement
   DROP TABLE #ParentChildRequirement

10. 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

11. 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 Microsoft 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 RASK to use 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))

RASK uses the new CREATE ENDPOINT statement in SQL Server 2005 to expose stored procedures as Web services. RASK 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/'
)

• Microsoft Office 2003 with Service Pack (SP) 2. Note that, at a minimum, you must install Microsoft Office Word 2003 and Microsoft Office Outlook 2003.

• Microsoft SQL Server 2005. Note that this solution has not been tested with Microsoft SQL Server 2005 SP 1.

• Microsoft Windows SharePoint Services 2.0 SP 2.

• Microsoft Visual Studio Team Foundation Server. Follow instructions in Visual Studio Team Foundation Server installation guide available on MSDN.

• Microsoft Visual Studio Team Edition. Note that Microsoft Visual Studio Tools for the Microsoft Office System is included with Visual Studio Team Edition.

• Microsoft Visual Studio Team Explorer. Note that this must be installed from the Visual Studio Team Foundation Server media in addition to Visual Studio Team Edition.

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 Solution

To set up Team Explorer in Visual Studio Team Foundation Server

To install the RequirementAuthoringStarterKitSupport Solution

Database Setup

To install the RequirementAuthoringStarterKitMaster Solution

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

To compile and register RequirementAuthoringStarterKitDAL

To compile and register the RequirementAuthoringTeamFoundation project

To create a Visual Studio Team Foundation Server project

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

To register and unregister assemblies in the Global Assembly Cache

To register or unregister an assembly in GAC

To register assemblies in the .NET Project Reference List

To publish the Requirement Authoring Document Template