How to design collection class(es) holding tabular data?

I realize this question is a bit large and general but I hope to get some ideas to put me on the right track rather than having a full solution. Useful links to articles on the subject of collection classes are also useful. Google on the subject just gives too many generic results.

In the past I have mainly been doing database design and related work. It's time to move on so I recently started to learn C# and found an interesting project that I'm partly using as a learning exercise. It's about analyzing results from surveys in order to identify and price-tag bad management which I think is deserving. ;-) The methodology works well but I realized that all analysis was done manually. I figured that an application could be built to save lots of time and ease the process + add some new features that would be too time consuming to be done manually. We will look into the data-entry and database design later on but right now I get the data from an html formatted Excel sheet.

The data are in columns and the most important ones are:
Assessed, Assessor, Assessor_Role, Variable1, Variable2...
* Assessed = The person who was assesed.
* Assessor = The person who did the assessment. ("Anonymized" but have additional data like age, sex, salary etc.)
* Assessor_Role = The relation to the assessed person. Currently we have only three fixed roles: Boss, Co-worker, myself.
* VariableX = can be some 200 columns with response values. Values are always integers between 1 and 6.

I can read the data in, I can do the analysis, graphing etc but I have not yet figured out a good way to keep the data internally to the program and this is where I need some help. I suppose I need to create some collection classes that can hold the data for me and make it easy to access. I have hardly used collections so far so this subject is quite new to me.

What is the best approach? HashTable, List, what else?

I have pasted a code snippet that shows some ideas on how I want to set/get the information and to use it for graphing and further calculations. This could be entirely wrong but is what I have come up with so far.

// Need to be able to add values...
if (!assessedPerson["John Doe"].Exist)
	assessedPerson.add("John Doe");
assessedPerson["John Doe"].addValue("BOSS", "VAR_1234", 3);
// Need to read the grand totals for each variable...
double totalMean = assessedPerson["John Doe"].variable["VAR_1234"].Mean;
double totalStDev = assessedPerson["John Doe"].variable["VAR_1234"].StDev;
double totalCount = assessedPerson["John Doe"].variable["VAR_1234"].Count;
// Need to read "subtotals" for each variable based on the assessor...
double bossMean = assessedPerson["John Doe"].assessor["BOSS"].variable["VAR_1234"].Mean;
double bossStDev = assessedPerson["John Doe"].assessor["BOSS"].variable["VAR_1234"].StDev;
double bossCount = assessedPerson["John Doe"].assessor["BOSS"].variable["VAR_1234"].Count;

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Object Orientated Programming. :-) best and biggest topic for you i would say if you have started to learn C#.  So to your problem.

We create an Object called DataAnalysisItem

The object DataAnalysisItem has Five Properties:
Assessed - string
Assessor - string
Assessor_Role - string
Variables - Dictionary<string,int>

See Code Snippet A for the simple proposed Object

So what you could then do is use the list type and define that of type DataAnalysisItem i.e.

List<DataAnalysisItem> items = new List<DataAnalysisItem>();

Lets define an instance of DataAnalysisItem

        List<DataAnalysisItem> items = new List<DataAnalysisItem>();
        DataAnalysisItem item1 = new DataAnalysisItem();
        item1.Assessor = "Andy";
        item1.Assessed = "XX";
        item1.Assessor_Role = "ROLE";
        item1.Variables.Add("Variable1", 1);
        item1.Variables.Add("Variable2", 2);
        item1.Variables.Add("Variable3", 3);
        item1.Variables.Add("Variable4", 4);
        item1.Variables.Add("Variable5", 5);

I hope this helps, and please post any questions you may have.


--Code Snippet A--
public class DataAnalysisItem
	public DataAnalysisItem()
        variables = new Dictionary<string, int>();
    private string assessed;
    public string Assessed
        get { return assessed; }
        set { assessed = value; }
    private string assessor;
    public string Assessor
        get { return assessor; }
        set { assessor = value; }
    private string assessor_Role;
    public string Assessor_Role
        get { return assessor_Role; }
        set { assessor_Role = value; }
    private Dictionary<string, int> variables;
    public Dictionary<string, int> Variables
        get { return variables; }
        set { variables = value; }

Open in new window


Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I think ADO.NET is the best if you need to work a lot with snapshots of relational data. You can use DataSet, DataTable etc. These are the "in-memory version" of a database server.
Sharp2bAuthor Commented:
Thanks a lot. Looks like a good start. I will need a few hours to play around with this before I get back to you. Yes, OOP is where I need to start (and have) but I'm also very much hands-on. I need to see it done before I can understand the theory. Once I get on the track and see the initial how-to:s I can usually figure it out from reading. I think my problem here is that I get confused when I try to think objects within objects. It's not only the collections, these also need to be nested in classes which makes me dizzy. Now I have a structure to start with.

Thanks, I use ADO to get the data so initially I have them in a DataSet. The thing is that I want to do several calculations and have easy access to the results, this is why I figured I put it in a class. OK I could probably use LINQ to do most of what I want but I'm not sure I want to do all the ad-hoc SQL stuff over and over again. For sure, I will keep this in mind when I consider direct access to the db in the next phase of the project. I have spent abt. 10 years doing Oracle work so I also wanted to do something non-databaseish. ;-) Otherwise I could have done most of this in SQL*Plus, except for the graphing...I also need something very user friendly.
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Well, then ADO.NET sure is database-ish :)
But it is quite powerful and you can do most things that can be done on a SQL server, even aggregate functions (DataTable.Compute method). You can even sub-class the DataTable to suit your data structure if you want to. I believe you can also read from Excel spreadsheet, provided you have the right ODBC connection (never tried this myself though ...).
But at the end of the day, the choice is yours ...
Sharp2bAuthor Commented:
Thanks for the comments and the links. I looked briefly at them and it sure seem helpful.
Unfortunately, I just got a lot of preassure to finish some of my paid work so not time for the fun stuff ;-( I have to put this aside until after the weekend but I will be back beginning of next week.
Sharp2bAuthor Commented:
Thanks a lot! I still didn't have enough time to test it fully but it looks promising so I'll assign you the points and close. I might be back with more detailed questions later.
Not sure I agree with the Grade B, but hey ho!
Sharp2bAuthor Commented:
Sorry, by habit I'm very restrictive when grading things so that was kinda default to me. On the other hand, the question was very vague. Actually, after submitting, I realised that I should also have considered the links you posted. Very useful!!! I didn't see a way to go back and change.
np. :-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.