CRUD Operations in WPF using EntityFrameworkCore and SQLite

CRUD Operations in WPF using  EntityFrameworkCore and SQLite 1

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:

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

CRUD Operations in WPF using  EntityFrameworkCore and SQLite 2
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="CRUDWpfApp.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:local="clr-namespace:CRUDWpfApp"
             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 3
CRUD operations in WPF – SQLite database

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

CRUD Operations in WPF demo
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 Reads: How to implement Dependency Injection in WPF |  How to create WPF application using dotnet cli | Introduction to .NET Core WPF | CRUD operations in Blazor Application | How to use external tools in Visual Studio 2019 | Top 5 Blazor Component Libraries | Abstract Factory Design Pattern in C# | AI vs ML vs DL – The Basic Difference | Machine Learning Model Generation Process  ]

3.7 3 votes
Article Rating
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments