SaveToDB Framework 7 for SQL Server Compact

SaveToDB Framework 7 for SQL Server Compact

SaveToDB Framework for Microsoft SQL Server Compact

SaveToDB Framework for Microsoft SQL Server Compact contains ready-to-use solutions for application development using Microsoft Excel and Microsoft SQL Server Compact.

The framework can be easily installed and uninstalled.

The framework can be installed from the SaveToDB add-in in a couple of minutes. Also, you can generate an Excel workbook to customize SaveToDB features using Microsoft Excel.

See SaveToDB Framework Installer and Configuration Workbook Generator.

 

After installation you can customize required features:

 

Microsoft SQL Server Compact supports fewer features than other database servers.
Views and stored procedures are not supported. A standalone database file is used instead of a server database.

Microsoft SQL Server Compact has no OLEDB providers or ODBC drivers to use with Microsoft Excel.
SaveToDB uses .NET provider for working with a database file.

 

At the same time, Microsoft Excel applications with underlying Microsoft SQL Server Compact databases have great features:

  • The same data can be used on different sheets in different workbooks as the data stored in database tables.
  • The data views can be customized using SQL codes.
  • SQL codes or HTTP queries can be easily executed using the ribbon Query List, Microsoft Excel context menu, or the SaveToDB Actions menu.
  • SQL codes can be used for handling Microsoft Excel events.
  • Table fields can be translated into multiple languages within Microsoft Excel.

Microsoft SQL Server Compact perfectly complements Microsoft Excel for implementing applications for personal use.

For example, SQL codes can be used to build DDE formulas instead of Excel macros.

 

Additional tools for working with Microsoft SQL Server Compact are not necessary as SaveToDB allows end users to:

Install and Uninstall

Installation Methods

SaveToDB Framework supports four methods to install or remove the framework:

  1. Installation using the SaveToDB Framework Installer integrated into the SaveToDB add-in.
  2. Installation using the standalone SaveToDB Framework Installer.
  3. Installation using source codes.
  4. Installation using batch files.

The first two methods allow installing or removing the framework in databases on all supported platforms:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, NuoDB, PostgreSQL, and SQLite.

See SaveToDB Framework Installer.

The source code is located in the "SaveToDB Framework for SQL Server Compact" folder:

  • framework-install-en.sql
  • framework-remove-en.sql

The installation using batch files is described below.

Install and Uninstall Using Batch Files

The source code and batch files are located in the "SaveToDB Framework for SQL Server Compact" folder.

Installing SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-install-savetodb-framework.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Instead of clearing the setup password on step 3, you may protect gsqlcmd.exe.config:

select the "User protected" option in gConnectionManager.

Uninstalling SaveToDB Framework

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-remove.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Verifying Framework Objects

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run framework-list.cmd.
  3. Run 3-clear-credentials.cmd and clear the "setup" connection password.

Query Lists

Preface

SaveToDB allows changing queries using the ribbon Query List.

It is a useful feature as application workbooks can contain fewer worksheets. Also, users can easily connect to new database objects after query list refresh.

The query lists can contain database tables, custom SQL codes, HTTP and text file queries.

The query list configuration views are also used to configure base tables or custom SQL codes to save changes of data loaded from SQL codes and from HTTP and text file queries.

SaveToDB Framework for Microsoft SQL Server Compact creates database tables to configure these advanced features.

See Configuring Query List, Configuring Saving Changes.

Configuration Data Table

The QueryList table contains query list configuration data and is used by the SaveToDB add-in.

CREATE TABLE [QueryList](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_TYPE] [nvarchar](128) NOT NULL,
    [TABLE_CODE] [ntext],
    [INSERT_PROCEDURE] [ntext],
    [UPDATE_PROCEDURE] [ntext],
    [DELETE_PROCEDURE] [ntext],
    [PROCEDURE_TYPE] [nvarchar](128),
 CONSTRAINT [PK_QueryList] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [QueryList_TableName]
    ON [QueryList] (
    [TABLE_NAME]
);

