question

BoomBa-2054 avatar image
0 Votes"
BoomBa-2054 asked HuiLiu-MSFT answered

How to do simple CRUD operation on SQLite database with MVVM pattern

I'm totally new to MVVM and I've been going through a lot of online posts regarding how do I implement CRUD operations and properly bind SQLite database data to WPF controls like Datagrid, Combobox, Textbox, Button etc. but struggling to find what I need. Also, for someone who is new to MVVM pattern it seems pretty intimidating to say the least.

Anyways, coming to the point, I've found this post online and tried my best to make it work for SQLite databases but struggling at the moment.

My project structure looks like this:

190975-image.png

and the relevant files as follows:

StudentRepository.cs

 using System;
 using System.Collections.Generic;
 using System.Data;
 using System.Data.Common;
 using System.Data.SQLite;
 using System.Linq;
 using TestWpfMVVM.Model;
    
 namespace TestWpfMVVM.DataAccess
 {
     public class StudentRepository
     {
    
         public void ExecuteWrite(string query, Dictionary<string, object> args)
         {
    
             //setup the connection to the database
             using (var con = new SQLiteConnection(@"Data Source=/.Students.db;"))
             {
                 con.Open();
    
                 //open a new command
                 using (var cmd = new SQLiteCommand(query, con))
                 {
                     //set the arguments given in the query
                     foreach (var pair in args)
                     {
                         cmd.Parameters.AddWithValue(pair.Key, pair.Value);
                     }
    
                     cmd.ExecuteNonQuery();
                 }
    
             }
         }
    
         public DataTable Execute(string query, Dictionary<string, object> args)
         {
             if (string.IsNullOrEmpty(query.Trim()))
                 return null;
    
             using (var con = new SQLiteConnection(@"Data Source=/.Students.db;"))
             {
                 con.Open();
                 using (var cmd = new SQLiteCommand(query, con))
                 {
                     foreach (KeyValuePair<string, object> entry in args)
                     {
                         cmd.Parameters.AddWithValue(entry.Key, entry.Value);
                     }
    
                     var da = new SQLiteDataAdapter(cmd);
    
                     var dt = new DataTable();
                     da.Fill(dt);
    
                     da.Dispose();
                     return dt;
                 }
             }
         }
    
         public void AddStudent(Student student)
         {
             const string query = "INSERT INTO tblStud(stdName, stdAge, stdAddress, stdContact) VALUES(@stdName, @stdAge, @stdAddress, @stdContact)";
    
             var args = new Dictionary<string, object>
             {
                 {"@stdName", student.stdName},
                 {"@stdAge", student.stdAge},
                 {"@stdAddress", student.stdAddress},
                 {"@stdContact", student.stdContact}
             };
    
             ExecuteWrite(query, args);
         }
    
         public void UpdateStudent(Student student)
         {
             const string query = "UPDATE tblStud SET stdName = @stdName, stdAge = @stdAge, stdAddress = @stdAddress, stdContact = @stdContact WHERE stdId = @stdId";
    
             var args = new Dictionary<string, object>
             {
                 {"@id", student.stdId},
                 {"@stdName", student.stdName},
                 {"@stdAge", student.stdAge},
                 {"@stdAddress", student.stdAddress},
                 {"@stdContact", student.stdContact}
             };
    
             ExecuteWrite(query, args);
         }
    
         public void RemoveStudent(Student student)
         {
             const string query = "Delete from tblStud WHERE stdId = @stdId";
    
             var args = new Dictionary<string, object>
             {
                 {"@stdId", student.stdId}
             };
    
             ExecuteWrite(query, args);
         }
    
         public Student Get(int id)
         {
             var student = new Student();
    
             var query = "SELECT * FROM tblStud WHERE stdId = @stdId";
    
             var args = new Dictionary<string, object>
             {
                 {"@stdId", student.stdId}
             };
    
             DataTable dt = Execute(query, args);
    
             if (dt == null || dt.Rows.Count == 0)
             {
                 return null;
             }
    
             student = new Student
             {
    
                 stdId = Convert.ToInt32(dt.Rows[0]["stdId"]),
                 stdName = Convert.ToString(dt.Rows[0]["stdName"]),
                 stdAge = Convert.ToInt32(dt.Rows[0]["stdAge"]),
                 stdAddress = Convert.ToString(dt.Rows[0]["stdAddress"]),
                 stdContact = Convert.ToString(dt.Rows[0]["stdContact"])
             };
    
             return student;
         }
    
         public List<Student> GetAll()
         {
             var student = new Student();
    
             var query = "SELECT * FROM tblStud";
    
             var args = new Dictionary<string, object>
             {
                 {"@stdId", student.stdId}
             };
    
             DataTable dt = Execute(query, args);
    
             if (dt == null || dt.Rows.Count == 0)
             {
                 return null;
             }
    
             student = new Student
             {
    
                 stdId = Convert.ToInt32(dt.Rows[0]["stdId"]),
                 stdName = Convert.ToString(dt.Rows[0]["stdName"]),
                 stdAge = Convert.ToInt32(dt.Rows[0]["stdAge"]),
                 stdAddress = Convert.ToString(dt.Rows[0]["stdAddress"]),
                 stdContact = Convert.ToString(dt.Rows[0]["stdContact"])
             };
    
             return student.ToList();
         }
    
     }
 }

