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
No comments:
Post a Comment