Fremus.co.za

Okay so in the last two weeks or so I have been developing some code that generates a pivot-table with x, y and z axis by using a datareader and generics. A datareader is a forward-only datareader, in other words the data is simply ‘relayed’ so-to-speak to wherever and this works just great for the pivot-table. And the beauty of generics is that the objects you store in it are real objects that retain their properties. So if for example you use a DateTime object in a dictionary the object retains its properties throughout. You can have dictionaries within dictionaries, or lists within lists, or dictionaries within dictionaries. In my scenario the usage of a dictionary within a dictionary was very useful because I had a datatable that conained a list of dates that were to be used as the y column in my pivot table and the x values were a list of various other variables. It meant that I had to generate an unique list of dates but still somehow maintain a reference to each individual date with its associated value. So basically I had:

Dictionary myDict = new Dictionary();

So essentially what happened was I created a dictionary that added an unique DateTime object by using the Dictionary’s ContainsKey method to check if the key exists and in the Add() method it called another function that generated the second dictionary in the same collection that added all the dates from the data reader plus an int that acted as a count for the dates.

I’ll write some more tomorrow on the rest of the logic. But its not too difficult to build a pivot table from this. Remember that the pivot table is used in a web service and in the end is generated as HTML.

The point is though that to create a stored procedure that can handle this so elegantly is not easily done. I mean you can use the PIVOT SQL command or follow one of the many articles available, but then you have to look at the amount of work that goes into creating and developing that stored procedure. The C# code is less than 10 lines of code, the rest of the code is business logic particular to some part of the UI.

I have been having many a thought on the whole stored procedure vs .NET Code debate, and whether more business logic should reside in the database management system or in your code (C#, VB.NET). There are benefits to both approaches and the key is to identify and understand when the benefits of one approach makes that approach more feasible.

When building a pivot table, that generates HTML, use C#. When doing transactional processing, use the DBMS.