Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and exploring the core foundations for app libraries.
Filtering a datagrid view
I have a datagridview bound to binding source. i am using bindingsource.filter to filter for a search string in a column. My requirement is as below
I have 4 search textboxes. department, sercode, sername, bedtype.
I enter a search string in department textbox. it filters for the entered department in the grid. when i next enter sercode, it has to filter for the text in sercode in already filtered data grid view for the deparment
I have 4 search textboxes. department, sercode, sername, bedtype.
I enter a search string in department textbox. it filters for the entered department in the grid. when i next enter sercode, it has to filter for the text in sercode in already filtered data grid view for the deparment
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I would recommend following pattern to apply multiple filters based instant search.
1- Create a master data source (a copy of your data source which you have set into binding source)
2- Create a data source/binding source from master by applying filter.
3- Bind the filtered data to Gridview.
Because you have multiple filters, when one of them is applied, then your data source only contains filtered data and subsequent filters cannot be applied on that hence its better to persist the master copy of data source.
Here is pseudo code:
List<UserDTO> masterList=new List<UserDTO>();
//Search by user ID
List<UserDTO> users =masterList.Where(u=>u.Use
//Bind to gridview
gridview.datasource=users;
//Apply fiter on name and age
List<UserDTO> users =masterList.Where(u=>u.age
//Bind to gridview
gridview.datasource=users;
Regards,
1- Create a master data source (a copy of your data source which you have set into binding source)
2- Create a data source/binding source from master by applying filter.
3- Bind the filtered data to Gridview.
Because you have multiple filters, when one of them is applied, then your data source only contains filtered data and subsequent filters cannot be applied on that hence its better to persist the master copy of data source.
Here is pseudo code:
List<UserDTO> masterList=new List<UserDTO>();
//Search by user ID
List<UserDTO> users =masterList.Where(u=>u.Use
//Bind to gridview
gridview.datasource=users;
//Apply fiter on name and age
List<UserDTO> users =masterList.Where(u=>u.age
//Bind to gridview
gridview.datasource=users;
Regards,
Dim filterString As String = ""
If TextBox1.Text<>"" Then 'or your condition here
filterString = "(ColumnName1 = '" & TextBox1.Text & ")"
End If
If TextBox2.Text<>"" Then 'or your condition here
If filterString <>"" then filterString = filterString & " AND "
filterString &= "(ColumnName2 LIKE '%" & TextBox2.Text & ")"
End If
If TextBox3.Text<>"" Then 'or your condition here
If filterString <>"" then filterString = filterString & " AND "
filterString &= "(ColumnName3 = '" & TextBox3.Text & ")"
End If
If TextBox4.Text<>"" Then 'or your condition here
If filterString <>"" then filterString = filterString & " AND "
filterString &= "(ColumnName4 = '" & TextBox4.Text & ")"
End If
If filterString<>"" Then bs.Filter = filterString Else bs.RemoveFilter
*No Points*
There is no need to maintain separate lists. Filtering does not change the contents of the source, simply what is presented to the UI.
Proof of concept for Ark's recommendation:
Form1.vb -
Initial load -
Filtering on First Name using 'ar' -
Filtering on Last Name using 14 -
-saige-
There is no need to maintain separate lists. Filtering does not change the contents of the source, simply what is presented to the UI.
Proof of concept for Ark's recommendation:
Form1.vb -
Imports System.ComponentModel
Imports System.Globalization
Imports System.Runtime.CompilerServices
Public Class Form1
Private IsLoading As Boolean
Private filterString As String
Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
IsLoading = True
BindingSource1.DataSource = (From i In Enumerable.Range(0, 20) Select New With {.ID = i, .FirstName = String.Format("{0}{1}", If(i Mod 5 = 0, "Paul", If(i Mod 4 = 0, "Larry", If(i Mod 3 = 0, "Susan", If(i Mod 2 = 0, "Mary", "Peter")))), i), .LastName = String.Format("LastName{0}", i), .BirthDate = Today.AddYears(-(9 * i)), .IsWorking = i Mod 2 = 0}).ConvertToDataTable()
DataGridView1.DataSource = BindingSource1
ComboBox1.Items.AddRange(New Object() {"None", "False", "True"})
ComboBox1.SelectedIndex = 0
IsLoading = False
End Sub
Private Function GetFilter() As String
GetFilter = String.Empty
Dim filters As New List(Of String)
Try
If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
filters.Add(String.Format("(FirstName LIKE '%{0}%')", TextBox1.Text))
End If
If Not String.IsNullOrWhiteSpace(TextBox2.Text) Then
filters.Add(String.Format("(LastName LIKE '%{0}%')", TextBox2.Text))
End If
If Not String.IsNullOrWhiteSpace(MaskedTextBox1.Text) Then
filters.Add(String.Format("(BirthDate = '{0}')", Date.ParseExact(MaskedTextBox1.Text, "ddMMyyyy", CultureInfo.InvariantCulture)))
End If
If Not String.IsNullOrWhiteSpace(ComboBox1.Text) AndAlso Not ComboBox1.Text.Equals("none", StringComparison.OrdinalIgnoreCase) Then
filters.Add(String.Format("(IsWorking = {0})", Convert.ToBoolean(ComboBox1.Text)))
End If
Catch ex As Exception
Finally
GetFilter = String.Join(" AND ", filters)
End Try
Return GetFilter
End Function
Private Sub OnTextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged, MaskedTextBox1.TextChanged
If Not IsLoading Then
filterString = GetFilter()
If String.IsNullOrWhiteSpace(filterString) Then
BindingSource1.RemoveFilter()
Else
BindingSource1.Filter = filterString
End If
End If
End Sub
Private Sub OnSelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
If Not IsLoading Then
filterString = GetFilter()
If String.IsNullOrWhiteSpace(filterString) Then
BindingSource1.RemoveFilter()
Else
BindingSource1.Filter = filterString
End If
End If
End Sub
End Class
Module Extensions
<Extension()>
Public Function ConvertToDataTable(Of T)(ByVal source As IEnumerable(Of T)) As DataTable
Dim properties As PropertyDescriptorCollection = TypeDescriptor.GetProperties(GetType(T))
Dim table As DataTable = New DataTable()
For i As Integer = 0 To properties.Count - 1
Dim [property] As PropertyDescriptor = properties(i)
If [property].PropertyType.IsGenericType AndAlso [property].PropertyType.GetGenericTypeDefinition().Equals(GetType(Nullable)) Then
table.Columns.Add([property].Name, [property].PropertyType.GetGenericArguments()(0))
Else
table.Columns.Add([property].Name, [property].PropertyType)
End If
Next
Dim values(properties.Count - 1) As Object
For Each item As T In source
For i As Integer = 0 To properties.Count - 1
values(i) = properties(i).GetValue(item)
Next
table.Rows.Add(values)
Next
Return table
End Function
End Module
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Form1
Inherits System.Windows.Forms.Form
'Form overrides dispose to clean up the component list.
<System.Diagnostics.DebuggerNonUserCode()> _
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
Try
If disposing AndAlso components IsNot Nothing Then
components.Dispose()
End If
Finally
MyBase.Dispose(disposing)
End Try
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
<System.Diagnostics.DebuggerStepThrough()> _
Private Sub InitializeComponent()
Me.components = New System.ComponentModel.Container()
Me.Label1 = New System.Windows.Forms.Label()
Me.Label2 = New System.Windows.Forms.Label()
Me.Label3 = New System.Windows.Forms.Label()
Me.Label4 = New System.Windows.Forms.Label()
Me.TextBox1 = New System.Windows.Forms.TextBox()
Me.TextBox2 = New System.Windows.Forms.TextBox()
Me.MaskedTextBox1 = New System.Windows.Forms.MaskedTextBox()
Me.ComboBox1 = New System.Windows.Forms.ComboBox()
Me.DataGridView1 = New System.Windows.Forms.DataGridView()
Me.BindingSource1 = New System.Windows.Forms.BindingSource(Me.components)
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'Label1
'
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(9, 9)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(60, 13)
Me.Label1.TabIndex = 0
Me.Label1.Text = "First Name:"
'
'Label2
'
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(160, 9)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(61, 13)
Me.Label2.TabIndex = 1
Me.Label2.Text = "Last Name:"
'
'Label3
'
Me.Label3.AutoSize = True
Me.Label3.Location = New System.Drawing.Point(313, 9)
Me.Label3.Name = "Label3"
Me.Label3.Size = New System.Drawing.Size(52, 13)
Me.Label3.TabIndex = 2
Me.Label3.Text = "Birthdate:"
'
'Label4
'
Me.Label4.AutoSize = True
Me.Label4.Location = New System.Drawing.Point(479, 9)
Me.Label4.Name = "Label4"
Me.Label4.Size = New System.Drawing.Size(61, 13)
Me.Label4.TabIndex = 3
Me.Label4.Text = "Is Working:"
'
'TextBox1
'
Me.TextBox1.Location = New System.Drawing.Point(12, 25)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(100, 20)
Me.TextBox1.TabIndex = 4
'
'TextBox2
'
Me.TextBox2.Location = New System.Drawing.Point(163, 25)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.Size = New System.Drawing.Size(100, 20)
Me.TextBox2.TabIndex = 5
'
'MaskedTextBox1
'
Me.MaskedTextBox1.Location = New System.Drawing.Point(316, 25)
Me.MaskedTextBox1.Mask = "00/00/0000"
Me.MaskedTextBox1.Name = "MaskedTextBox1"
Me.MaskedTextBox1.Size = New System.Drawing.Size(100, 20)
Me.MaskedTextBox1.TabIndex = 6
Me.MaskedTextBox1.TextMaskFormat = System.Windows.Forms.MaskFormat.ExcludePromptAndLiterals
Me.MaskedTextBox1.ValidatingType = GetType(Date)
'
'ComboBox1
'
Me.ComboBox1.Location = New System.Drawing.Point(482, 25)
Me.ComboBox1.Name = "ComboBox1"
Me.ComboBox1.Size = New System.Drawing.Size(100, 21)
Me.ComboBox1.TabIndex = 7
'
'DataGridView1
'
Me.DataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize
Me.DataGridView1.Location = New System.Drawing.Point(12, 52)
Me.DataGridView1.Name = "DataGridView1"
Me.DataGridView1.Size = New System.Drawing.Size(570, 263)
Me.DataGridView1.TabIndex = 8
'
'Form1
'
Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
Me.ClientSize = New System.Drawing.Size(594, 327)
Me.Controls.Add(Me.DataGridView1)
Me.Controls.Add(Me.ComboBox1)
Me.Controls.Add(Me.MaskedTextBox1)
Me.Controls.Add(Me.TextBox2)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.Label4)
Me.Controls.Add(Me.Label3)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.Label1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataGridView1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.BindingSource1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
Me.PerformLayout()
End Sub
Friend WithEvents Label1 As Label
Friend WithEvents Label2 As Label
Friend WithEvents Label3 As Label
Friend WithEvents Label4 As Label
Friend WithEvents TextBox1 As TextBox
Friend WithEvents TextBox2 As TextBox
Friend WithEvents MaskedTextBox1 As MaskedTextBox
Friend WithEvents ComboBox1 As ComboBox
Friend WithEvents DataGridView1 As DataGridView
Friend WithEvents BindingSource1 As BindingSource
End Class
Initial load -
Filtering on First Name using 'ar' -Filtering on Last Name using 14 --saige-
And yet another implementation using dynamic expression based filtering -
-saige-
Imports System.ComponentModel
Imports System.Globalization
Imports System.Linq.Expressions
Imports System.Runtime.CompilerServices
Public Class Form1
Private IsLoading As Boolean
Private filter As Func(Of Person, Boolean)
Private data As List(Of Person)
Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
IsLoading = True
data = New List(Of Person)(From i In Enumerable.Range(0, 20) Select New Person() With {.ID = i, .FirstName = String.Format("{0}{1}", If(i Mod 5 = 0, "Paul", If(i Mod 4 = 0, "Larry", If(i Mod 3 = 0, "Susan", If(i Mod 2 = 0, "Mary", "Peter")))), i), .LastName = String.Format("LastName{0}", i), .BirthDate = Today.AddYears(-(9 * i)), .IsWorking = i Mod 2 = 0})
BindingSource1.DataSource = data
DataGridView1.DataSource = BindingSource1
ComboBox1.Items.AddRange(New Object() {"None", "False", "True"})
ComboBox1.SelectedIndex = 0
IsLoading = False
End Sub
Private Function GetFilter() As Func(Of Person, Boolean)
GetFilter = Nothing
Dim filters As New List(Of Filter)
Try
If Not String.IsNullOrWhiteSpace(TextBox1.Text) Then
filters.Add(New Filter() With {.PropertyName = "FirstName", .Operation = Operation.Contains, .Value = TextBox1.Text})
End If
If Not String.IsNullOrWhiteSpace(TextBox2.Text) Then
filters.Add(New Filter() With {.PropertyName = "LastName", .Operation = Operation.Contains, .Value = TextBox2.Text})
End If
If Not String.IsNullOrWhiteSpace(MaskedTextBox1.Text) Then
filters.Add(New Filter() With {.PropertyName = "BirthDate", .Operation = Operation.Equals, .Value = Date.ParseExact(MaskedTextBox1.Text, "ddMMyyyy", CultureInfo.InvariantCulture)})
End If
If Not String.IsNullOrWhiteSpace(ComboBox1.Text) AndAlso Not ComboBox1.Text.Equals("none", StringComparison.OrdinalIgnoreCase) Then
filters.Add(New Filter() With {.PropertyName = "IsWorking", .Operation = Operation.Equals, .Value = Convert.ToBoolean(ComboBox1.Text)})
End If
Catch ex As Exception
Finally
GetFilter = filters.GetExpression(Of Person)()?.Compile()
' This is using the null-conditional operator - '?.'; using it is the same as
' GetFilter = If(filters.Count = 0, Nothing, filters.GetExpression(Of Person)().Compile())
End Try
Return GetFilter
End Function
Private Sub OnTextChanged(sender As Object, e As EventArgs) Handles TextBox1.TextChanged, TextBox2.TextChanged, MaskedTextBox1.TextChanged
If Not IsLoading Then
filter = GetFilter()
If filter Is Nothing Then
BindingSource1.DataSource = data
Else
BindingSource1.DataSource = data.Where(filter)
End If
End If
End Sub
Private Sub OnSelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged
If Not IsLoading Then
filter = GetFilter()
If filter Is Nothing Then
BindingSource1.DataSource = data
Else
BindingSource1.DataSource = data.Where(filter)
End If
End If
End Sub
End Class
Class Person
Public Property ID() As Integer
Public Property FirstName() As String
Public Property LastName() As String
Public Property BirthDate() As Date
Public Property IsWorking() As Boolean
End Class
Class Filter
Public Property PropertyName() As String
Public Property Operation() As Operation
Public Property Value() As Object
End Class
Enum Operation
Equals
GreaterThan
LessThan
GreaterThanOrEqual
LessThanOrEqual
Contains
StartsWith
EndsWith
End Enum
Module Extensions
<Extension()>
Public Function GetExpression(Of T)(param As ParameterExpression, filter As Filter)
Dim member As MemberExpression = Expression.Property(param, filter.PropertyName)
Dim constant As ConstantExpression = Expression.Constant(filter.Value)
Select Case filter.Operation
Case Operation.Equals
Return Expression.Equal(member, constant)
Case Operation.GreaterThan
Return Expression.GreaterThan(member, constant)
Case Operation.GreaterThanOrEqual
Return Expression.GreaterThanOrEqual(member, constant)
Case Operation.LessThan
Return Expression.LessThan(member, constant)
Case Operation.LessThanOrEqual
Return Expression.LessThanOrEqual(member, constant)
Case Operation.Contains
Return Expression.Call(member, GetType(String).GetMethod("Contains"), constant)
Case Operation.StartsWith
Return Expression.Call(member, GetType(String).GetMethod("StartsWith", New Type() {GetType(String)}), constant)
Case Operation.EndsWith
Return Expression.Call(member, GetType(String).GetMethod("EndsWith", New Type() {GetType(String)}), constant)
Case Else
Return Nothing
End Select
End Function
<Extension()>
Public Function GetExpression(Of T)(filters As IList(Of Filter)) As Expression(Of Func(Of T, Boolean))
If filters.Count = 0 Then Return Nothing
Dim param As ParameterExpression = Expression.Parameter(GetType(T), "t")
Dim exp As Expression = Nothing
If filters.Count = 1 Then
exp = param.GetExpression(Of T)(filters(0))
ElseIf (filters.Count = 2) Then
exp = param.GetExpression(Of T)(filters(0), filters(1))
Else
While filters.Count > 0
Dim f1 = filters(0)
Dim f2 = filters(1)
If exp Is Nothing Then
exp = param.GetExpression(Of T)(filters(0), filters(1))
Else
exp = Expression.AndAlso(exp, param.GetExpression(Of T)(filters(0), filters(1)))
End If
filters.Remove(f1)
filters.Remove(f2)
If filters.Count = 1 Then
exp = Expression.AndAlso(exp, param.GetExpression(Of T)(filters(0)))
filters.RemoveAt(0)
End If
End While
End If
Return Expression.Lambda(Of Func(Of T, Boolean))(exp, param)
End Function
<Extension()>
Private Function GetExpression(Of T)(param As ParameterExpression, f1 As Filter, f2 As Filter)
Return Expression.AndAlso(param.GetExpression(Of T)(f1), param.GetExpression(Of T)(f2))
End Function
End Module
-saige-
>>*No Points*
it_sage, you desire them!
One thing though - I suggest using
"(BirthDate = Convert('{0}','System.Date
or format DateTime using standard SQL ANSI "yyyy-MM-dd" for date data type
or "yyyy-MM-dd HH:mm:ss" for datetime/datetime2
due to possible difference in system and SQL formats
it_sage, you desire them!
One thing though - I suggest using
"(BirthDate = Convert('{0}','System.Date
or format DateTime using standard SQL ANSI "yyyy-MM-dd" for date data type
or "yyyy-MM-dd HH:mm:ss" for datetime/datetime2
due to possible difference in system and SQL formats
@it_saige, how would you say, there is no need to maintain master? in case of list, I named it master but in your case data is basically master and you apply filters to master to pull the filtered results.
I was without visual studio at the time of writing answer so, I written pseudo code however, the logic remains same.
thanks for working demo, definitely its more helpful.
I was without visual studio at the time of writing answer so, I written pseudo code however, the logic remains same.
thanks for working demo, definitely its more helpful.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial