Not a common requirement, but as it can happen, and was an interesting one to solve, I thought it worth documenting.
The scenario is this: For a medical system, there are a number of treatment sites (ie bits of the human body) that must be checked. For each treatment site, there will be a number of check types that need to be done. We needed to display this information in a grid like this (this is a prototype, so only shows True and False, rather than having checkboxes that can be altered)…
The treatment sites are listed in the first column, and are pulled from a database table.
The interesting bit here is that the 2nd column onwards are not fixed, these are also pulled from a database table, making the grid dynamic. We don’t know in advance what columns will be needed (other than the first one of course).
There were two parts to this task, first to pull the data from the database in a manner suitable for building the grid dynamically, and second, building the grid. This article will only cover the first part, hopefully the second part will be written up when it has been completed.
Here are the three tables used in the scenario…
The TreatmentSites table holds the data you saw in the first grid column. The TreatmentChecktypes table holds the descriptions used for the grid column headers for the 2nd and subsequent grid columns, and the middle table joins the other two. For a specific system, and entry in the SystemTreatmentSitesChecked table is surfaced as a checked checkbox in the grid (or the word “True” in the prototype).
The server-side query
The first thing we need to do is make a cross join between the TreatmentSites table and the TreatmentCheckTypes table, in such a way that we get an indicator for each entry (or lack of entry) in the joining table. This was fairly straightforward…
int systemID = 210; var cross = TreatmentSites.Select(ts => new { TreatmentSiteID = ts.ID, TreatmentSiteName = ts.Description, Checked = string .Join("", TreatmentCheckTypes .OrderBy(tct => tct.ID) .Select(cs => SystemTreatmentSitesCheckeds .Any(s => s.Active && s.SystemID == systemID && s.TreatmentSiteID == ts.ID && s.TreatmentCheckTypeID == cs.ID) ? "y" : "n")) }) .ToArray();
For each TreatmentSite, we select its ID and name, and then get all TreatmentCheckTypes for the TreatmentSite, building an array of “y” and “n” for the SystemTreatmentSitesChecked entries. We then use string.Join() to join the array into a single string.
Note that we order the TreatmentCheckTypes (in this case by ID, although that may change if they request a DisplayOrder column to be added), so that we know the order. This will be important later.
The result of this query is the following…
The string in the Checked column depends on what entries are in the SystemTreatmentSitesChecked table. I just added a few for testing, so the string are mostly “nnnn”.
Ideally, we would have written a query that created a results set that contained a separate column for each entry in the TreatmentCheckTypes table, but as we don’t know how many columns there are in advance, we wouldn’t be able to pass this out of the code block that created the query. Once you want to pass data out of your current code block, .NET requires static types, and that means you have to know what they are in advance (there are a few ways of getting around this, but they are massively complex and not the sort of code you’d want to look at on an empty stomach!).
The results set we have is a simple and compact way of sending the data out of the service to the client, with a known type. Once received, the client needs to decode this into objects with separate properties for each TreatmentCheckType in order to pass the data to the dynamically built grid.
The client-side manipulation
The client needs to know the TreatmentCheckTypes, which we’ll assume have been passed up already. Note that these must be ordered in the same way as they were in the first query, otherwise we won’t be able to match up the “y” and “n” characters with the correct TreatmentCheckType.
Here’s where it got fun. It was clear that we couldn’t use ordinary types, as we didn’t know how many properties there were. I played around with ExpandoObjects, which were introduced in .NET 4.0, and have the ability to have properties added and removed at run time. This eventually worked, but required the ExpandoObject to be cast as an IDictionary<string, object> in order to be able to manipulate the properties. After some fiddling, I realised that I didn’t actually need the ExpandoObject at all, I could just use a dynamic.
So, without further waffle, here is the code the client used to create the collection of anonymous types that would be passed to the grid…
Dictionary<String, dynamic>[] data = new Dictionary<string, dynamic>[cross.Count()]; for (int crossN = 0; crossN < cross.Count(); crossN++) { var d = new Dictionary<String, dynamic>(); d["TreatmentSiteID"] = cross[crossN].TreatmentSiteID; d["TreatmentSiteName"] = cross[crossN].TreatmentSiteName; for (int checktypeN = 0; checktypeN < treatmentCheckTypes.Count(); checktypeN++) { d["C" + checktypeN] = cross[crossN].Checked[checktypeN] == 'y'; } data[crossN] = d; }
As mentioned above, the client will already have a list of TreatmentCheckTypes, which are stored in the wittily named treatmentCheckTypes variable above.
For each entry in the cross-joined data (produced by the first query above), we extract the TreatmentSiteID and TreatmentSiteName, and then loop though the string (the “Checked” property) and add a bool property named Cn
(where n
is the index) that is true if the character was ‘y’ and false otherwise. This is now suitable for use with the grid.
Just to demonstrate that this worked, we can dump out the results using the following code…
foreach (var d in data) { var p = d as IDictionary<string, object>; Console.Write(p["TreatmentSiteID"] + "\t" + p["TreatmentSiteName"] + "\t"); for (int i = 0; i < treatmentCheckTypes.Count(); i++) { Console.Write(p["C" + i] + "\t"); } Console.WriteLine(""); }
This of course would not be used in production code, it was just there for testing. It produces the following output…
1 FB Breast True False True False 2 DIBH Breast False False True False 3 Lung False False False False 4 DIBH Lung False False False False 5 Exhale Lung False False False False 6 Head and Neck False False False False 7 SRS False False False False 8 Brain (non SRS) False False False False 9 Extremity False False False False 10 Abdomen False False False False 11 Pelvis False False False False 12 SBRT Lung False False False False 13 SBRT Liver False False False False 14 SBRT Other False False False False 15 CNS False False False False 16 Pediatrics False False False False 17 Electron False False False False 18 Face False False False False 19 Spine False False False False
If you can be bothered, you can check and see that the bool values shown here correspond to the “y” and “n” values in the Checked string produced by the first query.
All that’s left to do is query the treatmentCheckTypes variable and add columns to the grid for each entry, then bind the data to the grid. As mentioned before, that will hopefully be covered by another article.
Be First to Comment