Database Automation in UiPath

Data is a collection of a distinct unit of information. This data is used in a variety of forms of text, numbers, media, and many more. Data is information that can be translated into a particular form for efficient movement and processing.

A database is a data structure that stores organized information.

Most databases contain multiple tables, each table can include several different fields.

For example, a company database may include tables for products, employees, and financial records.

These companies use a database management system (or DBMS), such as Microsoft Access, FileMaker Pro, or MySQL as the back end to the website.

Database automation is used to create unattended processes and self-updating procedures for administrative tasks in a database.

The automation of databases and their procedures reduces errors on deployments, improves reliability, and increases the speed of implementing changes.

Characteristics of Database:

  • In the early 1980s, Relational databases became very popular, which was followed by object-oriented databases later on.
  • More recently, NoSQL databases came up as a response to the growth of the internet and the need for faster speed and processing of unstructured data.
  • Today, we have cloud databases and self-driving databases that are creating a new ground when it comes to how data is collected, stored, managed, and utilized.
How to Create a Database:

We use the CREATE DATABASE statement to create a new database.

The Syntax to create database is

//Syntax//
CREATE DATABASE databasename;
//Example//
CREATE DATABASE College 

The database called College will be created.

Database Components:

The major components of the Database are :

  • Hardware : This consists of a set of physical, electronic devices such as I/O devices, storage devices, and many more. It also provides an interface between computers and real-world systems.
  • Software : This is the set of programs that are used to control and manage the overall Database. It also includes the DBMS software itself.
  • Data : Database Management System collects, stores, processes, and accesses data. The Database holds both the actual or operational data and the metadata.
  • Procedure : These are the rules and instructions on how to use the Database to design and run the DBMS, to guide the users that operate and manage it.
  • Database Access Language :
    It is used to access the data to and from the database. To enter new data, updating or retrieving requires data from databases. You can write a set of appropriate commands in the database access language, submit these to the DBMS, which then processes the data and generates it, displays a set of results into a user-readable form.
  • Relational Database
  • Object-Oriented Database
  • Distributed Database
  • NoSQL Database
  • Graph Database
  • Cloud Database
  • Centralization Database
  • Operational Database

SQL Database:

Structured Query language SQL is pronounced as S-Q-L or sometimes as See-Quel, which is the standard language for dealing with Relational Databases.

  • It is effectively used to insert, search, update, delete, modify database records.
  • SQL is regularly used not only by database administrators but also by the developers to write data integration scripts and data analysts.
Why do we need to Connect UiPath with SQL Server:

As we know, the Queues, Excel/Data Table, Emails, Folders, and files are the different kinds of Input and output sources. In the same way, the Database(Table) is also an input and output source too. Once you connect the Database with UiPath studio.

  • You can easily fetch store/fetch the configuration/input data to/from the database.
Why we are going to use SQL Server Database :
  • We use the SQL server to store the configured data to the table and read the set data from the table instead of using excel.
  • Use the SQL server to store input data to the table and read from the table instead of using Excel/Queue.
  • It is used to keep the configured/Input data secure.
How to get Database activities in UiPath:
  • To work with Database activities, we need to install a Database package from the Manage packages in the UiPath Studio.
  • Next, go to your UiPath Studio and open Manage packages and search for a Database package activities under Official packages.
    select-database-activities-rpa-uipath
  • Click on the UiPath.Database.Activities and install the package
  • The Database package is a collection of all the necessary database activities, which is required to establish a connection with the UiPath and SQL server database, execute the Query and Non-Query, Disconnect the connection when not needed.

    Introduction and Installation of RPA UiPath

How to Connect UiPath Studio with SQL Server Database

To work with Database, First, we need to establish a connection between uipath and SQL Server using Connect activity.

  • The Connect activity is used to connect the database with UiPath, and it gives us an option to write the Connection String and Connection Provider.
  • Connection String: is a collection of information that is used to connect UiPath(Any Application) with the Database.

