Using the Office Requirements Authoring Starter Kit (Part 2 of 2)
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
Microsoft Corporation
May 2006
Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Visual Studio 2005 Team System, Microsoft Visual Studio 2005 Team Foundation Server, Microsoft SQL Server 2005, Microsoft Windows SharePoint Services, Microsoft Office Word 2003
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 RASK.msi.
Contents
Overview
Code Examples for RASK Features
Establishing Requirement Dependencies
Code Examples for RASK Features Using T-SQL
Installing and Setting Up the RASK Solution
Detailed Instructions for Installing and Setting Up the RASK Solution
Troubleshooting RASK
Conclusion
Additional Resources
Using the Office 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 also reviews installation and setup.
This article, Using the Requirements Authoring Starter Kit (Part 2 of 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:
**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.
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.
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.
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);
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);
}
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;
}
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();
}
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.
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:
RequirementModifiedUpdate passes in XML using a stored procedure parameter of type XML:
CREATE PROCEDURE [dbo].[RequirementModifiedUpdate] @XmlDocument XMLRequirementModifiedUpdate 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, @XmlDocumentOPENXML 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 #TempRequirementDependencyWhen OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:
EXEC sp_xml_removedocument @docHandleFinally, 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:
RequirementModifiedUpdate passes in XML using a stored procedure parameter of type XML:
CREATE PROCEDURE [dbo].[RequirementModifiedDelete] @XmlDocument XMLRequirementModifiedUpdate 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, @XmlDocumentOPENXML 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 @docHandleWhen OPENXML processes the document, it closes the handle using the sp_xml_removedocument stored procedure:
EXEC sp_xml_removedocument @docHandleAfter 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])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 TRANSACTIONUpdating 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])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] = 1Once RASK processes the UPDATE and DELETE statements, it drops the temporary tables:
DROP TABLE #TempRequirement DROP TABLE #ParentChildRequirementThe 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 CATCHAfter 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/'
)
The following sections provide details about how to install and set up the RASK solution.
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.
Install all code to a folder.
For example, by default, this solution is installed to \User Data Folder\Visual Studio 2005\Projects\VST02005RASK.
Optional step: Run TFSrefs.bat (This adds the Team Foundation DLLs to the Visual Studio list of References on the .NET tab, when you add References to projects.)
At the Visual Studio command prompt, run Createdb.bat to create the database.
Note
You may need to modify the paths in Createdb.bat and register.bat to reflect your installation paths.
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.
Note
You may need to modify the paths in Createdb.bat and register.bat to reflect your installation paths.
Close the solution.
Open the RequirementAuthoringStarterKitMaster solution.
If necessary, connect to Visual Studio Team Foundation Server from the Tools menu.
Create a Team Project.
Edit Workitem.bat to use the name of the Team Project that 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 \User Data Folder\Visual Studio 2005\Projects\VST02005RASK\PUBLISH.
In the RequirementAuthoring_1.0.0.X subfolder of the Publish folder, create a shortcut to the RequirementAuthoring.dot file.
For instructions without installation details, see Quick Start: Installing and Setting Up RASK Solutions.
Setup the RequirementsAuthoring database. For more information, see Database Setup.
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 and RequirementAuthoringTeamFoundation projects for your changes to take effect in RASK.
Open SQL Management Studio and log on.
Drop/Delete RequirementsManager Database
Open the following files in order and execute.
RequirementsAuthoringDB.sql: this creates the RequirementsAuthoring database including its user defined data types, tables, indexes and constraints.
RequirementsAuthoringCode.sql: this creates the stored procedures, triggers and user define functions.
RequirementsAuthoringSeed.sql: creates the data used in the database (requirement types, root level projects and root level requirements)
Using SQL Command Line Utility, SqlCmd
On the Start menu, click Run.
Type "cmd" and click OK. This brings up a console window.
Type the following command syntax in the console window:
Syntax
sqlcmd -E -S "Server" -I -i "InputSQLScriptFile"Example
sqlcmd -E -S "SOFTAGON-VPC001" -I -i "RequirementsAuthoringDB.sql"Execute the following scripts in order.
RequirementsAuthoringDB.sql
RequirementsAuthoringCode.sql
RequirementsAuthoringSeed.sql
Open the RequirementAuthoringStarterKitMaster solution
If Visual Studio prompts you to enable Microsoft Visual Basic for Applications (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 work item.
For more information, see To import a custom work item into a Visual Studio Team Foundation Server project, in this article.
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 Microsoft SQL Server, open Management Studio, and then log in.
Drop/Delete the RequirementsManager database.
Open these files, in the order shown:
RequirementsAuthoringDB.sql
This file creates the RequirementsAuthoring database including its user-defined data types, tables, indexes, and constraints.
RequirementsAuthoringCode.sql
This file creates the stored procedures, triggers, and user-defined functions.
RequirementsAuthoringSeed.sql
This file creates the data used in the database: requirement types, root-level projects, and root-level requirements.
Click Execute.
On the taskbar, click Start, and then click Run.
Type cmd, and then click OK.
In the Command Prompt window, type the following syntax:
Syntax
sqlcmd -E -S "Server" -I -i "InputSQLScriptFile"Example
sqlcmd -E -S "SOFTAGON-VPC001" -I -i "RequirementsAuthoringDB.sql"Execute the following scripts, in the order shown:
RequirementsAuthoringDB.sql
RequirementsAuthoringCode.sql
RequirementsAuthoringSeed.sql
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\ ReleaseFor information about manually registering or unregistering the assembly in GAC, see To register and unregister assemblies in the Global Assembly Cache, in this article.
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 provide adequate code access security when Word interacts with Web Services, specifically T-SQL stored procedures and Team Foundation Services, these assemblies were placed in the GAC in RASK. For best practices and other approaches to VSTO deployment, see the following: Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer (Part 1 of 2) Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer: Walkthroughs (Part 2 of 2) VSTO Blogger: Misha Shneerson
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\ ReleaseCompile RequirementAuthoringTeamFoundation Solution.
Reregister the RequirementAuthoringTeamFoundation assembly to update the GAC version of the 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, in this article.
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 developers need to recognize that in order to get around security issues related to Word talking to Web services (stored procedures and Team Foundation Services) certain assemblies must be placed in the GAC.
Display Team Explorer in Visual Studio (on the View menu, click Team Explorer).
If the Team Explorer window does not display a tree of Server/Servers (root nodes) and team services projects (child nodes) then, on the Tools menu, click 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 window, and then select New Team Project.
Create a project named Testing123, and then click Next. Click Next. Click Next. Click Finish.
At the Visual Studio command prompt, locate the directory containing the Requirement.xml file, as shown in this example:
RequirementAuthoringStarterKit\WorkItemType the following command syntax:
witimport /f "template filename" /t "Team Foundation Server" /p "project name"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.
The Global Assembly Cache (GAC) tool, Gacutil.exe, allows you to view and manipulate the contents of the GAC 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.
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 be registered or unregistered is located:
To unregister an assembly, type the following syntax, and then press ENTER:
gacutil /u Assembly nameFor example:
gacutil /u RequirementAuthoringTeamFoundationTo register an assembly, type the following syntax, and then press ENTER:
gacutil /i Assembly nameFor example:
gacutil /i RequirementAuthoringTeamFoundation.dllNote
The path to Gacutil.exe is C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin.
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\AssemblyFoldersRight-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.
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.
The following list describes common problems using RASK and suggests ways to solve them:
Parameter not optional
Exception from HRESULT: 0x8002000F (DISP_E_PARAMNOTOPTIONAL)
On the Word Tools menu, click Templates and Add-ins, then select the XML Schema tab.
Remove the entire XML schema, except ActionsPane.
Run RASK.
Operation Timed Out
This error occurs with CTP of Microsoft SQL Server before September 2005.
Start, and then stop, the SQL Server Service.
Double-click the SQL Server icon on the taskbar.
From the Services drop-down list box, select SQL Server.
Click Stop.
After the SQL Server Service stops, click Start.
Attempt to start and stop SQL Server Service again.
Configuring Code Access Security
Go to Admin Tools, and then run Microsoft .NET Framework 2.0 Configuration.
InWindows Explorer,click My Computer , Runtime Security Policy, User, Code Groups, All Codes, and then click RASK.
Right-click RASK, and then select the properties that you want.
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:\Office Samples\VSTO2005RASK\*).Select the Permission Set tab, and then select full trust.
Code Access Security in Windows SharePoint Services
"The customization does not have the required permissions to execute. . ."
Go to Admin Tools and run the .NET Framework Configuration.
In Windows Explorer, click My Computer, Runtime Security Policy, Machine, Code Groups, and then click All Codes.
Right-click All Codes, and then click New.
Type the name and description
Go to the Membership tab, be sure that URL Condition Type is selected, and that the URL points to the document on the SharePoint site, for example, http://myserver/*.
Select the Permission Set tab, and then select full trust.
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.
The RASK solution illustrates one way that you can integrate Microsoft products to create a custom solution.
Using the Office 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.