In general, I know the best practice while handling database connections in C#/ADO.NET is to open Db connection just before querying the Db & close the connection immediately after query execution, so that connection gets back to pool. My question now is, can we cache the database connections & re-use the same connection to execute all the queries? Let's say in one Rest API request, is it good to use the same Db connection to perform N number of database operations? The reason why we want to do this is, in case of parallel processing we want to use same database connection & hence reduce the number of database connections being created. Does this a good practice to do or will it effect connection pooling? Please advise. Thanks.

So you want to roll your own connection pooling? Just keep in mind that your Rest API is supposed to be multi-thread, things will be "interesting" if you cache one connection object on module level and all calling threads try to reuse that connection. You can avoid with having a semaphore or similar device around your connection object. Which means that your parallel processes will be seriliased. I may be misunderstanding what you are trying to do, but I think the answer is: Don't do it.

NO, its a bad idea. while a connection can be shared between threads, they can not use it at the same time. one thread must complete the query and reading results before another thread can use it. as suggested you would need to do locking to prevent concurrent use. instead, every concurrent task should have it own connection. setting the connection pool size will control the number of concurrent connections. if the all connections are in use, the thread will wait until one is free. note: if one task awaits another, it can pass a connection that is not currently in use.

Is it good to use single database connection for all database operations in one API request?

Accepted answer

Erland Sommarskog 112.6K Reputation points MVP

2024-09-30T21:22:56.48+00:00

So you want to roll your own connection pooling?

Just keep in mind that your Rest API is supposed to be multi-thread, things will be "interesting" if you cache one connection object on module level and all calling threads try to reuse that connection. You can avoid with having a semaphore or similar device around your connection object. Which means that your parallel processes will be seriliased.

I may be misunderstanding what you are trying to do, but I think the answer is: Don't do it.
Please sign in to rate this answer.

1 person found this answer helpful.
Abhilash Perla 20 Reputation points

2024-10-01T10:22:22.77+00:00

Thanks for your response @Erland Sommarskog

What we are trying to do is, we want to use a single database connection for all the database calls within one API request i.e. we will open one Db connection at beginning of the API request & perform whole business logic (which runs many queries parallely/sequentially & executes business logic) & then close that Db connection at the end before returning the response. Even in case of parallel processing, we want to use the same Db connection for all the threads because this way we want to limit the number of Db connections being created & all threads can re-use the same connection parallely. Is this a correct way of handling Db connections? Are we achieving something with this approach or will it lead to any other issues?

API Logic:

{

// Open database connection List<Task> tasks = new(); foreach (int i = 0; i<=100; i++) { tasks.Add(Task.Run(async () => { //Performs businees logic & Performs many database operations with same database connection })); } await Task.WhenAll(tasks); //Close database connection

}

With the above logic, for one API request only one Db connection gets created & when 100 API requests came at a time, 100 Db connections gets created.

Lets say in above logic, if we create Db connection for each task separately instead os re-using the same existing connection then one API request creates ~100 Db connections (one for each task) & when 100 API requests came at a time, 10000 (100 * 100) Db connections gets created.

Please check & advise the correct approach for this.

Many Thanks!!!

Abhilash Perla 20 Reputation points

2024-10-01T10:47:14.11+00:00

Thanks for your response @Erland Sommarskog

We don't want to maintain our own connection pooling mechanism (But its kind of doing the same though). What we are trying to do is, we want to open database connection at the beginning of the API request & use the same connection to perform all the database operations (parallel/sequential) & then close the connection at the end before returning the resposne. Even we want all the threads within the request to use same connection because this way we want to limit the number of connections being created. Is it good practice to make all the parallel threads to use same database connection or will it leads any issues?

Example API Request Flow:

{ // Performs some business logic // Open database connection // Performs some database operations List<Task> tasks = new(); foreach (int i = 0; i<=100; i++) { tasks.Add(Task.Run(async () => { // Performs all businees logic & Performs many database operations with same database connection })); } await Task.WhenAll(tasks); // Performs some database operations // Close database connection }

Here, all the tasks are using the same connection & hence for one API request only one Db connection gets created and for 100 parallel API requests at a time, 100 connections gets created. And lets say in above code, if we make each task to create its own connection then for one API request, 100 Db connection gets created (as we have 100 parallel tasks running) and for 100 parallel API requests at a time, 10000 (100 * 100) connections gets created. So to reduce the number of databse connections, we want to use single connection for all the Db calls/threads within a single API call.

Please check & advise

Erland Sommarskog 112.6K Reputation points MVP

2024-10-01T17:03:40.86+00:00

Yes, you can share a connection between thread. But because it is a common resource, you need to guard and serialise access to it. Thus, it is an utterly bad idea. It would be eaier to have a single task within the call, unless the database call is only something like 5% of the total work of their work.

Abhilash Perla 20 Reputation points

2024-10-09T13:26:21.63+00:00

Thanks for the information @Erland Sommarskog it helped!!!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Is it good to use single database connection for all database operations in one API request?

1 additional answer

Your answer