SaveToDB Framework для SQL Server Compact

SaveToDB Framework для SQL Server Compact

SaveToDB Framework for Microsoft SQL Server Compact

SaveToDB Framework for Microsoft SQL Server Compact содержит готовые решения задач разработки приложений с использованием Microsoft SQL Server Compact и Microsoft Excel.

Фреймворк корректно устанавливается и удаляется.

Фреймворк можно быстро установить непосредственно из плагина SaveToDB и сгенерировать книгу для настройки фреймворка из Microsoft Excel.

См. Мастер установки SaveToDB Framework и Мастер создания книги настройки.

 

Далее можно настроить наиболее интересующие возможности:

 

Microsoft SQL Server Compact содержит значительно меньше возможностей, чем остальные сервера баз данных.
Не поддерживаются представления и хранимые процедуры. Подключение производится к файлу, а не серверу.

Microsoft SQL Server Compact не имеет OLEDB провайдера или ODBC драйвера, с которым может работать Microsoft Excel.
Вся работа с файлом базы данных ведется через .NET провайдер непосредственно плагином SaveToDB.

Но в то же время, приложения на Microsoft Excel получаются с большими возможностями:

  • Хранение данных в таблице позволяет использовать их на различных листах и в различных книгах.
  • За счет применения SQL-кода можно одни и те же данные видеть в различных разрезах.
  • За счет применения SQL-кода и http-запросов можно получать данные как из Списка запросов и контекстных меню, так и в обработчиках событий.
  • Имена таблиц и полей таблиц могут быть разными в Microsoft Excel и базе данных за счет возможностей перевода.

При персональном использовании данных Microsoft SQL Server Compact отлично дополняет недостающие возможности Microsoft Excel.

Очень удобно использовать SQL-код для формирования формул DDE, что позволяет отказаться от использования макросов.

 

Для работы с Microsoft SQL Server Compact не требуются дополнительные инструменты, т.к. плагин SaveToDB позволяет:

Установка и удаление

Варианты установки и удаления SaveToDB Framework

Возможны следующие варианты установки и удаления SaveToDB Framework:

  1. С использованием Мастера установки SaveToDB Framework из плагина SaveToDB.
  2. С использованием программы SaveToDB Framework Installer из состава SaveToDB SDK.
  3. С использованием исходного SQL-кода.
  4. С использованием командных файлов.

Первые два способа позволяют установить или удалить SaveToDB Framework во всех поддерживаемых серверах баз данных:
Microsoft SQL Server, Microsoft SQL Server Compact, Oracle Database, IBM DB2, MySQL, MariaDB, PostgreSQL, NuoDB и SQLite.

Описание шагов SaveToDB Framework Installer полностью совпадает с Мастером установки SaveToDB Framework.

Исходный SQL код находится в папке "SaveToDB Framework for SQL Server Compact" в файлах:

  • framework-install-ru.sql
  • framework-remove-ru.sql

Вариант с использованием командных файлов описан ниже.

Замечания к установке и удалению

В процессе удаления удаляются только объекты, которые были созданы в процессе установки фреймворка.

Важно! При удалении объектов SaveToDB Framework удаляется вся информация о выполненных настройках из конфигурационных таблиц.

Если Вы планируете установить SaveToDB Framework повторно, сохраните предварительно данные конфигурационных таблиц.

Установка и удаление командными файлами

Код и утилиты фреймворка находится в папке "SaveToDB Framework for SQL Server Compact".

Установка

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите 2-install-savetodb-framework.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Вместо удаления пароля в строке подключения на шаге 3, можно зашифровать строку:

выберите опцию "Только пользователь" в программе gConnectionManager.

Удаление

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите framework-remove.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Вывод объектов базы данных

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите framework-list.cmd.
  3. Запустите 3-clear-credentials.cmd и удалите пароль строки подключения "setup".

Список запросов

Назначение

