CODEWITHSUNDEEP
.netsqlado.net
tom.dietrich
tom.dietrich

Reputation: 8347

Should I persist a sqlconnection in my data access layer?

It seems like there is a lot of overhead involved in rapidly opening and closing sqlconnections. Should I persist a connection (one, per client, per database), or continue declaring a new sqlconnection object whenever I need one, and making sure I clean up after myself?

What have you done? What worked well and what worked poorly?

Upvotes: 8

Views: 3906

Answers (6)

Ian Boyd
Ian Boyd

Reputation: 256761

For years we had the client keep a single persistent connection to the database. The problem comes in detecting an intermittent connection failure and gracefully reconnecting. Quite often you won't know that a connection failed until you try to use it (i.e. issuing a select will throw a 'General SQL Error')

We now use a globally available static class who's job is to hand you a new connection to the database, and when you're done with it you use the same class to get rid of the connection. 

DbConnection conn = Database.GetConnection();
try
{
   //do stuff with the connetion
   ...
}
finally
{
   Database.DisposeConnection(conn);
}

We do this because there is initialization needed when we connect to the database (we store information is SQL Server's CONTEXT_INFO, and must empty that information when we disconnect)

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89671

This is generally not a good thing to do (you could cause a leak and eventually run out of connections), but instead rely on the Connection Pool for performance and open connections as needed and close connections as quickly as possible.

Bill Vaughn has a number of useful articles about connection pooling and data access including this one

Upvotes: 1

Corbin March
Corbin March

Reputation: 25724

In most cases, .NET connection pooling handles this for you. Even though you're opening and closing connections via code, that's not what's happening behind the scenes. When you instantiate and open a connection, .NET looks for an existing connection in the connection pool with the same connectionstring and gives you that instead. When you close the connection, it returns to the connection pool for future use.

If you're using SQL Server: http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

OLE DB, ODBC, Oracle: http://msdn.microsoft.com/en-us/library/ms254502.aspx

Dino Esposito article: http://www.wintellect.com/Articles/ADO%20NET%20Connection.pdf

You can override default pooling behavior with connectionstring name/values: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx. See the second table of settings containing 'Connection Lifetime'.

Upvotes: 22

faulty
faulty

Reputation: 8347

I did have the same thought, so I used the same connection in a tight loop to prevent having to instantiate another one when I needed it. But sometime it's hard to keep track of it and debug, if you get a DataReader off the connection and then try to do another one while the same reader is still active, then you'll get exception. So, I would only recommend it if it's really frequent like a tight loop, otherwise it's not worth the trouble.

Upvotes: 1

Mark S. Rasmussen
Mark S. Rasmussen

Reputation: 35476

There is not much overhead since, by default settings, pools are stored in the connection pool. Thus, when you open a connection, often you'll just get a ready connection from the pool. Creating SqlConnections has not given me any troubles.

Upvotes: 5

jonnii
jonnii

Reputation: 28312

If you are using the same connection string you're connections will be pooled. You should only have a connection open as long as you need it.

Upvotes: 4

Related Questions