使用工作项查询语言(wiql)为Visual Studio团队服务(VSTS)编写和执行自定义查询的过程

Question

我想使用"WIQL"从VSTS中提取数据，并使用该数据做一些报告。1)有人可以让我知道，如果它是可能的使用"WIQL"在PowerShell?如果是这样，请告诉我在哪里可以找到一些样本或演示?

2)另外，是否有其他客户端工具支持"WIQL"对VSTS进行自定义查询?如果是这样，请让我知道在哪里我可以找到一些演示或一些样品与此相关?

Answer 1

是的，在PowerShell中使用WIQL是可能的，简单的方法是您可以通过使用PowerShell调用工作项查询REST API。

例如:

$vstsAccount = "XX"
$projectName = "XX"
$user = ""
$token = "personal access token"
Function QueryWorkItem{

# Base64-encodes the Personal Access Token (PAT) appropriately
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$token)))
# Construct the REST URL to obtain Build ID
$uri = "https://$($vstsAccount).visualstudio.com/$($projectName)/_apis/wit/wiql?api-version=1.0"

$body = "{
    'query':'Select [System.Id],[System.Title] From WorkItems Where [System.Id] = 123'
}"
# Invoke the REST call and capture the results (notice this uses the PATCH method)
$result = Invoke-RestMethod -Uri $uri -Method Post -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Body $body
Write-Output ("work item title: '$($result.WorkItems[0].URL)'")
    }
    QueryWorkItem

更多信息，可参考本文。

对于第二个问题，你可以自己构建一个应用程序，比如用c#语言构建控制台应用程序。

1. 安装Microsoft.TeamFoundationServer.ExtendedClient包
简单的c#代码

:

 var u = new Uri("https://XXX.visualstudio.com");
                VssCredentials c = new VssCredentials(new Microsoft.VisualStudio.Services.Common.WindowsCredential(new NetworkCredential("user name", "password")));
                var connection = new VssConnection(u, c);
                var workitemClient = connection.GetClient<WorkItemTrackingHttpClient>();
                var result = workitemClient.QueryByWiqlAsync(new Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models.Wiql() { Query= "Select [System.Id],[System.Title] From WorkItems Where [System.Id] = 123" },"Scrum2015").Result;
                Console.WriteLine(result.WorkItems.First().Url);

更新1:

在PowerShell中可以使用TFS/VSTS SDK或扩展SDK。例如:

if((Get-PSSnapIn -Name Microsoft.TeamFoundation.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin Microsoft.TeamFoundation.PowerShell
}
$Tfs2015AssembliesPath="[vs Installation path]Microsoft Visual Studio 14.0Common7IDECommonExtensionsMicrosoftTeamFoundationTeam Explorer"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.Common.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.Build.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.Build.Common.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.Git.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.SourceControl.WebApi.dll"
#Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.TestManagement.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.VersionControl.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPathMicrosoft.TeamFoundation.WorkItemTracking.Client.dll"
Function GetWorkItems{
    param([string]$teamProjectName,[string]$address)
    $credentials = New-Object System.Net.NetworkCredential("[user name]", "[password]")
    $tfsCollection = New-Object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection((New-Object System.URI($address)))
    $wis = $tfsCollection.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])
    $wiqlQuery = "SELECT [System.ID], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State] FROM WorkItems WHERE [System.TeamProject] = 'Scrum2015' AND  [State] = 'New' AND  [System.WorkItemType] in ('Bug','User Story')  ORDER BY [System.ID]";
    $witCollection = $wis.Query($wiqlQuery);
# add logical to export to excel.
    Foreach($witItem in $witCollection)
        {
            Write-Host $witItem.Title
        }
}
GetWorkItems "[project name]" "[tfs/vsts address]"