Example : Server Name, Database, User ID, Password, etc...

The following is the syntax of the Connection String

"Data Source=DESKTOP-HL8NM9A;Initial Catalog=tempdb;Integrated Security=True"
//* Data Source=DESKTOP-HL8NM9A=>Server Name, Initial Catalog=tempdb=>Databse Name, Integrated Security=True=>Windows athentication*//

Server Name: Required server name as Data Source which shows in SSMS while connecting to server.
Database Name: Required Database name as Initial catalog on which we are about to perform a query
Windows Authentication: It is used when you use windows credentials to connect SQL Server. And Each data provider has a different syntax.
  • Data providers: Represent diverse sources of data such as SQL databases, indexed-sequential files, spreadsheets, document stores, and mail files. Providers expose data uniformly using a common abstraction called the rowset.
    ADO is powerful and flexible because it can connect to any of several different data providers and still expose the same programming model, regardless of the specific features of any given provider. However, because each data provider is unique, how your application interacts with ADO will vary by the data provider.
    System.Data.ODBC //*It provides data access for data sources exposed using ODBC*//
    System.Data.OLEDB //*It provides data access for data sources exposed using OLEDB*//
    System.Data.SQLClient //*It provides data access for Microsoft SQL Server*//
    System.Data.OracleClient //*It provides a data access for Oracle*//

Automation Basics in UiPath

Configure Database Connection with UiPath

  • The first step is to download Microsoft SQL Server Management Studio
  • I have explained in detail about how to download SQL server and how to create a data table in the SQL server
  • I have created a table called Table4 in the SQL server
  • Next, go to UiPth Studio and create a new process called Database_Automation_Example
    create-new-process-database-automation-example-rpa-uipath
  • Next, open the Designer pane and add the sequence into it and then search for Database in the activity pane, you will see a different kind of Database activities.
    different-database-activities-rpa-uipath
  • Add Connect activity inside the sequence.
    add-connect-activity-inside-sequence-rpa-uipath
  • Once you click on Configure Connection, a Connection wizard will be displayed.
    connection-wizardrpa-uipath
  • Click on the Connection Wizard and select Microsoft SQL Server under Data Source and select .NET Framework Data Provider for SQL Server under Data Provider and then click on Ok.
    choose-data-source-rpa-uipath
  • Once you click on Ok the Connection Properties Wizard will open, Enter the Server Name which is same as your system name( you will find the system name/Computer name by right-clicking on the properties under This PC)
    this-pc-properties-rpa-uipath
    computer-name-rpa-uipath
  • And if you open the SQL Server, you will find the Server name(DESKTOP-HL8NM9A)
    server-name-on-sql-ser-rpa-uipath
  • Next, you can use either Windows Authentication or SQL Server Authentication; here, I am using Windows Authentication.
  • Next, select the Database Name(Tempdb), and then click on Test connection as shown below.
    connection-proprty-wizard-rpa-uipath
  • If you did everything correctly, then you will receive a pop-up message as Connection Successful.
    test-connection-succeded-rpa-uipath
  • Next, click on Ok, you will find a Connection String in the Edit Connection Settings wizard.
    connection-string-rpa-uipath
  • Next, Go to Uipath Studio, Click on Connect Activity and create a variable(Connect_Variable) in the Properties panel, for Output property.
    connect-variable-rpa-uipath
  • Next, click on Variable pane and check for Variable type, which is automatically selected as Database Connection.
    database-connection-variable-type-rpa-uipath

PDF Automation Using RPA UiPath

Execute Non-Query Activity in UiPath

  • The Execute Non-Query activity is used for executing queries that do not return any data.
  • You can write queries inside the Execute Non Query activity to Insert, Update, and Delete the data in the SQL server database. In the query, you can pass the variable/parameters to insert dynamic value to the database.
  • If you are going to pass the data into the table dynamically, then the syntax is as shown below
    "INSERT INTO TableName (Name, Value, Description) VALUES ('"+Name+"','"+Value+"', '"+Desc+"')"​
  • If you are passing the Parameters, then the syntax is as shown below
    "INSERT INTO TableName(Name, Value,Description) VALUES (@Name, @Value, @Description)"​
  • Add the Execute Non Query activity inside the sequence
    add-execute-non-query-rpa-uipath
  • Click on Execute Non-Query and in the Properties Pane, Enter the Variable name which you have created in the Connect activity (Connect_Variable) into the Existing Connection property, as shown below.
    existing-connection-rpa-uipath
  • Next, go to Microsoft SQL Server Management Studio and connect to the server and then select the Datatable which you have created(dbo.Table4) and then click on the New Query.
    select-new-quesry-by-selecting-table4-rpa-uipath
  • Once the New Query page has opened, drag and drop the table(dbo.Table4).
    drag-and-drop-table4-in-the-query-rpa-uipath
  • Next, I am going to insert a ROW into the table by writing a query as shown below
    SELECT * FROM [dbo].[Table4]
    INSERT INTO [dbo].[Table4] (Name,Value,Description) VALUES('URL','www.google.com','This is the URL of Google')​
  • After writing the query click on the Execute button
    executed-query-rpa-uipath
  • Now, go to the UiPath Studio, copy the SQL query from Microsoft SQL Server management studio, and paste it in the Execute Non-Query activity, as shown below.
    add-sql-query-rpa-uipath
  • Click on the Execute Non-Query activity, and in the Properties pane, create a new variable for AffectedRecords. I am creating a variable called a new variable called Affectedrows_Variable.
  • Click on the Execute Non-Query activity and in the Properties Pane enter the Name of the Variable in the Existing Connection Property which you have created in the Connection section(Connect_Variable).
    create-variable-affected-variable-rpa-uipath
  • Next, add Message box activity inside the sequence and enter the variable name(Affectedrows_Variable), as shown below.
    message-box-with-variable-rpa-uipath
  • Now, save the sequence and run, After the execution, you will find the pop-up message box showing as 1, which means one row has affected.
    pop-up-message-rpa-uipat
  • We executed the same query 3 times if you go to Microsoft SQL Server Management Studio, and if you click on the result, you will find three results.
    three-results-in-management-studio-rpa-uipath
Passing Variable in the Execute Non-Query Activity:
  • Add three Assign activity inside the sequence, before the Execute Non-Query activity and then create a variable in each Assign activity and then pass the string values for each.
    add-assign-activity-bfr-non-query-rpa-uipath
  • Click on the Edit Query in the Execute Non-Query and edit the query as follow.
    edit-query-rpa-uipath
    "INSERT INTO [dbo].[Table4] (Name,Value,Description) 
    VALUES ('"+Var_Name+"','"+Var_Value+"','"+Var_Description+"')"
  • Now, Save and run the sequence, you will receive a pop-up message as 1.
    pop-up-message-after-passing-parameters-rpa-uipat
  • Now go to the Microsoft SQL Server Management Studio and execute the query, you will find that the new record has been added.
    passing-variable-throght-execute-non-query-rpa-uipath
Passing Parameters in the Execute Non-Query activity:
  • Click on the Edit query and write it as shown below
    edit-sql-rpa-uipath
  • Next, click on the Execute Non-Query, and in the Properties panel, click on the Parameters property in the Input section and pass the parameters. Once you click on the parameters, you will find the Parameters wizard.
  • Enter the Arguments name, and the arguments name doesn't need to match with the variables you have created, and then select the variable type.
    parametrs-name-rpa-uipath
  • In the value section, enter the variable name because we are going to access the value of the variable through the variable itself. Enter the name of the variable in the Value section.
    passing-parameters-rpa-uipat
  • Next, we have to pass the three parameters in the query(Argument1, Argument2, Argument3), which are nothing but the Name of the parameters in the first column.
  • By adding @ symbol as a prefix in each argument name as shown below, we can pass these parameters in the query.
  • Click on Edit Query in Execute Non-Query and add the parameters and click Ok.
    passing-variable-as-parameters-rpa-uipath
  • Now save and run the sequence. After the execution, you will find a pop-up message as 1.
    pop-up-message-after-passing-parameter-rpa-uipat
  • If you want to check with the proof that it will work fine, change the value of the arguments, and then execute it.
    changing-the-value-of-the-variable-rpa-uipath
  • Next, go to the Microsoft SQL Server Management Studio and execute the query, you will find that the new record has been added.
    executing-select-query-rpa-uipath
  • This is how we can successfully pass the parameters.

