Home > front end >  wpf datagrid combo box binding to database
wpf datagrid combo box binding to database

Time:09-06

I want my DataGrid have ComboBox with default value for example I have a column for Genre:

  1. Action
  2. Drama
  3. Comedy

I Want ComboBox Show those data and then select the item in database (for example in database Genre is Drama.

  1. I use WPF Net 6 with EF Sqlite for manage Database.

  2. in Database Class I set Genre as string.

  3. DataGrid of other Column I use Something like this:

     <DataGridTextColumn Header="ID" Binding="{Binding Path=ID,UpdateSourceTrigger=PropertyChanged}"/>
    
  4. in Code Behind: DgTest.ItemsSource=db.Test.ToList();

CodePudding user response:

i would strongly advise to use MVVM pattern as it is a best practice for WPF applications. It could look like this

Boilerplate

public abstract class ObservableObject : INotifyPropertyChanged
{
    public event PropertyChangedEventHandler PropertyChanged;

    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
    protected virtual void SetValue<T>(ref T field, T value, [CallerMemberName] string propertyName = null)
    {
        field = value;
        OnPropertyChanged(propertyName);
    }
}

public class DelegateCommand : ICommand
{
    private readonly Action _execute;
    private readonly Func<bool> _canExecute;

    public DelegateCommand(Action execute, Func<bool> canExecute = null)
    {
        if (execute is null)
            throw new ArgumentNullException(nameof(execute));

        _execute = execute;
        _canExecute = canExecute ?? DefaultCanExecute;
    }

    private bool DefaultCanExecute() => true;

    public event EventHandler CanExecuteChanged;

    public bool CanExecute()
    {
        return _canExecute();
    }

    public void Execute()
    {
        _execute();
    }

    public void RaiseCanExecuteChanged()
    {
        CanExecuteChanged?.Invoke(this, EventArgs.Empty);
    }

    bool ICommand.CanExecute(object parameter)
    {
        return CanExecute();
    }

    void ICommand.Execute(object parameter)
    {
        Execute();
    }
}

public class DelegateCommand<T> : ICommand
{
    private readonly Action<T> _execute;
    private readonly Func<T, bool> _canExecute;

    public DelegateCommand(Action<T> execute) : this(execute, null) { }
    public DelegateCommand(Action<T> execute, Func<T, bool> canExecute)
    {
        if (execute is null)
            throw new ArgumentNullException(nameof(execute));

        _execute = execute;
        _canExecute = canExecute ?? DefaultCanExecute;
    }

    private bool DefaultCanExecute(T _) => true;

    public event EventHandler CanExecuteChanged;

    public bool CanExecute(T parameter)
    {
        return _canExecute(parameter);
    }

    public void Execute(T parameter)
    {
        _execute(parameter);
    }

    public void RaiseCanExecuteChanged()
    {
        CanExecuteChanged?.Invoke(this, EventArgs.Empty);
    }

    bool ICommand.CanExecute(object parameter)
    {
        return CanExecute((T)parameter);
    }

    void ICommand.Execute(object parameter)
    {
        Execute((T)parameter);
    }
}

Model

public class Movie
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Genre { get; set; }
}

ViewModel

public class MoviesViewModel : ObservableObject
{
    private readonly MyDbContext _dbContext;

    private Task _runningQuery;
    private CancellationTokenSource _runningQueryCancellation;
    private const int _pageSize = 100;

    public MoviesViewModel()
    {
        _dbContext = new MyDbContext();

        _dbContext.Movies.Add(new Movie { Name = "foo", Genre = "Genre1" });
        _dbContext.Movies.Add(new Movie { Name = "bar", Genre = "Genre1" });
        _dbContext.Movies.Add(new Movie { Name = "baz", Genre = "Genre2" });
        _dbContext.SaveChanges();

        ReloadCommand = new DelegateCommand(ReloadMovies);
        NextPageCommand = new DelegateCommand(() => Page  , () => ((Page   1) * _pageSize) < _movieCount);
        PreviousPageCommand = new DelegateCommand(() => Page--, () => Page > 0);
        _runningQuery = LoadGenresAndCount();
    }

    private async Task LoadGenresAndCount()
    {
        try
        {
            IsBusy = true;
            var genres = await _dbContext.Movies.Select(m => m.Genre).Distinct().OrderBy(g => g).ToListAsync();
            genres.Insert(0, null);   // add option for no Genre selected
            Genres = genres;
            MovieCount = await _dbContext.Movies.CountAsync();
            ReloadMovies();
        }
        catch (Exception ex)
        {
            Error = $"Error while loading {ex}";
        }
    }

    public DelegateCommand ReloadCommand { get; }
    public DelegateCommand NextPageCommand { get; }
    public DelegateCommand PreviousPageCommand { get; }

    // properties use SetValue to tell the view to update itself when the value changes
    private bool _isBusy;
    public bool IsBusy { get { return _isBusy; } set { SetValue(ref _isBusy, value); } }

    private string _error;
    public string Error { get { return _error; } private set { SetValue(ref _error, value); } }

    private IEnumerable<string> _genres;
    public IEnumerable<string> Genres { get => _genres; private set => SetValue(ref _genres, value); }

