locked
Using Double as a DataTable.DefaultView.RowFilter RRS feed

  • General discussion

  • Hi

    I have been playing around with some code, just as a timewaster with no purpose what so ever, just keeping my mind alive :)

    The idea I was trying, was to set up a DataTable with differing DataTypes - just String, Double, Decimal, Date, Boolean and Integer, and then have a routine that would determine the DataType to build a RowFilter to act properly for the different types and produce  Filtered results across the entire DataGridView display of the Data.

    *

    As I developed the idea, everything was fine - until I fell foul of the indistinct natue of floating point numbers in comparison situations. This would manifest itself as a Filter with obviously equal visible values when viewed on screen but failing to match when the Filter is applied.

    *

    I overcame the hurdle right away by using the DataType of Decimal for any Filter comparisons on Double values. This works fine and appears to not care about this treatment.

    *

    Now, although I have no trouble in determining a (close as necessary) type of equality between two Double values, using the difference to be within a tolerance to straddle to minute differences from the way Doubles are held in memory. However, that is of no use in building a DataRow Filter.

    *

    What I do not understand fully is whether or not there are any pitfalls in using a Decimal equivalency for two Double values? Would there be any hazards lurking in the background with this approach. I have tried many many tests to try and find anomalies, but so far I have found none.

    *

    Here is the entire code I am using.

    *

    ' This example needs a Form1 with
    ' a blank DataGridView1, 
    ' TextBox1 and Label1
    
    ' This code is an attempt to auto Filter DataTable
    ' rows on some of the most common Data Types
    ' String, Date, Boolean, Decimal, Integer
    ' NOTE: to enable comparisons for the Filter,
    ' both Decimal and Double are compared as Decimal.
    
    Option Strict Off
    Option Explicit On
    Public Class Form1
        Dim myTable As New DataTable("Freddy")
        Dim rand As New Random
        Dim Path As String = Application.StartupPath & "\Data\Data.xml"
        Private Sub Form1_FormClosing(sender As Object, e As FormClosingEventArgs) Handles Me.FormClosing
            Dim parent As String = My.Computer.FileSystem.GetParentPath(Path)
            If Not IO.Directory.Exists(parent) Then
                IO.Directory.CreateDirectory(parent)
            End If
            ' save data file when exit the application
            myTable.WriteXml(Path, XmlWriteMode.WriteSchema)
        End Sub
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            If IO.File.Exists(Path) Then
                ' data file found so load it
                myTable.ReadXml(Path)
            Else
                ' no data file found so create dummy data
                With myTable
                    ' add some random columns
                    .Columns.Add("Integer", GetType(Integer))
                    .Columns.Add("Boolean", GetType(Boolean))
                    .Columns.Add("Double", GetType(Double))
                    .Columns.Add("First name", GetType(String))
                    .Columns.Add("Last name", GetType(String))
                    .Columns.Add("Gender", GetType(String))
                    .Columns.Add("DateTime", GetType(Date))
                    .Columns.Add("Decimal", GetType(Decimal))
    
                    ' just some test data
                    Dim r1() As String = {"Freddy", "Mary", "Brian", "Les", "Smith", "Brown", "MacHine", "Elizabeth", "Jones", "Tom"}
                    Dim r2() As String = {"Male", "Female", "Undeclared"}
                    Dim r3() As Boolean = {True, False}
    
                    For i As Integer = 0 To 222
                        .Rows.Add(i, If(rand.Next(0, 2) = 1, "False", "True"), rand.NextDouble() * 10000, r1(rand.Next(r1.Length)), r1(rand.Next(r1.Length)), r2(rand.Next(r2.Length)), Now.Date, rand.NextDouble() * 100)
    
                    Next
                    Dim md As Double = Double.MaxValue
                    .Rows.Add(3333, True, 123.12234567890128, "Freddy", "MacHine", "Male", Now.AddDays(11).Date, 1.12)
                    .Rows.Add(3334, False, 123.12234567890127, "Mary", "Smith", "Female", Now.AddDays(12).Date, 1.25)
                    .Rows.Add(3335, True, 123.12234567890125, "George", "Mathews", "undeclared", Now.AddDays(13).Date, 1.255)
    
                End With
            End If
    
            bs.DataSource = myTable
    
            ' just some formatting of DGV
            With DataGridView1
                .DataSource = bs
                .MultiSelect = False
                .RowHeadersWidth = 24
                .ColumnHeadersDefaultCellStyle.WrapMode = DataGridViewTriState.False
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                .SelectionMode = DataGridViewSelectionMode.CellSelect
                .Columns("Decimal").AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
                For Each c As DataGridViewColumn In DataGridView1.Columns
                    c.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
                Next
            End With
            Label1.Text = "Rows = " & myTable.DefaultView.Count.ToString
        End Sub
        Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged
            Label1.Text = "Rows = " & DoFilter().ToString
        End Sub
        Function DoFilter() As Integer
            Dim t As String = Trim(TextBox1.Text)
            If t.Length < 1 Then
                myTable.DefaultView.RowFilter = Nothing
                Return myTable.DefaultView.Count.ToString
            End If
            Dim fstring As String = Nothing
            Try
                For Each c As DataColumn In myTable.Columns
                    Select Case c.DataType
                        ' ================================
                        ' Treat both Double and Decimal as
                        ' Decimal to enable matching
                        ' ================================
                        Case GetType(Decimal), GetType(Double)
                            Dim v As Decimal = 0
                            If Decimal.TryParse(t, v) Then
                                fstring &= "[" & c.ColumnName & "] = '" & v & "' or "
                            End If
    
                        Case GetType(Date)
                            Dim d As DateTime = Now
                            If Date.TryParse(t, d) Then
                                fstring &= "[" & c.ColumnName & "] = '" & CDate(t) & "' or "
                            End If
    
                        Case GetType(Boolean)
                            If t.ToLower = "true" Or t.ToLower = "false" Then
                                fstring &= "[" & c.ColumnName & "] = '" & t & "' or "
                            End If
    
                        Case GetType(Integer)
                            Dim v As Integer = 0
                            If Integer.TryParse(t, v) Then
                                fstring &= "[" & c.ColumnName & "] = '" & v & "' or "
                            End If
    
                        Case Else
                            fstring &= "[" & c.ColumnName & "] like '" & t & "' or "
    
                    End Select
                Next
                fstring = fstring.Substring(0, fstring.Length - 3)
                myTable.DefaultView.RowFilter = fstring
            Catch ex As Exception
                MessageBox.Show("ERROR: " & ex.Message)
            End Try
            Return myTable.DefaultView.Count.ToString
        End Function
    End Class
    


    Regards Les, Livingston, Scotland


    • Edited by leshay Thursday, November 2, 2017 1:12 AM typos and grammer corrections.
    Thursday, November 2, 2017 12:47 AM

All replies

  • I would suggest if this is strictly for a DataGridView/DataTable to look at the following component. Literally it takes one line of code and figures out the filtering for you. Take a look at the source (it's C#), see how they did it and then apply similar logic if you really want to write this yourself without a component.

    Note that each column can be filtered or just one. Also it permits you to customize the interface and data sources (which I have done). I've used this in one app back in 2005, still running and filtering perfectly.

    https://www.codeproject.com/Articles/33786/DataGridView-Filter-Popup


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, November 2, 2017 1:59 AM
  • Hi

    Thanks Karen.

    Non of the the code from the link is usable on my setup as I have removed all database stuff from my installation of VS.

    I don't have any use for managed databases in any shape or form. I am interested in the use of DataTables and simple data handling for simple uses though and that is what is behind the post.

    My simple attempts above are no more than that. The main point of my post is that I have done something that works as expected in all the tests, but I still have a niggle with whether or not I have introduced a 'faulty' idea in the way I decide equality between Doubles by using Decimal comparisons.



    Regards Les, Livingston, Scotland

    Thursday, November 2, 2017 2:37 AM