Database Automation Using 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
CREATE DATABASE databasename;
CREATE DATABASE College
The database called College will be created.
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.
Following are the popular types of Databases:
- Relational Database
- Object-Oriented Database
- Distributed Database
- NoSQL Database
- Graph Database
- Cloud Database
- Centralization Database
- Operational 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, 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.
Automation Basics in 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
- 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.
- Add Connect activity inside the sequence.
- Once you click on Configure Connection, a Connection wizard will be displayed.
- 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.
- 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)
- And if you open the SQL Server, you will find the Server name(DESKTOP-HL8NM9A)
- 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.
- If you did everything correctly, then you will receive a pop-up message as Connection Successful.
- Next, click on Ok, you will find a Connection String in the Edit Connection Settings wizard.
- Next, Go to Uipath Studio, Click on Connect Activity and create a variable(
Connect_Variable) in the Properties panel, for Output property.
- Next, click on Variable pane and check for Variable type, which is automatically selected as Database Connection.
PDF Automation Using RPA 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
- 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.
- 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.
- Once the New Query page has opened, drag and drop the table(dbo.Table4).
- 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
- 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.
- 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
- 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(
- Next, add Message box activity inside the sequence and enter the variable name(
Affectedrows_Variable), as shown below.
- 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.
- 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.
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.
- Click on the Edit Query in the Execute Non-Query and edit the query as follow.
"INSERT INTO [dbo].[Table4] (Name,Value,Description)
- Now, Save and run the sequence, you will receive a pop-up message as 1.
- Now go to the Microsoft SQL Server Management Studio and execute the query, you will find that the new record has been added.
Passing Parameters in the Execute Non-Query activity:
- Click on the Edit query and write it as shown below
- 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.
- 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.
- 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.
- Now save and run the sequence. After the execution, you will find a pop-up message as 1.
- If you want to check with the proof that it will work fine, change the value of the arguments, and then execute it.
- Next, go to the Microsoft SQL Server Management Studio and execute the query, you will find that the new record has been added.
- This is how we can successfully pass the parameters.
Install/ Manage Packages 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
- Next, click on the Execute Query and then and add the Existing Connection(
Connect_Variable) property in the Properties Pane.
- 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.
- Click on the Execute query and create a new variable for Data Table property in the Properties pane.
- Next, add Write Range Activity into the sequence
- 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.
- Select the Add Headers property in the Properties Pane and Save then run the sequence
- After the execution, the pop-up message will be displayed as 1.
- If you check in the SQL Server Management Studio, there are 26 records, and all these records were added in the Excel Sheet.
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.
Passing the parameters in Execute Query :
- Click on the Edit Query in the Execute Query activity and then click on the Parameters
- 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)
- Next enter the query as follow
- Now save and run the sequence; after the execution, the Sheet3 will be created and contains the records with the name Project.
Excel Automation Using UiPath
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)
- 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.
- 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.
- 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 (
- Enter the Data table variable name in the Insert activity, as shown below.
- Before executing the Sequence, we have only one row in the SQL Server Management Studio.
- Now, save and run the sequence, after the execution, the data which we have created will be pushed to the SQL table.
Conditional Statements 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.