Install/ Manage Packages in UiPath

Execute Query activity in UiPath

Execute query Activity is used for executing queries that return query results from Database. It is used to Execute the SQL statements like select queries.

You can write the Query in the Execute Query Activity to select the data from the SQL Server Database. In the Query, you can pass the variable/parameters to pass a dynamic value to the Database.

"SELECT * FROM TableName"
"SELECT Columnname FROM TableName WHERE Name='"+Name"'"
"SELECT Columnname FROM  TableName WHERE [email protected]"
Let us understand this practically :
  • Add Execute Query activity into the sequence
    execute-query-rpa-uipat
  • Next, click on the Execute Query and then and add the Existing Connection(Connect_Variable) property in the Properties Pane.
    add-existing-connection-rpa-uipath
  • Click on the Edit Query in the Execute Query activity and Copy the quey from the Microsoft SQL Server Management Studio and paste it in the Execute Query activity.
    copy-select-query-rpa-uipath
    paste-query-in-execute-query-rpa-uipath
  • Click on the Execute query and create a new variable for Data Table property in the Properties pane.
    create-data-table-variable-rpa-uipath
  • Next, add Write Range Activity into the sequence
    write-range-activity-rpa-uipath
  • Enter the Name of the xlsx file where you want to store the output and enter the Datatable variable name (Execute_Variable) in the Datatable box.
    create-xl-sheet-for-execute-query-rpa-uipath
  • Select the Add Headers property in the Properties Pane and Save then run the sequence
    check-in-add-header-rpa-uipath
  • After the execution, the pop-up message will be displayed as 1.
    pop-up-message-after-passing-parameter-rpa-uipat
  • If you check in the SQL Server Management Studio, there are 26 records, and all these records were added in the Excel Sheet.
    records-in-sql-server-rpa
    excel-sheet-records-rpa-uipat
The following Example is to execute a query to print records where Name='Project.'

First, comment Execute Non-Query activity and Message box activity by pressing Ctrl+D you can add Comment Out activity so that the activities except Execute Query won't execute.

add-comment-out-activity-rpa-uipath

  • Next, go to the SQL Server Management Studio and write the query as follow.
    "SELECT * FROM [dbo].[Table4] WHERE Name='Project'"
  • Now execute the Query; after execution, you will find records with the name project.
    records-with-name-project-rpa-uipath
  • Copy the query from SQL Server Management Studio and Paste it in the Execute query activity in the UiPath studio. But instead of writing the value of the variable, I am going to write the Variable name itself in the query. So Assign Var_Name="Project" in the assign activity.
    assign-var-name-to-project-rpa-uipath
  • And then mention the query as shown below.
    execute-query-sql-query-rpa-uipath
  • Before running the sequence, you can either change the sheet name so that the new records will be added in the new Excel sheet, so I had given Sheet2 or Else, you can delete the existing excel sheet and run the sequence after the execution new excel file will be created.
    change-excel-sheet-name-rpa-uipath
  • Now, save and run the sequence, After the execution, the new records with name Projects have been added in the Sheet2.
    project-records-in-excel-sheet2-rpa-uipath
