public static string BuildDynamicQuery(IList filters, string orderBy) { string query = "SELECT * FROM WorkItems"; string separator = "WHERE"; foreach (TFSQueryFilter filter in filters) { query = string.Format("{0} {1} {2}", query, separator, filter.GetFilter()); separator = "AND"; } if (!string.IsNullOrEmpty(orderBy)) query = string.Format("{0} ORDER BY {1}", query, orderBy); return query; } using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using Newtonsoft.Json; using TFSStatistics.Domain.Enums; namespace TFSStatistics.Domain.SharepointEntities { public class TFSProfile { public int Id { get; set; } public string Title { get; set; } public string TitleValue { get; set; } public string TitleOperator { get; set; } [JsonProperty("Area")] public string AreaValue { get; set; } public string AreaOperator { get; set; } [JsonProperty("State")] public string StateValue { get; set; } public string StateOperator { get; set; } public string AssignedToValue { get; set; } public string AssignedToOperator { get; set; } [JsonProperty("Iteration")] public string IterationValue { get; set; } public string IterationOperator { get; set; } [JsonProperty("Release")] public string ReleaseValue { get; set; } public string ReleaseOperator { get; set; } public string WorkItemTypeValue { get; set; } public string WorkItemTypeOperator { get; set; } public string WorkItemType { get; set; } public IList GenerateQueryFilters(string tfsProjectName) { IList queryFilters = new List(); queryFilters.Add(new TFSQueryFilter("Team Project", TFSQueryOperatorEnum.EQUALS, tfsProjectName)); if (!string.IsNullOrEmpty(TitleValue) && !string.IsNullOrEmpty(TitleOperator)) queryFilters.Add(new TFSQueryFilter("Title", TitleOperator, TitleValue)); if (!string.IsNullOrEmpty(AreaValue) && !string.IsNullOrEmpty(AreaOperator)) queryFilters.Add(new TFSQueryFilter("Area Path", AreaOperator, AreaValue)); if (!string.IsNullOrEmpty(StateValue) && !string.IsNullOrEmpty(StateOperator)) queryFilters.Add(new TFSQueryFilter("State", StateOperator, StateValue)); if (!string.IsNullOrEmpty(AssignedToValue) && !string.IsNullOrEmpty(AssignedToOperator)) queryFilters.Add(new TFSQueryFilter("Assigned To", AssignedToOperator, AssignedToValue)); if (!string.IsNullOrEmpty(IterationValue) && !string.IsNullOrEmpty(IterationOperator)) queryFilters.Add(new TFSQueryFilter("Iteration", IterationOperator, IterationValue)); if (!string.IsNullOrEmpty(ReleaseValue) && !string.IsNullOrEmpty(ReleaseOperator)) queryFilters.Add(new TFSQueryFilter("Target Release", ReleaseOperator, ReleaseValue)); if (!string.IsNullOrEmpty(WorkItemTypeValue) && !string.IsNullOrEmpty(WorkItemTypeOperator)) queryFilters.Add(new TFSQueryFilter("Work Item Type", WorkItemTypeOperator, WorkItemTypeValue)); return queryFilters; } } public class TFSProfileResult { [JsonProperty("results")] public List TFSProfiles { get; set; } } public class TFSProfileResultRoot { [JsonProperty("d")] public TFSProfileResult Result { get; set; } } } // builds dynamic query IList queryFilters = profile.GenerateQueryFilters(mTeamFoundationProject); string queryStr = TFSHelper.BuildDynamicQuery(queryFilters, null); private IList GetProfilesFromSharepoint() { TFSProfileResultRoot data = new TFSProfileResultRoot(); // urls string clientUrl = ConfigurationManager.AppSettings["SharepointServerUrl"]; string requestUrl = ConfigurationManager.AppSettings["TFSProfileToRetrieveQuery"]; // user and password string user = ConfigurationManager.AppSettings["SharepointUser"]; var passWord = new SecureString(); string pwd = ConfigurationManager.AppSettings["SharepointPassword"]; pwd.ToList().ForEach(passWord.AppendChar); // sets credentials HttpClientHandler clientHandler = new HttpClientHandler(); clientHandler.Credentials = new NetworkCredential(user, passWord); //creates client HttpClient client = new HttpClient(clientHandler); client.BaseAddress = new Uri(clientUrl); // sets headers client.DefaultRequestHeaders.Add("Accept", "application/json;odata=verbose"); client.DefaultRequestHeaders.Add("ContentType", "application/json"); client.DefaultRequestHeaders.Add("ContentLength", "0"); // reads resonpse var response = client.GetAsync(requestUrl).Result; if (response.IsSuccessStatusCode) { string content = response.Content.ReadAsStringAsync().Result; data = JsonConvert.DeserializeObject(content); } return data.Result.TFSProfiles; }