I'm trying to make a simple WPF application where I can do CRUD operations on the selected row of a datagrid and all the related datagrid's and other controls view get updated instantly.
In my app there are currently 2 tabitems, in the first tabitem there is a datagrid which shows some filtered data from a table of a SQLite database on app load.
The second tabitem also has a datagrid but it doesn't show any data initially on app load but when an item is selected from the combo box in that same tabitem the datagrid filters the database data based on the combo box selected item and displays on the datagrid. So far so good.
Now when I select a row on the datagrid (2nd tabitem) then the selected row data are shown in a
ItemsControl
whose
ItemTemplate
is a
DataTemplate
shown as below:
<DataTemplate x:Key="UserGrid">
<Border Background="Transparent" BorderBrush="White" BorderThickness="1" CornerRadius="5" >
<Grid Margin="10">
<Grid.RowDefinitions>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
<RowDefinition/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="1*"/>
<ColumnDefinition Width="7*"/>
</Grid.ColumnDefinitions>
<TextBlock Margin="5" Text="Party" Grid.Row="1" Grid.Column="0"/>
<TextBlock Margin="5" Text="BillNo" Grid.Row="2" Grid.Column="0"/>
<TextBlock Margin="5" Text="BillDt" Grid.Row="3" Grid.Column="0"/>
<TextBlock Margin="5" Text="Amt" Grid.Row="4" Grid.Column="0"/>
<TextBlock Margin="5" Text="DueDt" Grid.Row="5" Grid.Column="0"/>
<TextBlock Margin="5" Text="PaidOn" Grid.Row="6" Grid.Column="0"/>
<TextBox Text="{Binding Party, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="1"/>
<TextBox Text="{Binding BillNo, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="2"/>
<TextBox Text="{Binding BillDt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="3"/>
<TextBox Text="{Binding Amt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="4"/>
<TextBox Text="{Binding DueDt, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="5"/>
<TextBox Text="{Binding PaidOn, BindingGroupName=Group1, UpdateSourceTrigger=Explicit}" Grid.Column="1" Grid.Row="6"/>
<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>
The ItemsControl has multiple TextBox's and Button's for CRUD operations like Add, Save, Delete and Cancel.
The current issues are when I hit the Add button and then fill in the TextBox's and hit the Save button the view's i.e. the datagrid's in both the 1st and 2nd tabitem does not get updated even though the database is updated, similarly when I hit the Delete button, the datagrid's in both the 1st and 2nd tabitem does not get updated even though the database is updated. I have to relaunch the app to see the effect. However when I just Edit i.e. simply change anything in the TextBox's and hit the Save button both the datagrid's update instantly.
Here is what my main ViewModel i.e. BillsViewModel look like (a part of it)
What I have tried:
public class BillsViewModel : ViewModelBase
{
...
public ICollectionView PendingBills { get; private set; }
private ObservableCollection<Bills> _allBills;
...
public BillsViewModel()
{
...
_allBills = DatabaseLayer.GetAllBillsFromCB();
...
PendingBills = new ListCollectionView(_allBills)
{
Filter = o => ((Bills)o).PaidOn=="" && (DateTime.ParseExact(((Bills)o).DueDt, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) >= DateTime.Today.AddDays(-50) && DateTime.ParseExact(((Bills)o).DueDt, "yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture) < DateTime.Today.AddDays(50))
};
AllBills = new ListCollectionView(_allBills)
{
Filter = o => ((Bills)o).Party == SelectedCBItem
};
...
bills = new BillsBusinessObject();
bills.BillChanged += new EventHandler(bills_BillChanged);
UpdateBindingGroup = new BindingGroup { Name = "Group1" };
CancelCommand = new RelayCommand(DoCancel);
SaveCommand = new RelayCommand(DoSave);
AddCommand = new RelayCommand(AddUser);
DeleteUserCommand = new RelayCommand(DeleteUser);
}
...
private ICollectionView _allBillsCollection;
public ICollectionView AllBills
{
get { return _allBillsCollection; }
set
{
_allBillsCollection = value;
SetValue(ref _allBillsCollection, value);
}
}
...
BillsBusinessObject bills;
private ObservableCollection<Bills> _Bill;
public ObservableCollection<Bills> Bill
{
get
{
_Bill = new ObservableCollection<Bills>(bills.GetBills());
return _Bill;
}
}
...
private BindingGroup _UpdateBindingGroup;
public BindingGroup UpdateBindingGroup
{
get
{
return _UpdateBindingGroup;
}
set
{
if (_UpdateBindingGroup != value)
{
_UpdateBindingGroup = value;
OnPropertyChanged("UpdateBindingGroup");
}
}
}
void bills_BillChanged(object sender, EventArgs e)
{
Application.Current.Dispatcher.BeginInvoke(DispatcherPriority.Background, new Action(() =>
{
OnPropertyChanged("Bill");
}));
}
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)
SelectedInv = null;
}
void DoSave(object param)
{
UpdateBindingGroup.CommitEdit();
var bill = SelectedInv as Bills;
if (SelectedIndex == -1)
{
bills.AddBill(bill);
OnPropertyChanged("Bill");
}
else
bills.UpdateBill(bill);
SelectedInv = null;
PendingBills.Refresh();
OnPropertyChanged("Cnt");
}
void AddUser(object param)
{
SelectedInv = null;
var bill = new Bills();
SelectedInv = bill;
}
void DeleteUser(object parameter)
{
var bill = SelectedInv as Bills;
if (SelectedIndex != -1)
{
bills.DeleteBill(bill);
OnPropertyChanged("Bill");
}
else
{
SelectedInv = null;
OnPropertyChanged("Bill");
}
AllBills.Refresh();
PendingBills.Refresh();
}
}
The helper classes are like
public static class DatabaseLayer
{
public static ObservableCollection<Bills> GetAllBillsFromCB()
{
try
{
SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=Bills.db");
SQLiteCommand sqlCom = new SQLiteCommand("Select * From billdata", m_dbConnection);
SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom);
DataTable dt = new DataTable();
sda.Fill(dt);
var Bill = new ObservableCollection<Bills>();
foreach (DataRow row in dt.Rows)
{
var p = (row["PaidOn"] == DBNull.Value) ? String.Empty : (string)(row["PaidOn"]);
var obj = new Bills()
{
Id = Convert.ToInt32(row["Id"]),
Party = (string)row["Party"],
BillNo = (string)row["BillNo"],
BillDt = (string)(row["BillDt"]),
Amt = float.Parse(row["Amt"].ToString()),
DueDt = (string)(row["DueDt"]),
PaidOn = p
};
Bill.Add(obj);
m_dbConnection.Close();
}
return Bill;
}
catch (Exception ex)
{
throw ex;
}
}
internal static int InsertBill(Bills bill)
{
try
{
const string query = "INSERT INTO billdata(Party,BillNo, BillDt,Amt,DueDt,PaidOn) VALUES(@Party, @BillNo,@BillDt,@Amt,@DueDt,@PaidOn)";
var args = new Dictionary<string, object>
{
{"@Party", bill.Party},
{"@BillNo", bill.BillNo},
{"@BillDt", bill.BillDt},
{"@Amt", bill.Amt},
{"@DueDt", bill.DueDt},
{"@PaidOn", bill.PaidOn},
};
return ExecuteWrite(query, args);
}
catch (Exception ex)
{
throw ex;
}
}
internal static int UpdateBill(Bills bill)
{
try
{
const string query = "UPDATE billdata SET Party = @Party, BillNo = @BillNo, BillDt=@BillDt, Amt=@Amt, DueDt=@DueDt , PaidOn=@PaidOn WHERE Id = @Id";
var args = new Dictionary<string, object>
{
{"@Id", bill.Id},
{"@Party", bill.Party},
{"@BillNo", bill.BillNo},
{"@BillDt", bill.BillDt},
{"@Amt", bill.Amt},
{"@DueDt", bill.DueDt},
{"@PaidOn", bill.PaidOn},
};
return ExecuteWrite(query, args);
}
catch (Exception ex)
{
throw ex;
}
}
internal static int DeleteBill(Bills bill)
{
try
{
const string query = "Delete from billdata WHERE Id = @id";
var args = new Dictionary<string, object>
{
{"@id", bill.Id}
};
return ExecuteWrite(query, args);
}
catch (Exception ex)
{
throw ex;
}
}
private static int ExecuteWrite(string query, Dictionary<string, object> args)
{
int numberOfRowsAffected;
using (var con = new SQLiteConnection("Data Source=Bills.db"))
{
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 BillsBusinessObject
{
internal EventHandler BillChanged;
ObservableCollection<Bills> Bill { get; set; }
public BillsBusinessObject()
{
Bill = DatabaseLayer.GetAllBillsFromCB();
}
public ObservableCollection<Bills> GetBills()
{
return Bill = DatabaseLayer.GetAllBillsFromCB();
}
public void AddBill(Bills bill)
{
DatabaseLayer.InsertBill(bill);
OnBillChanged();
}
public void UpdateBill(Bills bill)
{
DatabaseLayer.UpdateBill(bill);
OnBillChanged();
}
public void DeleteBill(Bills bill)
{
DatabaseLayer.DeleteBill(bill);
OnBillChanged();
}
void OnBillChanged()
{
if (BillChanged != null)
BillChanged(this, null);
}
}
Please help me fix this.