Home

repost: CRUD Operations in WPF using EntityFrameworkCore and SQLite

In this blog post, we will discuss how to perform CRUD operations in WPF using EntityFrameworkCore and SQLite as the backend database. We will create a .NET Core WPF application and all the basic Create, Read, Update, and Delete operations will be performed via the user interface interactions. If you are completely new to .NET Core WPF, I would highly recommend you go through the introductory post of Windows Presentation Foundation in .NET Core.

# Prerequisites

In this blog post, we are going to create a .NET Core WPF Application using the default template provided by the Visual Studio 2019. The other tools and packages required are as follows:

  • Visual Studio 2019 (v16.4) download here
  • .NET Core SDK 3.1 and above [download here(https://dotnet.microsoft.com/download/dotnet-core/3.1)]
  • SQLite Studio download here

# Demo Application Overview

We are going to create a straight forward .NET Core WPF application, that reads all the records from the Products table inside a Product.db, SQLite database. These product details are displayed inside a DataGrid on the user interface.

An individual Edit button is displayed on every row of the DataGrid, clicking the same will update the selected product details on the “Edit product”, from where the user can change any value of the product and update the same.

Likewise, an individual Delete button is displayed in every row of the DataGrid, clicking the same deletes the record from the Product table.

The application also contains a section from where the user can add (create) a new product in the database.

# Implementing CRUD operations in WPF

# Step 1: Create the .NET Core WPF Application

The first step is to create a .NET Core WPF Application in Visual Studio 2019, this quick video display how to create the WPF application in Visual Studio 2019.

# Step 2. Add the NuGet Packages

The following NuGet packages are to be added to the project.

  • Microsoft.EntityFrameworkCore.Sqlite
  • Microsoft.Extensions.DependencyInjection

# Step 3: Add a Data folder in the project and following two classes in it.

Product.cs: This is the product class that represent the Product Entity.

1
2
3
4
5
6
7
8
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public double Price { get; set; }
    public int Unit { get; set; }
}

ProductDbContext.cs: This is a DbContext class that helps us to interact and perform database operations. The class also overrides the OnModelCreating() so that the database can have some seed data for testing purposes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
public class ProductDbContext : DbContext
{
    #region Constructor
    public ProductDbContext(DbContextOptions<ProductDbContext> options) : base(options)
    {
        Database.EnsureCreated();
    }
    #endregion

    #region Public properties
    public DbSet<Product> Products { get; set; }
    #endregion

    #region Overridden methods
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>().HasData(GetProducts());
        base.OnModelCreating(modelBuilder);
    }
    #endregion

    #region Private methods
    private Product[] GetProducts()
    {
        return new Product[]
        {
            new Product { Id = 1, Name = "TShirt", Description = "Blue Color", Price = 2.99, Unit =1},
            new Product { Id = 2, Name = "Shirt", Description = "Formal Shirt", Price = 12.99, Unit =1},
            new Product { Id = 3, Name = "Socks", Description = "Wollen", Price = 5.00, Unit =2},
            new Product { Id = 4, Name = "Tshirt", Description = "Red", Price = 2.99, Unit =3},
        };
    }
    #endregion
}

# Step 4: Register the ProductDbContext and MainWindow class in ServiceProvider in App.xaml.cs

Here in this class, we are making use of Dependency Injection (DI), and registering the ProductDbContext and MainWindow with the DI’s ServiceProvider. As well as, we have created a handler for the Startup event where we are displaying the MainWindow after getting the instance from the ServiceProvider.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public partial class App : Application
{
    #region Private members
    private readonly ServiceProvider serviceProvider;
    #endregion

    #region Constructor
    public App()
    {
        ServiceCollection services = new ServiceCollection();

        services.AddDbContext<ProductDbContext>(options =>
        {
            options.UseSqlite("Data Source = Product.db");
        });

        services.AddSingleton<MainWindow>();
        serviceProvider = services.BuildServiceProvider();
    }
    #endregion


    #region Event Handlers
    private void OnStartup(object s, StartupEventArgs e)
    {
        var mainWindow = serviceProvider.GetService<MainWindow>();
        mainWindow.Show();
    }
    #endregion
}

# Step 5. Update the App.xaml

Here remove the StartupUri and add the Startup event handler we created in App.xaml.cs, this will help to launch the MainWindow.

1
2
3
4
5
6
7
8
9
<Application x:Class="WpfAppDemoCRUD.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:local="clr-namespace:WpfAppDemoCRUD"
             Startup="OnStartup">
    <Application.Resources>

    </Application.Resources>
</Application>

# Step 6. Replace the Grid tag in MainWindow.xaml with the given code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
<Grid >
    <Grid.RowDefinitions>
        <RowDefinition Height="45"/>
        <RowDefinition/>
        <RowDefinition/>
    </Grid.RowDefinitions>

    <Label FontSize="18" HorizontalAlignment="Center" VerticalAlignment="Center" Margin="5"
           Grid.Row="0" Content="CRUD Application using EntityFrameworkCore and SQLite"/>

    <DataGrid x:Name="ProductDG" AutoGenerateColumns="False" CanUserAddRows="False" IsReadOnly="True"
              Grid.Row="1" ColumnWidth="*" Margin="5" IsSynchronizedWithCurrentItem="True" >

        <DataGrid.Columns>
            <DataGridTextColumn Header="Product Id" Binding="{Binding Id}"/>
            <DataGridTextColumn Header="Name" Binding="{Binding Name}"/>
            <DataGridTextColumn Header="Description" Binding="{Binding Description}"/>
            <DataGridTextColumn Header="Price" Binding="{Binding Price}"/>
            <DataGridTextColumn Header="Unit" Binding="{Binding Unit}"/>

            <DataGridTemplateColumn Header="Edit Product">
                <DataGridTemplateColumn.CellTemplate>
                    <DataTemplate>
                        <Button Content="Edit" Click="SelectProductToEdit" />
                    </DataTemplate>
                </DataGridTemplateColumn.CellTemplate>
            </DataGridTemplateColumn>

            <DataGridTemplateColumn Header="Delete Product">
                <DataGridTemplateColumn.CellTemplate>
                    <DataTemplate>
                        <Button Content="Delete" Click="DeleteProduct"/>
                    </DataTemplate>
                </DataGridTemplateColumn.CellTemplate>
            </DataGridTemplateColumn>
        </DataGrid.Columns>
    </DataGrid>

    <Grid Grid.Row="2">
        <Grid.ColumnDefinitions>
            <ColumnDefinition/>
            <ColumnDefinition/>
        </Grid.ColumnDefinitions>

        <Border Grid.Column="0" Margin="5" BorderThickness="1" BorderBrush="Black">
            <StackPanel  Margin="5">
                <Label Content="Add new product" FontWeight="Bold"
                       HorizontalAlignment="Center"
                       VerticalAlignment="Center" Margin="5"/>

                <Grid Name="NewProductGrid">
                    <Grid.RowDefinitions>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                    </Grid.RowDefinitions>

                    <Grid.ColumnDefinitions>
                        <ColumnDefinition/>
                        <ColumnDefinition/>
                    </Grid.ColumnDefinitions>

                    <Label Grid.Row="0" Grid.Column="0" Content="Product Name"/>
                    <TextBox Grid.Row="0" Grid.Column="1" Text="{Binding Name}"/>

                    <Label Grid.Row="1" Grid.Column="0" Content="Description"/>
                    <TextBox Grid.Row="1" Grid.Column="2" Text="{Binding Description}"/>

                    <Label Grid.Row="2" Grid.Column="0" Content="Price"/>
                    <TextBox Grid.Row="2" Grid.Column="3" Text="{Binding Price}"/>

                    <Label Grid.Row="3" Grid.Column="0" Content="Unit"/>
                    <TextBox Grid.Row="3" Grid.Column="4" Text="{Binding Unit}"/>

                    <Button Grid.Row="4" Grid.ColumnSpan="2" Width="150" Content="Add" Margin="5" Click="AddItem"
                            HorizontalAlignment="Center" VerticalAlignment="Center"/>
                </Grid>

            </StackPanel>
        </Border>

        <Border Grid.Column="1" Margin="5" BorderThickness="1" BorderBrush="Black">
            <StackPanel Margin="5">
                <Label Content="Edit product" FontWeight="Bold"
                       HorizontalAlignment="Center"
                       VerticalAlignment="Center" Margin="5"/>

                <Grid Name="UpdateProductGrid">
                    <Grid.RowDefinitions>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                        <RowDefinition/>
                    </Grid.RowDefinitions>

                    <Grid.ColumnDefinitions>
                        <ColumnDefinition/>
                        <ColumnDefinition/>
                    </Grid.ColumnDefinitions>

                    <Label Grid.Row="0" Grid.Column="0" Content="Product Name"/>
                    <TextBox Grid.Row="0" Grid.Column="1" Text="{Binding Name}"/>

                    <Label Grid.Row="1" Grid.Column="0" Content="Description"/>
                    <TextBox Grid.Row="1" Grid.Column="2" Text="{Binding Description}"/>

                    <Label Grid.Row="2" Grid.Column="0" Content="Price"/>
                    <TextBox Grid.Row="2" Grid.Column="3" Text="{Binding Price}"/>

                    <Label Grid.Row="3" Grid.Column="0" Content="Unit"/>
                    <TextBox Grid.Row="3" Grid.Column="4" Text="{Binding Unit}"/>

                    <Button Grid.Row="4" Grid.ColumnSpan="2" Width="150" Click="UpdateItem"
                            Content="Edit" Margin="5"
                            HorizontalAlignment="Center" VerticalAlignment="Center"/>
                </Grid>

            </StackPanel>
        </Border>
    </Grid>
</Grid>

# Step 7. Replace MainWindow.xaml.cs with the given code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
public partial class MainWindow : Window
{
    ProductDbContext context;
    Product NewProduct = new Product();
    Product selectedProduct = new Product();


    public MainWindow(ProductDbContext context)
    {
        this.context = context;
        InitializeComponent();
        GetProducts();
        NewProductGrid.DataContext = NewProduct;
    }


    private void GetProducts()
    {
        ProductDG.ItemsSource = context.Products.ToList();
    }

    private void AddItem(object s, RoutedEventArgs e)
    {
        context.Products.Add(NewProduct);
        context.SaveChanges();
        GetProducts();
        NewProduct = new Product();
        NewProductGrid.DataContext = NewProduct;
    }

    private void UpdateItem(object s, RoutedEventArgs e)
    {
        context.Update(selectedProduct);
        context.SaveChanges();
        GetProducts();
    }

    private void SelectProductToEdit(object s, RoutedEventArgs e)
    {
        selectedProduct = (s as FrameworkElement).DataContext as Product;
        UpdateProductGrid.DataContext = selectedProduct;
    }

    private void DeleteProduct(object s, RoutedEventArgs e)
    {
        var productToDelete = (s as FrameworkElement).DataContext as Product;
        context.Products.Remove(productToDelete);
        context.SaveChanges();
        GetProducts();
    }
}

# Step 8. Execute the application.

Product.db – Products table view in SQLiteStudio

After executing the application, the Product.db will be created in the binary directory of the application. The records of the Product table when the application creates the database for the first time.

# Source Code

GitHub: https://github.com/technicalbundle/wpfcrudefcoresqlite

This concludes the post on how to perform CRUD operations in WPF application, I hope you found this post helpful, thanks for visiting, Cheers!!!

Edited on