The table is used to:

  • Define database objects to show in the ribbon Query List.
    Specify table names in the TABLE_NAME field.
  • Define SQL codes to select data using the ribbon Query List.
    Specify an SQL code in the TABLE_CODE field and the CODE type in the TABLE_TYPE field.
  • Define HTTP queries to load web data using the ribbon Query List.
    Specify an HTTP query in the TABLE_CODE field and the HTTP type in the TABLE_TYPE field.
  • Define queries to load data from text files using the ribbon Query List.
    Specify a text file query in the TABLE_CODE field and the TEXT type in the TABLE_TYPE field.
  • Define base tables to save changes of data selected using SQL codes or to merge data loaded from any data source including HTTP and text file queries.
    To have the possibility to save changes, specify the table in all INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
    To have the possibility to merge data, specify the table in the INSERT_PROCEDURE field only.
  • Define SQL codes to save changes of data selected using SQL codes.
    Specify the code in the INSERT_PROCEDURE, UPDATE_PROCEDURE, and DELETE_PROCEDURE fields.
  • Define SQL codes to merge data loaded from any data source including HTTP and text file queries.
    Specify the code in the INSERT_PROCEDURE field.

TABLE_NAME field values must be non-empty and unique.

The TABLE_SCHEMA field is ignored by the SaveToDB add-in. However, the field may be used by developers.

A database can contain multiple query list tables used for different business areas. Create other query list tables using the code above or using the Publish Wizard with the QueryList table as a source.

The tables are used in Microsoft SQL Server Compact instead of configuration views in other database platforms.

See Configuring Query List, Configuring Saving Changes.

Event Handlers

Preface

SaveToDB allows using SQL codes and HTTP queries for handling the following Microsoft Excel events:

  • Change
  • DoubleClick
  • SelectionChange

Using event handling developers can implement tasks like database data changes, drill-down queries, or master-detail interfaces.

Also, developers can customize the Microsoft Excel context menu and SaveToDB Actions menu to run:

  • SQL codes
  • HTTP queries
  • Text file queries
  • Macros
  • Windows Shell and CMD commands

See Configuring Event Handlers.

Configuration Data Table

The EventHandlers table contains configuration data used by the SaveToDB add-in to get the event handler configuration data.

CREATE TABLE [EventHandlers](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [COLUMN_NAME] [nvarchar](128),
    [EVENT_NAME] [nvarchar](50) NOT NULL,
    [HANDLER_SCHEMA] [nvarchar](128),
    [HANDLER_NAME] [nvarchar](128) NOT NULL,
    [HANDLER_TYPE] [nvarchar](60),
    [HANDLER_CODE] [ntext],
    [TARGET_WORKSHEET] [nvarchar](255),
    [MENU_ORDER] [int],
    [EDIT_PARAMETERS] [bit],
 CONSTRAINT [PK_EventHandlers] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [EventHandlers_TableName_ColumnName_EventName_HandlerName]
    ON [EventHandlers] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [EVENT_NAME],
    [HANDLER_NAME]
);

See field description in the Configuring Event Handlers topic.

The TABLE_SCHEMA field is ignored in Microsoft SQL Server Compact configurations. Database tables and non-database objects must be identified by unique names.

The HANDLER_CODE field can contain SQL codes, HTTP and text queries, macro codes, or Windows Shell and CMD commands.

Data Translation

Preface

SaveToDB allows translating names of database tables, table fields, and code parameters within Microsoft Excel.

It is a useful feature as the names can be changed in configuration tables without changes of underlying database objects.

In addition, the translation data are used in ScreenTips and table header comments.

See Configuring Data Translation.

Object Translation Table

The ObjectTranslation table contains database object translation data and is used by the SaveToDB add-in to get the object translation data.

CREATE TABLE [ObjectTranslation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128) NULL,
    [TRANSLATED_DESC] [nvarchar](255) NULL,
    [TRANSLATED_COMMENT] [nvarchar](4000) NULL,
 CONSTRAINT [PK_ObjectTranslation] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [IX_ObjectTranslation_TableName_LanguageName]
    ON [ObjectTranslation] (
    [TABLE_NAME],
    [LANGUAGE_NAME]
);

See field description in the Configuring Data Translation topic.

