Skip to main content

How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2)

  • 9 minutes to read

This tutorial shows how to modify the data-bound spreadsheet application created in the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1) example to enable end-users to add, modify, and remove data in the connected data table.

Develop a User Interface

To provide the capability to interact with the database, add the following UI elements to the spreadsheet application created in the How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1) example.

UI Element

Implementation

Data Entry Form

On the template worksheet, create a data entry form that will be used to insert new records into the database. For this, do the following.

  1. Unhide rows 3 through 10.
  2. Format the cell range “B3:J9” as shown in the following image, enter the field captions, and add the Save and Cancel cells.

    DXSpreadsheet_DataBinding_DataEntryForm

    When a user clicks the Save cell, a new record with the entered values is added to the Suppliers data table. Clicking the Cancel cell clears the data entry form and hides it.

  3. Hide rows 3 through 10 again.

Ribbon Buttons

Create a new ribbon group and add the following buttons to it.

DXSpreadsheet_DataBinding_DatabaseRibbonGroup

The table below contains the description of each bar button item.

Content

Name

Description

Add Record

buttonAddRecord

Displays a data entry form on the template worksheet to add a new record to the Suppliers table.

Remove Record

buttonRemoveRecord

Invokes the Delete dialog, which allows a user to remove the selected record from the database or cancel the delete operation.

DXSpreadsheet_DataBinding_DeleteRecordDialog

Apply Changes

buttonApplyChanges

Posts the updated data back to the database.

Cancel Changes

buttonCancelChanges

Cancels the recent changes and loads the latest saved data from the data table.

Post Data to the Database

When you bind a control to a DataTable containing data from a database and then change data by adding, deleting or modifying records, these changes are accumulated in the DataTable but are not automatically posted to the underlying database. You have to manually call the Update method of the TableAdapter to propagate the modified data to the data source. Before calling this method, make sure that the appropriate INSERT, UPDATE, and DELETE SQL statements are specified for the data adapter. Otherwise, the Update method will generate an exception. For the SuppliersTableAdapter used in the current example, the required commands were generated automatically when the adapter was originally configured.

The table below describes how to add, modify, or delete data in the connected data source step by step.

Action

Implementation

Add a record

Important

The SpreadsheetControl does not support inserting rows at the end of a data-bound range, while a DataTable supports only this kind of operation.

To avoid this restriction, the current example uses a data entry form to add new records to the data source.

  1. Handle the Add Record button’s ItemClick event. Add code that displays the data entry form to the event handler.
  2. Handle the PreviewMouseLeftButtonDown event of the SpreadsheetControl to identify a cell that a user has currently clicked.

    • If the user clicks the Save cell in the data entry form, use the SuppliersDataTable.AddSuppliersRow method to add a new record to the Suppliers data table

      and then call the SuppliersTableAdapter.Update method to save changes to the database.

    • If the Cancel cell is clicked, clear the data entry form and hide it.

All changes made in the data source are immediately reflected in the bound worksheet.

Apply changes

In the Apply Changes button’s ItemClick event handler, call the Update method of the SuppliersTableAdapter to save the modified data to the database.

Cancel changes

Handle the Cancel Changes button’s ItemClick event.

In the event handler, close the cell’s in-place editor if it’s currently active and then call the Fill method of the SuppliersTableAdapter to load the latest saved data from the database.

Remove a record

  1. Handle the Remove Record button’s ItemClick event. In the event handler, verify that the currently selected range belongs to the data-bound range.

    If so, call the RowCollection.Remove method to remove the topmost row of the selected range.

  2. Handle the SpreadsheetControl.RowsRemoving event that fires before a row is deleted. Add code that displays the Delete confirmation dialog to the event handler.
  3. Handle the SpreadsheetControl.RowsRemoved event. This event occurs if a user confirmed the delete operation and the record was deleted. Call the SuppliersTableAdapter.Update method in the event handler to save changes to the database.

Code Sample

View Example

void spreadsheetControl_PreviewMouseLeftButtonDown(object sender, System.Windows.Input.MouseButtonEventArgs e)
{
    Point winPoint = e.GetPosition(spreadsheetControl);
    System.Drawing.Point point = new System.Drawing.Point((int)winPoint.X, (int)winPoint.Y);
    Cell cell = spreadsheetControl.GetCellFromPoint(point);
    if (cell == null)
        return;
    Worksheet sheet = spreadsheetControl.ActiveWorksheet;
    string cellReference = cell.GetReferenceA1();
    // If the "Save" cell is clicked in the data entry form,
    // add a row containing the entered values to the database table.
    if (cellReference == "I4")
    {
        AddRow(sheet);
        HideDataEntryForm(sheet);
        ApplyChanges();
    }
    // If the "Cancel" cell is clicked in the data entry form,
    // cancel adding new data and hide the data entry form.
    else if (cellReference == "I6")
    {
        HideDataEntryForm(sheet);
    }
}

void AddRow(Worksheet sheet)
{
    try
    {
        // Append a new row to the "Suppliers" data table.
        dataSet.Suppliers.AddSuppliersRow(
            sheet["C4"].Value.TextValue, sheet["C6"].Value.TextValue, sheet["C8"].Value.TextValue,
            sheet["E4"].Value.TextValue, sheet["E6"].Value.TextValue, sheet["E8"].Value.TextValue,
            sheet.Cells["G4"].DisplayText, sheet.Cells["G6"].DisplayText);
    }
    catch (Exception ex)
    {
        string message = string.Format("Cannot add a row to a database table.\n{0}", ex.Message);
        MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
}

void HideDataEntryForm(Worksheet sheet)
{
    CellRange range = sheet.Range.Parse("C4,C6,C8,E4,E6,E8,G4,G6");
    range.ClearContents();
    sheet.Rows.Hide(2, 9);
}

void ApplyChanges()
{
    try
    {
        // Send the updated data back to the database.
        adapter.Update(dataSet.Suppliers);
    }
    catch (Exception ex)
    {
        string message = string.Format("Cannot update data in a database table.\n{0}", ex.Message);
        MessageBox.Show(message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
    }
}

void spreadsheetControl_RowsRemoving(object sender, RowsChangingEventArgs e)
{
    Worksheet sheet = spreadsheetControl.ActiveWorksheet;
    CellRange rowRange = sheet.Range.FromLTRB(0, e.StartIndex, 16383, e.StartIndex + e.Count - 1);
    CellRange boundRange = sheet.DataBindings[0].Range;
    // If the rows to be removed belong to the data-bound range,
    // display a dialog requesting the user to confirm the deletion of records. 
    if (boundRange.IsIntersecting(rowRange))
    {
        MessageBoxResult result = MessageBox.Show("Want to delete the selected supplier(s)?", "Delete",
            MessageBoxButton.YesNo, MessageBoxImage.Question);
        applyChangesOnRowsRemoved = result == MessageBoxResult.Yes;
        e.Cancel = result == MessageBoxResult.No;
        return;
    }
}
void spreadsheetControl_RowsRemoved(object sender, RowsChangedEventArgs e)
{
    if (applyChangesOnRowsRemoved)
    {
        applyChangesOnRowsRemoved = false;
        // Update data in the database.
        ApplyChanges();
    }
}
void buttonAddRecord_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
    CloseInplaceEditor();
    Worksheet sheet = spreadsheetControl.ActiveWorksheet;
    // Display the data entry form on the worksheet to add a new record to the "Suppliers" data table.
    if (!sheet.Rows[4].Visible)
        sheet.Rows.Unhide(2, 9);
    spreadsheetControl.SelectedCell = sheet["C4"];
}

void buttonRemoveRecord_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
    CloseInplaceEditor();
    Worksheet sheet = spreadsheetControl.ActiveWorksheet;
    CellRange selectedRange = spreadsheetControl.Selection;
    CellRange boundRange = sheet.DataBindings[0].Range;
    // Verify that the selected cell range belongs to the data-bound range.
    if (!boundRange.IsIntersecting(selectedRange) || selectedRange.TopRowIndex < boundRange.TopRowIndex)
    {
        MessageBox.Show("Select a record first!", "Remove Record", MessageBoxButton.OK, MessageBoxImage.Error);
        return;
    }
    // Remove the topmost row of the selected cell range.
    sheet.Rows.Remove(selectedRange.TopRowIndex);
}

void buttonApplyChanges_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
    CloseInplaceEditor();
    // Update data in the database.
    ApplyChanges();
}

void buttonCancelChanges_ItemClick(object sender, DevExpress.Xpf.Bars.ItemClickEventArgs e)
{
    // Close the cell in-place editor if it's currently active. 
    CloseInplaceEditor();
    // Load the latest saved data into the "Suppliers" data table.
    adapter.Fill(dataSet.Suppliers);
}

void CloseInplaceEditor()
{
    if (spreadsheetControl.IsCellEditorActive)
        spreadsheetControl.CloseCellEditor(DevExpress.XtraSpreadsheet.CellEditorEnterValueMode.Default);
}
See Also