Show/Delete/Edit data in WPF DataGrid using LINQ to SQL Classes

This article will demonstrate how to Show, Delete, and Edit data in WPF Data Grid using LINQ to SQL Data Classes. What is LINQ to SQL?? When you want to use Language-Integrated Query (LINQ) to access data in a database, you do not connect directly to the database. Instead, you create classes that represent the database and its tables, and use those classes to interact with data. You can generate the classes through the Object Relational Designer or by running the SqlMetal.exe utility. For more information, see Object Relational Designer (O/R Designer) and Code Generation Tool (SqlMetal.exe).

So Let's start first of all add a new item LINQ to SQL data Classes.

Figure 1.

Now drag and drop database table using server explorer.



Figure 2.

If you are using Visual studio 2008 then you need to add a reference of WPF Toolkit, you don't need to add this reference in visual studio 2010.

Figure 3.

Add wpf toolkit name space on your .xaml page or window whatever you using.

xmlns:grid=http://schemas.microsoft.com/wpf/2008/toolkit

This is my DataGrid.

<Grid>
        <grid:DataGrid x:Name="MyDataGrid" x:Uid="MyDataGrid" AutoGenerateColumns="False"
                       AlternationCount="2" SelectionMode="Single" Margin="0,31,0,0">
            <grid:DataGrid.Columns>
                <grid:DataGridTextColumn Binding="{Binding  Path=CustomerID}" IsReadOnly="True" Header="Customer ID" Width="SizeToHeader" />
                <grid:DataGridTextColumn Binding="{Binding Path=CompanyName}" Header="Company" Width="SizeToHeader" />
                <grid:DataGridTextColumn Binding="{Binding Path=ContactName}" Header="Name" Width="SizeToHeader" />
                <grid:DataGridTextColumn Binding="{Binding Path=City}"
                                    Header="City" Width="SizeToHeader" />
                <grid:DataGridTextColumn Binding="{Binding Path=Country}" Header="Country" Width="SizeToHeader" />
                <grid:DataGridTextColumn Binding="{Binding Path=Phone}"
 Header="Phone" Width="SizeToHeader" />
                <grid:DataGridTemplateColumn Header="Edit Row">
                    <grid:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Button Content="Edit" Click="EditButton_Click" />
                        </DataTemplate>
                    </grid:DataGridTemplateColumn.CellTemplate>
                </grid:DataGridTemplateColumn>
                <grid:DataGridTemplateColumn Header="Delete Row">
                    <grid:DataGridTemplateColumn.CellTemplate>
                        <DataTemplate>
                            <Button Content="Delete" Click="DeleteButton_Click" />
                        </DataTemplate>
                    </grid:DataGridTemplateColumn.CellTemplate>
                </grid:DataGridTemplateColumn>
            </grid:DataGrid.Columns>
        </grid:DataGrid>
        <Button Height="23" Margin="12,2,0,0" Name="LoadButton" Content="Load Customers" VerticalAlignment="Top" Click="LoadButton_Click" HorizontalAlignment="Left" Width="126"></Button>
    </Grid>

.XAML.CS

private void LoadButton_Click(object sender, RoutedEventArgs e)
{
    LoadCustomers();
}

private void LoadCustomers()
{
    CustomersDataContext cd = new CustomersDataContext();
    var customers = (from p in cd.Customers
                             select p).Take(10);
    MyDataGrid.ItemsSource = customers;
    LoadButton.Content = "Customers Loaded";
}

Now run your application, you will see output like this. When you click on Load Customers button your data will display in datagrid.



Figure 4. 

 

Figure 5. 

private void EditButton_Click(object sender, RoutedEventArgs e)
{
    try
    {
        CustomersDataContext dataContext = new CustomersDataContext();
        Customer customerRow = MyDataGrid.SelectedItem as Customer;
        string m = customerRow.CustomerID;
        Customer customer = (from p in dataContext.Customers
                                     where p.CustomerID == customerRow.CustomerID
                                     select p).Single();
        customer.CompanyName = customerRow.CompanyName;
        customer.ContactName = customerRow.ContactName;
        customer.Country = customerRow.Country;
        customer.City = customerRow.City;
        customer.Phone = customerRow.Phone;
        dataContext.SubmitChanges();
        MessageBox.Show("Row Updated Successfully.");
        LoadCustomers();
    }
    catch (Exception Ex)
    {
        MessageBox.Show(Ex.Message);
        return;
    }
}

Figure 6.

private void DeleteButton_Click(object sender, RoutedEventArgs e)
{
    CustomersDataContext cd = new CustomersDataContext();
    Customer customerRow = MyDataGrid.SelectedItem as Customer;
    var customer = (from p in cd.Customers
                            where p.CustomerID == customerRow.CustomerID
                            select p).Single();
    cd.Customers.DeleteOnSubmit(customer);
    cd.SubmitChanges();
    MessageBox.Show("Row Deleted Successfully.");
    LoadCustomers();
}

 

Figure 7.