Taking advantage of the DataView's Rowfilter

by William Ryan

If you are regular at KnowDotNet and read our Data Access section, you know I'm in love DataViews.  Well, after running around evangelizing all the benefits of ADO.NET for the last two years, I came across a problem that threw me for a loop.  I'm one of those Obsessive/Compulsive  types who is driven nuts by stuff like this, and I was bound and determined to solve it!

Here's the problem.  A while ago, I had to rewrite a Human Resources application at my company that was originally done in MS Access.   It had a somewhat attractive UI, but that was about the only thing in it that wasn't broken.  Anyway, I decided I'd redo it in ADO.NET.  Every employee has an Employee Number (EmplNum) which is automatically generated by the database.  The navigation is similar to Outlook in that it has to show the Employee Number, a space, then the LastName, FirstName.  Anyway, it took me about four hours to rewrite this thing.  I used an Expression Column and used DataColumn.Expression = "EmplNum + '  ' + LastName + ', ' + FirstName" and set the DisplayMember of the ListBox to the expression colum field and the ValueMember to the EmployeeNumber (Originally I used Val(ListBox.SelectedItem) but I don't like Admitting that in public).  Anyway, when I first load the listbox, I use a HashTable  and use the position that the record comes back as a key, and the EmplNum as the value.  This may seem silly, but I have to filter on many different employee types (Exempt, NonExempt, Former, Contractor, Temp) so I need to keep track of this so I can set the position of my BindingContext.

Anyway, the problem came from this.  I didn't hard code EmployeeStatus in the database...it was inferred.  If an Employee had a DepartureDate then they are considered a former employee.  Otherwise they are Current.  If an employee was a Contractor than they'd have a ContractingCompany assigned.  If they were a Temp, they'd have a BecamePermanentDate as Null.

I could bore you with the 'wrong' way I did this originally, but that'd be lame.  Suffice to say that I have four Radio Buttons, For Active, Former, Temp and Contractor (the default being Active).

Here's the Challenge.  Since all of this is done from Inference, how do I code it.  Sure, I could set the DataView's RowFilter  to "EmplType = 'A'" if I had a Denormalized database, but I didn't.

So, I had to determine is something was null or not and go from there.  Here's how I did it:

lstEmployees.BeginUpdate() If optActive.Checked Then     dv.RowFilter = "IsNull(Departure_Date, '01/01/1900') = '01/01/1900'" ElseIf optFormer.Checked Then     dv.RowFilter = "IsNull(Departure, '01/01/1900') <> '01/01/1900'" ElseIf optTemp.Checked Then     dv.RowFilter = "Emp_Type = 'Temporary' AND IsNull(Departure, '01/01/1900') = '01/01/1900'" ElseIf optContractor.Checked Then     dv.RowFilter = "Emp_Type = 'Contractor' AND IsNull(Departure, '01/01/1900') = '01/01/1900'" End If     lstEmployees.SelectedIndex = -1 Catch ex As Exception     Debug.Assert(False, ex.ToString) Finally     lstEmployees.EndUpdate() End Try

First, before I try to change my rowfilter, I call the BeginUpdate Method of the listbox so that it doesn't repaint itself each time an item is added (when I'm done, I call EndUpdate).  Next, let's say they clicked the 'Active' CheckBox.  I use the IsNull Function and set it to a dummy value.  Then I set my expression to test for that dummy value (thereby acknowledging that it's a Null Value and if Departure_Date is Null, Then they are still employed).  Since I can't use IS NOT NULL like I can in Standard SQL, this works pretty well.  As such, I can use the inverse (If it's null, it will equal my value, so if it's not equal, then it's not null - how's that for induction?).  Similarly, I can use an AND clause to set some other condition and test for Null.

One caution though.  Let's say that I used this piece:

dv.RowFilter = "IsNull(Departure_Date, 'NG') = 'NG'"

This would blow up because the ADO.NET couldn't convert 'NG' to a Date and when it tests for equality, that's what it does....

You have the other option of using ISNULL Server Side, but that's a broken approach for many reasons.  1)  Not every DB Supports ISNULL (Tragically Lame Access comes to mind).  2)  You will pull back data that is NULL but won't appear that way and you'll have to write code to see to it that the integrity is maintained.

Anyway, what I wanted to show is how you can get around the whole Null thing when you use Expressions.  Moreoever, Expression Columns and the RowFilter support the exact same subset of functions, so you can use this logic with expression columns or rowfilters.

Every time I think ADO.NET has let me down, I've realized I didn't know nearly as much as I should have....