RPA Database Interaction

Many enterprise systems store their data in relational databases — Oracle, SQL Server, MySQL, PostgreSQL. Instead of navigating through an application's UI to read or update records, a bot can connect directly to the database and run SQL queries. This is significantly faster and more reliable for bulk data operations.

A bot that reads 10,000 records through a web UI might take 2 hours. The same bot reading directly from the database completes in seconds.

When to Use Database Interaction vs UI Automation

Database Activities in UiPath

UiPath provides database activities through the UiPath.Database.Activities package. The four core activities are Connect, Execute Query, Execute Non Query, and Disconnect.

Connect to Database

 Connect Activity:
 ├── Connection Type: SQL Server
 ├── Connection String:
 │   "Server=db01.company.com;Database=FinanceDB;
 │    User Id=rpauser;Password=****;"
 └── Output: dbConnection (DatabaseConnection variable)

 Note: Store the connection string as an Orchestrator Asset —
 never hardcode it in the workflow.

Execute Query (Read Data)

Execute Query runs a SELECT statement and returns the results as a DataTable that the bot can loop through and process.

 Execute Query Activity:
 ├── Connection: dbConnection
 ├── SQL: "SELECT InvoiceNo, Vendor, Amount, Status
           FROM Invoices
           WHERE Status = 'PENDING'
           AND InvoiceDate >= DATEADD(day, -7, GETDATE())"
 └── Output: dt_pendingInvoices (DataTable)

 Result: DataTable with all pending invoices from the past 7 days

 FOR EACH row IN dt_pendingInvoices:
   invoiceNo = row("InvoiceNo").ToString()
   vendor    = row("Vendor").ToString()
   amount    = CDbl(row("Amount"))
   → Process this invoice in the ERP system

Execute Non Query (Write Data)

Execute Non Query runs INSERT, UPDATE, or DELETE statements that modify data. It does not return rows — only the count of affected records.

INSERT Example

 Execute Non Query:
 ├── Connection: dbConnection
 ├── SQL: "INSERT INTO AuditLog
           (InvoiceNo, ProcessedAt, Status, SAPDocNo, BotName)
           VALUES
           (@InvoiceNo, @ProcessedAt, @Status, @SAPDocNo, @BotName)"
 └── Parameters:
     @InvoiceNo    = invoiceNo
     @ProcessedAt  = Now
     @Status       = "Posted"
     @SAPDocNo     = sapDocNumber
     @BotName      = "InvoiceBot_v1"

UPDATE Example

 Execute Non Query:
 ├── Connection: dbConnection
 ├── SQL: "UPDATE Invoices
           SET Status = @NewStatus,
               SAPDocNo = @SAPDocNo,
               ProcessedAt = @ProcessedAt
           WHERE InvoiceNo = @InvoiceNo"
 └── Parameters:
     @NewStatus   = "Posted"
     @SAPDocNo    = sapDocNumber
     @ProcessedAt = Now
     @InvoiceNo   = invoiceNo

Disconnect

Always disconnect from the database after the workflow finishes. Leaving connections open wastes database resources and can cause connection limit errors.

 Disconnect Activity:
 └── Connection: dbConnection

 Best practice: Use a Try-Finally block:
 ├── Try: All database operations
 └── Finally: Disconnect (runs even if an error occurs)

Using Parameterised Queries

Never build SQL queries by concatenating strings with user data. This opens the door to SQL injection — where malicious input in the data can modify or delete your database. Always use parameterised queries, as shown in the examples above, where variable values are passed as named parameters separate from the SQL text.

Dangerous (SQL Injection Risk)

 BAD: "SELECT * FROM Customers WHERE Name = '" + customerName + "'"

 If customerName = "'; DROP TABLE Customers;--"
 The query becomes:
 SELECT * FROM Customers WHERE Name = ''; DROP TABLE Customers;--'
 → This deletes your entire Customers table!

Safe (Parameterised)

 GOOD: "SELECT * FROM Customers WHERE Name = @CustomerName"
 Parameter: @CustomerName = customerName

 The database treats @CustomerName as a data value only —
 it cannot be interpreted as SQL commands.

Stored Procedures

A stored procedure is a pre-written SQL routine stored inside the database. Instead of writing complex SQL in the bot, you call a stored procedure by name and pass parameters. This keeps business logic in the database where DBAs can control it, and makes the bot simpler.

 Execute Non Query:
 ├── CommandType: StoredProcedure
 ├── SQL: "sp_ProcessInvoice"
 └── Parameters:
     @InvoiceNo  = invoiceNo
     @Amount     = invoiceAmount
     @UserID     = "RPABot"

 The stored procedure handles all validation and insertion logic.
 The bot simply calls it and checks the result.

Full Database Workflow Example

 USE CASE: Nightly customer account status update

 STEP 1: Connect to Oracle Database
         Connection: Oracle_FinanceDB asset

 STEP 2: Query accounts due for review
         SELECT AccountNo, CustomerName, LastReviewDate, Status
         FROM CustomerAccounts
         WHERE Status = 'ACTIVE'
         AND LastReviewDate < SYSDATE - 365
         → dt_accountsForReview

 STEP 3: FOR EACH account in dt_accountsForReview:
         Log into CRM portal (UI automation)
         Navigate to account page
         Run automated compliance check
         Read result: complianceStatus

 STEP 4: Update database with result
         UPDATE CustomerAccounts
         SET Status = complianceStatus,
             LastReviewDate = SYSDATE,
             ReviewedBy = 'RPA_BOT'
         WHERE AccountNo = @AccountNo

 STEP 5: If status changed to 'REVIEW_REQUIRED':
         INSERT into ComplianceAlerts table

 STEP 6: Disconnect database

 STEP 7: Send email summary to compliance team

Database Security Best Practices for RPA

• Create a dedicated database user for the RPA bot with only the permissions it needs (read-only if it only queries, insert/update if it writes)
• Store connection strings and credentials in Orchestrator Assets and Credential Store — never in workflow code
• Always use parameterised queries to prevent SQL injection
• Log every database modification the bot makes with a timestamp and bot identifier
• Test on a development or staging database before connecting to production

Summary

Database interaction gives RPA bots direct access to the data layer of enterprise systems, enabling fast bulk reads and writes that would take hours through a UI. UiPath's database activities (Connect, Execute Query, Execute Non Query, Disconnect) handle the full lifecycle. Always use parameterised queries to prevent SQL injection. Store credentials in Orchestrator Assets. Use stored procedures to keep complex logic in the database. Disconnect cleanly in a Finally block. Direct database integration, combined with UI automation, makes enterprise bots significantly more powerful and efficient.