How to Save Changes with SaveToDB Add-In

The SaveToDB add-in enables 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 the saving process for any object, such as by providing stored procedures to handle changes.

Remember, users must click the Save button to commit their changes. Developers can also set up cell change handlers to save changes immediately after a cell is modified.

For more details, see the Developer Guide.

Common Requirements for Saving Changes

To support saving changes, an Excel table must include a row number column, such as _RowNum in the English version.

To enable row numbers, open the Excel Data tab, select Connections, then Properties, and check Include row numbers.

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

Additionally, the workbook must contain the SaveToDB data worksheets. The add-in automatically adds these sheets the first time you use the Database Connection Wizard or the Web Data Connection Wizard.

To activate saving changes in an existing workbook, simply reconnect the table using the wizard.

Saving Changes for Tables

To enable saving changes for tables, ensure the following three requirements are met:

1. The database table must have primary key or identity columns.
2. Primary key or identity columns must be selected.
3. The 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 scenarios:

1. The view is updatable by default.
2. The view is updatable via instead-of triggers.
3. The add-in can detect the underlying table, which must support saving changes.

To facilitate the detection of underlying tables, grant the VIEW DEFINITION permission.

In the first two scenarios, the add-in generates INSERT, UPDATE, and DELETE commands on the views. In the third scenario, it operates on the underlying tables.

Saving Changes for Stored Procedures

The SaveToDB add-in can save changes from stored procedures if it can detect the underlying table.

To enable this detection, grant the VIEW DEFINITION permission on the relevant procedures.

Saving Changes for SQL Queries

The SaveToDB add-in parses SQL queries and can identify underlying tables, views, and stored procedures. It applies the same rules discussed above for these objects.

Saving Changes for REST API Objects

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

Saving Changes for OData Objects

The SaveToDB add-in supports saving changes for OData EntitySets by default. However, note that services can disable saving changes through metadata annotations.

Saving Changes for DBGate Objects

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