DBModifications

DBModifications can be used to

The target data referred to by DBMapper and DBAction (data is the DML SQL statement(s)) is specified by special Range names, any other definitions (environment, target database, etc.) is stored in a custom property of the workbook having the same name as the target range.

Examples for the usage of DBMapper can be found in the DBMapperTests.xlsx Workbook.

Create DBModifiers

You can create the three DB Modifiers by using the cell context menu:
image

The DBModifier creation/editing is shown below (examples already filled, when activated on a blank cell all entries are empty):
(one feature that can not be set in the dialogs is a customized confirmation text for the “Ask for execution” dialog, this is done with Edit DBModifier Definitions, see below)

DB Mappers are created/edited with the following dialog:

image

You can always edit these parameters by selecting a cell in the DB Mapper area and invoking the context menu again.

The range that is used for holding the data to be stored can be identified in three different ways:

The clickable Hyperlink shows the range address of the data range, a named offset formula is displayed after the address:
image

DB Actions are created/edited with following dialog:

image

You can always edit these parameters by selecting a cell in the range of the DB Action area and invoking the context menu again.

DB Sequences are created/edited with following dialog:

image

As DB Sequences have no Range with data/definitions, invoking the context menu always creates new DB Sequences. You can edit existing DB Sequences by Ctrl-Shift clicking the Execute DBModifier Groups dropdown menus or by Ctrl-Shift clicking the created commandbuttons.

Edit DBModifier Definitions

All DBModifier definitions (done in XML) can be viewed by clicking the dialogBox Launcher on the right bottom corner of the Execute DBModifier Ribbon Group together with Ctrl and Shift. This opens the Edit DBModifier Definitions Window:
image

Here you can edit the definitions directly and also insert hidden features like the customized confirmation text in the element confirmText.

The DBModifiers can be executed either

… using the Execute DBModifier Groups dropdown menus..
image

… or using commandbuttons that were generated with the creation dialogs (the name of the control box has to be the same as the DBModifier definition/DBModifier Range)..

… or be done on saving the Workbook.

You can edit the DBModifiers either by Ctrl-Shift clicking the Execute DBModifier Groups dropdown menus..
.. or by Ctrl-Shift clicking the created commandbuttons.
.. or by using the Insert/Edit DBFunc/DBModif context menu within a DBMapper or DBAction range.

Settings

Following Settings in DBAddin.xll.config or the referred DBAddinCentral.config affect behaviour of DBModifiers:

    <add key="CmdTimeout" value="30" />
    <add key="CnnTimeout" value="15" />
    <add key="DefaultEnvironment" value="3" />
    <add key="DontChangeEnvironment" value="False" />
    <add key="DBMapperCUDFlagStyle" value="TableStyleLight11" />
    <add key="DBMapperStandardStyle" value="TableStyleLight9" />
    <add key="DebugAddin" value="False" />
    <add key="maxNumberMassChange" value="10" />
    <add key="connIDPrefixDBtype" value="MSSQL" />
    <add key="DBSheetAutoname" value="True" />

Explanation: