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.
- public class BhanuHelper
- {
-
- public static Array GetMonths(DateTime date1, DateTime date2)
- {
-
- return GetDates(date1, date2).Select(x => x.ToString("MMMM yyyy")).ToArray();
- }
-
-
- public static IEnumerable<DateTime> GetDates(DateTime date1, DateTime date2)
- {
- while (date1 <= date2)
- {
- yield return date1;
- date1 = date1.AddMonths(1);
- }
- if (date1 > date2 && date1.Month==date2.Month)
- {
-
- yield return date1;
- }
- }
- public static DataTable ToDataTable<T>( IList<T> data)
- {
- PropertyDescriptorCollection props =
- TypeDescriptor.GetProperties(typeof(T));
- DataTable table = new DataTable();
- for (int i = 0; i < props.Count; i++)
- {
- PropertyDescriptor prop = props[i];
- table.Columns.Add(prop.Name, prop.PropertyType);
- }
- object[] values = new object[props.Count];
- foreach (T item in data)
- {
- for (int i = 0; i < values.Length; i++)
- {
- values[i] = props[i].GetValue(item);
- }
- table.Rows.Add(values);
- }
- return table;
- }
- }
Implementation of above class
- 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.