for Microsoft SQL Server
Version 9.0, September 10, 2018
SaveToDB Framework is a server-side part of the SaveToDB add-in.
It allows configuring Excel application features in a database.
The features include:
- Configuring target tables or procedures used to save data back to a database;
- Configuring cell change handlers to check and save data on user input;
- Configuring actions for the Actions and context menus;
- Configuring drill-down queries for double-click and selection change events;
- Configuring validation lists;
- Configuring ribbon parameters and their value lists;
- Configuring Excel formulas selected from views and stored procedures;
- Configuring translation of object names, columns and parameters within Excel;
- Configuring saving and loading Excel table formats from a database;
- Configuring end-user workbooks for Application Workbooks wizard.
These are the basic features of the SaveToDB Framework 8.
You may use SaveToDB Framework 8 as a run-time for production databases.
SaveToDB Framework 9 includes additional features for database developers:
- Generating views and procedures to select data;
- Generating stored procedures for INSERT, UPDATE, and DELETE operations;
- Generating cell change handlers;
- Generating handlers for the Actions and context menus, double-click and selection change events;
- Generating views and procedures to select values for validation lists and parameters;
- Generating primary key, unique, and foreign key constraints;
- Managing role members.
These features save a lot of time for developers.
For example, creating a view and edit procedures to wrap a table takes a minute.
Also, generated objects include the most useful built-in parameters like @column_name or @cell_value and the required configuration to use the objects.
So, this saves a lot of time on finding the required SaveToDB add-in features.
SaveToDB Framework 9 is an application itself.
It configures the framework features using the framework tables.
So, you find good examples for all used features.
You may install, update, and remove SaveToDB Framework using SaveToDB Application Installer wizard.
You may generate a workbook to edit configuration tables using the SaveToDB Application Workbooks wizard.
We also recommend installing the Database Help Framework.
The Database Help Framework is a great tool to learn and document databases.
This documentation is generated using this framework.
Also, we recommend installing the Change Tracking Framework.
The Change Tracking Framework is a great tool integrated with Microsoft Excel to track and revert data changes.
If you are looking for a planning application, take a look at Planning Application for Microsoft and SQL Server.
This is a ready-to-use application. You may also use it to learn design patterns of applications for Microsoft Excel and databases.
We are making a lot for developers.
We love to hear your feedback. Feel free to contact us.
You may install and uninstall the framework using the Application Installer wizard at any time.
1. Open Microsoft Excel, select the Database tab of the SaveToDB add-in.
2. Run Wizards, Application Installer.
3. Connect to your Microsoft SQL Server database and install SaveToDB Framework 9.
4. Create a new workbook and connect to any table in your database using the Connection Wizard.
5. Click the Actions menu and try any feature.
You may create a workbook to edit the configuration tables at any time.
1. Run Wizards, Application Workbooks.
2. Connect to your Microsoft SQL Server database.
3. Generate the savetodb9_configuration.xlsx workbook.
- The xls.xl_actions_generate_procedures procedure allows generating ready-to-use objects:
- SELECT views and procedures, from tables and views;
- procedures for INSERT, UPDATE, and DELETE operations;
- Change event handler procedures.
- The xls.xl_actions_generate_handlers procedure allows generating template procedures of event handlers:
- The Actions menu includes menu items for generating objects described above.
To disable this feature, set the DENY SELECT permission on the xls.view_developer_handlers view.
- The xls.xl_actions_generate_constraints procedure allows generating primary, unique, and foreign keys.
Use the xls.view_primary_keys, xls.view_unique_keys, and xls.view_foreign_keys views to check and generate keys using the context menu.
- The xls.usp_translations procedure allows translating object names, fields, and parameters.
- The xls.view_all_translations view allows translating names into several languages at once.
- The xls.usp_role_members procedure allows managing user roles.
- The xls.xl_export_settings and related import procedures allow exporting and importing settings.
- The xls_formats role includes the permission to save table formats into the xls.formats table.
To add or remove users from a role, use a form based on the xls.usp_role_members procedure.
- The xls.xl_actions_set_role_permissions procedure sets permissions for the framework roles.
- The Application Workbooks wizard includes the new SaveToDB Framework 9 configuration workbook.
- The Actions menu includes updated links to the SaveToDB add-in documentation and SaveToDB Framework objects
|Diagram X01||SaveToDB Framework Tables|
SaveToDB Framework allows customizing Excel applications using database configuration tables.
The xls.objects table contains the configuration how to save data changes back to a database.
For example, you may specify the target table for any view or stored procedure.
Also, you may supply stored procedures or SQL codes for INSERT, UPDATE, and DELETE operations.
The xls.objects table also is a source of the xls.queries view that is used in the SaveToDB Connection Wizard and Query List.
You may add new objects based on SQL codes, HTTP queries, or text files.
Add SQL codes, URLs, and text files into the TABLE_CODE field.
The xls.handlers table contains the handler configuration.
You may use handlers to configure drill-down queries, cell change handlers, validation lists, parameter values, column formulas, and much more.
The xls.translations table contains translations of database objects, columns, and parameters.
The SaveToDB add-in uses this table to translate names within Microsoft Excel depending on the selected data language in the SaveToDB Options dialog box.
So, you may translate database names to business names in any language.
The xls.formats table contains Excel table formats including sets of table views.
The SaveToDB add-in applies these formats in the first connection to database objects.
Users can save and reload table formats using SaveToDB Table Format Wizard or Save Table Format and Load Table Format menu items.
The SaveToDB add-in uses the xls.workbooks table in the Application Workbooks wizard.
You may add workbook definitions and templates for your applications.
These tables are the required run-time framework for Excel applications.
These tables are common for SaveToDB Framework 8 and SaveToDB Framework 9.
You may install SaveToDB Framework 8.11 in production databases, if you do not need advanced features of the SaveToDB Framework 9.
|xls_developers||The role includes permissions for Excel application developers.|
|xls_formats||The role includes permissions for saving Excel table formats.|
|xls_users||The role includes permissions for Excel application users.|
The role includes permissions for Excel application developers.
Assign this role to developers and advanced users who can customize Excel applications.
Members of this role have permissions to read an write the configuration of the SaveToDB Framework used to configure SaveToDB add-in features.
See actual database permissions in the xls.xl_actions_set_role_permissions procedure.
The role includes permissions for saving Excel table formats.
Assign this role to end-users of Excel applications to allow saving Excel table formats into the xls.formats table.
Users may use SaveToDB Table Format Wizard or the Save Table Format menu item to save formats.
This role was added in SaveToDB Framework 8.11.
The role includes permissions for Excel application users.
Assign this role to end-users of Excel applications.
Members of this role have permissions to read the configuration of the SaveToDB Framework used to configure SaveToDB add-in features.
See actual database permissions in the xls.xl_actions_set_role_permissions procedure.
|xls||The schema contains SaveToDB Framework tables, views, and procedures.|
The schema contains SaveToDB Framework tables, views, and procedures.
Members of the xls_users role have permissions to select the configuration.
Members of the xls_developers role have permissions to update the configuration.