How To Get Azure DevOps Data

We will fetch data using Work Item Query Language (WIQL).

WIQL query is like SQL query. The WIQL syntax is not case sensitive. It has the following clauses like:

AND
OR
NOT
UNDER
SORT

WIQL syntax will never provide you detailed work item information, because to execute this, they use REST API, but that it doesn’t have an endpoint to provide such detailed info.

So, to get the detailed info we must follow 2 steps,

Get Ids of work items using “WorkItemQueryResult”, which is the result of "QueryByWiqlAsync(wiql)". Here parameter wiql object consists of our search query.
Once we get ids in “WorkItemReference” which will be IEnumerable type, then we can fetch all details using “GetWorkItemsAsync” method.

Create PAT token

To get started first we need a PAT token which is required to communicate with Azure DevOps. PAT is an alternative for password that you can use to authenticate into Azure DevOps. To create it go to Security, from the top left corner menu choose the “New Token”.

Then as per your requirement select the options, most of them are self-explanatory.

Important Note
Once you create token, copy it somewhere because it will not be available again.

Create C# Console App targeting .NET Framework 4.5.

Then add the below package from Nuget,

Microsoft.VisualStudio.Services.Client
Microsoft.TeamFoundationServer.Client

Now in main method use the below code,

Uri orgUrl = new Uri("https://dev.azure.com/{Organization name}/");
String personalAccessToken = "{pat token}";
VssConnection connection = new VssConnection(orgUrl, new VssBasicCredential(string.Empty, personalAccessToken));

Just put your respective values instead of {Organization name}, {pat token} so you can access your DevOps server data.

Now we will move on to how to write and execute WIQL and fetch data. For this first we need to create “WorkItemTrackingHttpClient” using,

WorkItemTrackingHttpClient witClient = connection.GetClient<WorkItemTrackingHttpClient>();

Then create WIQL object and its query like,

Wiql wiql = new Wiql();
wiql.Query = "SELECT * "
+ " FROM WorkItems WHERE [System.WorkItemType] in ('Bug','Product Backlog Item')"
+ " AND [Microsoft.VSTS.Common.ClosedDate] >'" + Convert.ToDateTime("2019-01-01").ToString() + "'"
+ " AND [System.CreatedDate] >'" + Convert.ToDateTime("2019-01-01").ToString() + "'";

As above I explain we have to use “QueryByWiqlAsync” to execute query, we can do it as,

WorkItemQueryResult tasks = await witClient.QueryByWiqlAsync(wiql);
IEnumerable<WorkItemReference> tasksRefs;
tasksRefs = tasks.WorkItems.OrderBy(x => x.Id);

Until this point we get all the work item ids, only now we have to get detailed info using the second step which I have explained above,

List<WorkItem> tasksList =witClient.GetWorkItemsAsync(tasksRefs.Select(wir => wir.Id)).Result;

Once you get data in list you can play with it as per your requirement.

Note
“GetWorkItemsAsync” method has a limit, only take 200 ids at a time.

To get detailed info for one work item you have to use for each loop on the above list

Then you can access its respective data like,

foreach (var task in tasksList)
{
string wiType = task.Fields["System.WorkItemType"].ToString()
}

Mostly work item objects have the below information,

System.AreaPath
System.TeamProject
System.IterationPath
System.WorkItemType
System.State
System.Reason
System.CreatedDate
System.CreatedBy
System.ChangedDate
System.ChangedBy
System.CommentCount
System.Title And so on…