internal WorkRequest[] GetWorkRequest([Optional] WorkRequest matchOnValues) { List <WorkRequest> results = new List <WorkRequest>(); string connString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; try { string sql = "Select * from [data].[Requests] "; if (matchOnValues != null) { var properties = matchOnValues.GetType().GetProperties(); sql += "Where "; int numProps = properties.Length; for (int i = 0; i < numProps; i++) { var key = properties[i].Name; var value = properties[i].GetValue(matchOnValues); if (value == null) { continue; } sql += " " + key + " = '" + value + "' "; if (numProps > 0 && i < numProps - 1) { sql += " AND"; } } if (sql.EndsWith("AND")) { sql = sql.Remove(sql.Length - 3); } } DataTable dt = new DataTable(); using (SqlDataAdapter da = new SqlDataAdapter(sql, connString)) da.Fill(dt); foreach (var row in dt.AsEnumerable()) { try { results.Add( new WorkRequest { RequestID = row.Field <dynamic>("RequestID"), Requestor = row.Field <dynamic>("Requestor"), Title = unescape(row.Field <dynamic>("Title")), Manager = row.Field <dynamic>("Manager"), Goal = unescape(row.Field <dynamic>("Goal")), // Problem field deprecated, Iteration 9, January 2018 // Problem = unescape(row.Field<dynamic>("Problem")), BusinessValueUnit = row.Field <dynamic>("BusinessValueUnit"), BusinessValueAmount = row.Field <dynamic>("BusinessValueAmount"), RequestedCompletionDate = row.Field <dynamic>("RequestedCompletionDate"), // Quantification field deprecated, Iteration 6, December 2017, replaced by BusinessValueUnit/BusinessValueAmount // Quantification = unescape(row.Field<dynamic>("Quantification")), // Benefit field deprecated, Iteration 9, January 2018 // Benefit = unescape(row.Field<dynamic>("Benefit")), Status = row.Field <dynamic>("Status"), StatusDate = row.Field <dynamic>("StatusDate"), LastModified = row.Field <dynamic>("LastModified"), CorporateGoals = JsonConvert.DeserializeObject <List <string> >(row.Field <dynamic>("CorporateGoals")), GoalSupport = unescape(row.Field <dynamic>("GoalSupport")), SupportsDept = row.Field <dynamic>("SupportsDept"), DeptGoalSupport = unescape(row.Field <dynamic>("DeptGoalSupport")), ConditionsOfSatisfaction = unescape(row.Field <dynamic>("ConditionsOfSatisfaction")), NonImplementImpact = unescape(row.Field <dynamic>("NonImplementImpact")), RealizationOfImpact = unescape(row.Field <dynamic>("RealizationOfImpact")) }); } catch (Exception ex) { Error.Log(ex); } } if (results.Count < 1) { throw new KeyNotFoundException("No matching Work Request was returned from your search: " + JsonConvert.SerializeObject(matchOnValues)); } return(results.ToArray <WorkRequest>()); } catch (Exception ex) { Error.Log(ex); return(new WorkRequest[0]); //empty } }