CODEWITHSUNDEEP
c#sql-serversql-server-2008
Gold
Gold

Reputation: 62554

what is the correct use of connection to database

I have a Winforms program that connects to SQL Server 2008.

There are 100 users that connect to this database and

run search query --> and update the database.

I open the connection when the program starts and close when the program ends.

Is this the right thing to do ?

Is it better to open connection -> run query -> update -> and close connection ?

Thanks in advance

Upvotes: 3

Views: 106

Answers (3)

Robert
Robert

Reputation: 112

If you maintain a database connection open, you are keeping that much memory reserved for that task. Therefore, the performance may be affected. Plus, if there is a critical error in the program and it exits prematurely, that connection may not be automatically released.

Upvotes: 0

Aliostad
Aliostad

Reputation: 81700

This is a very old question and I believe it has been answered years before.

Always, start the connection -> run SQL -> Close.

Let the connection pooling worry about the cost of opening and closing connection. If you keep using the same connection string (achievable by using Integrated Security) you rarely open a new connection and resue existing ones.

Upvotes: 4

Josh
Josh

Reputation: 44916

is it be better to open connection -> run query -> update -> and close connection ?

Yes, SQL Server already optimizes connections and pooling is handled by ADO.Net. Open the connection use it to get what you want and then close it. Let SQL manage the rest.

From MSDN (source above):

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool.

Upvotes: 4

Related Questions