Tuesday, March 20, 2012

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.


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.



Here are the imports I used

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
            errmsg = "Port is in error:" & qcPort
        End If

        qcServer = qcServer & "/qcbin"

            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.")
        End Try

        'If (tdc Is Nothing) Then Call conerr("Could not connect, please try later or install Quality Center.")

            Call conerr(Err.Description & ".  Check Server value.")
        End Try
            ' Log on
            tdc.Login(myUser, myPass)
            Call conerr(Err.Description & ".  Username/Password Failure, please try again.")
        End Try
            tdc.Connect(myDomain, myProj)
            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
            listService.Url = txtSPSite & "/_vti_bin/Lists.asmx"   'in case they leave off the slash
            txtSPSite = txtSPSite & "/"
        End If

        'Determine if List exists
            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

                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"

        '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
                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
                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”


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.


No comments: