SQL NonQuery
Write down the Connection string and choose Provider name from the dropdown list (the database administrator should give you both).
SQL NonQuery executes the SQL statement (Insert, Update or Delete) or stored procedure. In this case SQL statement does not retrieve value, only performs operation. Type in the SQL statement or choose the stored procedure. You can declare the variable and put it in RowsAffected box in Properties window. This variable will store the number of rows that were inserted, deleted or updated.
More information about connection:
Provider name: choose one of the list: MS SQL Server, PostgreSQL, MySQL, Oracle or ODBC.
Connection string: string of the characters that allows you to connect to database; in general: "Data Source=xxxx;Database=db;User Id=user1;Password=pass" (however it may vary depending on database).
More usefull information on: https://www.connectionstrings.com
Connection string formats:
MS SQL Server
Data Source=my_server_address; Database=my_database_name; User Id=my_user_name; Password=my_pass
More information: https://www.connectionstrings.com/sqlconnection/
MySQL / MariaDB
Server=my_server_address; Port=my_port; Database=my_database_name; User=my_user_name; Password=my_pass
PostgreSQL
Server=my_server_address; Port=my_port; Database=my_database_name; User Id=my_user_name; Password=my_pass
More information: https://www.connectionstrings.com/npgsql/
Oracle
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=my_host)(PORT=my_port))) (CONNECT_DATA=(SERVER=DEDICATED)(SID=my_sid))); User Id=my_user_name; Password=my_pass;
More information: https://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/
ODBC
connection string depends on type of ODBC driver for database
| Parameter Name | Parameter Type | In/Out | Description |
|---|---|---|---|
| Command Text | String | Input | SQL statement or stored procedure |
| Command Type | Dropdown | Input | select the appropriate option from the dropdown list |
| Connection String | String | Input | phrase that allows to connect to database |
| Database Provider | String | Input | one of a list: MS SQL Server, PostgreSQL, MySQL, Oracle, ODBC |
| Display Name | Literal | Input | you can change the name of activity for greater clarity of the scenario |
| Exclude from Transaction Check | Boolean | Input | checkbox; default is "off" |
| Parameters | Collection | Input | parameters to database query |
| Affected Rows Count | Int32 | Output | variable (type: Int) to store the number of changed rows |
| Result | Boolean | Output | returns the Boolean value: it will return True if no error is in activity, otherwise it will return False |
| Result Code | Int32 | Output | Result Code will return 0 if there is no error in activity, otherwise in most cases it will return 1, the extended result code will show only in several chosen activities |
| Stored Procedure Return Value | Int32 | Output | variable to store procedure value (optional parameter) |