    private string _selectedGenre;
    public string SelectedGenre
    {
        get { return _selectedGenre; }
        set { SetValue(ref _selectedGenre, value); ReloadMovies(); }
    }

    private int _movieCount;
    public int MovieCount { get => _movieCount; private set => SetValue(ref _movieCount, value); }

    private IEnumerable<Movie> _movies;
    public IEnumerable<Movie> Movies { get => _movies; private set => SetValue(ref _movies, value); }

    private int _page;
    public int Page
    {
        get { return _page; }
        private set
        {
            SetValue(ref _page, value);
            NextPageCommand.RaiseCanExecuteChanged();
            PreviousPageCommand.RaiseCanExecuteChanged();
            ReloadMovies();
        }
    }

    private void ReloadMovies()
    {
        IsBusy = true;
        Error = null;
        // cancel the running query because the filters have changed
        _runningQueryCancellation?.Cancel();
        _runningQueryCancellation = new CancellationTokenSource();
        
        // having selectedGenre as parameter so it doesn't change for the asynchron operation
        _runningQuery = ReloadMoviesAsync(_runningQuery, SelectedGenre, _runningQueryCancellation.Token);
    }
    private async Task ReloadMoviesAsync(Task queryBefore, string selectedGenre, CancellationToken token)
    {
        // wait for running query to finish to prevent parallel access to the context which is not thread safe
        if (queryBefore != null)
            await queryBefore;
        try
        {
            IQueryable<Movie> query = _dbContext.Movies;
            if (selectedGenre != null)
            {
                query = query.Where(m => m.Genre == selectedGenre);
            }
            Movies = await query
                .OrderBy(m => m.Name)                           // usefull and nessesary for skip take
                .Skip(_page * _pageSize).Take(_pageSize)        // only load items of the page
                .AsNoTracking()                                 // tell ef to not track changes
                .ToListAsync(token);
        }
        catch (Exception ex)
        {
            Error = $"Error while loading {ex}";
        }
        IsBusy = false;
    }
}

View

<Window x:Class="SomeApplication.MoviesView"
        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:SomeApplication"
        mc:Ignorable="d"
        Title="Movies" Height="450" Width="800">
    <FrameworkElement.DataContext>
        <local:MoviesViewModel/>
    </FrameworkElement.DataContext>
    <FrameworkElement.Resources>
        <BooleanToVisibilityConverter x:Key="TrueToVisible"/>
    </FrameworkElement.Resources>
    <DockPanel>
        <!--header-->
        <StackPanel Orientation="Horizontal" DockPanel.Dock="Top">
            <Button Content="Reload" Command="{Binding ReloadCommand}"/>
            <ComboBox ItemsSource="{Binding Genres}" SelectedItem="{Binding SelectedGenre}"/>
        </StackPanel>
        <!--footer-->
        <StackPanel Orientation="Horizontal" HorizontalAlignment="Center" DockPanel.Dock="Bottom">
            <TextBlock Text="Page"/>
            <TextBlock Text="{Binding Page}"/>
            <TextBlock Text="Total Movies" Margin="10,0,0,0"/>
            <TextBlock Text="{Binding MovieCount}"/>
            <Button Command="{Binding PreviousPageCommand}" Content="&lt;" Margin="5"/>
            <Button Command="{Binding NextPageCommand}" Content="&gt;" Margin="5"/>
        
            <!--busy Indicator-->
            <TextBlock Text="Loading..." Visibility="{Binding IsBusy, Converter={StaticResource TrueToVisible}}"/>
        </StackPanel>
        <DataGrid ItemsSource="{Binding Movies}" AutoGenerateColumns="False" HorizontalAlignment="Stretch" CanUserAddRows="False">
            <DataGrid.Columns>
                <DataGridTextColumn Header="Id" Binding="{Binding Id}"/>
                <DataGridTextColumn Header="Name" Binding="{Binding Name}"/>
                <DataGridComboBoxColumn Header="Genre" SelectedItemBinding="{Binding Genre}" ItemsSource="{Binding DataContext.Genres, RelativeSource={RelativeSource AncestorType=DataGrid}}"/>
            </DataGrid.Columns>
            <DataGrid.Style>
                <Style TargetType="DataGrid">
                    <Setter Property="Visibility" Value="Collapsed"/>
                    <Style.Triggers>
                        <DataTrigger Binding="{Binding Error}" Value="{x:Null}">
                            <Setter Property="Visibility" Value="Visible"/>
                        </DataTrigger>
                    </Style.Triggers>
                </Style>
            </DataGrid.Style>
        </DataGrid>
        <TextBlock Text="{Binding Error}" HorizontalAlignment="Center" VerticalAlignment="Center">
            <TextBlock.Style>
                <Style TargetType="TextBlock">
                    <Style.Triggers>
                        <DataTrigger Binding="{Binding Error}" Value="{x:Null}">
                            <Setter Property="Visibility" Value="Collapsed"/>
                        </DataTrigger>
                    </Style.Triggers>
                </Style>
            </TextBlock.Style>
        </TextBlock>
    </DockPanel>
</Window>
  • Related