How to: Implement CRUD Operations in a Data-Bound Grid

• Oct 12, 2020
• 6 minutes to read

The following examples demonstrate how to implement CRUD operations (create, read, update, and delete) in the GridControl bound to a data source.

View Example

These examples follow the One DbContext per request approach. This pattern has the following advantages:

• Thread-safety.
• Complete control over which data fields the client retrieves.
• Complete control over data source queries.
• The view model and view can work with any Object-Relational Mapping implementation.

Tip

If you want to implement the One DbContext per form approach, modify the view model and data layer accordingly.

GridControlCRUDSimple

This example illustrates a basic non-MVVM approach. The data sources for the grid and the combo box editor are set in the window’s constructor.

Bind Grid to Data

The grid displays two columns, so the SELECT query is configured to only fetch the corresponding data fields.

C#

grid.ItemsSource = context
    .Products
    .Select(product => new ProductInfo {
        Id = product.Id,
        Name = product.Name,
        CategoryId = product.CategoryId
    })
.ToList();

VB.NET

grid.ItemsSource = context.Products.Select(Function(product) New ProductInfo With {
    .Id = product.Id,
    .Name = product.Name,
    .CategoryId = product.CategoryId
}).ToList()

The combo box editor requires only two data fields:

• Id for item key values.
• Name for display values.

XAML

<dxg:GridColumn FieldName="CategoryId" Header="Category" Width="*">
    <dxg:GridColumn.EditSettings>
        <dxe:ComboBoxEditSettings x:Name="categoriesLookup" ValueMember="Id" DisplayMember="Name"/>
    </dxg:GridColumn.EditSettings>
</dxg:GridColumn>

C#

 categoriesLookup.ItemsSource = context
    .Categories
    .Select(category => new CategoryInfo {
        Id = category.Id,
        Name = category.Name,
    })
.ToList();

VB.NET

 categoriesLookup.ItemsSource = context.Categories.Select(Function(category) New CategoryInfo With {
    .Id = category.Id,
    .Name = category.Name
}).ToList()

Synchronize Changes with Database

The ValidateRow event is handled to add or update data records.

If the view’s FocusedRowHandle property returns the NewItemRowHandle value, a new record is added to the database.

If the user updates properties in an existing data row instead, the Queryable.SingleOrDefault method checks if there is a data record whose Id matches the row’s Id. If no such record is found, an exception is thrown.

The DbContext.SaveChanges method saves changes to the database.

C#

void tableView_ValidateRow(object sender, GridRowValidationEventArgs e) {
    var productInfo = (ProductInfo)e.Row;
    using(var context = new NorthwindContext()) {
        Product product;
        if(view.FocusedRowHandle == DataControlBase.NewItemRowHandle) {
            product = new Product();
            context.Products.Add(product);
        } else {
            product = context.Products.SingleOrDefault(p => p.Id == productInfo.Id);
            if(product == null) {
                throw new NotImplementedException
                ("The modified row no longer exists in the database. Handle this case according to your requirements.");
            }
        }
        product.Name = productInfo.Name;
        product.CategoryId = productInfo.CategoryId;
        context.SaveChanges();
        if(view.FocusedRowHandle == DataControlBase.NewItemRowHandle) {
            productInfo.Id = product.Id;
        }
    }
}

VB.NET

Private Sub tableView_ValidateRow(ByVal sender As Object, ByVal e As GridRowValidationEventArgs)
    Dim productInfo As ProductInfo = CType(e.Row, ProductInfo)
    Using context = New NorthwindContext()
        Dim product As Product
        If view.FocusedRowHandle = DataControlBase.NewItemRowHandle Then
            product = New Product()
            context.Products.Add(product)
        Else
            product = context.Products.SingleOrDefault(Function(p) p.Id = productInfo.Id)
            If product Is Nothing Then
                Throw New NotImplementedException
                ("The modified row no longer exists in the database. Handle this case according to your requirements.")
            End If
        End If
        product.Name = productInfo.Name
        product.CategoryId = productInfo.CategoryId
        context.SaveChanges()
        If view.FocusedRowHandle = DataControlBase.NewItemRowHandle Then
            productInfo.Id = product.Id
        End If
    End Using
End Sub

The KeyDown event is handled to implement row deletion. The DbSet.Find method checks if there is a data record whose Id matches the row’s Id value. The GridCommands.DeleteFocusedRow command deletes the row in the grid itself.

C#

