Saving Changes

Saving Changes

The SaveToDB add-in allows saving data changes by default for the following objects:

  • Database tables
  • Database views, stored procedures, and SQL queries
  • REST API queries
  • OData objects
  • DBGate objects

Developers can customize saving changes for any object.

For example, developers can supply stored procedures to save changes.

Note that users must click the Save button to save changes.

Developers can configure cell change handlers to save changes after the cell change immediately.

See Developer Guide to learn more.

Common Requirements for Saving Changes

An Excel table must have the row number column like _RowNum in the English version to support saving changes.

Open the Excel Data, Connections, Properties dialog box and check Include row numbers to turn on row numbers.

Alternatively, open the SaveToDB, Reload, Configure Query dialog box and click OK.

Also, the workbook must have the SaveToDB data worksheets.

The add-in adds such sheets automatically on the first use of the Database Connection Wizard or the Web Data Connection Wizard.

So, to activate saving changes in your existing workbook, reconnect the table using the wizard.

Saving Changes for Tables

There are three requirements to enable saving changes for tables:

  1. A database table must have primary key or identity columns.
  2. Primary key or identity columns must be selected.
  3. A user must have INSERT, UPDATE, and DELETE permissions on the table.

Saving Changes for Views

The SaveToDB add-in allows saving changes for views by default in three cases:

  1. A view is updatable by default.
  2. A view is updatable by instead-of triggers.
  3. The add-in can detect the underlying table, and the underlying table supports saving changes.

To allow detecting underlying tables, grant the VIEW DEFINITION permission.

In the first two cases, the add-in generates INSERT, UPDATE, and DELETE commands on views, and in the third case, on the underlying tables.

Saving Changes for Stored Procedures

The SaveToDB add-in allows saving changes of data from stored procedures if it can detect the underlying table.

To allow detecting underlying tables, grant the VIEW DEFINITION permission on the required procedures.

Saving Changes for SQL Queries

The SaveToDB add-in parses SQL queries and can detect underlying tables, views, and stored procedures.

Accordingly, it applies the rules discussed above.

Saving Changes for REST API Objects

Users can define REST API commands for insert, update, and delete operations in the Web Data Connection Wizard.

Note that developers can configure REST API commands also.

Saving Changes for OData Objects

The SaveToDB add-in supports saving changes for OData EntitySets by default.

Note that services can disable saving changes using the metadata annotations.

Saving Changes for DBGate Objects

The SaveToDB add-in supports saving changes for DBGate objects by default.