Maintaining a connection

[TGPChris]

I have an application that is used daily by 20 people - each person runs the app on their own computer. It is entirely DB-driven (yeah, what's not).

 

Is it better to open the DB connection on program load and keep open until the application is closed OR

Is it better to open/close the connections only when needed?

 

What's better and what is the reason?

[bri189a]

DBA's don't like connections tied up for no reason. Open and close only when needed. Use disconnected data as much as possible (DataSet).

[PlausiblyDamp]

If you are developing a web application then the client machines shouldn't be having any connections to the DB itself - the web server would be the only computer connecting direct to the database anyway.

However as bri189a says - only connect as and when you need, release connections as soon as possible and try to work in a disconnected fashion when possible.

[Mister E]

Do what PlausiblyDamp said and also understand that something like the .NET Framework has robust connection pooling for databases. You may *think* that you're opening up a new connection when, in reality, you're just using one that is currently in use. The handoff occurs seamlessly to you and is handled by the framework. So, in the end, the best practice is the open and close things as you need them.

[HJB417]

Do what PlausiblyDamp said and also understand that something like the .NET Framework has robust connection pooling for databases. You may *think* that you're opening up a new connection when' date=' in reality, you're just using one that is currently in use. The handoff occurs seamlessly to you and is handled by the framework. So, in the end, the best practice is the open and close things as you need them.[/quote']

 

the .net framework doesn't do/support connection pooling. It's something the dataprovider needs to implement such as the ms sql managed driver/provider that ships w/ the .net framework.

[Mister E]

the .net framework doesn't do/support connection pooling. It's something the dataprovider needs to implement such as the ms sql managed driver/provider that ships w/ the .net framework.

The System.Data.SqlClient class is not simply a driver that "ships" with the Framework. It is part of the Framework or, as Microsoft puts it, "The System.Data.SqlClient namespace is the .NET Framework Data Provider for SQL Server." So, as I said, the framework supports connection pooling.

 

If you use a third-party driver you are, obviously, going to be limited to the capabilities of that driver.

[HJB417]

you shouldn't assume everyone uses sql server and your statement, the 1 I quoted, is amgigous. Maybe you should say "has robust connection pooling for ms sql databases" or " the sqlconnection class supports connection pooling". Only after my reply do you state "If you use a third-party driver you are, obviously, going to be limited to the capabilities of that driver.".

[Mister E]

you shouldn't assume everyone uses sql server and your statement' date=' the 1 I quoted, is amgigous. Maybe you should say "has robust connection pooling for ms sql databases" or " the sqlconnection class supports connection pooling". Only after my reply do you state "If you use a third-party driver you are, obviously, going to be limited to the capabilities of that driver.".[/quote']

 

There are no ambiguities. I simply said that the ".NET Framework has robust connection pooling for databases". The SqlClient class is part of the framework. The SqlClient class supports connection pooling. Therefore the .NET Framework supports connection pooling. That's it, nothing more, nothing less.

 

You needlessly chimed in to say that the .NET Framework "doesn't do/support connection pooling". Which, as I have pointed out, is dead wrong.

 

PS -- The System.Data.OleDb class also supports connection pooling.

[penfold69]

And in addition, the MySql.Data.MySqlClient also supports connection pooling, and follows the IDBConnector/IDBCommand interfaces, so is 100% compatible with the SqlClient libraries

 

Although I do acknowledge what HJB417 says - it *IS* provider dependant, but any drivers that ship *as part* of the framework *do* support pooling by default.

 

B.

[Mister E]

I should clarify. System.Data.OleDb and System.Data.SqlClient are namespaces not classes. OleDbConnection and SqlConnection are the respective classes that support connection pooling within those namespaces.

[TGPChris]

Thanks, all your replies were VERY helpful. Time for me to change some code...

[bri189a]

Yeah, ODBC when used with AS400... no built in connection pooling there...really wish it did though, let me tell ya!