SQL Example WPF Datagrid to View, Update, Insert and Delete data

WPF > Controls > ItemsControl > DataGrid > SQL Server example

Using WPF Datagrid to View, Update, Insert and Delete data

In the following example I will show you how to use DataGrid to display, edit, add and delete data from SQL Server Database and ADO.NET Entity Data Model Designer.

Step 1: Create a new project in Visual Studio: and select WPF Application

Step 2:  Create SQL Server database and Product sample table

Go to left Pane in SQL Server Object Explorer and create a new database named Invoices

 Create new table name Product using the following script

CREATE TABLE [dbo].[Products] (
    [Id]                    INT            IDENTITY (1, 1) NOT NULL,
    [ProductCode]           NVARCHAR (MAX) NOT NULL,
    [ProductDescription]    NVARCHAR (MAX) NOT NULL,
    [ProductPrice]          FLOAT (53)     NOT NULL,
    [ProductExpirationDate] SMALLDATETIME  NULL,
    [ProductStockQuantity]  INT            NULL,
    [ProducVatRate]         FLOAT (53)     DEFAULT ((0)) NOT NULL,
    [IsBio]                 BIT            DEFAULT ((0)) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);
  

Step 3: Create ADO.Net Entity Data Model

Go to solution explorer and create a new folder named DAL (Data Access Layer) and then Right click and add new ADO.Net Entity Data Model.

 Connect to your database and add Products table to your model

Step 4: XAML File

<Window x:Name="Main" x:Class="DataGrid.MainWindow"

        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="623" Loaded="Main_Loaded">
    <Grid>
        <DataGrid x:Name="dgProducts" HorizontalAlignment="Left" Margin="10,31,0,0" VerticalAlignment="Top"
                   ItemsSource="{Binding}"   AutoGenerateColumns="False" RowEditEnding="dgProducts_RowEditEnding" AddingNewItem="dgProducts_AddingNewItem" BeginningEdit="dgProducts_BeginningEdit" PreviewKeyDown="dgProducts_PreviewKeyDown" Width="595" AlternatingRowBackground="{DynamicResource {x:Static SystemColors.ActiveCaptionBrushKey}}" >
            <DataGrid.Columns>
                <DataGridTextColumn Binding="{Binding Id, NotifyOnTargetUpdated=True,Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}"
                                    Width="75" Header="Product ID" IsReadOnly="True" />
                <DataGridTextColumn Binding="{Binding ProductCode, NotifyOnTargetUpdated=True, Mode=TwoWay, UpdateSourceTrigger=PropertyChanged}"
                                    Width="100" Header="Code"/>
                <DataGridTextColumn Binding="{Binding ProductDescription, NotifyOnSourceUpdated=True, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                                    Width="100" Header="Description"/>
                <DataGridTextColumn  Binding="{Binding ProductPrice, NotifyOnSourceUpdated=True, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                                    Width="100" Header="Price"/>
                <DataGridTemplateColumn Header="Expiration date">
                    <DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <TextBlock Text="{Binding ProductExpirationDate, StringFormat=\{0:d\}}" />
                        </DataTemplate>
                    </DataGridTemplateColumn.CellTemplate>
                    <DataGridTemplateColumn.CellEditingTemplate>
                        <DataTemplate>
                            <DatePicker SelectedDate ="{Binding ProductExpirationDate, NotifyOnSourceUpdated=True,Mode=TwoWay,
                                UpdateSourceTrigger=PropertyChanged}" />
                        </DataTemplate>
                    </DataGridTemplateColumn.CellEditingTemplate>
                </DataGridTemplateColumn>
                <DataGridCheckBoxColumn Binding="{Binding IsBio, NotifyOnSourceUpdated=True, Mode=TwoWay,UpdateSourceTrigger=PropertyChanged}"
                                    Width="100" Header="Bio"/>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>
</Window>

Step 5: Code file:

using DataGrid.DAL;

using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace DataGrid

{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        InvoicesEntities context = new InvoicesEntities();
        bool isInsertMode = false;
        bool isBeingEdited = false;

        public MainWindow()
        {
            InitializeComponent();
        }
        private void Main_Loaded(object sender, RoutedEventArgs e)
        {
            dgProducts.ItemsSource = GetProductList();
        }
        private ObservableCollection<Products> GetProductList()
        {
            var list = from e in context.Products select e;
            return new ObservableCollection<Products>(list);
        }
        private void dgProducts_RowEditEnding(object sender, DataGridRowEditEndingEventArgs e)
        {
            Products product = new Products();
            Products curProd = e.Row.DataContext as Products;
            if (isInsertMode)
            {
                var InsertRecord = MessageBox.Show("Do you want to add " + curProd.ProductCode  + " as a new product?", "Confirm", MessageBoxButton.YesNo,

                    MessageBoxImage.Question);

                if (InsertRecord == MessageBoxResult.Yes)
                {
                    product.ProductCode = curProd.ProductCode;
                    product.ProductDescription = curProd.ProductDescription;
                    product.ProductPrice = curProd.ProductPrice;
                    product.ProductExpirationDate = curProd.ProductExpirationDate;
                    context.Products.Add(product);
                    context.SaveChanges();
                    dgProducts.ItemsSource = GetProductList();
                    MessageBox.Show(product.ProductCode + " " + product.ProductDescription + " has being added!", "Add product", MessageBoxButton.OK, MessageBoxImage.Information);
                    isInsertMode = false;

                }

                else
                    dgProducts.ItemsSource = GetProductList();
            }
            context.SaveChanges();
        }
        private void dgProducts_AddingNewItem(object sender, AddingNewItemEventArgs e)
         {
            isInsertMode = true;
        }
        private void dgProducts_BeginningEdit(object sender, DataGridBeginningEditEventArgs e)
        {
            isBeingEdited = true;
        }
        private void dgProducts_PreviewKeyDown(object sender, KeyEventArgs e)
        {
            if (e.Key == Key.Delete && !isBeingEdited)
            {
                if (dgProducts.SelectedItems.Count > 0)
                {
                    var Res = MessageBox.Show("Are you sure you want to delete " + dgProducts.SelectedItems.Count + " products?", "Deleting products",
                        MessageBoxButton.YesNo, MessageBoxImage.Exclamation);
                    if (Res == MessageBoxResult.Yes)
                    {
                        foreach (var row in dgProducts.SelectedItems)
                        {
                            Products product = row as Products;
                            context.Products.Remove(product);
                        }
                         context.SaveChanges();
                        MessageBox.Show(dgProducts.SelectedItems.Count + " Products have being deleted!");
                    }
                    else
                        dgProducts.ItemsSource = GetProductList();
                }
            }
        }
    }
}