What approach should be taken to track changes in database operations in other windows?

fatih uyanık 100

Hello

I continue to develop an application by injecting services with the MVVM pattern in a C# wpf project. I encountered a problem. I designed a DataAccess class to manage database operations (Insert, delete) with EF. A new record is added in a window where the data is listed, or after the selected record is updated in the new window, it is closed and the page is returned to the page where the listing was made. Here, while the listing window is closed, if there is a change, recording is made while the window is closed.

The problem is this: Every time I use a new link clause or a short-lived Context, I cannot achieve the result I want in the listing window. There are a few more problems like this.

In such a scenario I thought of injecting DataAccess class as Singleton. But I think I might have memory leaks and performance issues.

What is the approach that should be taken in this regard?

Thanks.

Hui Liu-MSFT 48,571 Reputation points Microsoft Vendor

2023-12-15T09:02:49.8366667+00:00
Hi,@fatih uyanık. Welcome to Microsoft Q&A.

When dealing with database operations in a WPF application with the MVVM pattern, it's crucial to manage the lifetime of your data context appropriately. Using a singleton for the DataAccess class or the database context can indeed lead to memory leaks, especially if the context is kept alive for a long time.

Here are some common approaches to handle database operations and track changes:

1.Short-Lived Contexts: Create short-lived database contexts for each operation and ensure they are disposed of when the operation is complete. This helps prevent memory leaks and ensures that each operation has a fresh context.

