Log In
Home
Support
Demos
Documentation
Blogs
Training
Webinars
[Expand]General Information
[Collapse]WinForms Controls
  Prerequisites
 [Expand]What's Installed
 [Expand]Build an Application
 [Collapse]Controls and Libraries
  [Expand]Forms and User Controls
  [Expand]Messages, Notifications and Dialogs
  [Expand]Editors and Simple Controls
  [Expand]Ribbon, Bars and Menu
  [Expand]Application UI Manager
  [Expand]Docking Library
  [Expand]Data Grid
  [Expand]Vertical Grid
  [Expand]Pivot Grid
  [Expand]Tree List
  [Expand]Chart Control
  [Expand]Diagrams
  [Expand]Gauges
  [Expand]Map Control
  [Expand]Scheduler
  [Collapse]Spreadsheet
   [Expand]Product Information
    Product Structure
    Getting Started
   [Expand]Spreadsheet Document
    Supported Formats
   [Expand]Cell Basics
   [Expand]Spreadsheet Formulas
    Defined Names
    Data Binding
    Data Validation
   [Expand]Data Presentation
    Data Grouping
   [Expand]Pivot Table Overview
   [Expand]Charting Overview
    Printing
    Events
   [Expand]Mail Merge Overview
    Protection
    Find and Replace
   [Expand]Visual Elements
    Services
   [Expand]Shortcuts
   [Collapse]Examples
    [Expand]Files
    [Expand]Workbooks
    [Expand]Worksheets
    [Expand]Rows and Columns
    [Expand]Cells
    [Expand]Formulas
    [Expand]Import and Export Data
    [Collapse]Data Binding
      How to: Bind a Spreadsheet to an MS SQL Server Database (Part 1)
      How to: Bind a Spreadsheet to an MS SQL Server Database (Part 2)
      How to: Bind a Data Grid to a Cell Range
      How to Bind a Spreadsheet to a List of Objects
    [Expand]Mail Merge
    [Expand]Formatting Cells
    [Expand]Conditional Formatting
    [Expand]Group Data
    [Expand]Filter Data
    [Expand]Tables
    [Expand]Pivot Tables
    [Expand]Printing
    [Expand]Pictures
    [Expand]Charts
    [Expand]Protection
    [Expand]Customization
  [Expand]Rich Text Editor
  [Expand]Spell Checker
  [Expand]Form Layout Managers
  [Expand]Navigation Controls
  [Expand]Printing-Exporting
  [Expand]PDF Viewer
   Reporting
  [Expand]Snap
  [Expand]TreeMap Control
 [Expand]Common Features
  Get More Help
 [Expand]API Reference
[Expand]ASP.NET Controls and MVC Extensions
[Expand]ASP.NET Bootstrap Controls
[Expand]WPF Controls
[Expand]Xamarin Controls
[Expand]Windows 10 App Controls
[Expand]Document Server
[Expand]Reporting
[Expand]Report Server
[Expand]Dashboard
[Expand]eXpressApp Framework
[Expand]CodeRush
[Expand]Cross-Platform Core Libraries
[Expand]Tools and Utilities
 End-User Documentation

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

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.

Expanded 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.

    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.

The table below contains the description of each button.

Caption

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.
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.

Expanded 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 MouseClick 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.

Expanded Code Sample

Show Me

A complete sample project is available in the DevExpress Code Examples database at http://www.devexpress.com/example=T472324.

How would you rate this topic?​​​​​​​