Do you need to create a new connection every time you call? Or should we constantly keep open one connection through which everyone should work? Now every time when I need to connect to the database I create a new connection:

SqlConnection connection = new SqlConnection(connectionString);

Well, then close it. But how to make the entire application have only one common connection? And is it necessary to do that at all?

    1 answer 1

    Using a single connection for an entire application makes it difficult to work with a database in multiple threads. Therefore, in most scenarios, you should open the connection immediately before and close it immediately after completing the database query.

    Opening and closing a physical connection to the database is costly, so the connection pool is used by default, and there is no need to worry about it. When SqlConnection.Open() is called, the connection is taken from the pool; when SqlConnection.Close() called, it is returned to the pool and can be reused.

    I do not know the specifics of the task, but I would advise myself to pay attention to such tools as, for example, the Entity Framework.