ExecuteNonQuery

Estimated reading: 6 minutes 4943 views

This activity assists users in executing SQL commands on the provided database that do not return any data as output. It enables users to carry out essential operations such as inserting new data into the tables (INSERT), deleting existing records (DELETE), updating information (UPDATE), dropping tables (DROP TABLE), creating new tables (CREATE TABLE), and various other data manipulation tasks.

Limitations

1. When using the “CREATE TABLE” command to create a table and if there are spaces in the column names, you should enclose the column names within square brackets []. For instance, in the command below, “Start date” has a space, so it’s enclosed in brackets:
a. “CREATE TABLE Employees (
Name VARCHAR(100),
Position VARCHAR(150,
Project VARCHAR(100),
Location VARCHAR(150),
[Start date] DATE,
Salary DECIMAL(10, 2)
);”
2. The column names in the database table must exactly match the names in the “Datatable” variable when uploading values via the “Bulk Insert” option. Any differences in lowercase or uppercase letters will cause a mismatch error.

Properties

INPUT

CommandTimeout: This parameter indicates the timeout value for executing the activity within the connected database. If the execution cannot be established within this specified time, it will throw an exception.

It accepts values in “Integer” datatype. You can either enter the values hardcoded in “INT32” format or pass the values as “Int32” datatype.

DBConnection*: This parameter indicates the existing SqlConnection to be provided for execution of the activity. Here, you should mention the output variable declared in the “ConnectDB” activity.

This parameter only accepts values in the “SqlConnection” data type.

Parameters: Indicates to provide the collection of parameters that are required to be passed in the SQL query provided.

This parameter accepts the “Dictionary” datatype, which is bound to the SQL query. Refer the below documentation on how to provide the parameters.

SqlQuery*: This parameter indicates to provide the SQL Command/ query that needs to be executed. You should mention the “SQLQuery” based on the “Command Type” chosen in the “Type” parameter.

It accepts values in the “String” datatype. You can either enter the values hardcoded in “String” format or pass the values as a “String” datatype.

Type: Indicates to choose the “Command Type” that is required to execute along with the provided “SqlQuery”. Select the option from drop-down:

Text: Use this option when you want to execute a straightforward SQL command directly without involving stored procedures.
StoredProcedure: Use this option when you want to execute a pre-defined stored procedure that resides in your database. Refer the below documentation.

MISC

DisplayName: Displays the name of the activity. The activity name can be customized, which aids in troubleshooting.

SkipOnError: Specify the “Boolean” value as “True” or “False.”

True: Continue executing the workflow regardless of any errors thrown.
False: Halt the workflow if it encounters any errors.
None: If the option is left blank, the activity will, by default, behave as if “False” were chosen.

Version: It indicates the version of the feature being used.

OUTPUT

Result: It provides the ability to view the execution status of the activity. It returns values in “Boolean.”

True: Indicates that the activity has been executed successfully without any errors.
False: Indicates that the activity has been unsuccessful due to an unexpected error being thrown.

* Represents mandatory fields to execute the workflow.

Text & Stored Procedure

The choice between using the “Text” and “Stored Procedure” types in the Execute Non-Query activity depends on the nature of the SQL command you want to execute:

1. Text (SQL Query):
a. Use the “Text” type when you want to execute a straightforward SQL command directly in the activity without involving stored procedures.
b. This is suitable for commands like INSERT, DELETE, UPDATE, and other data manipulation tasks where you provide the SQL query directly in the “SQLQuery” parameter.

2. Stored Procedure:
a. Use the “Stored Procedure” type when you want to execute a pre-defined stored procedure that resides in your database.
b. This is beneficial for complex operations or when you have stored procedures designed to handle specific tasks within your database.
c. Instead of providing the SQL query directly, you specify the name of the stored procedure in the “SQLQuery” parameter.

Here’s an example of how the activity is used in the workflow –

In the following example, I have already created a stored procedure in the provided database. Using this activity, I am going to delete the stored procedure from the database. Here I am continuing from the “ConnectDB” activity.

Please note that in the following example, I have added the credentials in a respective variable which is then used in the example.

Steps to build the bot:

1. Continuing from the “ConnectDB” steps.
2. Drag and drop the “Execute Non-Query” activity and place it below the “Writelog”.
3. Navigating to the “DB Connection” in the properties and providing the “SQL Connection” variable declared as output in the “Connect DB” activity. Here it is as “DB_OUTPUT”.
4. Now, moving to “SqlQuery” and providing the query to delete the table. Here it is as,
a. “DROP PROCEDURE InsertEmployee;”
b. In my case, the stored procedure created in the database is known as “InsertEmployee”, hence I have mentioned the same.
5. Next, since here I am providing the SQL command directly, I am opting the “Type” as “Text” in the properties.
6. Moving to the “Result” in the output section of the properties to view the state of the activity.
a. There are two ways to declare a variable –
b. Method 1: Double-click on the variable parameter in the Output section and enter a name that helps you easily identify the flow. Here, I’m using the name “Table_Delete” and using the shortcut key “Ctrl+Q” to create the variable.
c. Method 2: Click on the variable pane, enter your preferred name. (here, I’m using “Altered_Table”), and choose the data type as “Boolean” since the values are returned as “True or False”.
7. You can use the write log activity to print the “Table_Delete” variable output.
a. Here the input is “Delete Status:” + Table_Delete. ToString”.
b. Then choosing the log level as “Info”.
8. Now, save and execute the workflow.

The bot will execute the workflow and initiates to connect the Database, once it has been connected, it will proceed to delete the mentioned stored procedure. In the above image, you can view the success state of the “Execute Non-Query” activity.

ExecuteNonQuery

Limitations

Properties

Text & Stored Procedure

ExecuteNonQuery

CONTENTS