С плагином SaveToDB пользователи могут переключать запросы к базе данных, используя список запросов на ленте.

Это удобно, т.к. в книге Microsoft Excel не нужно делать много листов, а можно динамически переключать запросы на одном листе. К тому же, при добавлении новых запросов в базу данных, пользователю достаточно обновить список запросов, и можно получать данные новых объектов.

Список запросов может содержать объекты базы данных, SQL-код, http-запросы и запросы к текстовым файлам.

Все эти возможности могут быть настроены в базе данных, и SaveToDB Framework предлагает готовую инфраструктуру.

См. Настройка списка запросов.

Таблица конфигурации списка запросов

Таблица QueryList содержит объекты списка запросов.

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]
);

Таблица позволяет:

  • Указать SQL-код в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип CODE.
  • Указать http-запрос в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип HTTP.
  • Указать запрос к текстовому файлу в списке запросов.
    Код указывается в поле TABLE_CODE, в поле TABLE_TYPE должен быть указан тип TEXT.
  • Указать таблицу сохранения данных, полученных из SQL-кода.
    Таблица должна быть указана во всех трех полях INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE.
  • Указать SQL код для сохранения изменений данных, полученных из SQL-кода.
    Должны быть заполнены все три поля INSERT_PROCEDURE, UPDATE_PROCEDURE и DELETE_PROCEDURE.
  • Указать таблицу или SQL код для слияния данных, полученных из любых источников, в том числе из веб или текстовых файлов.
    Должно быть заполнено одно поле INSERT_PROCEDURE.

См. Настройка списка запросов и Настройка сохранения данных.

Поле TABLE_NAME должно быть заполнено для всех объектов и быть уникальным.

Обработка событий

Назначение

Используя плагин SaveToDB к Microsoft Excel можно обрабатывать события Microsoft Excel из Microsoft SQL Server Compact заданным SQL-кодом или http-запросами:

  • Change
  • DoubleClick
  • SelectionChange

За счет этого можно изменять любые данные базы данных из Microsoft Excel, реализовывать уточняющие запросы, логику Master-Details и т.д.

Также можно настроить контекстное меню Microsoft Excel и меню "Действия" плагина для запуска:

  • SQL-кода,
  • http-запросов,
  • запросов к текстовым файлам,
  • макросов,
  • команд Windows Shell и CMD,
  • обновления справочников Excel из баз данных.

См. Настройка обработчиков событий.

Таблица конфигурации обработчиков событий

Таблица EventHandlers содержит конфигурацию обработчиков событий.

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]
);

Описание полей конфигурации см. в разделе Настройка обработчиков событий.

Поле HANDLER_CODE может содержать SQL-код или текст http-запросов.

Поле TABLE_SCHEMA игнорируется в Microsoft SQL Server Compact.

Перевод данных

Назначение

Плагин SaveToDB позволяет переводить описания объектов базы данных и полей объектов на бизнес-язык пользователей или любой естественный язык в самом Microsoft Excel после загрузки запроса.

Это позволяет менять перевод полей в специальной таблице базы данных, не меняя сами объекты базы данных.

См. Настройка перевода данных.

Таблица перевода объектов базы данных

Таблица ObjectTranslation содержит данные перевода объектов.

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]
);

Описание полей см. в разделе Настройка перевода данных.

Поле TABLE_SCHEMA игнорируется в Microsoft SQL Server Compact.

Можно изменить разрядность полей TRANSLATED_*, если требуются описания большей длины.

Таблица перевода полей таблиц

Таблица ColumnTranslation содержит данные перевода полей объектов базы данных.

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]
);

Описание полей см. в разделе Настройка перевода данных.

Поле TABLE_SCHEMA игнорируется в Microsoft SQL Server Compact.

Значения параметров

Назначение

Используя плагин SaveToDB пользователи могут работать в Microsoft Excel с запросами в виде SQL кода так же, как с таблицами.

Если SQL код имеет параметры, то плагин SaveToDB выводит параметры на ленту и пользователи могут изменять значения.

Для таблиц пользователи могут выбрать поля, которые будут показаны на ленте и использоваться при формировании запроса WHERE.

Используя настройку в базе данных можно задать SQL код, который будет использоваться для формирования списков значений полей и параметров.

См. Настройка значений параметров.

Таблица конфигурации запросов списков значений полей

Таблица ParameterValues содержит конфигурацию запросов списков значений полей.

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] (
    [TABLE_NAME],
    [PARAMETER_NAME]
);

SQL-код запроса значений добавляется в поле SELECT_CODE, в поле SELECT_TYPE должен быть указан тип CODE.

Поле TABLE_SCHEMA игнорируется в Microsoft SQL Server Compact.

Форматы таблиц

Назначение

Данный компонент позволяет сохранять и восстанавливать форматирование таблиц Microsoft Excel для объектов БД в базе данных.

Форматирование таблицы загружается при первом подключении к объекту.
За счет этого пользователь получает не только данные, но и полное форматирование, включая:

  • форматы ячеек,
  • условное форматирование,
  • колонки формул,
  • строки итогов,
  • примененные авто-фильтры,
  • настройки печати,
  • настройки окна,
  • видимость колонок,
  • пользовательские виды таблиц.

Управление форматами производится из Мастера управления форматами таблиц.

См. также Настройка форматов таблиц.

Таблица форматов

Таблица TableFormats хранит форматирование таблиц Microsoft Excel для объектов базы данных.

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]
);

Поле TABLE_SCHEMA игнорируется в Microsoft SQL Server Compact.

Данные таблицы управляются плагином при сохранении из Мастера управления форматами таблиц с использованием встроенной процедуры.

История изменений

Версия 7.2 от 20.03.2017

[*] Новые типы обработчиков событий: ConvertFormulas и DoNotConvertFormulas.
[*] Добавлены списки проверки значений в таблицы QueryList, EventHandlers, ParameterValues.
[*] Поле EventHandlers.HANDLER_NAME может содержать значения NULL.

Версия 7.1 от 03.02.2017

[*] Добавлены типы RANGE и VALUES в поле SELECT_TYPE.
[*] Добавлены типы RANGE и VALUES в поле HANDLER_TYPE.

Версия 7.0 от 17.01.2017

[*] Обновлены командные файлы и файлы readme.txt.
SQL-код объектов SaveToDB Framework версии 6.9 остался неизменным.

Версия 6.9 от 21.06.2016

[+] Новый тип обработчика событий: ProtectRows.

Версия 6.7 от 14.04.2016

[*] Фреймворк включает обновленный список значений колонки EVENT_NAME таблиц конфигурации обработчиков событий.
Используйте файл savetodb-framework-62-upgrade-to-67.sql для обновления версий 6.2.
После обновления используйте Мастер управления форматами таблиц для перезагрузки списков в существующие таблицы Excel.
Также можно создать новые книги настройки, используя Мастер генерации книги настройки.

Версия 6.2 от 23.12.2015

[+] Новые типы обработчиков событий: DoNotSelect, DoNotSave, DoNotChange, Formula, FormulaValue, ValidationList.

Версия 5.2 от 12.05.2015

[*] Обновлены командные файлы.

Версия 5.0 от 13.04.2015

[*] В таблицу QueryList добавлены поля TABLE_CODE и PROCEDURE_TYPE.
[*] В таблицу EventHandlers добавлено поле HANDLER_CODE.
[*] В таблицу ParameterValues добавлено поле SELECT_CODE.
[*] Поля SPECIFIC_SCHEMA и SPECIFIC_NAME таблицы ParameterValues переименованы в TABLE_SCHEMA и TABLE_NAME.
[*] Типы полей TABLE_SCHEMA и HANDLER_SCHEMA изменены на nvarchar(128).

Версия 4.8 от 27.08.2014

[-] Исправлены ошибки кодировки при выводе сообщений в командных файлах.
Все командные файлы переведены на кодировку CP866.

Версия 4.7 от 15.07.2014

[*] Командные файлы обновлены на использование утилиты командной строки gsqlcmd.exe вместо SqlCeCmd.exe.
Вследствие этого, операции установки и удаления можно совершать без предустановленных компонент Microsoft SQL Server Compact.

Версия 4.0 от 13.12.2013

[*]Тип поля TABLE_SCHEMA таблицы QueryList изменен на ntext.
Поле может содержать SQL код, http-запросы и запросы к текстовым файлам.
[*]Типы полей INSERT_PROCEDURE, UPDATE_PROCEDURE, DELETE_PROCEDURE таблицы QueryList изменены на ntext.
Поля могут содержать SQL код для сохранения изменений или слияния данных.
[*]Тип поля HANDLER_SCHEMA таблицы EventHandlers изменен на ntext.
Поле может содержать SQL код, http-запросы, запросы к текстовым файлам, код макросов и команд Windows Shell и CMD.
[*]Размер поля TARGET_WORKSHEET таблицы EventHandlers увеличен до 255 символов.
Поле может содержать список листов для обновления справочников для обработчика REFRESH.
[*]Тип поля SELECT_SCHEMA таблицы ParameterValues изменен на ntext.
Поле может содержать SQL код запроса списка значений параметров.
[*]В таблицы SaveToDB Framework for SQL Server Compact добавлены уникальные индексы.
[*]В меню "Действия" добавлены ссылки на онлайн документацию для таблиц SaveToDB Framework for SQL Server Compact.

Версия 3.0 от 10.06.2013

[+]Первая версия.

Системные требования

Поддерживаемые версии SaveToDB:

  • SaveToDB 6.x и выше
  • SaveToDB 5.x, без новых возможностей SaveToDB 6.x-7.x

Поддерживаемые версии Microsoft SQL Server Compact:

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

Примеры для Microsoft SQL Server Compact

SaveToDB SDK включает рабочие книги Microsoft Excel и готовые базы данных примеров приложений для Microsoft SQL Server Compact.

SaveToDB SDK также включает полный исходный код примеров и командные файлы для установки и удаления приложений.

В примерах продемонстрированы все возможности SaveToDB для построения приложений с использованием Microsoft Excel.

Руководство разработчика 6.5 для Microsoft SQL Server Compact

Пример демонстрирует возможности создания приложений Microsoft Excel с использованием плагина SaveToDB и баз данных Microsoft SQL Server Compact.

Обязательно попробуйте.

Приложение загрузки данных торгов из Интернет

Приложение позволяет загружать данные торгов с сайтов Google Finance и Yahoo! Finance и сохранять данные в базе данных.

Приложение реестра платежей

Приложение позволяет вести реестр платежей и строить отчеты о движении денежных средств.

Пример DDE Interactive Brokers

Пример демонстрирует возможности настройки формул DDE без использования макросов.
Пример работает с Trader Workstation (TWS), мощной торговой платформой от Interactive Brokers.

Пример DDE Thinkorswim

Пример демонстрирует возможности настройки формул DDE без использования макросов.
Пример работает с Thinkorswim thinkDesktop, мощной торговой платформой от Thinkorswim, подразделения TD Ameritrade.

Пример загрузки данных торгов из Интернет

Пример демонстрирует возможности загрузки финансовых данных из Интернет.

Пример загрузки из текстовых файлов

Пример демонстрирует возможности загрузки данных из текстовых файлов в форматах CSV, XML и HTML.

Пример загрузки курсов валют ЦБ

Пример демонстрирует возможности загрузки и сохранения курсов валют, полученных с сайта ЦБ РФ.

Пример использования макросов

Пример демонстрирует возможности работы с макросами.

Пример обработки событий Excel

