How to connect UiPath Studio to SQLite .db file?

[harmeet_kaur]

Hi all,

I have a .db file. I want to connect that .db file to UiPath to get data from it by executing SQL statements. How can I do this?

Thanks,
Harmeet

[Gokul001 (Gokul Balaji)]

Hi @harmeet_kaur

Have a look on the thread

How do I connect to serverside DB like SQLite in UIPath?

Regards
Gokul

[harmeet_kaur]

Thanks for sharing. How can I create DSN for my .db file to use it in the connection activity in UIPath?

[Gokul001 (Gokul Balaji)]

Hi @harmeet_kaur

Have a look on the thread

Unable to create SQlite DB connection

I am trying to connect uipath to SQLite dB but i am not able to find custom activities to create the connection. can anyone please help me with Uipath and SQLite connection as i am not able to establish connection using Database activities. I have downloaded activities from manage packages given by “Thiago Minhaqui Oechsler” but in order to use this activities there is a dependency of System.data.SQLite. @StefanSchnell

Regards
Gokul

[marian.platonov]

Working Solution tested in UiPath Studio 24.10.5 with System.Data.SQLite.1.0.119 from https://api.nuget.org/v3/index.json for a Windows project compatibility

1. Install the System.Data.SQLite.1.0.119 dependency in your project in Manage Packages ribbon

2. Copy the C:\Users\robot_username\.nuget\packages\stub.system.data.sqlite.core.netstandard\1.0.119\runtimes\win-x64\native\SQLite.Interop.dll file to Studio/Robot installation folder
   

   

3. Add an Invoke Code activity for CSharp language

try
{
    string connectionString = "Data Source=C:\\Users\\robot_username\\Documents\\UiPath\\ExtractPDT_Test\\REPLACE_WITH_DATABASE_FILE_NAME.db;Version=3;";

    using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString)) 
    {
        conn.Open();
        
        string sql = "SELECT * FROM REPLACE_WITH_TABLE_NAME";
        using (System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(sql, conn)) 
        {
            using (System.Data.SQLite.SQLiteDataReader reader = command.ExecuteReader())
            {
                    DataTable dt = new System.Data.DataTable();
                    dt.Load(reader);
                    query_output = dt;
            }
        }
    }
}
catch (Exception e)
{
    string error = e.Message;
	error_output = error;
}

Results after execution:

[Zenki]

Thank you very much! Following the method you provided, I successfully connected the SQLite database file in UiPath.

[hemanth.bhat]

Hi @marian.platonov,

wanted to understand the behaviour of SQLite with UiPath studio,
if there any reason as to why the SQLite.Introp.dll needs to be copied to Studio installation?
Why are the dependencies not getting resolved in studio, whereas it works correctly in Visual studio or other languages?
How to deploy the process without this workaround?
are there any fixes planned?

Apologies if this is off-topic,
Kindly move to a new thread/topic if necessary.

Thanks
Hemanth Bhat

[marian.platonov]

@loginerror
Can you please review the last post and provide a response?

Thanks.

[marian.platonov]

If there any reason as to why the SQLite.Introp.dll needs to be copied to Studio installation?

Yes, because the Studio/Robot cannot find that dll file as installed on the %userprofile%/.nuget/packages
If you didn’t add that file in the Studio/Robot installation location you will have an error. Try by yourself and observe.

[hemanth.bhat]

Thank you @marian.platonov, for clarifying why the SQLite.Interop.dll must be manually copied—it really helps to understand the current dependency resolution process in Studio.

I’m curious, though: is there any plan to eliminate this manual workaround in future updates, especially for production deployments? It would be a huge improvement if Studio could automatically resolve these native dependencies.

[loginerror (Maciej Kuźmicz)]

Hi @hemanth.bhat

Just to add to this topic, I’ve just tried it with Database activities 1.9.0, and had no issue connecting to the local SQLite .db file like this:

Connection string: "Driver=SQLite3 ODBC Driver;Database=northwind.db;"
(the file is placed in my Studio project folder)

Provider name: "System.Data.Odbc"

