Connect UiPath to SQL Server Inside Code/Package

[martin.rauter]

Hello Guys!

I need help for a sql connection, but i cant find anything related to this.
I have a custom nuget package, which inside talks to a sql server to write out some logs.

First i wanted to try sqlite, so i installed the official package.
I added every functionality i wanted for my package inside my visual studio, and it worked.
There is a data base file created, and logs are written into it.

But when i export the project to a nuget package, and install it, the package works normally, until i get to the database methods.
UiPath throws an error (or the package specifically), which says that it cant find certain dlls (e_sqlite3.dll).

I looked up for solutions, but apparently including dlls in a UiPath project and reference it isn’t a thing (please correct me if i am wrong).

So i tried the System.Data.SqlClient, and Microsoft.Data.SqlClient, both do work in my visual studio execution, but not in my UiPath execution, with similar errors like the one mentioned above, but this time it is ‘sni.dll’.

So my question is: how do i get this to work, that i can access my db from code/package. I know that you can use DB Activities, but my package has some complexity, which i cannot simply repeat with the ui (i would need multiple workflows, and this could not be integrated into the package, without exposing every method to the user).

I have looked for solutions similar to my question, but no one has asked even related questions, so i am asking:

TL;DR: Is it possible to use SQL Libs / Connections inside a nuget package / invoke code activity without using the db activities UiPath provides (graphical), and if so, how?

Thank you for your time!

[Anil_G (Anil Gorthi)]

@martin.rauter

you can write them as stored procedures and invoke them

cheers

[martin.rauter]

Thank you for your answer.
Yes i could store that, but that would mean i need to create a new way of storing the log infos, the procedures and such. Also for future implementation, i dont think this will be a viable way in the long run.

I read some posts inside the forum like How can I add System.Data.SqlClient to Visual Studio while using the UIpath Custom Activity Package? - #4 by sven.wullum1 , and it says i need to use Microsoft.Data.SqlClient. I installed the package in my Project in visual studio, and in UiPath, both use the version 5.1.2. But still, when i want to access a method which works with the lib, it throws an exception:

RemoteException wrapping System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> RemoteException wrapping System.DllNotFoundException: Unable to load DLL ‘Microsoft.Data.SqlClient.SNI.dll’ or one of its dependencies: Das angegebene Modul wurde nicht gefunden. (0x8007007E)
at Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted(IntPtr token,
Boolean& isRestricted)
at Microsoft.Data.Win32NativeMethods.IsTokenRestrictedWrapper(IntPtr token) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Interop\SNINativeMethodWrapper.Windows.cs:line 535
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrentNative() in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPoolIdentity.Windows.cs:line 31
at Microsoft.Data.ProviderBase.DbConnectionPoolIdentity.GetCurrent() in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionPoolIdentity.Windows.cs:line 17
at Microsoft.Data.ProviderBase.DbConnectionPoolGroup.GetConnectionPool(DbConnectionFactory connectionFactory) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\ProviderBase\DbConnectionPoolGroup.cs:line 170
at Microsoft.Data.ProviderBase.DbConnectionFactory.GetConnectionPool(DbConnection owningObject,
DbConnectionPoolGroup connectionPoolGroup) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 197
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,
TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 43 at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry,
DbConnectionOptions userOptions) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 341
at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection,
DbConnectionFactory connectionFactory,
TaskCompletionSource1 retry, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Common\src\Microsoft\Data\ProviderBase\DbConnectionClosed.cs:line 39 at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource1 retry,
SqlConnectionOverrides overrides) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1844
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in D:\a_work\1\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1333

I would like to add this dll to the UiPath project, but i cant see to do this anywhere.
Greetings, Martin

[martin.rauter]

Hello Guys!

I found the fix, which isnt very straight forward.

When building the project inside visual studio, there are dlls inside the Debug / Release Folder,
under bin/Release/net6.0-windows/runtimes/win-x64/native/Microsoft.Data.SqlClient.SNI.dll
Build the project under Release, and choose ‘Any CPU’ to get all the runtime dlls.

Then look for the right runtime depending on the UiPath project (i had x64), and add the dll inside the C:\Program Files\UiPath\Studio folder. You will find many other dlls, just drag and drop the needed dll into this folder from the native folder.

UiPath should find the dll when you access a method which uses it inside your Invoke Code Activity. If you have the wrong runtime, it will throw the error ‘Bad File Format’. Try other dlls if this is happening.

If its working, you wont notice anything, besides that there are no more exception thrown.
I hope this will help any future readers, as i could only find the answer after a long trial error session with different answers from the web. But maybe this wasnt a thing for most, which i cannot believe.

Have a great day,
Martin!