Best way to create statistics out of a DB with Lists<T>

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 1315 Views - Last Post: 21 January 2013 - 04:11 PM Rate Topic: -----

#1 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 06:33 AM

Hello guys

I'm doing some practice with Lists in C# to get more experience, so I thought I could do some statistics with data stored in a database.

My idea:
I have a Sqlite DB with some data about customers calls. Who called, when did he call, how much time did the call last, and other values that I do not care about. Now I would like to get out theese values and calculate how many times every customer called per month and per year. Afterwards, calculate how much time was invested for every customer per month and per year.

How to do it:
The calculation in fact is not difficult and getting everything out of the DB aswell. I did that already. What I want, tough, is to create a well structured list to fill out.

How would you structure your list?

As there are over 200000 rows in the DB I thought it would be best to insert every customer only once in the list and than update the specified fields to increase the desired values. With this in mind I began with creating a class for my list:
public class KdStats
        {
            public Int32 customernumber { get; set; }
            public String customername { get; set; }
            public Int32 calls { get; set; }
            public Decimal time { get; set; }
            public Int32 month { get; set; }
            public Int32 year { get; set; }
        }

but I realized soon, that this way I can not make a calculation per month/year. So my first thought was: is there a possibility to define an array within my list's class? this would solve my problem, wouldn't it? I tried to define for example "public Int32[] calls { get; set; }" but afterwards I do not know where to inizialize the array. (in the class or in the function, where I modify the list's vlaues) I did not find anything while googling... :(

Do you have other suggestions on how to do something like that? Or would it really be possible to do it with an array in my list?

This post has been edited by Anthonidas: 19 January 2013 - 06:38 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Best way to create statistics out of a DB with Lists<T>

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 08:16 AM

Quote

is there a possibility to define an array within my list's class?

Yes. But I wouldn't use arrays. Use a List<>. They are a more advanced collection with more features. For example you can .RemoveAt() with a list unlike an array. Or .InsertAt()

There is a tutorial on classes linked in my signature block. Take 20 minutes to do the tutorial then come back to your whiteboard and rethink your project.

Also, why are you using a decimal for time. A proper DateTime object would be better.
Was This Post Helpful? 1
  • +
  • -

#3 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 08:43 AM

View PosttlhIn`toq, on 19 January 2013 - 04:16 PM, said:

Also, why are you using a decimal for time. A proper DateTime object would be better.


Because the time is stored as decimal in this DB and in fact i am not really interested in having an exact conversion of the time showed in hours and minutes... I am more interested in how to store everything in the list. ;)

Ok thank you for your input. I'll do the tutorial and come back if I have any question.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 09:03 AM

Just because its in the DB as a decimal doesn't mean you have to restrict yourself to using a decimal. Personally I would convert it to a proper DateTime for my application when read in from the db so I can use it for sorting, comparing to other DateTimes, determining if it was a weekdayor weekend etc.
Was This Post Helpful? 0
  • +
  • -

#5 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 05:17 PM

Ok you convinced me ;) lol
I'll do it as DateTime... this will be a good excercise too.

So I did your tutorial and it was helpful to repeat everything about classes and objects but I'm still there, at the initial "problem". I have a list inside an other list, and I am not able to add data into it. Sure I'm doing something wrong, but what is it? :P

I show you what I already did:

My Classes & Lists:
public class KdStats
{
	public Int32 customernumber { get; set; }
	public String customername { get; set; }
	public List<number> calls = new List<number>();
	public List<time> time = new List<time>();
}

public class number : KdStats
{
	public Int32 number { get; set; }
	public Int32 month { get; set; }
	public Int32 year { get; set; }
}

public class time : KdStats
{
	public Int32 time { get; set; }
	public Int32 month { get; set; }
	public Int32 year { get; set; }
}

List<KdStats> KdStatistic = new List<KdStats>();


After having sorted the desired data off the database and having done all the checks I need, i do the following:
KdStatistic.Add(new KdStats
{
	customernumber = Int32.Parse(DB.sql_rd["kunde"].ToString().Trim()),
	customername = null, // I do not have the name at the moment... but this doesn't matter, I know how to retrive it ;)/>
	calls = // how the hell do I add some data here in?! I can not use calls.Add()
	//time = // the same as above...
});

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,870
  • Joined: 12-December 12

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 05:59 PM

            KdStatistic.Add(new KdStats {
                customernumber = 43, customername = "Eric", 
                calls = { new number {number = 3, month = 4, year = 5},
                    new number {number = 3, month = 4, year = 5}
                }
            });

or you could do it in stages; follow the code above with:

KdStatistic.Last().calls.Add(new number { number = 3, month = 4, year = 5 });

I would prefer to use struct's, rather than classes. They are more suited to representing database records, mainly because they are simpler objects.

Quote

In general, classes are used to model more complex behavior, or data that is intended to be modified after a class object is created. Structs are best suited for small data structures that contain primarily data that is not intended to be modified after the struct is created.

http://msdn.microsof...y/ms173109.aspx

Personally, and as suggested, I would convert the date-numbers to DateTime instances, and get rid of the two additional classes.

NB You cannot, and shouldn't anyway!, use the same name for a class and a member of this class (number and time).

This post has been edited by andrewsw: 19 January 2013 - 06:04 PM

Was This Post Helpful? 2
  • +
  • -

#7 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,870
  • Joined: 12-December 12

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 06:06 PM

Also, number doesn't/shouldn't inherit from KdStats.
Was This Post Helpful? 3
  • +
  • -

#8 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: Best way to create statistics out of a DB with Lists<T>

Posted 19 January 2013 - 06:20 PM

A couple issues here.

Your number and time classes shouldn't be inheriting from KdStats. There is no relationship for that. An engine and a seatbelt are both car related but you wouldn't inherit a seatbelt from an engine class. You would inherit a SixCylinder or an EightCyclinder from an Engine. Does that help see how they relate?

Don't make a property with the same name as the class (number.number). This will certainly lead to confusion and problems!

Both number and time have the same properties? WTF?
06 public List<time> time = new List<time>();
06 public List<DateTime> CallTimes = new List<DateTime>();


var temp = new KdStats();
temp.customername = null;
temp.calls.Add(someNewValue);
kdStatistics.Add(temp);


In fact I just don't understand the purpose of most of your classes.
Let's start over. This is probably a bit more than you needed but just like guns and condoms: Its better to have it and not need it, than need it and not have it.

A customer has a name, id and a list of their calls.
A call has a time and duration.
We don't try to keep a list of calls and a number of calls synchronized: There's no need to and it can lead to inconsistencies. We just get the .Count of the list.

I through this together pretty quickly so don't rely 100% on it being perfect but it should give you a better design idea to follow.

#region

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;

#endregion

namespace MyProgram
{
    public class KdStats
    {

        public List<Customer> Customers = new List<Customer>();

        public decimal AverageNumberOfCalls
        {
            get
            {
                int total = Customers.Sum(customer => customer.NumberOfCalls);

                // For educational purposes we are showing both ways
                // Only use one or the other Linq or non-linq
                #region Non-linq
                foreach (Customer customer in Customers)
                {
                    total += customer.NumberOfCalls;
                }
                #endregion Non-linq
                #region Linq equivelent
                //int total = Customers.Sum(customer => customer.NumberOfCalls);
                #endregion Linq equivelent


                return ((decimal)total / (decimal)Customers.Count);
            }
        }

        #region Nested type: Call

        public class Call : INotifyPropertyChanged
        {
            #region Public Events

            public event PropertyChangedEventHandler PropertyChanged;

            #endregion

            #region Fields

            private TimeSpan _CallDuration;
            private DateTime _CallTime;

            #endregion

            #region Properties

            public DateTime CallTime
            {
                get { return _CallTime; }
                set
                {
                    if (value == _CallTime)
                        return;

                    _CallTime = value;
                    RaisePropertyChanged("CallTime");
                }
            }


            public TimeSpan CallDuration
            {
                get { return _CallDuration; }
                set
                {
                    if (value == _CallDuration)
                        return;

                    _CallDuration = value;
                    RaisePropertyChanged("CallDuration");
                }
            }

            #endregion

            #region Methods

            private void RaisePropertyChanged(string propertyName)
            {
                if (PropertyChanged != null) PropertyChanged(this, e: new PropertyChangedEventArgs(propertyName));
            }

            #endregion
        }

        #endregion

        #region Nested type: Customer

        public class Customer
        {
            #region Fields

            private List<Call> _Calls;
            private string _ID;


            private string _Name;

            #endregion

            #region Properties

            public string ID
            {
                get { return _ID; }
                set
                {
                    if (value == _ID)
                        return;

                    _ID = value;
                    //RaisePropertyChanged("ID");
                }
            }

            public string Name
            {
                get { return _Name; }
                set
                {
                    if (value == _Name)
                        return;

                    _Name = value;
                    //RaisePropertyChanged("Name");
                }
            }


            public List<Call> Calls
            {
                get { return _Calls; }
                set
                {
                    if (value == _Calls)
                        return;

                    _Calls = value;
                    //RaisePropertyChanged("Calls");
                }
            }

            public int NumberOfCalls
            {
                get { return Calls.Count; }
            }

            #endregion
        }

        #endregion
    }
}


UPDATE: Looks like Andrew and I were on the same wavelength at about the same time. LOL
Was This Post Helpful? 2
  • +
  • -

#9 CodingSup3rnatur@l-360  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 991
  • View blog
  • Posts: 971
  • Joined: 30-September 10

Re: Best way to create statistics out of a DB with Lists<T>

Posted 20 January 2013 - 04:50 AM

I agree with tlhIn`toq and andrewsw in that your use of inheritance is very wrong. Using inheritance purely as a means of reuse when there is no relationship between the classes involved is one of the most horrible things you can do. I too looked at your code, and had a real WTF moment. That's bad. WTF moments when reading code should be avoided at all cost. Inheritance can be a powerful tool for managing complexity, but should be used very sparingly, and with great care.

One thing I don't agree with is this:

Quote

I would prefer to use struct's, rather than classes. They are more suited to representing database records, mainly because they are simpler objects.


I think classes should always be your default, over structures. Only if you discover a genuine reason why a structure is a better choice should you switch to using a structure. Using a struct just because the object only has data, and no behaviour (which is often (but not exclusively) a warning sign in the OOP world anyway) is just wrong, in my opinion.

Structures have value type semantics, and just don't behave intuitively, and are more restrictive than classes. The probability is high that, if you use structures unnecessarily, at some stage you will be tearing your hair out trying to work out why a part of your code isn't working, and the reason will be the semantics of structs vs classes. Unnecessarily cluttering your code with structs will lead to more of those horrible WTF moments.

I go over a few reasons why structs should can be harmful in the VB.NET forum (exactly the same arguments apply to C#), here.
Was This Post Helpful? 3
  • +
  • -

#10 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,870
  • Joined: 12-December 12

Re: Best way to create statistics out of a DB with Lists<T>

Posted 20 January 2013 - 06:27 AM

CodingSup3rnatur@l-360 makes a very strong case against the use of structs and I have no compelling reason to continue to advocate their use (except, perhaps, in the simplest of cases).

I wish that MS had made structs a much simpler object. They are too similar to Classes (in a number of ways), and their original purpose, and usefulness, has been lost.

This post has been edited by tlhIn`toq: 20 January 2013 - 07:08 AM
Reason for edit:: Fixed [member] tag

Was This Post Helpful? 0
  • +
  • -

#11 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5832
  • View blog
  • Posts: 12,685
  • Joined: 16-October 07

Re: Best way to create statistics out of a DB with Lists<T>

Posted 20 January 2013 - 08:22 AM

Databases don't really store things like complex objects, though. Make types for what comes out of, or goes into, the database. Looks to me like your DB should have two simple tables, so we'll model that:
class KdCustomerData {
	public int CustomerNumber { get; set; }
	public string Name { get; set; }
}

class KdCallData {
	public int CustomerNumber { get; set; }
	public DateTime StartTime { get; set; }
	public int DurationInSeconds{ get; set; } // this could be EndTime instead
}



That's to mirror how data might be stored in the a database, with tables and rows.

If you really want something more OOPish, you can create structures for that:
class KdCustomer {
	public int CustomerNumber { get; set; }
	public string Name { get; set; }
	public class Call {
		public DateTime StartTime { get; set; }
		public int DurationInSeconds{ get; set; }
	}
	public List<Call> Calls { get; set; }
}



However, it really depends on what you're after. If you're only asking questions about Customer, then it makes sense to isolate data under a Customer object. However, if you want to ask questions like how many transactions take place on a given day, then you've made your life more difficult.

Make a list of the methods you want to implement, the results you expect from them, and go from there. It's possible you'll want other data types to support those results.
Was This Post Helpful? 1
  • +
  • -

#12 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,870
  • Joined: 12-December 12

Re: Best way to create statistics out of a DB with Lists<T>

Posted 20 January 2013 - 08:39 AM

Just to mention that it is possible for a database to store complex objects: as binary or XML-serializable data (which can be queried with XQuery). It may not be a preferred method, but it is possible.

This post has been edited by andrewsw: 20 January 2013 - 08:41 AM

Was This Post Helpful? 1
  • +
  • -

#13 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5529
  • View blog
  • Posts: 11,839
  • Joined: 02-June 10

Re: Best way to create statistics out of a DB with Lists<T>

Posted 20 January 2013 - 08:49 AM

View Postandrewsw, on 20 January 2013 - 09:39 AM, said:

Just to mention that it is possible for a database to store complex objects: as binary or XML-serializable data (which can be queried with XQuery). It may not be a preferred method, but it is possible.


Agreed. I do it with complex objects by serializing them and storing them as blobs.
An EmailObj for example might have a List<string> of an unknown number of recipients, BCC addresses, attached photos and so on.

class EmailObj
{
   public List<string> ToAddys = new List<string>;
   public List<Object> Attachments = new List<Object>;
   // blah blah
}

So when I stick that in a MySQL database I have columns for UniqueIDKey, DateTimeSent, Subject, FirstToAddy just to make the basic display on screen a bit useful, along with a blob for the entire serialied EmailObj. The ListView on the user interface lists the basic info columns. When one is clicked, then the blob is deserialized and shown in a panel as a complete email. Kind of like when you click on an email in Outlook and then see it expanded in the reader panel.
Was This Post Helpful? 0
  • +
  • -

#14 Anthonidas  Icon User is offline

  • D.I.C Head

Reputation: 30
  • View blog
  • Posts: 218
  • Joined: 25-April 11

Re: Best way to create statistics out of a DB with Lists<T>

Posted 21 January 2013 - 04:31 AM

woow guys I would never had thought, that this discussion would have become as rich as it is now!! I learned more from your posts than reading my C# book and tutorials. :P

However, I am a bit confused now if I should use classes or structures. After having read the VB.Net article I think classes are everytime better than structures, or am I wrong? In my specific "little" project, where would you use structures and where classes?

All other points you explained are clear now. Thank you! I will correct everything and come back if I then have any question.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3480
  • View blog
  • Posts: 11,870
  • Joined: 12-December 12

Re: Best way to create statistics out of a DB with Lists<T>

Posted 21 January 2013 - 05:06 AM

The article linked in codingsup3rnaturl-36's post describes in detail why structures are inadvisable in most circumstances.

[I might still use structures occasionally but unless you are aware of the pitfalls then you should avoid them.]

baavgai's post suggests how you might approach this using classes.

This post has been edited by andrewsw: 21 January 2013 - 05:07 AM

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2