Using Reflection rather than PL/SQL…
Our primary domain database, our student information system, is a packaged solution that sits on top of a huge Oracle database of hundreds of tables, strange naming conventions, and other pains.
For me to open up SQLPlus and query up a record, like a student, and see what grade their in, who their teacher is, etc. is very time consuming (there are no stored procedures for such queries and I have no access to add them). 😦
Because of that, and to keep with standards, I created a framework library that is used by all of .NET applications—it handles the database calls, queries, and returns. It’s further simplified by that the library can only read from the system.
So, I found myself today, on the phone with a customer… trying to look up their class roster. This requires fetching their SSN (I loathe asking for it), querying their teacher ID from that, doing a look up across about 8 tables to find their classes, schools, class lists, and student records—just to print a list of student names.
After finding what I needed, I finished with the call and sat trying to figure out how I could use .NET and the library for a better way—everything I need exists in our student domain objects. I just need a quick way to get to them and their properties!
Using Will’s addiction to Reflection as motivation, I ended up with:
Student student = Student.GetStudentByStudentId(123456789);
foreach (PropertyInfo prop in
student.GetType().GetProperties().OrderBy(i => i.Name))
The extension method of OrderBy is a nice touch—putting the property bag into alphabetical order and making it easier to read.
The end result looks like (given no data because… well, that’d just be bad):
The final touch was to remove the actual student Id and turn this into a “real” command line application. I updated the parameter for GetStudentByStudentId to grab the first argument passed to it; and added some help if the argument is missing or incorrect.
if (args.Count() > 0)
Student student = Student.GetStudentByStudentId(int.Parse(args));
Console.WriteLine(“eSIS Query Tool usage: “);
It’s REALLY basic, lacks proper column formatting (something I may fix), but REALLY helpful when someone calls me up. I realize the Oracle and Database gurus will be beside themselves by the fact that I’d rather write a .NET program than query the database directly, but IMHO, part of being a developer is also seeing the tradeoffs. I can’t “fix” the database design, make it easier, etc.—but I can write a quick little tool to help me on the off chance someone calls and needs an answer. Isn’t that what it’s all about?