Background

There is often a need to get a List/Datatable between two supplied dates.

Two days ago I needed to make a join between two Datatables so I thought I must share it among developers.

A scenario is that there is a table named user with a registration date. What I need to do is to create a report that will display the record for the users registered every month of this year. What I did was I fetched the data from the database that is like the following.

Table in my database

    Table A

    UserId UserFname UserCreatedDate

    1 A 13/08/2013
    2 B 13/10/2013
    3 C 13/02/2014

    The fetched data was like:

    Table B

    Months NoOfUsers

    0 82013 1
    1 02013 1
    0 22014 1
Now, I need to display a report for the users registered between 01/08/2013 and 01/03/2014.

So, I created a Datatable for all the months between those dates.

For this given below.

Just created a class.
  1. public class BhanuHelper
  2. {
  4. public static Array GetMonths(DateTime date1, DateTime date2)
  5. {
  6. //Note - You may change the format of date as required.
  7. return GetDates(date1, date2).Select(x => x.ToString("MMMM yyyy")).ToArray();
  8. }
  11. public static IEnumerable<DateTime> GetDates(DateTime date1, DateTime date2)
  12. {
  13. while (date1 <= date2)
  14. {
  15. yield return date1;
  16. date1 = date1.AddMonths(1);
  17. }
  18. if (date1 > date2 && date1.Month==date2.Month)
  19. {
  20. // Include the last month
  21. yield return date1;
  22. }
  23. }
  24. public static DataTable ToDataTable<T>( IList<T> data)
  25. {
  26. PropertyDescriptorCollection props =
  27. TypeDescriptor.GetProperties(typeof(T));
  28. DataTable table = new DataTable();
  29. for (int i = 0; i < props.Count; i++)
  30. {
  31. PropertyDescriptor prop = props[i];
  32. table.Columns.Add(prop.Name, prop.PropertyType);
  33. }
  34. object[] values = new object[props.Count];
  35. foreach (T item in data)
  36. {
  37. for (int i = 0; i < values.Length; i++)
  38. {
  39. values[i] = props[i].GetValue(item);
  40. }
  41. table.Rows.Add(values);
  42. }
  43. return table;
  44. }
  45. }
Implementation of above class
  1. BhanuHelper.ToDataTable(BhanuHelper.GetMonths(Convert.ToDateTime("First date string"),Convert.ToDateTime("second date string")));
And the output will be like below:
    Table C

    Months

    082013
    092013
    102013
    112013
    122013
    012014
    022014
    032014

    To fulfil my requirement finally, I did like below.

    Now, I just did a Left Join of my two Datatables and got the desired output.

    Months NoOfUser

    082013 1
    092013 0
    102013 1
    112013 0
    122013 0
    012014 0
    022014 1
    032014 0
I hope this article is useful for all readers, if you have any suggestion then please contact me including beginners also.