Yay me, and a question...
Firstly, yay me!
I don't care what you might think about the pros and cons of microsoft certifications. You may think they're pointless. You may think they're not worth the paper they're printed on.
Either way, I passed my first exam today. Woo and yay! 70-315 - I have a certificate that says I can "Develop and Implement Web Applications with Microsoft Visual C# .NET and Microsoft Visual Studio .NET". Woo!
The second thing was a question that someone actually asked me this morning that piqued my interest.
What this guy was trying to achieve was filtering on a Date field in a Dataview, but rather than filtering on a continuous date range, he was trying to filter on a series of date ranges.
Put simply, he wanted to pull out all the records that had a date somewhere in March (for instance). I don't know the exact context he was coming from, but it would be useful if, for example, you had sales data spanning a few years and wanted to see how things were looking on a month-by-month basis.
Well.
I discovered it doesn't seem to be as easy as all that to do. I started out by creating a Dataset in Visual C# Express, just by dragging the Employees table from the AdventureWorks database on my toolbar (Ah, the company name may change, but the samples stay the same!).
My goal was to pull out Employees as a DataTable in my dataset, create a DataView from it and filter the view to just contain everyone that has a birthday in March.
To start, I thought I'd create a new column populated with an expression, something along the lines of BirthDate.Month (or something similar). However, it looks like you can't do that. Although you can take DataColumns and do simple stuff to them, adding values, subtracting values etc, you can't do anything (it would seem) that's specific to a slightly more complex datatype, like a DateTime.
The next thing I though of was to try a do some quick filtering with a LIKE clause in the dataview's RowFilter property, so it looked something like 'view.RowFilter = "BirthDate LIKE '03/%'"' think I could pull out all the March birthdays that way. (n.b. I didn't have to worry about the formatting of the Date because of the culture insensitivity of the expression used to filter DateTime columns.)
Apparently not, though. Apparently you can't apply 'LIKE' and wildcards to DateTime fields. Which, I must admit, makes sense. I was pretty much clutching at straws when I went down that route.
So, to recap, I wanted to filter on a set of dates. These happened to be all the dates in March for a number of years, but that's by the by.
In then end, this was how I managed to do it. When I started out, I though it looked a bit hokey, and a bit of a fudge, but as I looked into it more, I'm actually quite impressed with it. It may not be that elegant a solution, but hey, it does tha job, and at the end of the day, if it works then that's cool.
TestAdventureWorks.dsTest data = new dsTest();
TestAdventureWorks.dsTestTableAdapters.EmployeeTableAdapter da =
new TestAdventureWorks.dsTestTableAdapters.EmployeeTableAdapter();
da.Fill(data.Employee);
DataView view = data.Employee.DefaultView;
StringBuilder s = new StringBuilder();
int StartYear = 1945;
int Month = 4;
for (int i = StartYear; i < 2000; i++)
{
if (i != StartYear) s.Append(" OR ");
s.AppendFormat("BirthDate >= #{0}/01/{1}# AND BirthDate < #{2}/01/{1}#", Month, i, Month + 1);
}
view.RowFilter = s.ToString();
Console.WriteLine(string.Format("Rows: {0}", view.Count));
Console.WriteLine(string.Format("Total: {0}", data.Employee.Rows.Count));
Console.ReadLine();
I don't know what the performance is like on it, though. In this case, going through 55 years on a set of 290 rows took around 70ms to complete. Not too shabby, I thought. Considering the length of the RowFilter string that went into it!
However, my question is this (and if you've read this far, perhaps you know the answer!) Is there a better way of doing it?
I thought of 1, which is to pull out the month component of the date in the original select statement, so your SELECT would look something along the lines of 'SELECT *, Month(BirthDate) FROM Employees'. It acheives the same thing as my first plan, and would allow you to just set the RowFilter to be 'BirthDateMonth=3'. But that's cheating!
Anyway, just wanted to share.
Yay me!
![[Valid RSS]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_ukFnToM2tDCrYrb3ILIQglmKkbSTnPOvyeDZxYtqgY9wOcmbQCt5GaJcjlktUyCnrh_QoImShOvvTjy0DR1sRLzymqwLT6ccz7_ZPXSMsmnja1Z9QpGFnlWGlRV4KYn64=s0-d "Validate my RSS feed"){kind=small}
