Fill Observable collection with EF6, MVVM inside wpf

Question

I've got a SQL Server database up and running, initialized with EF6. I'm quite new to all three of these subjects and am struggling to fill up a datagrid using the SQL database.

In short, I realize I need to

1. Fill up an observable collection with data from the already existing database (Seeing as what the datagrid needs to do, I've determined an observable collection would
   be my best choice)
2. Connect that collection to the datagrid
3. Bind all the columns for said datagrid

So far this is what I have.

View1.xaml

<DataGrid Grid.Row="2" Grid.RowSpan="3" x:Name="GameDatagrid" AutoGenerateColumns="False" ItemsSource="{Binding GameCollection}">
    <DataGrid.Columns>
        <DataGridTextColumn Header="GameID" Binding="{Binding GameID, Mode=OneWay}" Width="SizeToHeader" />
        <DataGridTextColumn Header="Game Name" Binding="{Binding Name, Mode=OneWay}" Width="SizeToHeader"/>
    </DataGrid.Columns>
</DataGrid>

View1.xaml.cs

public partial class View1 : UserControl
{
    public ObservableCollection<Game> GameCollection { get; set; }


    public View1()
    {
        InitializeComponent();
        ObservableCollection<Game> GameCollection = new ObservableCollection<Game>();

        GameDatagrid.ItemsSource = GameCollection;

    }

And the object class

public class Game
{
    public int GameID { get; set; }
    public string Name { get; set; }
    public ObservableCollection<Game> GameCollection { get; set;}
}

The attached viewmodel doesn't have anything of relevance as of yet.

I do realize that it probably shows nothing because the collection is empty, but what is the most efficient MVVM way to fill one up and use?

Answer 1

Hi,
try following demo:

XAML:

<Window x:Class="WpfApp1.Window043"  
        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:WpfApp043"  
        mc:Ignorable="d"  
        Title="Load data EF 6" Height="450" Width="800">  
  <Window.DataContext>  
    <local:ViewModel/>  
  </Window.DataContext>  
    <StackPanel>  
    <Button Content="Create data" Command="{Binding Cmd}" CommandParameter="Create" Margin="5"/>  
    <Button Content="Load data" Command="{Binding Cmd}" CommandParameter="Load" Margin="5"/>  
    <DataGrid AutoGenerateColumns="False" ItemsSource="{Binding GameCollection}">  
      <DataGrid.Columns>  
        <DataGridTextColumn Header="GameID" Binding="{Binding GameID, Mode=OneWay}" Width="SizeToHeader" />  
        <DataGridTextColumn Header="Game Name" Binding="{Binding Name, Mode=OneWay}" Width="SizeToHeader"/>  
      </DataGrid.Columns>  
    </DataGrid>  
  </StackPanel>  
</Window>  

And classes:

using System;  
using System.ComponentModel;  
using System.Data;  
using System.Data.SqlClient;  
using System.Linq;  
using System.Windows;  
using System.Windows.Data;  
using System.Windows.Input;  
  
namespace WpfApp043  
{  
  public class ViewModel :INotifyPropertyChanged  
  {  
    private CollectionViewSource cvs = new CollectionViewSource();  
    public ICollectionView GameCollection { get => cvs.View; }  
  
    public ICommand Cmd { get => new RelayCommand(CmdExec); }  
  
    private void CmdExec(object parameter)  
    {  
      switch (parameter.ToString())  
      {  
        case "Create":  
          CreateData();  
          break;  
        case "Load":  
          cvs.Source = from item in (new WpfApp1.Window043DBEntities1()).Game1.AsEnumerable() select item;  
          OnPropertyChanged(nameof(GameCollection));  
          break;  
        default:  
          break;  
      }  
    }  
  
    private void CreateData()  
    {  
      using (SqlConnection cn = new SqlConnection(WpfApp1.Properties.Settings.Default.cnSQL))  
      {  
        cn.Open();  
        using (SqlCommand cmd = new SqlCommand() { Connection = cn })  
        {  
          // delete previous table in SQL Server 2016 and above  
          cmd.CommandText = "DROP TABLE IF EXISTS Game1;";  
          //' delete previous table in versions  
          //cmd.CommandText = "If OBJECT_ID('Game1', 'U') IS NOT NULL DROP TABLE IF EXISTS Table1;"  
          cmd.ExecuteNonQuery();  
          // Create Tables  
          cmd.CommandText = "CREATE Table Game1([GameID] Integer Identity, [Name] nvarchar(50), CONSTRAINT [PK_Game1] PRIMARY KEY ([GameID]));";  
          cmd.ExecuteNonQuery();  
          // Insert data records    
          cmd.CommandText = "INSERT INTO Game1([Name]) VALUES('Game 1');INSERT INTO Game1([Name]) VALUES('Game 2');INSERT INTO Game1([Name]) VALUES('Game 3');";  
          cmd.ExecuteNonQuery();  
        }  
      }  
    }  
  
    #region INotifyPropertyChanged Members  
    public event PropertyChangedEventHandler PropertyChanged;  
    public void OnPropertyChanged(string propertyName) =>  
      PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));  
    #endregion  
  }  
  
  public class Game  
  {  
    public int GameID { get; set; }  
    public string Name { get; set; }  
  }  
  
  public class RelayCommand : ICommand  
  {  
    private readonly Predicate<object> _canExecute;  
    private readonly Action<object> _action;  
    public RelayCommand(Action<object> action) { _action = action; _canExecute = null; }  
    public RelayCommand(Action<object> action, Predicate<object> canExecute) { _action = action; _canExecute = canExecute; }  
    public void Execute(object o) => _action(o);  
    public bool CanExecute(object o) => _canExecute == null ? true : _canExecute(o);  
    public event EventHandler CanExecuteChanged  
    {  
      add { CommandManager.RequerySuggested += value; }  
      remove { CommandManager.RequerySuggested -= value; }  
    }  
  }  
}  

Result: