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();
}
}
}
}
}