Пример демонстрирует возможности обработки событий Excel за счет SQL кода и объектов базы данных.
Достаточно просто можно добавить сохранение изменений в базу данных, детализирующие отчеты к базе данных, вывод детализированных данных в связанное окно и т.д.

Пример сохранения изменений в базу данных

Пример демонстрирует различные варианты реализации сохранения изменений в базу данных.

Установка и удаление примеров приложений

Установка примеров приложений SQL Server Compact

Для развертывания примера следует выполнить следующие шаги:

  1. Установить базу данных примера приложения.
  2. Изменить данные подключения в книге Microsoft Excel примера.

Установка базы данных примера

Код развертывания базы данных примеров SQL Server Compact находится в папке "source code" соответствующего примера.

Откройте папку и выполните следующие командные файлы:

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите 2-create-database.cmd, если необходимо.
  3. Запустите 3-install-savetodb-framework.cmd для установки SaveToDB Framework for SQL Server Compact.
  4. Запустите 4-install-application.cmd для установки приложения.
  5. Запустите 5-clear-credentials.cmd и удалите пароль подключения "setup".

На шаге 1 можно также создать базу данных SQL Server Compact в визуальном режиме, если необходимо.

Вместо удаления пароля в строке подключения на шаге 5, можно зашифровать строку:
выберите опцию "Только пользователь" в программе gConnectionManager.

Пример также можно установить вручную, используя файл application-install.sql.

Изменение параметров подключения в рабочей книге Microsoft Excel

В папке примеров содержатся предварительно настроенные рабочие книги Microsoft Excel для конечных пользователей и разработчиков приложений.

После развертывания примера, возможно, следует изменить данные подключения на новый файл примера.

По умолчанию, все книги используют базу данных SQL Server Compact в текущем каталоге и без пароля.

Для изменения параметров подключения следует:

  1. Активировать вкладку SaveToDB и запустить "Мастер изменения строк подключения".
  2. Ввести новые параметры сервера и базы данных и нажать кнопку "Далее".
  3. Отметить все таблицы и нажать кнопку "Готово".

Далее следует обновить данные всех таблиц рабочей книги Microsoft Excel.

Это можно сделать, используя пункт меню "Обновить, Обновить все таблицы книги".

Удаление примеров приложений SQL Server Compact

Для удаления откройте папку "source code" соответствующего примера и выполните следующие командные файлы:

  1. Запустите 1-edit-config.cmd и настройте строку подключения "setup".
  2. Запустите application-remove.cmd.
  3. Запустите 5-clear-credentials.cmd и удалите пароль подключения "setup".

Вместо удаления пароля в строке подключения на шаге 3, можно зашифровать строку:
выберите опцию "Только пользователь" в программе gConnectionManager.

Пример также можно удалить вручную, используя файл application-remove.sql.

Утилита командной строки gsqlcmd

Командные файлы установки и удаления примеров используют утилиту командной строки gsqlcmd,
которая позволяет выполнять SQL код и экспортировать данные на всех поддерживаемых платформах баз данных.

Утилита устанавливается в составе SaveToDB и доступна через переменную среды PATH,
соответственно, приложения могут быть установлены из любого каталога.

В составе SaveToDB SDK утилита находится в подкаталоге gsqlcmd и доступна из только подкаталогов SaveToDB SDK.

История изменений

Версия 7.2 от 20.03.2017

[*] Обновлен SaveToDB Framework 7.2.

Версия 7.0 от 17.01.2017

[*] Обновлены даты на 2017 год.
[*] Обновлены SaveToDB Framework 7.0, командные файлы и файлы readme.txt.

Системные требования

Поддерживаемые версии SaveToDB:

  • SaveToDB 5.x и выше

Поддерживаемые версии SaveToDB Framework for Microsoft SQL Server Compact:

  • SaveToDB Framework 5.0 for Microsoft SQL Server Compact и выше

Поддерживаемые версии Microsoft SQL Server Compact:

  • Microsoft SQL Server Compact 4.0