SharePoint and Hewlett-Packard Quality Center

SharePoint and Hewlett-Packard Quality Center

I recently decided to take on a project that would pull defects from Hewlett-Packard (HP) Quality Center (QC) into SharePoint. Here is a summary of what I discovered as well as my final result and code.

Input

The tool requires users to input their general QC information:

·         QC Host Name: This is the URL that is entered to reach QC

·         QC User Name: This is the users QC user name that they use to login to QC

·         QC Password: This is the users QC password that they use to login to QC

·         QC Domain: This is the domain that QC requires to group projects

·         QC Project Name: This is the project under the domain chosen above that holds the defect data

The tool prompts users for a SharePoint list name; this is not a required field, as a SharePoint list will be created if they do not supply one.

The tool prompts users for their SharePoint site. This is the URL of the SharePoint site on which the list will be saved.

Process

Imports

Here are the imports I used

Imports TDAPIOLELib	This is the library of tools required to work with HP Quality Center
Imports System.IO
Imports System.Web.Configuration
Imports System.Web.HttpApplicationState
Imports System.Data.OleDb
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Xml.Schema
Imports System.Xml.Linq
Imports System.Xml.Serialization
Imports com.xxx.xxx.sharepoint	This is the library used to access the SharePoint tools
Imports ADOX

Making a Connection

The first thing I needed to do was connect to Quality Center. Here is the code I used to accomplish that:

Public Function makeConnection(ByVal qcHostName$, ByVal qcDomain$, ByVal qcProject$, _ ByVal qcUser$, ByVal qcPassword$, Optional ByVal qcPort$ = "") As Boolean         ' Connect to the TDConnection object         Dim qcServer As String         Dim errmsg As String         Dim IsMissing As Boolean         Dim chkSPList As String         Dim chkSPURL As String         'Set Globals         '============= From QCConfig ====================         myHost = Me.txtHostname.Text         myUser = Me.txtUserName.Text         myPass = Me.txtPassword.Text         '============ From Projects =====================         myProj = Me.txtProj.Text         myDomain = Me.txtDomain.Text

This code handles creating the URL and making the connection

qcServer = "http://" & myHost         If Not (IsNothing(qcPort)) Then             If Len(qcPort) > 0 Then qcServer = qcServer & qcPort         Else             errmsg = "Port is in error:" & qcPort         End If         qcServer = qcServer & "/qcbin"         Try             If (tdc Is Nothing) Then tdc = New TDConnection         Catch badCon As Exception             Call conerr(badCon.Message & ". Could not connect, please try later or install Quality Center.")             Response.End()         End Try         'If (tdc Is Nothing) Then Call conerr("Could not connect, please try later or install Quality Center.")         Try             tdc.InitConnectionEx(qcServer)         Catch             Call conerr(Err.Description & ".  Check Server value.")         End Try         Try             ' Log on             tdc.Login(myUser, myPass)         Catch             Call conerr(Err.Description & ".  Username/Password Failure, please try again.")         End Try         Try             tdc.Connect(myDomain, myProj)         Catch             Call conerr(Err.Description & ".  Connection failure, check Domain and/or Project values and try again.")         End Try         makeConnection = True     End Function

The BUG factory

This code pulls the defects from QC and puts them into a list using the tdc object created above.

Public Sub BugSnarf()         Dim BugF As BugFactory         Dim BugFilter As TDFilter         Dim bugL As List         'Not filtering because I want all bugs returned.         runstat = "Beginning Bug Snarfing"         BugF = tdc.BugFactory         BugFilter = BugF.Filter         bugL = BugFilter.NewList         'Add items to SharePoint         Call wrtToSP(bugL)     End Sub

Writing to SharePoint

This is the code I used to write the defects to a SharePoint list. In order to gain access to some of the SharePoint Services, you will have to add them.

Right-click on your project name and select “Add Web Reference”

Select or enter the address of the service, which will consist of your SharePoint URL for your site, _vti_bin/list.asmx for list services

Once you enter that URL you will be presented with the following: Click “Add Reference” to add the list web service to your project

You should see it in your Solution Explorer

And...Here is the SharePoint code that places the defects into a SharePoint list:

