Database connection best practices

What are best practices in terms of creating a database connection. Right now I have multiple Execute Query activities using the same ConnectionString attribute for every query which I am not explicitly closing. Is it best to

Create and reuse one connection string and if so close connection after use.

If you use a separate connection screen for each activity is it even necessary to close it?

Thanks

1 Like

I think as long as you disconnect at some point it is fine. What you don’t want to do is reconnect to the database over and over which will take time. Also, I’m fairly sure UiPath closes the connection when the job throws an Exception or finishes (although I can’t remember for sure)

Essentially, it will be good practice if you can create a DB-Connect workflow which can use and receive some settings to formulate your connectionstring (this way, any project that relies on the database connection can invoke this without needing to recreate the wheel). This will also return the connection back as a DatabaseConnection variable that can be used to determine if a connection is already made.

Then, you will want to make another workflow that Executes both the DB-Connect and the DB-Query. The reason you want to include the DB-Connect as part of the workflow is so you only need to worry about the logic once for making a connection, like checking if there is already a connection etc. Otherwise, you need to think about this everytime you use the DB-Query for each project… so why not include the connect logic as part of the Query workflow itself.

Here is a snippet of what you can do to make the connection in the Query workflow:
image

Then, execute your query however that is needed to create your table
Here is a snippet from our DB-Query workflow, but yours will probably be different:

Finally, make the Disconnection:
image

In my example, I used in_DisconnectSwitch as an argument so you can invoke the DB-Query to not make a disconnection, therefore making multiple Queries if needed.

And, since your Connection is made within the Query workflow, you only need one invoke in your Main flow, to provide a more eye-friendly representation of the process.

I don’t know if this the best approach, but this kind of design has helped by eliminating repetition in logic and developer-friendly.

Yours might end up being a little more complicated depending on if there are other requirements toward your Query executions.

Regards.

8 Likes

Thanks Clayton!

@pavanh003 look something use full here…

2 Likes