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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
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-
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trial
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.
I didn't say that you would not need to maintain a *master* list. I had stated that you don't need to maintain *separate* (or multiple) lists which is what your psuedo code alludes to.
-saige-
-saige-
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET
From novice to tech pro — start learning today.
-
Microsoft ApplicationsCertification: MOS Microsoft Office Specialist - Word 2016 Part … 134 lessons
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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,