I would like to implement Excel like filter to datagrid by three columns.
Here is my code:
using System.Data.Odbc;
using System.Windows;
using System.Data;
namespace DB_inspector
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
}
private void Button_Click(object sender, RoutedEventArgs e)
{
OdbcConnection dbConnection = new OdbcConnection("Driver={Pervasive ODBC Client Interface};ServerName=875;dbq=@DBFS;Uid=Username;Pwd=Password;");
string strSql = "select NRO,NAME,NAMEA,NAMEB,ADDRESS,POSTA,POSTN,POSTADR,COMPANYN,COUNTRY,ID,ACTIVE from COMPANY";
dbConnection.Open();
OdbcDataAdapter dadapter = new OdbcDataAdapter();
dadapter.SelectCommand = new OdbcCommand(strSql, dbConnection);
DataTable table = new DataTable("COMPANY");
dadapter.Fill(table);
DataGrid1.DataContext = table;
DataGrid1.ItemsSource = table.DefaultView;
dadapter.Update(table);
dbConnection.Close();
}
private void TextBox_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
DataView dv = DataGrid1.ItemsSource as DataView;
string filter = NameSearch.Text;
if (string.IsNullOrEmpty(filter))
dv.RowFilter = null;
else
dv.RowFilter = string.Format("NAME Like '%{0}%'", filter);
}
private void TextBox_TextChanged_1(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
DataView dv = DataGrid1.ItemsSource as DataView;
string filter = ActiveSearch.Text;
if (string.IsNullOrEmpty(filter))
dv.RowFilter = null;
else
dv.RowFilter = string.Format("ACTIVE Like '%{0}%'", filter);
}
private void CustomerNumberSearch_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
DataView dv = DataGrid1.ItemsSource as DataView;
string filter = CustomerNumberSearch.Text;
if (string.IsNullOrEmpty(filter))
dv.RowFilter = null;
else
dv.RowFilter = string.Format("NRO Like '%{0}%'", filter); //this should be "Begins with" not "Like"
}
}
}
XAML
<Window x:Class="DB_inspector.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
xmlns:local="clr-namespace:DB_inspector"
mc:Ignorable="d"
Title="DB database inspector" Height="595.404" Width="1005.571">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="11*"/>
<RowDefinition Height="553*"/>
</Grid.RowDefinitions>
<DataGrid x:Name="DataGrid1" Margin="0,51,0,0" Grid.Row="1"/>
<Image Height="41" Margin="847,10,10,502" Width="141" Source="Logo_small.jpg" Grid.Row="1" HorizontalAlignment="Right" VerticalAlignment="Top"/>
<TextBox x:Name="NameSearch" HorizontalAlignment="Left" Height="23" Margin="77,14,0,0" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="409" Grid.Row="1" TextChanged="TextBox_TextChanged"/>
<Button Content="Refrsh" HorizontalAlignment="Left" Height="23" Margin="736,14,0,0" VerticalAlignment="Top" Width="72" Click="Button_Click" Grid.Row="1" Background="#FF51C951" BorderBrush="{x:Null}" Foreground="White"/>
<TextBox x:Name="ActiveSearch" HorizontalAlignment="Left" Height="23" Margin="502,14,0,0" Grid.Row="1" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="66" TextChanged="TextBox_TextChanged_1"/>
<TextBox x:Name="CustomerNumberSearch" HorizontalAlignment="Left" Height="23" Margin="580,14,0,0" Grid.Row="1" TextWrapping="Wrap" Text="" VerticalAlignment="Top" Width="82" TextChanged="CustomerNumberSearch_TextChanged"/>
<ProgressBar x:Name="ProgressBar" HorizontalAlignment="Left" Height="10" Margin="0,1,0,0" VerticalAlignment="Top" Width="998" BorderBrush="{x:Null}" Background="{x:Null}"/>
<Button Content="Button" HorizontalAlignment="Left" Height="23" Margin="680,14,0,0" Grid.Row="1" VerticalAlignment="Top" Width="51" Click="Button_Click_1"/>
</Grid>
</Window>
I would like to implement filters for column NRO and ACTIVE so that during load of data in column NRO will be numbers starting with 3 only and in column ACTIVE values with 1 only. In ACTIVE there are values 1 and 0 (1 active, 0 not active).
My current setup is filtering only by one criteria. How to make all three criteria function correct?
you need to combine multiple conditions in one Filter string:
private Dictionary<string, string> _conditions = new Dictionary<string, string>();
private void UpdateFilter()
{
var activeConditions = _conditions.Where(c => c.Value != null).Select(c => "(" + c.Value + ")");
DataView dv = DataGrid1.ItemsSource as DataView;
dv.RowFilter = string.Join(" AND ", activeConditions);
}
private void TextBox_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
string filter = NameSearch.Text;
if (string.IsNullOrEmpty(filter))
_conditions["name"] = null;
else
_conditions["name"] = string.Format("NAME Like '%{0}%'", filter);
UpdateFilter();
}
private void TextBox_TextChanged_1(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
string filter = ActiveSearch.Text;
if (string.IsNullOrEmpty(filter))
_conditions["active"] = null;
else
_conditions["active"] = string.Format("ACTIVE Like '%{0}%'", filter);
UpdateFilter();
}
private void CustomerNumberSearch_TextChanged(object sender, System.Windows.Controls.TextChangedEventArgs e)
{
string filter = CustomerNumberSearch.Text;
if (string.IsNullOrEmpty(filter))
_conditions["nro"] = null;
else
_conditions["nro"] = string.Format("NRO Like '%{0}%'", filter); //this should be "Begins with" not "Like"
UpdateFilter();
}