Student.cs

 using System;
    
 namespace TestWpfMVVM.Model
 {
     public class Student
     {
         public int stdId { get; set; }
         public string stdName { get; set; }
         public int stdAge { get; set; }
         public string stdAddress { get; set; }
         public string stdContact { get; set; }
     }
 }


StudentRecord.cs

 using System;
 using System.Collections.Generic;
 using System.Collections.ObjectModel;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
 using TestWpfMVVM.ViewModel;
    
 namespace TestWpfMVVM.Model
 {
     public class StudentRecord : ViewModelBase
     {
         private int _id;
         public int Id
         {
             get
             {
                 return _id;
             }
             set
             {
                 _id = value;
                 OnPropertyChanged("Id");
             }
         }
    
         private string _name;
         public string Name
         {
             get
             {
                 return _name;
             }
             set
             {
                 _name = value;
                 OnPropertyChanged("Name");
             }
         }
    
         private int _age;
         public int Age
         {
             get
             {
                 return _age;
             }
             set
             {
                 _age = value;
                 OnPropertyChanged("Age");
             }
         }
    
         private string _address;
         public string Address
         {
             get
             {
                 return _address;
             }
             set
             {
                 _address = value;
                 OnPropertyChanged("Address");
             }
         }
    
         private string _contact;
         public string Contact
         {
             get
             {
                 return _contact;
             }
             set
             {
                 _contact = value;
                 OnPropertyChanged("Contact");
             }
         }
    
         private ObservableCollection<StudentRecord> _studentRecords;
         public ObservableCollection<StudentRecord> StudentRecords
         {
             get
             {
                 return _studentRecords;
             }
             set
             {
                 _studentRecords = value;
                 OnPropertyChanged("StudentRecords");
             }
         }
     }
 }


RelayCommand.cs

 using System;
 using System.Windows.Input;
    
 namespace TestWpfMVVM.ViewModel
 {
     public class RelayCommand : ICommand
     {
         private readonly Action<object> _execute;
         private readonly Predicate<object> _canExecute;
    
         public RelayCommand(Action<object> execute)
             : this(execute, null)
         {
         }
    
         public RelayCommand(Action<object> execute, Predicate<object> canExecute)
         {
             if (execute == null)
                 throw new ArgumentNullException("execute");
             _execute = execute;
             _canExecute = canExecute;
         }
    
         public bool CanExecute(object parameter)
         {
             return _canExecute == null ? true : _canExecute(parameter);
         }
    
         public event EventHandler CanExecuteChanged
         {
             add { CommandManager.RequerySuggested += value; }
             remove { CommandManager.RequerySuggested -= value; }
         }
    
         public void Execute(object parameter)
         {
             _execute(parameter);
         }
     }
 }


StudentViewModel.cs

 using System;
 using System.Collections.ObjectModel;
 using System.Windows;
 using System.Windows.Input;
 using TestWpfMVVM.DataAccess;
 using TestWpfMVVM.Model;
    
 namespace TestWpfMVVM.ViewModel
 {
     public class StudentViewModel
     {
         private ICommand _saveCommand;
         private ICommand _resetCommand;
         private ICommand _editCommand;
         private ICommand _deleteCommand;
         private StudentRepository _repository;
         private Student _studentEntity = null;
         public StudentRecord StudentRecord { get; set; }
    
         public ICommand ResetCommand
         {
             get
             {
                 if (_resetCommand == null)
                     _resetCommand = new RelayCommand(param => ResetData(), null);
    
                 return _resetCommand;
             }
         }
    
         public ICommand SaveCommand
         {
             get
             {
                 if (_saveCommand == null)
                     _saveCommand = new RelayCommand(param => SaveData(), null);
    
                 return _saveCommand;
             }
         }
    
         public ICommand EditCommand
         {
             get
             {
                 if (_editCommand == null)
                     _editCommand = new RelayCommand(param => EditData((int)param), null);
    
                 return _editCommand;
             }
         }
    
         public ICommand DeleteCommand
         {
             get
             {
                 if (_deleteCommand == null)
                     _deleteCommand = new RelayCommand(param => DeleteStudent((int)param), null);
    
                 return _deleteCommand;
             }
         }
    
         public StudentViewModel()
         {
             _studentEntity = new Student();
             _repository = new StudentRepository();
             StudentRecord = new StudentRecord();
             GetAll();
         }
    
         public void ResetData()
         {
             StudentRecord.Name = string.Empty;
             StudentRecord.Id = 0;
             StudentRecord.Address = string.Empty;
             StudentRecord.Contact = string.Empty;
             StudentRecord.Age = 0;
         }
    
         public void DeleteStudent(int id)
         {
             if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)
                 == MessageBoxResult.Yes)
             {
                 try
                 {
                     _repository.RemoveStudent(id);
                     MessageBox.Show("Record successfully deleted.");
                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("Error occured while saving. " + ex.InnerException);
                 }
                 finally
                 {
                     GetAll();
                 }
             }
         }
    
         public void SaveData()
         {
             if (StudentRecord != null)
             {
                 _studentEntity.stdName = StudentRecord.Name;
                 _studentEntity.stdAge = StudentRecord.Age;
                 _studentEntity.stdAddress = StudentRecord.Address;
                 _studentEntity.stdContact = StudentRecord.Contact;
    
                 try
                 {
                     if (StudentRecord.Id <= 0)
                     {
                         _repository.AddStudent(_studentEntity);
                         MessageBox.Show("New record successfully saved.");
                     }
                     else
                     {
                         _studentEntity.stdId = StudentRecord.Id;
                         _repository.UpdateStudent(_studentEntity);
                         MessageBox.Show("Record successfully updated.");
                     }
                 }
                 catch (Exception ex)
                 {
                     MessageBox.Show("Error occured while saving. " + ex.InnerException);
                 }
                 finally
                 {
                     GetAll();
                     ResetData();
                 }
             }
         }
    
         public void EditData(int id)
         {
             var model = _repository.Get(id);
    
             StudentRecord.Id = model.stdId;
             StudentRecord.Name = model.stdName;
             StudentRecord.Age = (int)model.stdAge;
             StudentRecord.Address = model.stdAddress;
             StudentRecord.Contact = model.stdContact;
         }
    
         public void GetAll()
         {
             StudentRecord.StudentRecords = new ObservableCollection<StudentRecord>();
             _repository.GetAll().ForEach(data => StudentRecord.StudentRecords.Add(new StudentRecord()
             {
                 Id = data.stdId,
                 Name = data.stdName,
                 Address = data.stdAddress,
                 Age = Convert.ToInt32(data.stdAge),
                 Contact = data.stdContact
             }));
         }
     }
 }


ViewModelBase.cs

 using System.ComponentModel;
    
 namespace TestWpfMVVM.ViewModel
 {
     public class ViewModelBase : INotifyPropertyChanged
     {
         public event PropertyChangedEventHandler PropertyChanged;
    
         protected void OnPropertyChanged(string propertyName)
         {
             if (PropertyChanged != null)
             {
                 PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
             }
         }
     }
 }


MainWindow.xaml.cs

 using System.Windows;
 using TestWpfMVVM.ViewModel;
    
 namespace TestWpfMVVM.View
 {
     public partial class MainWindow : Window
     {
         public MainWindow()
         {
             InitializeComponent();
             this.DataContext = new StudentViewModel();
         }
     }
 }


MainWindow.xaml

 <Window x:Class="TestWpfMVVM.View.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:TestWpfMVVM.View"
         mc:Ignorable="d"
         Title="MainWindow" Height="450" Width="800">
     <StackPanel Orientation="Vertical">
         <GroupBox Header="Student Form" Margin="10">
             <Grid Height="150">
                 <Grid.RowDefinitions>
                     <RowDefinition Height="1*"/>
                     <RowDefinition Height="1*"/>
                     <RowDefinition Height="1*"/>
                     <RowDefinition Height="1*"/>
                 </Grid.RowDefinitions>
                 <Grid.ColumnDefinitions>
                     <ColumnDefinition Width="100"/>
                     <ColumnDefinition Width="*"/>
                 </Grid.ColumnDefinitions>
                 <Label Content="Name" HorizontalAlignment="Left" 
                        VerticalContentAlignment="Center" Grid.Column="0" Grid.Row="0"/>
                 <TextBox Grid.Row="0" Grid.Column="1" x:Name="TextBoxName" Height="27" 
                        Text="{Binding Path=StudentRecord.Name, Mode=TwoWay}"  Margin="5"  Width="300" HorizontalAlignment="Left"/>
                 <Label Content="Age" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
                        Grid.Row="1" Grid.Column="0"/>
                 <TextBox Grid.Row="1" Grid.Column="1" x:Name="TextBoxAge" Height="27" 
                        Text="{Binding Path=StudentRecord.Age, Mode=TwoWay}" Margin="5" Width="70" HorizontalAlignment="Left"/>
                 <TextBlock Grid.Row="1" Grid.Column="1" x:Name="TextBlockId" 
                        Visibility="Hidden" Text="{Binding Path=StudentRecord.Id, Mode=TwoWay}"/>
                 <Label Content="Address" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
                        Grid.Row="2" Grid.Column="0" />
                 <TextBox Grid.Row="2" Grid.Column="1" x:Name="TextBoxAddress" Height="27" 
                        Text="{Binding Path=StudentRecord.Address, Mode=TwoWay}" Margin="5" Width="300" HorizontalAlignment="Left"/>
                 <Label Content="Contact" HorizontalAlignment="Left" VerticalContentAlignment="Center" 
                        Grid.Row="3" Grid.Column="0" />
                 <TextBox Grid.Row="3" Grid.Column="1" x:Name="TextBoxContact" Height="27"
                        Text="{Binding Path=StudentRecord.Contact, Mode=TwoWay}" Margin="5" Width="300" HorizontalAlignment="Left"/>
             </Grid>
         </GroupBox>
         <StackPanel Height="40" Orientation="Horizontal" HorizontalAlignment="Right">
             <Button x:Name="ButtonSave" Content="Save" Height="30" Width="80"
                     Command="{Binding SaveCommand}"/>
             <Button x:Name="ButtonCancel" Content="Cancel" Height="30" Width="80" 
                     Command="{Binding ResetCommand}" Margin="5,0,10,0"/>
         </StackPanel>
         <StackPanel Height="210">
             <DataGrid x:Name="DataGridStudents" AutoGenerateColumns="False"
                       ItemsSource="{Binding StudentRecord.StudentRecords}" CanUserAddRows="False" Height="200" Margin="10">
                 <DataGrid.Columns>
                     <DataGridTextColumn Header="Name" Binding="{Binding Path=Id}" Visibility="Hidden"/>
                     <DataGridTextColumn Header="Name" Binding="{Binding Path=Name}" Width="100"  IsReadOnly="True"/>
                     <DataGridTextColumn Header="Age" Binding="{Binding Path=Age}" Width="50"  IsReadOnly="True"/>
                     <DataGridTextColumn Header="Address" Binding="{Binding Path=Address}" Width="180" IsReadOnly="True"/>
                     <DataGridTextColumn Header="Contact" Binding="{Binding Path=Contact}" Width="125" IsReadOnly="True"/>
                     <DataGridTemplateColumn Width="50">
                         <DataGridTemplateColumn.CellTemplate>
                             <DataTemplate>
                                 <Button Content="Select" x:Name="ButtonEdit" CommandParameter="{Binding Path=Id}"
                                         Command="{Binding Path=DataContext.EditCommand,RelativeSource={RelativeSource FindAncestor,
                                                 AncestorType=Window}}"/>
                             </DataTemplate>
                         </DataGridTemplateColumn.CellTemplate>
                     </DataGridTemplateColumn>
                     <DataGridTemplateColumn Width="50">
                         <DataGridTemplateColumn.CellTemplate>
                             <DataTemplate>
                                 <Button Content="Delete" x:Name="ButtonDelete" CommandParameter="{Binding Path=Id}"
                                         Command="{Binding Path=DataContext.DeleteCommand, RelativeSource={RelativeSource FindAncestor,
                                                 AncestorType=Window}}"/>
                             </DataTemplate>
                         </DataGridTemplateColumn.CellTemplate>
                     </DataGridTemplateColumn>
                 </DataGrid.Columns>
             </DataGrid>
         </StackPanel>
     </StackPanel>
 </Window>




Now in the post, inside the Model folder, they added an ADO.NET Entity Data Model that connects to the Students table in the database and named it StudentModel while changing connectionstring name to StudentEntities.

But, I have a local SQLite database, how do I change that and what other things I need to change to make this app work?

Right now I have the error Error CS1061 'Student' does not contain a definition for 'ToList' and no accessible extension method 'ToList' accepting a first argument of type 'Student' could be found (are you missing a using directive or an assembly reference?) when I run it.

I'm not sure whether everything else is okay or not though.

Please help!













dotnet-csharpwindows-wpfdotnet-sqlite
image.png (21.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HuiLiu-MSFT avatar image
1 Vote"
HuiLiu-MSFT answered

You could refer to the code below.

Search for System.Data.SQLite in Manage NuGet Packages .

App.config:

 <configuration>
     <startup> 
         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.8" />
     </startup>
 </configuration>

MainWindow.xaml:

   <Window.Resources>
         <local:DateTimeConverter x:Key="converter"/>
         <local:DecimalConverter x:Key="decimalConverter"/>
         <local:SelectedItemToItemsSource x:Key="SelectedItemToItemsSource"/>
         <DataTemplate x:Key="UserGrid">
             <Border Background="Chocolate" BorderBrush="Black" BorderThickness="1" CornerRadius="5" >
                 <Grid Margin="10">
                     <Grid.RowDefinitions>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                         <RowDefinition/>
                     </Grid.RowDefinitions>
                     <Grid.ColumnDefinitions>
                         <ColumnDefinition/>
                         <ColumnDefinition/>
                     </Grid.ColumnDefinitions>
                     <TextBlock Text=" Id" Grid.Row="1" Grid.Column="0"/>
                     <TextBlock Text="Party" Grid.Row="2" Grid.Column="0"/>
                     <TextBlock Text="BillNo" Grid.Row="3" Grid.Column="0"/>
                     <TextBlock Text="BillDt" Grid.Row="4" Grid.Column="0"/>
                     <TextBlock Text="Amt" Grid.Row="5" Grid.Column="0"/>
                     <TextBlock Text="DueDt" Grid.Row="6" Grid.Column="0"/>
                     <TextBlock Text="PaidOn" Grid.Row="7" Grid.Column="0"/>
                     <TextBox Text="{Binding Id, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="1"/>
                     <TextBox Text="{Binding Party, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="2"/>
                     <TextBox Text="{Binding BillNo, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="3"/>
                     <TextBox Text="{Binding BillDt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="4"/>
                     <TextBox Text="{Binding Amt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="5"/>
                     <TextBox Text="{Binding DueDt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="6"/>
                     <TextBox Text="{Binding PaidOn, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="7"/>
                     <StackPanel Orientation="Horizontal" Grid.Row="10" Grid.ColumnSpan="2" HorizontalAlignment="Right" Margin="5,5,5,5">
                         <Button Foreground="White" Background="Green" Content="Cancel" Command="{Binding DataContext.CancelCommand, RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" Margin="4,0"/>
                         <Button Foreground="White" Background="Green" Content="Delete" Command="{Binding DataContext.DeleteUserCommand, RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" Margin="4,0"/>
                         <Button Foreground="White" Background="Green" Content="Save" Command="{Binding DataContext.SaveCommand, RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" Margin="4,0"/>
                         <Button Foreground="White" Background="Green" Content="Add" Command="{Binding DataContext.AddCommand, RelativeSource={RelativeSource AncestorType={x:Type ItemsControl}}}" Margin="4,0"/>
                     </StackPanel>
                 </Grid>
             </Border>
         </DataTemplate>
     </Window.Resources>
     <Grid Margin="0,0,0,-1">
         <Grid.ColumnDefinitions>
             <ColumnDefinition Width="7*"/>
             <ColumnDefinition Width="3*"/>
         </Grid.ColumnDefinitions>
         <GroupBox Header="Employee Data" HorizontalAlignment="Center" VerticalAlignment="Center" Height="383" Margin="5,5,5,5">
             <Grid>
                 <Grid.RowDefinitions>
                     <RowDefinition />
                     <RowDefinition Height="Auto"/>
                 </Grid.RowDefinitions>
                 <DataGrid x:Name="dg1" ItemsSource="{Binding Employee}" SelectedItem="{Binding SelectedEmployee}" CanUserAddRows="False"  
                               CanUserDeleteRows="False" SelectionMode="Single" SelectedIndex="{Binding SelectedIndex}" VerticalAlignment="Top" 
                               AutoGenerateColumns="False" Margin="5,5,5,5">
                     <DataGrid.Columns>
                         <DataGridTextColumn   Header="Party"  Binding="{Binding Path=Party, Mode=TwoWay}"  Width="105"   IsReadOnly="True" />
                         <DataGridTextColumn  Header="Bill No."  Binding="{Binding Path=BillNo, Mode=TwoWay}"  Width="75"  IsReadOnly="True" />
    
                         <DataGridTextColumn   Header="Bill Date"   Binding="{Binding Path=BillDt, StringFormat=d, Mode=TwoWay}"  Width="75"  IsReadOnly="True" />
                         <DataGridTextColumn Header="Amount" Width="75" Binding="{Binding Amt , Converter={StaticResource decimalConverter},StringFormat=N2}"></DataGridTextColumn>
                         <DataGridTextColumn  Header="Due Date"  Binding="{Binding Path=DueDt, StringFormat=d, Mode=TwoWay}"  Width="75"  IsReadOnly="True" />
                         <DataGridTextColumn Header="Paid On"  Binding="{Binding Path=PaidOn, Mode=TwoWay}"  Width="75"  IsReadOnly="True" />
                     </DataGrid.Columns>
                 </DataGrid>
             </Grid>
         </GroupBox>
         <ItemsControl BindingGroup="{Binding UpdateBindingGroup, Mode=OneWay}"  VerticalAlignment="Top" Margin="5,5,5,5" Grid.Column="1"
                       ItemTemplate="{StaticResource UserGrid}"  ItemsSource="{Binding SelectedEmployee, Converter={StaticResource SelectedItemToItemsSource}}"    />
    
     </Grid>

MainWindow.xaml.cs:

 using System;
 using System.Collections.Generic;
 using System.Collections.ObjectModel;
 using System.ComponentModel;
 using System.Data;
 using System.Data.SQLite;
 using System.Globalization;
 using System.Windows;
 using System.Windows.Data;
 using System.Windows.Input;
 using System.Windows.Threading;
 namespace SqliteCrudDemo
 {
   /// <summary>
   /// Interaction logic for MainWindow.xaml
   /// </summary>
   public partial class MainWindow : Window
   {
     public MainWindow()
     {
    
       InitializeComponent();
       DatabaseLayer.LoadData();
       this.DataContext = new ViewModelUser();
     }
   }
   public class ViewModelUser : ViewModelBase
   {
    
     public ViewModelUser()
     {
       personnel = new PersonnelBusinessObject();
       personnel.EmployeeChanged += new EventHandler(personnel_EmployeeChanged);
       UpdateBindingGroup = new BindingGroup { Name = "Group1" };
       CancelCommand = new RelayCommand(DoCancel);
       SaveCommand = new RelayCommand(DoSave);
       AddCommand = new RelayCommand(AddUser);
       DeleteUserCommand = new RelayCommand(DeleteUser);
     }
     PersonnelBusinessObject personnel;
     private ObservableCollection<MData> _Employee;
     public ObservableCollection<MData> Employee
     {
       get
       {
         _Employee = new ObservableCollection<MData>(personnel.GetEmployees());
         return _Employee;
       }
     }
     public int SelectedIndex { get; set; }
     object _SelectedEmployee;
     public object SelectedEmployee
     {
       get
       {
         return _SelectedEmployee;
       }
       set
       {
         if (_SelectedEmployee != value)
         {
           _SelectedEmployee = value;
           OnPropertyChanged("SelectedEmployee");
         }
       }
     }
     private BindingGroup _UpdateBindingGroup;
     public BindingGroup UpdateBindingGroup
     {
       get
       {
         return _UpdateBindingGroup;
       }
       set
       {
         if (_UpdateBindingGroup != value)
         {
           _UpdateBindingGroup = value;
           OnPropertyChanged("UpdateBindingGroup");
         }
       }
     }
     void personnel_EmployeeChanged(object sender, EventArgs e)
     {
       Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
       {
         OnPropertyChanged("Employee");
       }));
     }
     public RelayCommand CancelCommand { get; set; }
     public RelayCommand SaveCommand { get; set; }
     public RelayCommand AddCommand { get; set; }
     public RelayCommand DeleteUserCommand { get; set; }
    
     void DoCancel(object param)
     {
       UpdateBindingGroup.CancelEdit();
       if (SelectedIndex == -1)    //This only closes if new - just to show you how CancelEdit returns old values to bindings
         SelectedEmployee = null;
     }
    
     void DoSave(object param)
     {
       UpdateBindingGroup.CommitEdit();
       var employee = SelectedEmployee as MData;
       if (SelectedIndex == -1)
       {
         personnel.AddEmployee(employee);
         OnPropertyChanged("Employee"); // Update the list from the data source
       }
       else
         personnel.UpdateEmployee(employee);
    
       SelectedEmployee = null;
     }
    
     void AddUser(object param)
     {
    
    
       SelectedEmployee = null; // Unselects last selection. Essential, as assignment below won't clear other control's SelectedItems
       var employee = new MData();
       SelectedEmployee = employee;
     }
    
     void DeleteUser(object parameter)
     {
       var employee = SelectedEmployee as MData;
       if (SelectedIndex != -1)
       {
         personnel.DeleteEmployee(employee);
         OnPropertyChanged("Employee"); // Update the list from the data source
       }
       else
         SelectedEmployee = null; // Simply discard the new object
     }
   }
    
   public static class DatabaseLayer
   {
    
     public static void LoadData()
     {
       SQLiteConnection.CreateFile("MyDatabase.sqlite");
       SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");
       m_dbConnection.Open();
       string sql = "create table MyData (Id INTEGER PRIMARY KEY AUTOINCREMENT, Party varchar(20), BillNo varchar(20), BillDt varchar(20) ,Amt varchar(20) ,DueDt varchar(20),PaidOn varchar(20)) ";
       SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
       command.ExecuteNonQuery();
       sql = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES('Aakar Medicos1', 'AM-1877','05-01-2022','3324','26-02-2022','22-02-2022')";
    
    
       command = new SQLiteCommand(sql, m_dbConnection);
    
       command.ExecuteNonQuery();
       sql = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES('Aakar Medicos2', 'AM-1877','05-01-2022','3324','26-02-2022','22-02-2022')";
       command = new SQLiteCommand(sql, m_dbConnection);
    
       command.ExecuteNonQuery();
       m_dbConnection.Close();
     }
     public static List<MData> GetEmployeeFromDatabase()
     {
       try
       {
         SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite");
         SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection);
         SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);
         DataTable dt = new DataTable();
         sda.Fill(dt);
         var Employee = new List<MData>();
         foreach (DataRow row in dt.Rows)
         {
           var obj = new MData()
           {
             Id = Convert.ToInt32(row["Id"]),
             Party = (string)row["Party"],
             BillNo = (string)row["BillNo"],
             BillDt = (string)(row["BillDt"]),
             Amt = (string)row["Amt"],
             DueDt = (string)(row["DueDt"]),
             PaidOn = (string)(row["PaidOn"])
           };
           Employee.Add(obj);
           m_dbConnection.Close();
         }
         return Employee;
    
       }
       catch (Exception ex)
       {
         throw ex;
       }
     }
    
     internal static int InsertEmployee(MData employee)
     {
       try
       {
         const string query = "INSERT INTO MyData(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES(@Party, @BillNo,@BillDt,@Amt,@DueDt,@PaidOn)";
         var args = new Dictionary<string, object>
      {
            {"@Party", employee.Party},
            {"@BillNo", employee.BillNo},
            {"@BillDt", employee.BillDt},
            {"@Amt", employee.Amt},
            {"@DueDt", employee.DueDt},
            {"@PaidOn", employee.PaidOn},
      };
         return ExecuteWrite(query, args);
         MessageBox.Show("Data Saved Successfully.");
       }
       catch (Exception ex)
       {
         throw ex;
       }
       finally
       {
    
       }
     }
    
     internal static int UpdateEmployee(MData employee)
     {
       try
       {
         const string query = "UPDATE MyData SET Party = @Party, BillNo = @BillNo, BillDt=@BillDt, Amt=@Amt, DueDt=@DueDt , PaidOn=@PaidOn WHERE Id = @Id";
    
         var args = new Dictionary<string, object>
     {
          {"@Id", employee.Id},
          {"@Party", employee.Party},
            {"@BillNo", employee.BillNo},
            {"@BillDt", employee.BillDt},
            {"@Amt", employee.Amt},
            {"@DueDt", employee.DueDt},
            {"@PaidOn", employee.PaidOn},
     };
            
         return ExecuteWrite(query, args);
         MessageBox.Show("Data Updated Successfully.");
       }
       catch (Exception ex)
       {
         throw ex;
       }
       finally
       {
    
       }
     }
    
     internal static int DeleteEmployee(MData employee)
     {
       try
       {
         const string query = "Delete from MyData WHERE Id = @id";
       var args = new Dictionary<string, object>
         {
           {"@id", employee.Id}
         };
       return ExecuteWrite(query, args);
       MessageBox.Show("Data Deleted Successfully.");
     }
       catch (Exception ex)
       {
         throw ex;
       }
       finally
       {
    
       }
     }
     private static int ExecuteWrite(string query, Dictionary<string, object> args)
     {
       int numberOfRowsAffected;
    
       using (var con = new SQLiteConnection("Data Source=MyDatabase.sqlite"))
       {
         con.Open();
         using (var cmd = new SQLiteCommand(query, con))
         {
           foreach (var pair in args)
           {
             cmd.Parameters.AddWithValue(pair.Key, pair.Value);
           }
           numberOfRowsAffected = cmd.ExecuteNonQuery();
         }
         return numberOfRowsAffected;
       }
     }
   }
    
    
   public class PersonnelBusinessObject
   {
     internal EventHandler EmployeeChanged;
    
     List<MData> Employee { get; set; }
     public PersonnelBusinessObject()
     {
       Employee = DatabaseLayer.GetEmployeeFromDatabase();
     }
    
     public List<MData> GetEmployees()
     {
       return Employee = DatabaseLayer.GetEmployeeFromDatabase();
     }
    
     //public List<NationalityCollection> NationalityCollection { get; set; }
    
    
     //public List<NationalityCollection> GetNationality()
     //{
     //  return NationalityCollection = DatabaseLayer.GetNationality();
     //}
     public void AddEmployee(MData employee)
     {
       DatabaseLayer.InsertEmployee(employee);
       OnEmployeeChanged();
     }
    
     public void UpdateEmployee(MData employee)
     {
       DatabaseLayer.UpdateEmployee(employee);
       OnEmployeeChanged();
     }
    
     public void DeleteEmployee(MData employee)
     {
       DatabaseLayer.DeleteEmployee(employee);
       OnEmployeeChanged();
     }
    
     void OnEmployeeChanged()
     {
       if (EmployeeChanged != null)
         EmployeeChanged(this, null);
     }
   }
    
   public class MData : ViewModelBase
   {
     private int id;
     private string party;
     private string billNo;
     private string billDt;
     private string amt;
     private string dueDt;
     private string paidOn;
    
     public MData()
     {
     }
    
     public int Id
     {
       get { return id; }
       set
       {
         id = value;
         OnPropertyChanged("ID");
       }
     }
     public string Party
     {
       get { return party; }
       set
       {
         party = value;
         OnPropertyChanged("Party");
       }
     }
     public string BillNo
     {
       get { return billNo; }
       set
       {
         billNo = value;
         OnPropertyChanged("BillNo");
       }
     }
     public string BillDt
     {
       get { return billDt; }
       set
       {
         billDt = value;
         OnPropertyChanged("BillDt");
       }
     }
     public string Amt
     {
       get { return amt; }
       set
       {
         amt = value;
         OnPropertyChanged("Amt");
       }
     }
    
     public string DueDt
     {
       get { return dueDt; }
       set
       {
         dueDt = value;
         OnPropertyChanged("DueDt");
       }
     }
    
     public string PaidOn
     {
       get { return paidOn; }
       set
       {
         paidOn = value;
         OnPropertyChanged("PaidOn");
       }
     }
   }
    
   public class RelayCommand : ICommand
   {
    
     readonly Action<object> _execute;
     readonly Predicate<object> _canExecute;
    
    
     public RelayCommand(Action<object> execute)
         : this(execute, null)
     {
     }
    
     public RelayCommand(Action<object> execute, Predicate<object> canExecute)
     {
       if (execute == null)
         throw new ArgumentNullException("execute");
    
       _execute = execute;
       _canExecute = canExecute;
     }
    
     public bool CanExecute(object parameter)
     {
       return _canExecute == null ? true : _canExecute(parameter);
     }
    
     public event EventHandler CanExecuteChanged
     {
       add { CommandManager.RequerySuggested += value; }
       remove { CommandManager.RequerySuggested -= value; }
     }
    
     public void Execute(object parameter)
     {
       _execute(parameter);
     }
    
   }
    
   public class SelectedItemToItemsSource : IValueConverter
   {
     public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
     {
       if (value == null) return null;
       return new List<object>() { value };
     }
    
     public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
     {
       throw new NotImplementedException();
     }
   }
    
   public class ViewModelBase : INotifyPropertyChanged
   {
     internal void OnPropertyChanged(string prop)
     {
       if (PropertyChanged != null) { PropertyChanged(this, new PropertyChangedEventArgs(prop)); }
     }
     public event PropertyChangedEventHandler PropertyChanged;
   }
    
   [ValueConversion(typeof(DateTime), typeof(String))]
   public class DateTimeConverter : IValueConverter
   {
     public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
     {
       return DateTime.ParseExact(value.ToString(), "d-M-yyyy", CultureInfo.InvariantCulture);
     }
     public object ConvertBack(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
     {
       throw new NotImplementedException();
     }
   }
   [ValueConversion(typeof(double), typeof(String))]
   public class DecimalConverter : IValueConverter
   {
     public object Convert(object value, Type targetType, object parameter, CultureInfo culture)
     {
       return double.Parse(value.ToString(), CultureInfo.InvariantCulture);
     }
     public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture)
     {
       throw new NotImplementedException();
     }
   }
 }

The result:

204091-33.gif


If the response is helpful, please click "Accept Answer" and upvote it.
 Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 


33.gif (252.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HuiLiu-MSFT avatar image
0 Votes"
HuiLiu-MSFT answered HuiLiu-MSFT commented

The method public List<Student> GetAll() should return data of type List<Student>.
The method might look like the code below:

   public List<Student> GetAll()
         {
           List<Student> students = new  List<Student>();
           var student = new Student();
     ...
           students.Add(student);
           return students;
         }

The DeleteStudent(int id) method of the StudentViewModel class shows an error when I test it.
I modified it as follows:

  public void DeleteStudent(int id)
     {
       if (MessageBox.Show("Confirm delete of this record?", "Student", MessageBoxButton.YesNo)
           == MessageBoxResult.Yes)
       {
         try
         {
           var model= _repository.Get(id);
           _repository.RemoveStudent(model);
           MessageBox.Show("Record successfully deleted.");
         }
       ...
       }
    }

If the response is helpful, please click "Accept Answer" and upvote it.
 Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

As per your suggestion I did changes you suggested and after further checking I've also updated StudentRepository and StudentViewModel


Now the project runs and I also can add data to the table using the Save button. But after window loading I can only see the first row in the datagrid and not all of the rows. Furthermore, when I click on the Select and Delete button I get the errors respectively

191637-image.png

191529-image.png


0 Votes 0 ·
image.png (120.1 KiB)
image.png (138.2 KiB)

Hi,@ BoomBa-2054 . It should not be a public instantiation of the StudentRecord property.
You could try to declare and instantiate it in StudentViewModel class.

    public StudentRecord StudentRecord { get; set; } =new StudentRecord();
0 Votes 0 ·

Hi, @HuiLiu-MSFT . That didn't work, still getting the same error!

0 Votes 0 ·
Show more comments