Passing the parameters in Execute Query :
  • Click on the Edit Query in the Execute Query activity and then click on the Parameters
    click-on-parameters-rpa-uipath
  • Once you click on Parameters, the parameters wizard will open, enter the Parameter name and enter the Variable name(Where variable name should contain the value as Name)
    passing-parameters-var-name-rpa-uipat
  • Next enter the query as follow
    passing-parameter-through-variable-rpa-uipath
  • Now save and run the sequence; after the execution, the Sheet3 will be created and contains the records with the name Project.
    records-with-project-name-rpa-uipath

Excel Automation Using UiPath

Insert Uipath Datatable to Database

Insert activity is used to insert data from the DataTable to an existing SQL table in the Database. It returns the number of rows affected as output.

  • Create a new sequence called Insert_Data_Example
  • Next, add Build Data Table activity inside the sequence
  • Click on Datatable in the Build Data Table Activity and create a new column and enter the default value of it and then click on Ok. (Make sure that the table names should match with your SQL Server table)
    craete-new-column-name-rpa-uipath
  • In the same way, we have to create Value column and the Description column because our SQL Server table is having a column name as Name, Value, and Description.
    build-table-with-name-value-and-description-rpa-uipath
  • Click on the Build Data Table activity and create a Data Table variable in the Properties pane for Data Table property, I have created a variable called Data_Variable.
    craeting-a-data-table-variable-rpa-uipath
  • Next, add Insert activity inside the sequence and then configure the connection, Click on configure Connection.
  • After the successful connection, enter the name of the table, which you have created in the SQL Server Management Studio (Table4).
  • Enter the Data table variable name in the Insert activity, as shown below.
    insert-activity-with-details-rpa-uipath
  • Before executing the Sequence, we have only one row in the SQL Server Management Studio.
    one-row-rpa-uipath
  • Now, save and run the sequence, after the execution, the data which we have created will be pushed to the SQL table.
    inserted-data-in-table4-rpa-uipath

Conditional Statements in UiPath

Start Transaction activity in UiPath

Start Transaction activity is a container where we can start our Database transactions. It connects to a database and performs multiple transactions with the database.

The Start Transaction returns a Database connection variable. If the Use Transaction is set to True, the contained operations are executed in a single transaction and applied at the end.

If None of them failed, If USE Transaction is set to false, All the operations are committed individually. When this activity ends, the connection to the database will be closed.

  • Create a new sequence called Start_Transaction_Example
    b0f684fe-9041-47a8-9d2b-4c2c424ac6c2
  • Add the Start Transaction activity inside the sequence and then click on the Configure connection to connect with SQL Server.
    631d2394-992c-4068-b679-96582b034784
  • After the successful connection, you will receive a Success connection message.
    b93ea467-115a-453a-a88b-c9e97eb3627b
  • Click on the Start Transaction activity and create a new variable in the Properties pane for Database connection property. I have created a variable called Start_Connection_Variable.
    9f8bb975-7bd6-4b9d-8046-14d7d116f8db
  • Next, add Execute Query inside the DO Container of the sequence.
    43255bd9-9b2f-4ee1-ad0a-2b66c5fb569d
  • Click on the Configure Connection and enter the Existing Connection variable in it and then click on Ok.
    113d01fa-f0fc-44a2-941f-7e46cd551c3c
  • Next, write the query in the Execute Query, as shown below.
    "SELECT * FROM Table4"​

    f05a813f-b3bf-4960-ac0a-8ba365b79bf4

  • Click on Execute Query activity and then create a new DataTable variable in the Properties pane.
    819045e7-e5d6-405c-833b-358e7f0888b5
  • Add For Each Row Activity inside the sequence and then enter the Datatable variable in it.
  • And, add Write Line activity inside the body of the For Each Row Activity and enter the text as a row.Item(Name).ToString Which means it is going to display the Name of the Rows from the Table4 in the Output Panel.
    f06f4f17-f56d-49b1-9601-66c5d8290207
  • As I have only one row in the Table4
    5153abfc-9183-4489-bc9f-16a27f94b096
  • After execution, you can see the Names of the Row in the output pane.
    4a32f4ef-d449-4df3-b197-4519429dbdfd
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions