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:

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.

CRUD operations in WPF – Demo

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.

How to create .NET Core WPF application in Visual Studio 2019

Step 2. Add the NuGet Packages

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

CRUD Operations in WPF add NuGet Packages
Add NuGet Packages
  • Microsoft.EntityFrameworkCore.Sqlite
CRUD Operations in WPF sqlite
Add SQLite NuGet
  • Microsoft.Extensions.DependencyInjection
CRUD Operations in WPF
Dependency Injection NuGet

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

WPF CRUD NET CORE
Add Data folder

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

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.

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.

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.

<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

<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

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.

CRUD Operations in WPF using EntityFrameworkCore and SQLite 1
CRUD operations in WPF – SQLite database

As given in the application overview the application must perform all the CRUD operations.

CRUD operations in WPF Application Demo

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!!!

[Further Readings: How to implement Dependency Injection in WPF |  How to use External Tool in Visual Studio 2019 |  Top Features of Visual Studio 2019 Community Edition |  Basic CRUD operations in Blazor using SQLite as the database |  How to consume REST API in Blazor Application |  Blazor Lifecycle Methods |  A Simple way to Call Javascript in Blazor Application |  Creational Design Patterns |  Builder Design Pattern in C# |  Prototype Design Pattern in C# |  Top 5 Blazor Component Libraries |  Abstract Factory Design Pattern in C# |  Factory Method Pattern in C# |  Singleton Design Pattern in C# |  Introduction to Design Patterns |  Microsoft C# Version History |  Microsoft .NET Core Versions History |  Microsoft .NET Framework Version History  ]  

4.7 14 votes
Article Rating
Subscribe
Notify of
guest
33 Comments
oldest
newest most voted
Inline Feedbacks
View all comments
33
0
Would love your thoughts, please comment.x
()
x