Database Connection best practise

[jby1]

Hi

I am trying to figure out what the best practise is for handling database connections in a C# application. I have already designed my application with seperate classes responsible for the business logic and the database access.

I am thinking that I would be best to have a single database connection defined, which could then be shared amongst all of the table classes that need it. Are there any issues with this?

I will be grateful for any response at all to this question!

 

[dragonwell]

Having one connection shared among classes is probably not a good idea. Having one connection string is, though. Always create a new connection (unless you are doing transactions), execute your command or whatever and close it asap. Better yet, wrap it in a try finally block so that it is guaranteed to be closed.

ADO.NET optimizes this through pooling - so even though it seems like you are creating a bunch of connections throughought the application/classes, you don't need to wqorry about it.

The important thing is not to leave the connection open, which is what you would be doing by sharing it among classes.

If you put the connection string in a config file and reference that in your classes, you will be sure that each connection is using the same connection string and is thus pooled.

For more information, look here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp

Regards,

David

 

[jby1]

Thanks for that, especially the link. I think that will give me all the answers I am looking for

 

[chiph]

jby1 -

So far as leaving the connection open --- I think it depends on the kind of application you're writing. If it's an ASP or web-service based application, then you'll want to open/close/dispose your DB connection for every caller. If you're writing a WinForms app you can likely leave it open for as long as you like (Be sure to catch the errors in case network connectivity is lost!).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[theoxyde]

Handling your database connection depends on what you are doing. If you are issuing one command/query and then waiting on user input, I would probably close the connection. If you have several commands/queries together (as in a process) where database access is your bottleneck, leave it open. I use a hybrid of the two.

How many connections to use also depends on what you are doing. I usually allow myself a connection per thread. I usually don't need a connection for each thread, but I allow myself one if needed.

 

[jby1]

What I was planning to do was create a single Connection object from a single Connection String, then pass the object around as needed. When needed, I would open the connection, and when the operation was completed, I would then close it. I guess if it is all done on the same thread then there wouldn't be any potential conflicts here?

I also need to have transactions, which I understand will bring its own fun ;-)

Thanks for all your feedback, it is always good to get the opinion of a few experts on these matters