void grid_KeyDown(object sender, KeyEventArgs e) {
    if(e.Key == Key.Delete) {
        var productInfo = (ProductInfo)grid.SelectedItem;
        if(productInfo == null)
            return;
        if(DXMessageBox.Show(this, "Are you sure you want to delete this row?", "Delete Row", MessageBoxButton.OKCancel) == MessageBoxResult.Cancel)
            return;
        try {
            using(var context = new NorthwindContext()) {
                var result = context.Products.Find(productInfo.Id);
                if(result == null) {
                    throw new NotImplementedException
                    ("The modified row no longer exists in the database. Handle this case according to your requirements.");
                }
                context.Products.Remove(result);
                context.SaveChanges();
                view.Commands.DeleteFocusedRow.Execute(null);
            }
        } catch(Exception ex) {
            DXMessageBox.Show(ex.Message);
    }
}

VB.NET

Private Sub grid_KeyDown(ByVal sender As Object, ByVal e As KeyEventArgs)
    If e.Key = Key.Delete Then
        Dim productInfo As ProductInfo = CType(grid.SelectedItem, ProductInfo)
        If productInfo Is Nothing Then
            Return
        End If
        If DXMessageBox.Show
            (Me, "Are you sure you want to delete this row?", "Delete Row", MessageBoxButton.OKCancel) = MessageBoxResult.Cancel Then
            Return
        End If
        Try
            Using context = New NorthwindContext()
                Dim result = context.Products.Find(productInfo.Id)
                If result Is Nothing Then
                    Throw New NotImplementedException
                    ("The modified row no longer exists in the database. Handle this case according to your requirements.")
                End If
                context.Products.Remove(result)
                context.SaveChanges()
                view.Commands.DeleteFocusedRow.Execute(Nothing)
            End Using
        Catch ex As Exception
            DXMessageBox.Show(ex.Message)
        End Try
    End If
End Sub

GridControlCRUDMVVM

This example illustrates the MVVM approach.

Model

The Common.DataModel folder contains generic data access interfaces.

C#

public interface ICRUDDataProvider<T> : IDataProvider<T> where T : class {
    void Create(T obj);
    void Update(T obj);
    void Delete(T obj);
}
public interface IDataProvider<T> where T : class {
    IList<T> Read();
}

VB.NET

Public Interface ICRUDDataProvider(Of T As Class)
    Inherits IDataProvider(Of T)

    Sub Create(ByVal obj As T)
    Sub Update(ByVal obj As T)
    Sub Delete(ByVal obj As T)
End Interface
Public Interface IDataProvider(Of T As Class)
    Function Read() As IList(Of T)
End Interface

The DesignTimeDataProvider class implements the data access interfaces for use in design time.

The Northwind.DataModel folder contains the fully implemented model for the application.

View Model

The Common.ViewModel folder contains the generic view model - CollectionViewModel.

The CollectionViewModel is not an ObservableCollection. The GridControl handles all data updates and uses commands to pass data to the view model: OnCreateCommand, OnUpdateCommand, and OnDeleteCommand.

If the view model cannot retrieve data from the source, it raises an exception. The exception is handled by the grid, which shows the EntitiesErrorMessage text.

The Northwind.ViewModel folder contains the fully implemented view model for the application - ProductCollectionViewModel. The view model creates different data providers in design time and runtime to prevent the Visual Studio’s designer from fetching data from the database. The NorthwindDataStorageFactory class located in the Northwind.DataModel folder contains the data provider implementations.

Bind View Model Commands to Grid Events

The GridControlCRUDBehavior located in the Common.View folder is a custom behavior that binds the view model commands to the TableView events. You can use this behavior to connect the GridControl to any view model that provides the following commands:

• OnCreateCommand
• OnUpdateCommand
• OnRefreshCommand
• OnDeleteCommand

XAML

<dxg:GridControl.View>
    <dxg:TableView x:Name="view" AutoWidth="True" ShowUpdateRowButtons="OnCellEditorOpen" NewItemRowPosition="Top">
        <dxmvvm:Interaction.Behaviors>
            <view:GridControlCRUDBehavior x:Name="CRUDBehavior" NoRecordsErrorMessage="{Binding EntitiesErrorMessage}"
                                            OnCreateCommand="{Binding OnCreateCommand}" OnUpdateCommand="{Binding OnUpdateCommand}"
                                            OnDeleteCommand="{Binding OnDeleteCommand}" OnRefreshCommand="{Binding OnRefreshCommand}"/>
        </dxmvvm:Interaction.Behaviors>
    </dxg:TableView>
</dxg:GridControl.View>

The GridControlCRUDBehavior is also used to bind the toolbar commands to the view model.

XAML

<dxb:ToolBarControl>
    <dxb:BarButtonItem Content="Refresh (F5)" Command="{Binding RefreshCommand, ElementName=CRUDBehavior}" BarItemDisplayMode="ContentAndGlyph" Glyph="{dx:DXImage SvgImages/Icon Builder/Actions_Refresh.svg}"/>
    <dxb:BarButtonItem Content="Delete (Del)" Command="{Binding DeleteCommand, ElementName=CRUDBehavior}" BarItemDisplayMode="ContentAndGlyph" Glyph="{dx:DXImage SvgImages/Icon Builder/Actions_Delete.svg}"/>
</dxb:ToolBarControl>

GridControlCRUDMVVMAsync

This example illustrates the MVVM approach with asynchronous operations.

To implement asynchronous operations, you only need to change two components of the GridControlCRUDMVVM example for use with the asynchronous version of CollectionViewModel:

• The view model.

  The DataProviderAsyncExtensions class located in the Common.ViewModel folder defines asynchoronus CRUD Task operations for use in the CollectionViewModel.

• The behavior that binds the view model commands to the TableView events.

  In contrast to the GridControlCRUDBehavior in the previous example, the GridControlCRUDAsyncBehavior utilizes asynchronous commands.