I installed the x64 Windows ODBC SQLite driver from this page:

SQLite ODBC Driver

Via this link:

And was then able to iterate over the table of my sample DB I got from here:

GitHub - jpwhite3/northwind-SQLite3: SQLite3 version of Microsoft's Northwind Database

[hemanth.bhat]

Thanks @loginerror, for the update; however this introduces a ODBC driver installation dependency,

Can this dependency resolution be fixed so that it no longer relies on ODBC drivers or manual steps and instead
leverages NuGet’s automated capabilities as it did in the UiPath “windows-legacy” projects?

Thanks,
Hemanth

[loginerror (Maciej Kuźmicz)]

@hemanth.bhat

as it did in the UiPath “windows-legacy” projects?

This made me curious. Could you please provide context how it worked for you in the past?
How did your project look like?

[hemanth.bhat]

Sure @loginerror ,
One was the marketplace activity SQLiteDatabaseForUiPath targeting Windows legacy, and another one is by Stefan Schnell advent-challenge-8

The first one was a wrapper package over Sqlite, and it exposed the CRUD activities, with which many users were able to use it as a regular db activity targetting SQLite, i tried to upgrade it to Windows with the source code available but failed due to dependency resolution errors along with a few other users as well in below forum topics

1. Connection to database Sqlite
2. Connect UiPath to SQL Server Inside Code/Package
3. Sqlite could not generate view activities Thiago Minhaqui Oechsler

The Code advent challenge is where Stefan had solved a challenge with Sqlite db using invoke code

with these usages/approaches using SQLite was a breeze and help us create solutions/POC easily

Regards,
Hemanth

[loginerror (Maciej Kuźmicz)]

I see!

In this case, wouldn’t it be even easier now that we have coded workflows?
I’m sure there is a library that can be used this way.

This way, you could create a Library project in Studio, and hopefully rather quickly recreate any coded approach as easy-to-use activities.

Studio - Coded Workflow

[hemanth.bhat]

Hi @loginerror ,

I tried running a coded workflow activity in UiPath to open a simple SQLite connection without copying the SQLite.Interop.dll to the Studio installation folder, but I still encountered an error. In Visual Studio and other environments, this dependency is resolved automatically. Can we expect UiPath Studio to fix this so that the DLL dependency is automatically handled, eliminating the need for manual intervention?

code for reference

using RoboticEnterpriseFramework.ObjectRepository;
using System;
using System.Collections.Generic;
using System.Data;
using UiPath.CodedWorkflows;
using UiPath.Core;
using UiPath.Core.Activities.Storage;
using UiPath.Excel;
using UiPath.Excel.Activities;
using UiPath.Excel.Activities.API;
using UiPath.Excel.Activities.API.Models;
using UiPath.GoogleCloud.Core;
using UiPath.GoogleCloud.Models;
using UiPath.GSuite.Activities.Api;
using UiPath.Orchestrator.Client.Models;
using UiPath.Testing;
using UiPath.Testing.Activities.Api.Models;
using UiPath.Testing.Activities.Models;
using UiPath.Testing.Activities.TestData;
using UiPath.Testing.Activities.TestDataQueues.Enums;
using UiPath.Testing.Enums;
using UiPath.UIAutomationNext.API.Contracts;
using UiPath.UIAutomationNext.API.Models;
using UiPath.UIAutomationNext.Enums;
using System.Data.SQLite;

namespace RoboticEnterpriseFramework.Tests
{
public class Workflow : CodedWorkflow
{
[Workflow]
public void Execute()
{

try {

string sql = string.Empty;
SQLiteCommand cmd;

//-Create database----------------------------------------------------
SQLiteConnection.CreateFile(“DataAdvent.db”);
SQLiteConnection con;
con = new SQLiteConnection(“Data Source=DataAdvent.db;Version=3;”);
con.Open();

con.Close();

} catch(Exception ex) {
Console.WriteLine(ex.Message);
Log(ex.ToString(),LogLevel.Error);
}

//-End------------------------------------------------------------------
}
}
}