The TABLE_SCHEMA field is ignored in Microsoft SQL Server Compact configurations. Database tables and non-database objects must be identified by unique names.

The size of the TRANSLATED_* fields can be changed.

Column Translation Table

The ColumnTranslation table contains database object field and parameter translation data and is used by the SaveToDB add-in.

CREATE TABLE [ColumnTranslation](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NULL,
    [COLUMN_NAME] [nvarchar](128) NOT NULL,
    [LANGUAGE_NAME] [nchar](2) NOT NULL,
    [TRANSLATED_NAME] [nvarchar](128) NULL,
    [TRANSLATED_DESC] [nvarchar](1024) NULL,
 CONSTRAINT [PK_ColumnTranslation] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [ColumnTranslation_TableName_ColumnName_Language]
    ON [ColumnTranslation] (
    [TABLE_NAME],
    [COLUMN_NAME],
    [LANGUAGE_NAME]
);

See field description in the Configuring Data Translation topic.

The TABLE_SCHEMA field is ignored in Microsoft SQL Server Compact configurations. Database tables and non-database objects must be identified by unique names.

Parameter Values

Preface

SaveToDB allows using SQL codes to populate ribbon parameter values.

The ribbon parameters are used to change parameters of SQL codes, HTTP and text file queries, or WHERE fields for tables.

See Configuring Parameter Values.

Configuration Data Table

The ParameterValues table contains configuration data used by the SaveToDB add-in.

CREATE TABLE [ParameterValues](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128),
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [PARAMETER_NAME] [nvarchar](128) NOT NULL,
    [SELECT_SCHEMA] [nvarchar](128),
    [SELECT_NAME] [nvarchar](128) NOT NULL,
    [SELECT_TYPE] [nvarchar](128),
    [SELECT_CODE] [ntext],
 CONSTRAINT [PK_ParameterValues] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [ParameterValues_SpecificName_ParameterName]
    ON [ParameterValues] (
    [SPECIFIC_NAME],
    [PARAMETER_NAME]
);

See field description in the Configuring Parameter Values topic.

The TABLE_SCHEMA field is ignored in Microsoft SQL Server Compact configurations. Database tables and non-database objects must be identified by unique names.

The SELECT_CODE field can contain SQL codes to select parameter values. In this case, the SELECT_TYPE field must contain the CODE type.

Table Formats

Preface

SaveToDB allows storing and distributing Microsoft Excel table formats of database objects using a database.

Database objects are formatted in Microsoft Excel when the objects are connected for the first time, or when required using the Table Format Wizard.

Table formats include the following elements:

  • Cell format.
  • Conditional formatting.
  • Formula columns.
  • Table totals.
  • Applied auto-filters.
  • Print page setup.
  • Window properties.
  • Column visibility.
  • Custom table views.

Use the Table Format Wizard to save, clear, or apply table formats.

See also Configuring Table Formats.

Configuration Data Table

The TableFormats table contains Microsoft Excel table formats of database objects.

CREATE TABLE [TableFormats](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TABLE_SCHEMA] [nvarchar](128) NULL,
    [TABLE_NAME] [nvarchar](128) NOT NULL,
    [TABLE_EXCEL_FORMAT_XML] [ntext] NULL,
 CONSTRAINT [PK_TableFormat] PRIMARY KEY
(
    [ID]
)
);

CREATE UNIQUE INDEX [TableFormats_TableName]
    ON [TableFormats] (
    [TABLE_NAME]
);

The data are updated by the SaveToDB add-in using built-in procedures.

Version History

Version 7.2, March 20, 2017

[+] New types of event handlers: ConvertFormulas and DoNotConvertFormulas.
[*] Added validation lists for the QueryList, EventHandlers, and ParameterValues tables.
[*] The EventHandlers.HANDLER_NAME field may have NULL values.

Version 7.1, February 3, 2017

[*] New VALUES and RANGE types in the SELECT_TYPE field.
[*] New VALUES and RANGE types in the HANDLER_TYPE field.

Version 7.0, January 17, 2017

[*] Updated batch files and readme.txt.
SQL codes have no changes.

Version 6.9, June 21, 2016

[+] A new event handler type: ProtectRows.

Version 6.7, April 14, 2016

[+] The framework includes updated validation lists of the EVENT_NAME column of the event handler configuration tables.
Use savetodb-framework-62-upgrade-to-67.sql to update the existing frameworks.
Use Table Format Wizard to reload validation lists of existing Excel configuration tables.
Also, you may create new configuration workbooks using Configuration Workbook Generator.

Version 6.2, December 23, 2015

[+] New event handler types: DoNotSelect, DoNotSave, DoNotChange, Formula, FormulaValue, and ValidationList.

Version 5.2, May 12, 2015

[*] Batch files have been updated.

Version 5.0, April 13, 2015

[*] The TABLE_CODE and PROCEDURE_TYPE fields have been added to the QueryList table.
[*] The HANDLER_CODE field has been added to the EventHandlers table.
[*] The SELECT_CODE field has been added to the ParameterValues table.
[*] The SPECIFIC_SCHEMA and SPECIFIC_NAME fields have been renamed to TABLE_SCHEMA and TABLE_NAME in the ParameterValues table.
[*] Types of the TABLE_SCHEMA and HANDLER_SCHEMA fields have been changed to nvarchar(128).

Version 4.7, July 15, 2014

[*] The batch files have been updated for using the gsqlcmd.exe command line utility instead of SqlCeCmd.exe.
As a result, the framework operations can be executed on a machine without Microsoft SQL Server Compact components installed.

Version 4.0, December 13, 2013

[*]The type of the TABLE_SCHEMA field of the QueryList table has been changed to ntext.
This field can contain SQL codes, HTTP and text file queries.
[*]The types of the INSERT_PROCEDURE, UPDATE_PROCEDURE and DELETE_PROCEDURE fields of the QueryList table have been changed to ntext.
These fields can contain SQL codes to save data changes.
[*]The type of the HANDLER_SCHEMA field of the EventHandlers table has been changed to ntext.
This field can contain SQL codes, HTTP and text file queries, macros, Windows Shell and CMD commands.
[*]The size of the TARGET_WORKSHEET field of the EventHandlers table has been changed to 255 characters.
This field can contain a sheet list to refresh data if the REFRESH handler is specified.
[*]The type of the SELECT_SCHEMA field of the ParameterValues table has been changed to ntext.
This field can contain SQL codes to select field and parameter values.
[*]Unique indexes to framework tables have been added.
[*]Actions menu links to online help for framework tables have been added.

Version 3.0, June 10, 2013

[+]The first version.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of Microsoft SQL Server Compact:

  • Microsoft SQL Server Compact 3.5
  • Microsoft SQL Server Compact 4.0

SaveToDB Examples for Microsoft SQL Server Compact

SaveToDB SDK includes ready-to-use Microsoft Excel workbooks and Microsoft SQL Server Compact databases for a dozen of applications and examples.

The SDK includes complete source codes and batch files used to install and remove applications.

The examples for Microsoft SQL Server Compact show a complete set of SaveToDB features used for Excel application development.

Developer Guide 6.5

This guide shows how to develop Microsoft Excel applications using the SaveToDB add-in with Microsoft SQL Server Compact.

This is a complete guide. Try it first.

Application - Web Finance

The example application allows getting end-of-a-day stock data from Google Finance or Yahoo! Finance and saving loaded data into a local Microsoft SQL Server Compact database.

Application - AdWords Keyword Generator

This application allows generation keywords for Google AdWords using semantic model.

Example - Monthly Cash Flow

The example application allows getting money transactions in one place and building cash flow statements.
The application uses a local Microsoft SQL Server Compact database, so you may use it right after installation.

Example - Monthly Cash Flow Adv + VBA

The example application allows getting money transactions in one place and building cash flow statements.
In addition, the example shows how to work with normalized tables and to use SaveToDB as a VBA library.
The application uses a local Microsoft SQL Server Compact database, so you may use it right after installation.

Example - Northwind

The example shows new SaveToDB 5.0 features:

  • Cursors and forms fields.
  • Implementing master-detail interfaces.
  • Showing images.
  • Pivot table views.

Example - Budget Pivot

The example shows new SaveToDB 5.0 features:

  • Using pivot tables as edit forms.
  • Pivot table views.

Example - DDE Interactive Brokers

The example shows how to use and configure DDE formulas without using macros.
The example works with Trader Workstation (TWS), a powerful trading platform from Interactive Brokers.

Example - DDE Thinkorswim

The example shows how to use and configure DDE formulas without using macros.
The example works with Thinkorswim Desktop, a powerful trading platform from Thinkorswim, Division of TD Ameritrade.

Example - Loading Text

The example shows how to load data from text files in the CSV, XML, and HTML formats into a database.

Example - Saving Changes to Database

The example shows how to save data changes back to a database using different techniques.

Example - Saving Formula Values

The example shows how to save values calculated by Excel formulas to a database.

Example - Using Event Handlers

The example shows how to use database objects and codes for Excel event handling.
This is a simple and powerful feature. It allows saving changes on the cell change event, implementing master-detail interfaces, drill-down queries, etc.

Example - Using Macros

The example shows how to use macros with the SaveToDB add-in.
The SaveToDB add-in brings additional benefits for traditional Excel applications built using macros.

Installing and Uninstalling Examples

Installing Application Examples for Microsoft SQL Server Compact

Use the following steps to install the application examples for Microsoft SQL Server Compact:

  1. Install example database objects.
  2. Change connection data in Microsoft Excel example workbooks.

Installing Examples

The source codes of examples for Microsoft SQL Server Compact are located in the "source code" subfolders.

To install the example, open the subfolder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run 2-create-database.cmd if the database does not exist.
  3. Run 3-install-savetodb-framework.cmd if SaveToDB Framework for Microsoft SQL Server Compact is not installed yet.
  4. Run 4-install-application.cmd.
  5. Run 5-clear-credentials.cmd and clear the "setup" connection password.

You may create a Microsoft SQL Server Compact database in a visual mode in the first step.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-install.sql file to install the examples.

Changing Excel Workbook Connections

Examples contain preconfigured Excel workbooks.

You should change the database files and passwords in the workbooks if you have changed the default settings in the previous step.

By default, all the examples use database files with empty passwords in the example folders.

You may use the Change Connection Wizard to modify all connections at once:

  1. Select the SaveToDB tab and run the Change Connection Wizard,
  2. Connect to the target database.
  3. Select all the connections and click Finish.

Then you should refresh all the data tables. You may use the Reload, Reload All Workbook Tables menu item.

Uninstalling Application Examples for Microsoft SQL Server Compact

To uninstall the application example, open the "source code" folder and do the following:

  1. Run 1-edit-config.cmd and edit the "setup" connection.
  2. Run application-remove.cmd.
  3. Run 5-clear-credentials.cmd and clear the "setup" connection password.

Instead of clearing the setup password on the last step, you may protect gsqlcmd.exe.config by selecting the "User protected" option in gConnectionManager.

Alternatively to batch files, you may use queries from the application-remove.sql file.

Command Line Utility gsqlcmd

The batch files to install and remove the examples use the command line utility gsqlcmd.
gsqlcmd allows executing SQL scripts and exporting data against all SaveToDB supported database platforms including Microsoft SQL Server Compact.

SaveToDB installs gsqlcmd and changes the PATH environment variable that allows using gsqlcmd anywhere.

SaveToDB SDK includes the gsqlcmd folder with the executables, and the utility is available from the SaveToDB SDK subfolders only.

Version History

Version 7.2, March 20, 2017

[*] Updated SaveToDB Framework 7.2.

Version 7.0, January 17, 2017

[*] Updated SaveToDB Framework 7.0, batch files, and readme.txt.
[*] Updated example dates.

System Requirements

Supported Versions of SaveToDB:

  • SaveToDB 5.0 and higher

Supported Versions of SaveToDB Framework for Microsoft SQL Server Compact:

  • SaveToDB Framework 5.0 for Microsoft SQL Server Compact and higher

Supported Versions of Microsoft SQL Server Compact:

  • Microsoft SQL Server Compact 4.0