Data Manipulation in Windows Forms DataGrid (SfDataGrid)

7 Mar 202321 minutes to read

SfDataGrid listens and responds to the manipulation operations such as add, delete and data update (property change) at runtime. DataGrid refresh the sorting, filtering, grouping and summaries based on SfDataGrid.LiveDataUpdateMode property.

this.sfDataGrid1.LiveDataUpdateMode = LiveDataUpdateMode.AllowDataShaping;
Me.sfDataGrid1.LiveDataUpdateMode = LiveDataUpdateMode.AllowDataShaping

LiveDataUpdateMode

LiveDataUpdateMode – Default

Grid operations/ Data Manipulation operations

Add

Remove / delete

Property change

Sorting

 Record added at last Updated Sort order not updated

Grouping

 Updated Updated Groups not refreshed based on change

Filtering

 Updated Updated Filter not refreshed based on change

Summaries

 Not updated Not updated Not updated

LiveDataUpdateMode – AllowSummaryUpdate

Grid operations/ Data Manipulation operations

Add

Remove / delete

Property change

Sorting

 Record added at last Updated Sort order not updated

Grouping

 Updated Updated Groups not refreshed based on change

Filtering

 Updated Updated Filter not refreshed based on change

Summaries

 Updated Updated Updated

LiveDataUpdateMode – AllowDataShaping

Grid operations/ Data Manipulation operations

Add

Remove / delete

Property change

Sorting

 Updated Updated Updated

Grouping

 Updated Updated Updated

Filtering

 Updated Updated Updated

Summaries

 Updated Updated Updated

Limitations

  • AllowDataShaping and AllowSummaryUpdate is not supported when you are binding with dynamic data objects.
  • Complex and indexer properties doesn’t support LiveDataUpdateMode- AllowDataShaping and AllowSummaryUpdate.

AddNewRow

SfDataGrid provides built-in row (called AddNewRow) to add new records to underlying collection. AddNewRow can be enabled by specifying the position where it should be displayed by setting SfDataGrid.AddNewRowPosition property.
When start editing in AddNewRow, the SfDataGrid control creates an instance for the underlying data object and adds it to underlying collection when editing completed.

NOTE

The underlying data object must be defined with default constructor. Otherwise, create instance of data object by handling AddNewRowInitiating event.

sfDataGrid.AddNewRowPosition = RowPosition.Top;
sfDataGrid.AddNewRowPosition = RowPosition.Top

Windows forms datagrid showing addnewrow at top

Placing AddNewRow at Various Positions

SfDataGrid allows to add the AddNewRow at either top or fixed top or bottom or fixed bottom by setting the AddNewRowPosition property.

sfDataGrid.AddNewRowPosition = RowPosition.FixedBottom;
sfDataGrid.AddNewRowPosition = RowPosition.FixedBottom

Windows forms datagrid showing addnewrow at bottom

SfDataGrid allows to check whether the specified row index is AddNewRow index, by using SfDataGrid.IsAddNewRowIndex helper method.

sfDataGrid.IsAddNewRowIndex(1);
sfDataGrid.IsAddNewRowIndex(1)

Customize the Newly Added Row Position

SfDataGrid adds new data item from AddNewRow at the end of collection. When data operations (sorting, grouping) performed, the new item added based on data operations. The newly added data item position can be customized by setting NewItemPlaceholderPosition property.

sfDataGrid.NewItemPlaceholderPosition = Syncfusion.Data.NewItemPlaceholderPosition.AtBeginning;
sfDataGrid.NewItemPlaceholderPosition = Syncfusion.Data.NewItemPlaceholderPosition.AtBeginning

Initializing Default Values for AddNewRow

SfDataGrid allows to set the default values for AddNewRow while initiating, through AddNewRowInitiatingEventArgs.NewObject property in AddNewRowInitiating event.

this.sfDataGrid.AddNewRowInitiating += dataGrid_AddNewRowInitiating;

void dataGrid_AddNewRowInitiating(object sender, AddNewRowInitiatingEventArgs args)
{
    var data = args.NewObject as OrderInfo;
    data.OrderID = 101;
}
AddHandler sfDataGrid.AddNewRowInitiating, AddressOf dataGrid_AddNewRowInitiating

Private Sub dataGrid_AddNewRowInitiating(ByVal sender As Object, ByVal args As AddNewRowInitiatingEventArgs)
	Dim data = TryCast(args.NewObject, OrderInfo)
	data.OrderID = 101
End Sub

Windows forms datagrid showing the values for addnewrow

Validating the AddNewRow

The data in AddNewRow can validate like other data rows through built-in validation or custom validation. Here, AddNewRow is validated using RowValidating event by setting RowValidatingEventArgs.IsValid to false which doesn’t allow users to commit the AddNewRow until the validation gets succeeded.

this.sfDataGrid.RowValidating += dataGrid_RowValidating;

void dataGrid_RowValidating(object sender, RowValidatingEventArgs args)
{
    if (this.sfDataGrid.IsAddNewRowIndex(args.DataRow.RowIndex))
    {
        var data = args.DataRow.RowData as OrderInfo;
        if (data.OrderID < 10000)
        {
            args.IsValid = false;
            args.ErrorMessage = "OrderID should greater than 10000.";
        }
    }
}
AddHandler sfDataGrid.RowValidating, AddressOf dataGrid_RowValidating

Private Sub dataGrid_RowValidating(ByVal sender As Object, ByVal args As RowValidatingEventArgs)
	If Me.sfDataGrid.IsAddNewRowIndex(args.DataRow.RowIndex) Then
		Dim data = TryCast(args.DataRow.RowData, OrderInfo)

		If data.OrderID < 10000 Then
			args.IsValid = False
			args.ErrorMessage = "OrderID should greater than 10000."
		End If
	End If
End Sub

Windows forms datagrid showing validate the cell in addnewrow

Similarly, the cells can validate in AddNewRow by using theCurrentCellValidating event.

Prevent adding empty records

Adding empty record using AddNewRow can be prevented by setting the RowValidatingEventArgs.IsValid property to false in RowValidating event.

sfDataGrid.RowValidating += sfDataGrid_RowValidating;

void sfDataGrid_RowValidating(object sender, Syncfusion.WinForms.DataGrid.Events.RowValidatingEventArgs e)
{
    var data = e.DataRow.RowData as OrderInfo;

    if (string.IsNullOrEmpty(data.CustomerID) || string.IsNullOrEmpty(data.ProductName) || string.IsNullOrEmpty(data.ShipCountry))
    {
        e.ErrorMessage = "Records cannot be empty";
        e.IsValid = false;
    }
}
AddHandler sfDataGrid.RowValidating, AddressOf sfDataGrid_RowValidating 

Private Sub sfDataGrid_RowValidating(ByVal sender As Object, ByVal e As Syncfusion.WinForms.DataGrid.Events.RowValidatingEventArgs)
    Dim data = TryCast(e.DataRow.RowData, OrderInfo)

    If String.IsNullOrEmpty(data.CustomerID) OrElse String.IsNullOrEmpty(data.ProductName) OrElse String.IsNullOrEmpty(data.ShipCountry) Then
        e.ErrorMessage = "Records cannot be empty"
        e.IsValid = False
    End If
End Sub

Windows forms datagrid showing adding empty records

Update cell values based on selected value in the GridComboBoxCell

By default, changes made in the current record of add new row cell will be immediately reflected in the view. The following code shows how to change other cell value based on the selected value in the combo box cell of AddNewRow by using the CellComboBoxSelectionChanged event.

this.sfDataGrid.CellComboBoxSelectionChanged += OnSfDataGridCellComboBoxSelectionChanged;

private void OnSfDataGridCellComboBoxSelectionChanged(object sender, Syncfusion.WinForms.DataGrid.Events.CellComboBoxSelectionChangedEventArgs e)
{
    if (e.GridColumn.MappingName == "ShipCityID")
    {
        var shipCityDetails = e.SelectedItem as ShipCityDetails;
        var row = e.Record as OrderDetails;
        row.OrderID = shipCityDetails.ShipCityID;
        if (e.RowIndex == this.sfDataGrid.GetAddNewRowIndex())
            this.sfDataGrid.TableControl.Invalidate(this.sfDataGrid.TableControl.GetRowRectangle(this.sfDataGrid.GetAddNewRowIndex(), false));
    }
}
AddHandler CellComboBoxSelectionChanged, AddressOf OnSfDataGridCellComboBoxSelectionChanged
Private Sub SfDataGridCellComboBoxSelectionChanged(ByVal sender As Object, ByVal e As Syncfusion.WinForms.DataGrid.Events.CellComboBoxSelectionChangedEventArgs)
	If e.GridColumn.MappingName = "ShipCityID" Then
		Dim shipCityDetails = TryCast(e.SelectedItem, ShipCityDetails)
		Dim row = TryCast(e.Record, OrderDetails)
		row.OrderID = shipCityDetails.ShipCityID
		If e.RowIndex = Me.sfDataGrid.GetAddNewRowIndex() Then
			Me.sfDataGrid.TableControl.Invalidate(Me.sfDataGrid.TableControl.GetRowRectangle(Me.sfDataGrid.GetAddNewRowIndex(), False))
		End If
	End If
End Sub

Customizing AddNewRow text programmatically

You can change the add new row text programmatically using the sfDataGrid.AddNewRowText property.

this.sfDataGrid.AddNewRowText = "Click to add new row";
Me.sfDataGrid.AddNewRowText = "Click to add new row"

Windows forms datagrid showing customized addnewrow text

Customizing AddNewRow Text using Default Resource File

SfDataGrid allows to customize the watermark text of AddNewRow by changing value of AddNewRowText in Resource Designer. To customize the AddNewRowText, add the default Syncfusion.SfDataGrid.WinForms.resx file in Resources folder and then customize the value of AddNewRowText.

Addnewrow resx file customiztion in windows forms datagrid

windows forms datagrid showing lozalized addnewrow string

Appearance

The appearance of the AddNewRow can be customized by setting the AddNewRowStyle.

this.sfDataGrid.Style.AddNewRowStyle.BackColor = Color.DarkCyan;
this.sfDataGrid.Style.AddNewRowStyle.TextColor = Color.White;
Me.sfDataGrid.Style.AddNewRowStyle.BackColor = Color.DarkCyan
Me.sfDataGrid.Style.AddNewRowStyle.TextColor = Color.White

Windows forms datagrid showing customized addnewrow text color and backcolor

Adding a new row programmatically

Programmatically, a new row can be added to the SfDataGrid.

OrderInfoCollection orderInfoCollection = new OrderInfoCollection();
sfDataGrid.DataSource = orderInfoCollection.OrdersListDetails;

OrderInfo orderInfo = new OrderInfo() { OrderID = 10111, CustomerID = "FRANS", ProductName = "Alice Mutton", Quantity = 55, UnitPrice = 90, ContactNumber = 999789845, ShipCountry = "Brazil" };
orderInfoCollection.OrdersListDetails.Add(orderInfo);
Dim orderInfoCollection As New OrderInfoCollection()
sfDataGrid.DataSource = orderInfoCollection.OrdersListDetails

Dim orderInfo As New OrderInfo() With {.OrderID = 10111, .CustomerID = "FRANS", .ProductName = "Alice Mutton", .Quantity = 55, .UnitPrice = 90, .ContactNumber = 999789845, .ShipCountry = "Brazil"}
orderInfoCollection.OrdersListDetails.Add(orderInfo)

To add a new row when DataGrid bound to ObservableCollection

You can add the new row programmatically from a ObservableCollection to the SfDataGrid by creating a new row in the SfDataGrid.View and set required value for that row.

var orderData = this.sfDataGrid.DataSource;
if (orderData == null || this.sfDataGrid.View == null)
    return;
            
// Creates a new row.
var orderInfo = sfDataGrid.View.AddNew() as OrderInfo;
            
// Commits the newly created row.
sfDataGrid.View.CommitNew();

// Initialize the values of first row to the newly added row.
orderInfo.OrderID = 1001;
orderInfo.Quantity = 13;
orderInfo.OrderDate = new System.DateTime(2020, 3, 2);
orderInfo.CustomerID = "FRANS";
orderInfo.ContactNumber = 909093455;
Dim orderData = Me.sfDataGrid.DataSource
If orderData Is Nothing OrElse Me.sfDataGrid.View Is Nothing Then
	Return
End If

' Creates a new row.
Dim orderInfo = TryCast(sfDataGrid.View.AddNew(), OrderInfo)

' Commits the newly created row.
sfDataGrid.View.CommitNew()

' Initialize the values of first row to the newly added row.
orderInfo.OrderID = 1001
orderInfo.Quantity = 13
orderInfo.OrderDate = New System.DateTime(2020, 3, 2)
orderInfo.CustomerID = "FRANS"
orderInfo.ContactNumber = 909093455

To add a new row when DataGrid bound to DataTable

You can add the new row programmatically from a DataTable to the SfDataGrid by creating a new row in the SfDataGrid.View and set required value for that row.

DataTable gridDataTable = this.sfDataGrid.DataSource as DataTable;
if (gridDataTable == null || this.sfDataGrid.View == null)
    return;

// Creates a new row.
System.Data.DataRow addNewRow = sfDataGrid.View.AddNew() as System.Data.DataRow;

// Commits the newly created row.
sfDataGrid.View.CommitNew();

// Initialize the values of first row to the newly added row.
addNewRow.SetField(0, gridDataTable.Rows[0][0]);
addNewRow.SetField(1, gridDataTable.Rows[0][1]);
addNewRow.SetField(2, gridDataTable.Rows[0][2]);
addNewRow.SetField(3, gridDataTable.Rows[0][3]);
addNewRow.SetField(4, gridDataTable.Rows[0][4]);
Dim gridDataTable As DataTable = TryCast(Me.sfDataGrid.DataSource, DataTable)
If gridDataTable Is Nothing OrElse Me.sfDataGrid.View Is Nothing Then
	Return
End If

' Creates a new row.
Dim addNewRow As System.Data.DataRow = TryCast(sfDataGrid.View.AddNew(), System.Data.DataRow)

' Commits the newly created row.
sfDataGrid.View.CommitNew()

' Initialize the values of first row to the newly added row.
addNewRow.SetField(0, gridDataTable.Rows(0)(0))
addNewRow.SetField(1, gridDataTable.Rows(0)(1))
addNewRow.SetField(2, gridDataTable.Rows(0)(2))
addNewRow.SetField(3, gridDataTable.Rows(0)(3))
addNewRow.SetField(4, gridDataTable.Rows(0)(4))

You can download sample here.

AddNewRow support in Master-Details view

You can enable the AddNewRow in DetailsViewDataGrid using the GridViewDefinition.DataGrid property.

Enabling AddNewRow when manually defining relations

For manually defined relations, the AddNewRowPosition can be directly initialized to the datagrid of the defined GridViewDefinition.

GridViewDefinition firstLevelGridViewDefinition = new GridViewDefinition();
firstLevelGridViewDefinition.RelationalColumn = "OrderDetails";
SfDataGrid firstLevelNestedGrid = new SfDataGrid(); 
firstLevelNestedGrid.AddNewRowPosition = RowPosition.Top;
firstLevelGridViewDefinition.DataGrid = firstLevelNestedGrid;
sfDataGrid.DetailsViewDefinitions.Add(firstLevelGridViewDefinition);
Dim firstLevelGridViewDefinition As New GridViewDefinition() 
firstLevelGridViewDefinition.RelationalColumn = "OrderDetails" 
Dim firstLevelNestedGrid As New SfDataGrid() 
firstLevelNestedGrid.AddNewRowPosition = RowPosition.Top
firstLevelGridViewDefinition.DataGrid = firstLevelNestedGrid 
sfDataGrid.DetailsViewDefinitions.Add(firstLevelGridViewDefinition)

Enabling AddNewRow when auto-generating relations

