Click here to Skip to main content
15,867,141 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

C#
<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)    //This only closes if new 
				SelectedInv = null;
		}

		void DoSave(object param)
		{
			UpdateBindingGroup.CommitEdit();
			var bill = SelectedInv as Bills;
			if (SelectedIndex == -1)
			{
				bills.AddBill(bill);
				OnPropertyChanged("Bill"); // Update the observablecollection from the data source
			}
			else
				bills.UpdateBill(bill);

			SelectedInv = null;

			PendingBills.Refresh();
			OnPropertyChanged("Cnt");
		}

		void AddUser(object param)
		{
			SelectedInv = null; // Unselects last selection. Essential, as assignment below won't clear other control's SelectedItems
			var bill = new Bills();
			SelectedInv = bill;
		}

		void DeleteUser(object parameter)
		{
			var bill = SelectedInv as Bills;
			if (SelectedIndex != -1)
			{
				bills.DeleteBill(bill);
				OnPropertyChanged("Bill"); // Update the observablecollection from the data source
			}
			else
			{
				SelectedInv = null; // Simply discard the new object

				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.
Posted
Updated 11-Aug-22 3:05am
v3

1 solution

The ObservableCollection (OC) is a "persistant" data collection that is bound to the "UI". Updates to the OC are reflected in the UI when items are added, removed, etc.

In your case, you create a new OC every time it is accessed:

public ObservableCollection<Bills> Bill { get {
				_Bill = new ObservableCollection<Bills>(bills.GetBills());
				return _Bill;
} }


You need to think more about the "when" and "where" in creating, persisting and accessing your OC.

And ...

Quote:
The objects in your collection must satisfy the requirements described in the Binding Sources Overview. In particular, if you are using OneWay or TwoWay (for example, you want your UI to update when the source properties change dynamically), you must implement a suitable property changed notification mechanism such as the INotifyPropertyChanged interface.


How to: Create and Bind to an ObservableCollection - WPF .NET Framework | Microsoft Docs[^]
 
Share this answer
 
v2
Comments
Member 12692000 7-Aug-22 4:35am    
QUOTE: You need to think more about the "when" and "where" in creating, persisting and accessing your OC.
Can you help me with that ? I'm running out of ideas
[no name] 11-Aug-22 11:56am    
I usually create my OC and bind it in the "Loaded" event; be it a window, user control, stack panel ... depends on your app.

https://docs.microsoft.com/en-us/dotnet/desktop/wpf/advanced/how-to-handle-a-loaded-event?view=netframeworkdesktop-4.8

"Loaded" can be called multiple times based on the scenario; so you need a "flag" in those cases to say you have already "initialized" in your code.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900