using (var dbContext = new YourDbContext()) { // Perform database operations }

2.Unit of Work Pattern:

Implement the Unit of Work pattern to manage the database context's lifetime. This pattern involves creating a unit of work class that encapsulates a database context. The unit of work is responsible for committing changes to the database.

3.Dependency Injection with Scoped Context:

If you are using a dependency injection container, you could configure it to use scoped lifetime for the database context. This means that a new context will be created for each logical operation or request, and it will be disposed of when the scope ends.

4.Event Aggregator:

Use an event aggregator or a messaging system to notify other parts of the application when changes occur. For example, after adding or updating a record, you can publish an event, and other windows or components can subscribe to this event to refresh their data.

5.ViewModel Communication:

Use communication between ViewModels to notify each other of changes. For example, you could use an event or a callback mechanism to inform the listing window that a change has occurred.

6.Consider Asynchronous Programming:

If your database operations are potentially long-running, consider using asynchronous programming to avoid blocking the UI thread.
Harsh Patidar 0 Reputation points

2023-12-15T09:08:28.5066667+00:00
Hello,

@fatih uyanık

Certainly! When dealing with databases in a WPF application:

Use Dependency Injection: Let a framework manage how long your database-related code (like DataAccess) should stay active.

Create Contexts as Needed: Only create a new database connection (context) when you're about to do something with the database. Close it when you're done.

Avoid Keeping Contexts Alive Too Long: Don't keep a connection open for a long time. Open it, do what you need, then close it.

Consider Asynchronous Operations: If your database work takes time, do it in the background so your app stays responsive.

Handle Concurrency Issues: Be careful if many people might be changing the same data at once. Use techniques like optimistic concurrency to avoid issues.

By following these steps, you can make sure your app runs smoothly, doesn't waste memory, and handles database connections the right way.
fatih uyanık 100 Reputation points

2023-12-16T18:53:39.9+00:00

Hello

First of all, thank you for your answer. I kept the database operations separate from the ViewModel and collected them in the dataAccess class. This dataAccess class takes an EF Context in its constructor method and uses it within the class. The class implements the Disposable interface.

I do both listing and deleting operations in the same ViewModel. Is it necessary to use using in both cases? Can you help me in this situation?

Thanks.
Peter Fleischer (former MVP) 19,326 Reputation points

2023-12-16T21:22:58.1466667+00:00

Hi,
you can use your dataAccess class from both ViewModels. Try my demo.

1 answer

Peter Fleischer (former MVP) 19,326

Hi,
one approach you can see in following demo (one instance of dataAccess the whole time and edit window as dialog):

XAML Mainwindow:

<Window x:Class="WpfApp1.Window123"
        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:WpfApp123"
        mc:Ignorable="d"
        Title="fatih uyanık_231215" Height="250" Width="400">
  <Window.Resources>
    <local:ViewModelMain x:Key="vm"/>
  </Window.Resources>
  <Grid DataContext="{StaticResource vm}" Margin="5">
    <Grid.RowDefinitions>
      <RowDefinition/>
      <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>
    <DataGrid ItemsSource="{Binding View}" IsReadOnly="True" AutoGenerateColumns="False" Grid.ColumnSpan="2">
      <DataGrid.Columns>
        <DataGridTextColumn Binding="{Binding FirstName}"/>
        <DataGridTextColumn Binding="{Binding MiddleName}"/>
        <DataGridTextColumn Binding="{Binding LastName}"/>
        <DataGridTemplateColumn>
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <Button Content="Edit" Command="{Binding CmdEdit, Source={StaticResource vm}}" CommandParameter="{Binding}" Margin="2"/>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
        </DataGridTemplateColumn>
        <DataGridTemplateColumn>
          <DataGridTemplateColumn.CellTemplate>
            <DataTemplate>
              <Button Content="Delete" Command="{Binding CmdDelete, Source={StaticResource vm}}" CommandParameter="{Binding}" Margin="2"/>
            </DataTemplate>
          </DataGridTemplateColumn.CellTemplate>
        </DataGridTemplateColumn>
      </DataGrid.Columns>
    </DataGrid>
    <StackPanel HorizontalAlignment="Right" Orientation="Horizontal" Grid.Row="1" Grid.ColumnSpan="2">
      <Button Content="New" Command="{Binding Cmd}" CommandParameter="New"  Margin="5"/>
      <Button Content="Refresh" Command="{Binding Cmd}" CommandParameter="Refresh" Margin="5"/>
    </StackPanel>
  </Grid>
</Window>

XAML DetailWindow:

<Window x:Class="WpfApp1.Window123Detail"
        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:vm="clr-namespace:WpfApp123"
        mc:Ignorable="d"
        Title="Window123Sub" Height="200" Width="300"
        vm:ViewModelDetail.AttProp="True">
  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="auto"/>
      <RowDefinition Height="auto"/>
    </Grid.RowDefinitions>
    <Grid DataContext="{Binding View}">
      <Grid.ColumnDefinitions>
        <ColumnDefinition Width="auto"/>
        <ColumnDefinition/>
      </Grid.ColumnDefinitions>
      <Grid.RowDefinitions>
        <RowDefinition Height="auto"/>
        <RowDefinition Height="auto"/>
        <RowDefinition Height="auto"/>
      </Grid.RowDefinitions>
      <Label Grid.Row="0" Grid.Column="0" Content="FirstName:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="0" Grid.Column="1" Text="{Binding FirstName}" Margin="5"/>
      <Label Grid.Row="1" Grid.Column="0" Content="MiddleName:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="1" Grid.Column="1" Text="{Binding MiddleName}" Margin="5"/>
      <Label Grid.Row="2" Grid.Column="0" Content="LastName:" HorizontalAlignment="Right" Margin="5"/>
      <TextBox Grid.Row="2" Grid.Column="1" Text="{Binding LastName}" Margin="5"/>
    </Grid>
    <StackPanel Grid.Row="3" Grid.Column="0" Grid.ColumnSpan="2" Orientation="Horizontal" HorizontalAlignment="Right">
      <Button Content="Save" Command="{Binding Cmd}" CommandParameter="Save" Margin="5"/>
      <Button Content="Delete" Command="{Binding Cmd}" CommandParameter="Delete" Margin="5"/>
      <Button Content="Cancel" Command="{Binding Cmd}" CommandParameter="Cancel" Margin="5"/>
    </StackPanel>
  </Grid>
</Window>

ViewModels:

using System;
using System.ComponentModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Data;
using System.Windows.Input;
using WpfApp1;

namespace WpfApp123
{
	public class ViewModelMain : INotifyPropertyChanged
	{
		public ViewModelMain() => refreshData();

		private CollectionViewSource cvs = new CollectionViewSource(); // View source for displaying
		public ICollectionView View { get => cvs.View; }

		private void refreshData()
		{
			cvs.Source = (new DataAccess()).GetData();
			OnPropertyChanged(nameof(View));
		}

		public ICommand Cmd { get => new RelayCommand(CmdExec); }
		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Refresh":
					refreshData();
					break;
				case "New":
					Window123Tab1 newItem = new Window123Tab1();
					CmdEditExec(newItem);
					break;
				default:
					break;
			}
		}
		public ICommand CmdEdit { get => new RelayCommand(CmdEditExec); }
		private void CmdEditExec(object obj)
		{
			Window123Detail wnd = new Window123Detail();
			ViewModelDetail vm = new ViewModelDetail();
			wnd.DataContext = vm;
			DataAccess da = new DataAccess();
			Window123Tab1 item = obj as Window123Tab1;
			if (item.ID != 0) item = da.Tab1.SingleOrDefault(a => a.ID == item.ID);
			if (item != null)
			{
				vm.View = item;
				bool? toSave = wnd.ShowDialog();
				if (toSave.Value)
				{
					if (item.ID == 0) da.AddData(item);
					else da.UpdateData(item);
				}
				else
				{
					if (item.ID != 0) da.ReloadData(item);
				}
			}
			refreshData();
		}
		public ICommand CmdDelete { get => new RelayCommand(CmdDeleteExec); }
		private void CmdDeleteExec(object obj)
		{
			Window123Tab1 item = obj as Window123Tab1;
			if (item != null) (new DataAccess()).DeleteData(item);
			refreshData();
		}

		public event PropertyChangedEventHandler PropertyChanged;
		private void OnPropertyChanged([CallerMemberName] string propName = "") => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propName));
	}

	public class ViewModelDetail
	{
		public object View { get; set; }
		public ICommand Cmd { get => new RelayCommand(CmdExec); }

		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Save":
					SubWindow.DialogResult = true;
					SubWindow.Close();
					break;
				case "Delete":
					(new DataAccess()).DeleteData((Window123Tab1)View);
					SubWindow.DialogResult = true;
					SubWindow.Close();
					break;
				case "Cancel":
					SubWindow.DialogResult = false;
					SubWindow.Close();
					break;
				default:
					break;
			}
		}
		public static readonly DependencyProperty AttPropProperty = DependencyProperty.Register("AttProp",
	typeof(bool), typeof(Window), new UIPropertyMetadata(false, OnAttProp));
		public static bool GetAttProp(DependencyObject obj) => (bool)obj.GetValue(AttPropProperty);
		public static void SetAttProp(DependencyObject obj, bool value) => obj.SetValue(AttPropProperty, value);
		private static void OnAttProp(DependencyObject depObj, DependencyPropertyChangedEventArgs e)
		{
			var wnd = depObj as Window;
			if (wnd == null) return;
			if ((e.NewValue is bool) && (bool)(e.NewValue))
				wnd.Loaded += Wnd_Loaded;
		}
		private static void Wnd_Loaded(object sender, RoutedEventArgs e)
		{
			Window wnd = sender as Window;
			if (wnd == null) return;
			ViewModelDetail dc = wnd.DataContext as ViewModelDetail;
			if (dc == null) return;
			dc.SubWindow = wnd;
		}

		Window SubWindow { get; set; }
	}

	public partial class DataAccess : DbContext
	{
		public DataAccess() : base("name=Database2Entities") { }

		protected override void OnModelCreating(DbModelBuilder modelBuilder)
		{
			throw new UnintentionalCodeFirstException();
		}
		public virtual DbSet<Window123Tab1> Tab1 { get; set; }

		internal object GetData()=> from item in Tab1.AsEnumerable() select item;
		internal void AddData(Window123Tab1 item)
		{
			Tab1.Add(item);
			this.SaveChanges();
		}
		internal void UpdateData(Window123Tab1 item) => this.SaveChanges();
		internal void DeleteData(Window123Tab1 item)
		{
			Tab1.Remove(Tab1.Single(a => a.ID == item.ID));
			this.SaveChanges();
		}
		internal void ReloadData(Window123Tab1 item)
		{
			if (this.Entry(item).State != EntityState.Detached)
			{
				this.Entry(item).State = EntityState.Unchanged;
				this.SaveChanges();
			}
		}
	}

	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:

Peter Fleischer (former MVP) 19,326

Hi,
see another approach in following demo (each edit window with separate instance of dataAcess):

XAML like in previous demo.

ViewModels:

using System;
using System.ComponentModel;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Data;
using System.Windows.Input;
using WpfApp1;

namespace WpfApp124
{
	public class ViewModelMain : INotifyPropertyChanged
	{
		public ViewModelMain() => refreshData();

		private CollectionViewSource cvs = new CollectionViewSource(); // View source for displaying
		public ICollectionView View { get => cvs.View; }

		private void refreshData()
		{
			cvs.Source = (new DataAccess()).GetData();
			OnPropertyChanged(nameof(View));
		}

		public ICommand Cmd { get => new RelayCommand(CmdExec); }
		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Refresh":
					refreshData();
					break;
				case "New":
					CmdEditExec(null);
					break;
				default:
					break;
			}
		}
		public ICommand CmdEdit { get => new RelayCommand(CmdEditExec); }
		private void CmdEditExec(object obj)
		{
			int id = (obj is Tab1) ? ((Tab1)obj).ID : 0;
			ViewModelDetail vm = new ViewModelDetail() { ID = id };
			vm.ExecRefresh += (s, e) => refreshData();
			(new Window124Detail() { DataContext = vm }).Show();
		}

		public ICommand CmdDelete { get => new RelayCommand(CmdDeleteExec); }
		private void CmdDeleteExec(object obj)
		{
			Tab1 item = obj as Tab1;
			if (item != null) (new DataAccess()).DeleteData(item);
			refreshData();
		}

		public event PropertyChangedEventHandler PropertyChanged;
		private void OnPropertyChanged([CallerMemberName] string propName = "") => PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propName));
	}

	public class ViewModelDetail
	{
		public int ID { get; set; }
		private DataAccess da = new DataAccess();
		private Tab1 _view = null;
		public Tab1 View
		{
			get
			{
				if (_view == null) _view = da.Tab1.SingleOrDefault(a => a.ID == ID);
				if (_view == null) _view = new Tab1();
				return _view;
			}
		}

		public event EventHandler ExecRefresh;
		public ICommand Cmd { get => new RelayCommand(CmdExec); }

		private void CmdExec(object obj)
		{
			switch (obj.ToString())
			{
				case "Save":
					if (_view.ID == 0) da.AddData(_view);
					da.UpdateData(View);
					break;
				case "Delete":
					da.DeleteData(View);
					break;
				case "Cancel":
					da.ReloadData(View);
					break;
				default:
					break;
			}
			ExecRefresh.Invoke(this, null);
			SubWindow.Close();
		}

		#region Attached property
		public static readonly DependencyProperty AttPropProperty = DependencyProperty.Register("AttProp",
	typeof(bool), typeof(Window), new UIPropertyMetadata(false, OnAttProp));
		public static bool GetAttProp(DependencyObject obj) => (bool)obj.GetValue(AttPropProperty);
		public static void SetAttProp(DependencyObject obj, bool value) => obj.SetValue(AttPropProperty, value);
		private static void OnAttProp(DependencyObject depObj, DependencyPropertyChangedEventArgs e)
		{
			var wnd = depObj as Window;
			if (wnd == null) return;
			if ((e.NewValue is bool) && (bool)(e.NewValue))
				wnd.Loaded += Wnd_Loaded;
		}
		private static void Wnd_Loaded(object sender, RoutedEventArgs e)
		{
			Window wnd = sender as Window;
			if (wnd == null) return;
			ViewModelDetail dc = wnd.DataContext as ViewModelDetail;
			if (dc == null) return;
			dc.SubWindow = wnd;
		}

		Window SubWindow { get; set; }
		#endregion
	}

	public partial class DataAccess : DbContext
	{
		public DataAccess() : base("name=Database1Entities") { }

		protected override void OnModelCreating(DbModelBuilder modelBuilder)
		{
			throw new UnintentionalCodeFirstException();
		}
		public virtual DbSet<Tab1> Tab1 { get; set; }

		internal object GetData() => from item in Tab1.AsEnumerable() select item;
		internal void AddData(Tab1 item)
		{
			Tab1.Add(item);
			this.SaveChanges();
		}
		internal void UpdateData(Tab1 item)
		{
			if (Tab1.SingleOrDefault(a => a.ID == item.ID) != null) this.SaveChanges();
		}
		internal void DeleteData(Tab1 item)
		{
			Tab1.Remove(Tab1.Single(a => a.ID == item.ID));
			this.SaveChanges();
		}
		internal void ReloadData(Tab1 item)
		{
			if (this.Entry(item).State != EntityState.Detached)
			{
				this.Entry(item).State = EntityState.Unchanged;
				this.SaveChanges();
			}
		}
	}

	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:

Share via

What approach should be taken to track changes in database operations in other windows?

1 answer

Your answer