Provides an efficient way for executing bulk insert/update on a database without having to hand-write INSERT or UPDATE statements.
18.104.22.168 Initial Release
22.214.171.124 Minor updates & bugfixes
Type: Connection Input
See Example Connection Configurations below for configuration information.
A .Net connection string including the provider name.
The character that is used to denote a variable for the database.
Set to true if the SQL database supports named (rather than indexed parameters).
Characters used to escape an object that contains characters that would otherwise be illegal.
A SELECT statement template that can be used to query the column definition of a table by returning 0 rows.
Type: Xml Input
An XML representation of the data to be updated or inserted (see Remarks).
Type: String Input
A comma-separated list of fields that represent key fields for the update or insert operation. These values will be matched in the target table to determine whether the row already exists. The key fields listed in this property must be supplied in the TableXml property. It is not necessary to supply key fields if AllowUpdate is false.
Type: Boolean Input
Indicates whether inserts are allowed into the target table.
Type: Boolean Input
Indicates whether updates are allowed into the target table (requires at least one field to be specified in KeyFields).
Type: Int32 Output
Returns the number of rows affected by the insert/update operation.
The XML supplied should contain the following paths root\tablename\columnname. The name of the root node is not important. The second tier should contain the table name and only a single table can be updated per call. The third tier should contain the column name.
Here's an example of a payload that will add or insert two rows into a table called
<description>first inventory item</description>
<description>second inventory item</description>
When AllowUpdate is true, the fields specified in KeyFields will be matched from TableXml to the target table. All KeyFields supplied must exist in both TableXml and the target table. Multiple key fields should be separated with commas. Note that the specified KeyFields do not have to be the database primary key fields but should indicate the key fields for purposes of the update operation.
For example, if an inventory table contains the fields id (int), code (varchar(50)), description (varchar(100)), the KeyFields property could be set to code if it was necessary to update based on this value rather than the internal integer id field.
Refer to Integrating with SQL Databases for additional information including connection strings and Connection properties.
Note that the underlying .NET object being used (OdbcCommand), does not support named parameters. Therefore,
ParameterPrefix should always be
AllowNamedParameters should be
See https://www.connectionstrings.com/ for additional examples.