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:
- A database table must have primary key or identity columns.
- Primary key or identity columns must be selected.
- 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:
- A view is updatable by default.
- A view is updatable by instead-of triggers.
- 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.