Configuring Saving Changes

Configuring Saving Changes

Preface

SaveToDB allows saving data changes made in Microsoft Excel back to a database or OData web service.

SaveToDB supports the following scenarios:

Data SourceSave TypeStart Event
Database tableDefault saving for tablesSave button
View, procedure, or SQL codeSaving or merging to a base tableSave button
View, procedure, or SQL codeSaving or merging using SQL codeSave button
View, procedure, or SQL codeSaving or merging using stored proceduresSave button
OData EntitySetDefault saving for ODataSave button
HTTP-query or text fileMerging into a base tableSave button
HTTP-query or text fileMerging using SQL codeSave button
HTTP-query or text fileMerging using a stored procedureSave button
Any data sourceSaving using an event handlerChange event
Any data sourcePublish WizardWizard run
Any data sourceMerge WizardWizard run

There are three groups of the scenarios:

  1. Saving changes using the Save button.
  2. Saving changes using change event handlers.
  3. Publishing or merging data using SaveToDB wizards.

The second and third scenarios are described in the appropriate topics.

This topic is focused on the first group, saving changes using the Save button.

There are two different methods to save data depend on data sources:

  1. Saving data changes including inserting new rows, updating existing rows and deleting deleted rows.
  2. Merging data including inserting new rows and updating existing rows.

Saving data changes is used for data from databases or OData services. Merging data is used for other sources.

There are four implementation methods for saving or merging data:

  1. Saving data changes for tables and OData EntitySets from-the-box, without any coding.
  2. Saving or merging data into a base table using SaveToDB internal procedures.
  3. Saving or merging data using custom stored procedures.
  4. Saving or merging data using custom SQL codes.

Saving OData data is available in the SaveToDB Enterprise edition and the trial version.

SaveToDB saves the data required to save changes on hidden workbook sheets.
The workbook can be closed and reopened multiple times before saving changes to a database.
The hidden sheets are being inserted during the connection using the Data Connection Wizard or from the Options dialog box.

SaveToDB saves the changes in one transaction under the Read Committed isolation level.

The Query List configuration views are used to configure saving changes.
The Data Connection Wizard is used to select and modify the query list view for connected objects.

Configuration View

SaveToDB reads the query list configuration from tables* and views that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. TABLE_CODE
  5. INSERT_PROCEDURE
  6. UPDATE_PROCEDURE
  7. DELETE_PROCEDURE
  8. PROCEDURE_TYPE

* Tables are used starting SaveToDB 7 and in previous versions for Microsoft SQL Server Compact.

The tables and views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPETABLE_CODEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDUREPROCEDURE_TYPE
<Table schema><Table name>TABLE [<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
PROCEDURE
/CODE
<View schema><View name>VIEW [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Procedure schema><Procedure name>PROCEDURE [<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><SQL code name>CODE<SQL code>[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
TABLE
/PROCEDURE
/CODE
<Object schema><HTTP-query name>HTTP<HTTP-query>[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE
<Object schema><text-query name>TEXT<File name>
[;CodePage=
<Code page>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]TABLE
/PROCEDURE
/CODE

TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, and TABLE_CODE are used to specify the query list objects and are described in the Configuring Query List topic.

INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE are used to specify:

  • A target database table ("base table").
  • Custom stored procedures used to save or merge data.
  • Custom SQL codes used to save or merge data.

A base table, if specified, must be the same in the all three fields. See details about the mode below.

To merge data instead of saving, specify a base table, stored procedure, or an SQL code in the INSERT_PROCEDURE field only.
In this case, the action of the Save button is the same as Save by Merge.

The PROCEDURE_TYPE field is reserved for the future extensions and may specify the type of the edit procedure objects.
However, SaveToDB add-in detects types of database objects itself and allows mixing stored procedures and SQL codes.

Configuration View 2.x-4.x

SaveToDB also reads the query list configuration from the views* in the previous format that contain the following fields:

  1. TABLE_SCHEMA
  2. TABLE_NAME
  3. TABLE_TYPE
  4. INSERT_PROCEDURE
  5. UPDATE_PROCEDURE
  6. DELETE_PROCEDURE

* Tables are used instead of views for Microsoft SQL Server Compact.

The views can contain an additional primary key column.

Configuration data formats:

TABLE_SCHEMATABLE_NAMETABLE_TYPEINSERT_PROCEDUREUPDATE_PROCEDUREDELETE_PROCEDURE
<Table schema><Table name>TABLE[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
[<Procedure>
/<SQL code>]
<View schema><View name>VIEW[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<Procedure schema><Procedure name>PROCEDURE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<SQL code><SQL code name>CODE[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
[<Table>
/<Procedure>
/<SQL code>]
<HTTP-query><HTTP-query name>HTTP[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]
<File name>[;CodePage=<Code page>]<text-query name>TEXT[<Table>
/<Procedure>
/<SQL code>]
[<Table>][<Table>]

TABLE_SCHEMA, TABLE_NAME, and TABLE_TYPE are used to specify the query list objects and are described in the Configuring Query List topic.

INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE are used to specify:

  • A target database table ("base table").
  • Custom stored procedures used to save or merge data.
  • Custom SQL codes used to save or merge data.

A base table, if specified, must be the same in the all three fields. See details about the mode below.

To merge data instead of saving, specify a base table, stored procedure, or an SQL code in the INSERT_PROCEDURE field only.
In this case, the action of the Save button is the same as Save by Merge.

Saving Changes to Base Table

Saving changes to a base table allows implementing saving data of simple SQL queries, views, and stored procedures back to a database quickly and without coding.

For example, the following stored procedure selects the data from a single table:

CREATE PROCEDURE [xls].[uspStockHistory]
    @Symbol varchar(5) = 'YHOO'
AS
BEGIN
    SELECT
        *
    FROM
        dbo.StockTradeHistory th
    WHERE
        th.Symbol = @Symbol
    ORDER BY
        th.[Date] DESC
END

The data changes can be saved to a database if the dbo.StockTradeHistory table is specified as a base table for the xls.uspStockHistory procedure. It is simple.

The merge mode can be used for any object but is the most useful for HTTP-queries and text file queries.

In the merge mode, the SaveToDB add-in generates and executes INSERT and UPDATE statements for all rows in the Excel table.

Excel formulas can prepare base table data. For example, required FirstName and LastName columns can be calculated from an existing Name column.

Saving Changes using Custom SQL Codes and Stored Procedures

You may implement any logic of saving data changes using custom SQL codes or stored procedures, including saving data to multiple tables.

In the save mode, SaveToDB executes the INSERT_PROCEDURE SQL-code or stored procedure for new data rows, the UPDATE_PROCEDURE ones for updated data rows, and the DELETE_PROCEDURE ones for deleted rows.

In the merge mode, SaveToDB executes the INSERT_PROCEDURE SQL-code or stored procedure for each data row. So, the merge logic must be implemented by a developer.

The SQL codes and stored procedures must have parameters to get the data from Excel.

See Using Parameters about all possible parameter value sources.

The most used source is Excel table data. It is important that the parameter names should be identical to the column names.
This rule is a constraint for the database query column names.
However, there are no constraints on translated column names.

Configuration View Example

There is an example of the configuration view (in the SaveToDB 4.x format):

Default Query List Configuration View Considerations

The SaveToDB default query list query uses the "_insert", "_update", and "_delete" suffixes of stored procedure names to detect procedures to save changes of a base object, as shown above.

The "_select" suffix of a base object is omitted before linking. So the same procedures can be used for two objects: a base view and a base procedure with the "_select" suffix.

Of course, database developers can define other rules of linking in custom views.

Checking and Debugging Configuration

Use the Workbook Information dialog box to check the loaded configurations.

Use the View Save Change SQL item of the Save menu to check the generated SQL code to save data changes.

Creating Database Configuration Objects

SaveToDB Framework contains query list views that can be used as a data source for custom views on all supported platforms:
Microsoft Azure SQL Database, Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

SaveToDB Framework for Microsoft SQL Server also contains a stored procedure to generate custom INSERT, UPDATE and DELETE procedures.