When relation is auto-generated, you can initialize the AddNewRowPosition for GridViewDefinition.DataGrid in the AutoGeneratingRelations event.

this.sfDataGrid1.AutoGeneratingRelations += sfDataGrid1_AutoGeneratingRelations;

private void sfDataGrid1_AutoGeneratingRelations(object sender, Syncfusion.WinForms.DataGrid.Events.AutoGeneratingRelationsEventArgs e)
{
   e.GridViewDefinition.DataGrid.AddNewRowPosition = RowPosition.Top;
}
AddHandler sfDataGrid1. AutoGeneratingRelations, AddressOf SfDataGrid_AutoGeneratingRelations 
Private Sub SfDataGrid_AutoGeneratingRelations(ByVal sender As Object, ByVal e As AutoGeneratingRelationsEventArgs) 
     e.GridViewDefinition.DataGrid.AddNewRowPosition = RowPosition.Top;
End Sub

Winforms datagrid showing AddNewRow Support in Master-Details View

Delete Row

SfDataGrid provides built-in support to delete the selected records in user interface (UI) by pressing Delete key. The deleting support can be enabled by setting the SfDataGrid.AllowDeleting property to true. AllowDeleting is only supported when SelectionUnit is Row.

this.sfDataGrid.AllowDeleting = true;
Me.sfDataGrid.AllowDeleting = True

Delete the Selected Records through Code

The selected records can be deleted by using the DeleteSelectedRecords method in SfDataGrid. This method is only supported when SelectionUnit is Row.

this.sfDataGrid.DeleteSelectedRecords();
Me.sfDataGrid.DeleteSelectedRecords()

Customizing the Delete Operations

Conditionally Deleting Records

RecordDeleting event occurs when the record is being deleted from SfDataGrid. The record deletion can be cancel by using the RecordDeletingEventArgs.Cancel of RecordDeleting event. The certain records can be skipped when deleting more than one record by removing items from RecordDeletingEventArgs.Items.

this.sfDataGrid.RecordDeleting += dataGrid_RecordDeleting;

void dataGrid_RecordDeleting(object sender, RecordDeletingEventArgs args)
{
    foreach (var item in args.Items)
    {
        if ((item as OrderInfo).OrderID == 10001)
        {
            args.Cancel = true;
        }
    }
}
AddHandler sfDataGrid.RecordDeleting, AddressOf dataGrid_RecordDeleting

Private Sub dataGrid_RecordDeleting(ByVal sender As Object, ByVal args As RecordDeletingEventArgs)
	For Each item In args.Items
		If (TryCast(item, OrderInfo)).OrderID = 10001 Then
			args.Cancel = True
		End If
	Next item
End Sub

Handling Selection after Deleting the Record

RecordDeleted event occurs after the record is deleted. The selection can be changed after remove the records through SelectedIndex property of RecordDeleted event.

this.sfDataGrid.RecordDeleted += dataGrid_RecordDeleted;

void dataGrid_RecordDeleted(object sender, RecordDeletedEventArgs args)
{
    args.SelectedIndex = -1;
}
AddHandler sfDataGrid.RecordDeleted, AddressOf dataGrid_RecordDeleted

Private Sub dataGrid_RecordDeleted(ByVal sender As Object, ByVal args As RecordDeletedEventArgs)
	args.SelectedIndex = -1
End Sub

Deleting a row programmatically

Programmatically, a row can be deleted from the SfDataGrid. The following code shows how to delete particular row based on the values.

var orderInfo = orderInfoCollection.OrdersListDetails.FirstOrDefault(order => order.OrderID == 10000);
orderInfoCollection.OrdersListDetails.Remove(orderInfo);
Dim orderInfo = orderInfoCollection.OrdersListDetails.FirstOrDefault(Function(order) order.OrderID = 10000)
orderInfoCollection.OrdersListDetails.Remove(orderInfo)

The following code shows how to delete a row using the index of the row.

orderInfoCollection.OrdersListDetails.RemoveAt(1);
orderInfoCollection.OrdersListDetails.RemoveAt(1)