DataGrid not populating with binding

Question

I am trying to populate a DataGrid with an SQL query, and then be able to filter the datagrid. So far I have this:

XAML

<Window x:Name="ResultsWindow" x:Class="PixsellSheet.PixsellOrders"
    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:PixsellSheet"
    mc:Ignorable="d" Height="721.175" Width="1549.21" Title="edit" WindowStartupLocation="CenterScreen">
<Grid>
    <DataGrid x:Name="Grid" HorizontalAlignment="Stretch" Height="Auto" Margin="20,55,20,40" VerticalAlignment="Stretch" Width="Auto" ItemsSource="{Binding DataGridColletion}">
        <DataGrid.Resources>
            <ContextMenu x:Key="DataGridColumnHeaderContextMenu">
                <MenuItem Header="Filter" Click="MenuItem_Click"/>
            </ContextMenu>
            <Style TargetType="{x:Type DataGridColumnHeader}">
                <Setter Property="ContextMenu" Value="{StaticResource DataGridColumnHeaderContextMenu}"/>
            </Style>
        </DataGrid.Resources>
    </DataGrid>
    <Button x:Name="BtnBack" HorizontalAlignment="Left" Margin="20,10,0,0" VerticalAlignment="Top" Width="30" Click="BtnBack_Click" Padding="20,0,5,0">
        <Button.Template>
            <ControlTemplate TargetType="{x:Type Button}">
                <Grid>
                    <Image Name="Normal" Source="back.png"/>
                    <Image Name="Pressed" Source="back_pressed.png"/>
                </Grid>
                <ControlTemplate.Triggers>
                    <Trigger Property="IsPressed" Value="True">
                        <Setter TargetName="Normal" Property="Visibility" Value="Hidden"/>
                        <Setter TargetName="Pressed" Property="Visibility" Value="Visible"/>
                    </Trigger>
                    <Trigger Property="IsPressed" Value="False">
                        <Setter TargetName="Normal" Property="Visibility" Value="Visible"/>
                        <Setter TargetName="Pressed" Property="Visibility" Value="Hidden"/>
                    </Trigger>
                </ControlTemplate.Triggers>
            </ControlTemplate>
        </Button.Template>
    </Button>
    <TextBox Height="27" Margin="0,10,20,0" TextWrapping="Wrap" VerticalAlignment="Top" HorizontalAlignment="Right" Width="524" Name="FilterBox" Padding="5,0,20,0"/>
    <Label Content="Filter:" HorizontalAlignment="Right" Margin="0,10,550,0" VerticalAlignment="Top" Padding="5,0,5,0"/>
    <Grid HorizontalAlignment="Left" Height="25" Margin="60,10,0,0" VerticalAlignment="Top" Width="20" />
</Grid>

C#

public partial class PixsellOrders : Window, INotifyPropertyChanged
{
    public ICollectionView _dataGridCollection;
    private string _filterString;

    public ICollectionView DataGridCollection
    {
        get { return _dataGridCollection; }
        set { _dataGridCollection = value; NotifyPropertyChanged("DataGridCollection"); }
    }

    public PixsellOrders(string windowTitle)
    {
        InitializeComponent();

        string query = "";

        ResultsWindow.Title = windowTitle;

        Console.WriteLine(windowTitle);

        if (windowTitle == "PixSell Orders All")
        {
            query = "EXEC Reporting.dbo.Pixsell_Orders_All";
        }
        else if (windowTitle == "PixSell Orders Eday")
        {
            query = "EXEC Reporting.dbo.Pixsell_Orders_Eday";
        }

        Console.WriteLine(query);

        try
        {
            DataTable pixsellOrders = SqlConnect(query);

            foreach (DataColumn column in pixsellOrders.Columns)
            {
                column.ReadOnly = true;

                if (column.ColumnName == "Person" && windowTitle != "PixSell Orders All")
                {
                    pixsellOrders.Columns["Person"].ReadOnly = false;
                }
                else if (column.ColumnName == "Sales Notes" && windowTitle != "PixSell Orders All")
                {
                    pixsellOrders.Columns["Sales Notes"].ReadOnly = false;
                }
            }

            DataGridCollection = CollectionViewSource.GetDefaultView(pixsellOrders.AsEnumerable());
            DataGridCollection.Filter = new Predicate<object>(Filter);

            pixsellOrders.ColumnChanged += new DataColumnChangeEventHandler(Column_Changed);

        }
        catch (SqlException sqlEr)
        {
            Console.WriteLine(sqlEr);
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;

    private void NotifyPropertyChanged(string property)
    {
        if (PropertyChanged != null)
        {
            PropertyChanged(this, new PropertyChangedEventArgs(property));
            _dataGridCollection.Refresh();
        }
    }

    public DataTable SqlConnect(string query)
    {
        SqlConnection ohsql1;
        string sqlQuery = query;

        ohsql1 = new SqlConnection("Data Source=OHSQL1;Initial Catalog=Reporting;Integrated Security = true");


        DataTable table = new DataTable();

        try
        {
            //connect
            ohsql1.Open();

            //fill datatable with results
            SqlDataAdapter a = new SqlDataAdapter(sqlQuery, ohsql1);

            //fill table
            a.Fill(table);

            //kill connection
            a.Dispose();
            ohsql1.Close();

        }
        catch (SqlException e)
        {
            Console.WriteLine("SQL ERROR: " + e);
        }

        return table;
    }

    private void MenuItem_Click(object sender, RoutedEventArgs e)
    {
        _filterString = FilterBox.Text;

        if (_filterString == "")
        {
            Console.WriteLine("no filter");
            return;
        }
        else
        {
            Console.WriteLine(_filterString);
            FilterCollection();
        }
    }

    private void FilterCollection()
    {
        if (_dataGridCollection != null)
        {
            _dataGridCollection.Refresh();
        }
    }

    private bool Filter(object obj)
    {
        if (obj is DataRow data)
        {
            if (!string.IsNullOrEmpty(_filterString))
            {
                return data["CUNAME"].ToString().Contains(_filterString);
            }
            else
            {
                return true;
            }

        }
        return false;
    }

The "CUNAME" column specified is just a test, eventually I want to have it know which column the filter button was pressed on.

The problem I am getting is that the DataGrid is returning empty. When I do Grid.ItemsSource = pixsellOrders.DefaultView (or something to that effect, can't remember the exact syntax) it works fine and populates the grid.

I have tried changing to a List which would definitely be IEnumerable but that wasn't populating the data grid either. Adding AutoGenerateColumns (either to true or false) has no effect. No errors are being shown in the output. Commenting out all the filter parts also has no effect. Removing the Context menu also has no effect. Removing AsEnumerable() has no effect on the grid population but throws and error at DataGridCollection.Filter.

Can anyone see where this is going wrong? And if you can advise how I might go about getting the column name to check (rather than hard coding all columns) that we be really helpful as well)

Thank you in advance

Answer 1

Set the DataContext of the Window to itself:

public PixsellOrders(string windowTitle)
{
    InitializeComponent();
    DataContext = this;

    //...
}