Protected Sub wrtToSP(ByVal bugL As List)         'Write bug info to SharePoint list         Dim wHTML As String         Dim resolution As String         Dim ndReturn As XmlNode         Dim xmlDoc = New System.Xml.XmlDocument()         Dim ndProperties As XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "List", "")         Dim ndTitleAttrib As XmlAttribute = CType(xmlDoc.CreateNode(XmlNodeType.Attribute, "Title", ""), XmlAttribute)         Dim ndDescriptionAttrib As XmlAttribute = CType(xmlDoc.CreateNode(XmlNodeType.Attribute, "Description", ""), XmlAttribute)         Dim ndVersion As XmlNode         Dim methnumb As Integer = 1         'For the updateListItems method, you need to define these as elements (within the nodes)         Dim elBatch As System.Xml.XmlElement = xmlDoc.CreateElement("Batch")         'For the updatelist method, you need to define these as nodes         Dim ilBatch As System.Xml.XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "Fields", "") ‘Insert         Dim dBatch As System.Xml.XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "Fields", "")  ‘Delete         Dim uBatch As System.Xml.XmlNode = xmlDoc.CreateNode(XmlNodeType.Element, "Fields", "")  ‘Update         'Build batch element         Dim strBatch As String         Dim strAddListFld As String         Dim ndList As System.Xml.XmlNode      ‘List         Dim ndListView As System.Xml.XmlNode  ‘View         Dim ndComeBack As XmlNode         Dim strListID As String    ‘Holds GUID for list         Dim strViewID As String    ‘Holds GUID for view         Dim listService As New Lists()         Dim txtSPSite As String         Dim txtSPList As String         'Grab the SharePoint site and list to be created/written to         txtSPList = Me.txtSPList.Text         If txtSPList = "" Then             txtSPList = "QCDefects_Output"         End If         txtSPSite = Me.txtSPSite.Text         'Use user windows creds as authentication for SharePoint         listService.Credentials = System.Net.CredentialCache.DefaultCredentials         If Right(txtSPSite, 1) = "/" Then             listService.Url = txtSPSite & "_vti_bin/Lists.asmx"    'in case they include the slash         Else             listService.Url = txtSPSite & "/_vti_bin/Lists.asmx"   'in case they leave off the slash             txtSPSite = txtSPSite & "/"         End If         'Determine if List exists         Try             ndListView = listService.GetListAndView(txtSPList, "") 'Try to Get list         Catch ex As Exception   'If list is not already there, Create it             ndList = listService.AddList(txtSPList, "QC Defects", 100) 'Create List             ndVersion = ndList.Attributes("Version")             strListID = ndList.Attributes("Name").Value       'Find new list GUID             'Add List Fields, creates the structure of the list (columns) for us old schoolers             strAddListFld = "<Method ID='1'><Field Type='Number' FromBaseType='True' DisplayName='QC_ID' /></Method>" + _             "<Method ID='2'><Field Type='Text' DisplayName='QC_Proj' /></Method>" + _             "<Method ID='3'><Field Type='Text' DisplayName='QC_Summary' /></Method>" + _             "<Method ID='4'><Field Type='Note' DisplayName='QC_Description' /></Method>" + _             "<Method ID='5'><Field Type='Text' DisplayName='QC_Status' /></Method>" + _             "<Method ID='6'><Field Type='Note' DisplayName='QC_Resolution' /></Method>"             'Include update and delete methods, this is required even if they are not being used...             Dim strDelListFld As String = "<Method ID='6'><Field /></Method>"             Dim strUpdListFld As String = "<Method ID='7'><Field /></Method>"             ilBatch.InnerXml = strAddListFld 'Insert Fields             dBatch.InnerXml = strDelListFld  'Delete Fields             uBatch.InnerXml = strUpdListFld  'Update Fields             Try                 ndComeBack = listService.UpdateList(strListID, ndProperties, ilBatch, uBatch, dBatch, ndVersion.Value)    'Batch XML             Catch ex2 As Exception  'For some reason this throws an error, but still creates the list...???!                 ex2 = Nothing                 Exit Try             End Try             ndListView = listService.GetListAndView(txtSPList, "") 'Now...Get list info         End Try         'If strListID already exists, it means that the list needed to be created first, so we don't need to get it again.         If String.IsNullOrEmpty(strListID) Then             strListID = ndListView.ChildNodes(0).Attributes("Name").Value   'Get GUID         End If         strViewID = ndListView.ChildNodes(1).Attributes("Name").Value   'Get View GUID         'Parameters for UpdateListItems service         elBatch.SetAttribute("OnError", "Continue")         elBatch.SetAttribute("ListVersion", "1")         elBatch.SetAttribute("ViewName", strViewID)         'Data for Properties of the new fields         ndTitleAttrib.Value = "List_Name"         ndDescriptionAttrib.Value = "New_Description"         ndProperties.Attributes.Append(ndTitleAttrib)         ndProperties.Attributes.Append(ndDescriptionAttrib)         'Put QC data into the fields         For Each spbug In bugL             ' In order to get to certain fields not specifically supported like .Summary and .Project, use .Field and specify the field name             wHTML = spbug.Field("BG_Description")             nohtml = Regex.Replace(wHTML, "<(.|\n)*?>", String.Empty) 'Strip any HTML as to not trip up the XML parser             resolution = spbug.Field("BG_DEV_COMMENTS")             If String.IsNullOrEmpty(resolution) Then                 nohtmlrez = resolution             Else                 nohtmlrez = Regex.Replace(resolution, "<(.|\n)*?>", String.Empty)             End If             strBatch = "<Method ID='" & methnumb & "' Cmd='New'>" + _              "<Field Name='QC_ID'>" & spbug.ID & "</Field>" + _              "<Field Name='QC_Proj'>" & spbug.Project & " </Field>" + _              "<Field Name='QC_Summary'>" & spbug.Summary & " </Field>" + _              "<Field Name='QC_Description'>" & nohtml & " </Field>" + _              "<Field Name='QC_Status'>" & spbug.Status & " </Field>" + _              "<Field Name='QC_Resolution'>" & nohtmlrez & " </Field>" + _              "<Field Name='Title'>" & myProj & "</Field></Method>"             elBatch.InnerXml = strBatch     'Places the the <Method><Field /></Method> tags inside the <Batch> tags             Try                 ndReturn = listService.UpdateListItems(strListID, elBatch)  'Add the bug fields to the SharePoint list             Catch updList As Exception                 MsgBox("Update List Failed")                 Exit Sub             End Try         Next spbug         LinkButton2.PostBackUrl = txtSPSite & "lists/" & txtSPList    ‘This makes a link button visible that will allow users to view their         LinkButton2.Visible = True                                    ‘newly created SharePoint list     End Sub

Finished Product

This is what the screen looks like after data is filled in:

Here is the screen after clicking “Pull QC Data”

 Output

And the list (drum roll please……….)

Clicking a link displays the entire defect

Well…there you have it, a solution to pulling QC defects into SharePoint. I hope you’ve enjoyed this article and that it helps you with your own endeavors.

Vette