One public connection or not?

What's the recommended way in .Net? Or create one as you need it?

There would be arguments either way but I'd go with the single connection.

One connection but open and close it as necessary.

I have the same faith as you both but digging into some samples out there it seems they are creating a connection object each time hence I wish to be enlightened...

It depends how far you want to take encapsulation and code reuse. Even if you create multiple connections you need to have a common connection string. To code a different connection string for each connection goes against the grain because then a change would need to be replicated in multiple locations. It depends whether you want each form/object to have its own connection and each of those connections to get their connection string from a common location, or you want each form/object to use a common connection. Like I said, there would be valid arguments on either side.

This one is against our faith, what can you say about it? :)

I'm guessing that that guy knows more about ADO.NET than I do. I have heard of connection pooling but I have to admit that I don't know the details. From a quick glance it would appear that behind the scenes, two connection objects with the same connection string will be pooled into a single connection. You would still have to create multiple instances of the connection class, but that is quite possibly offset by other factors. From what he says it sounds like there are several valid reasons to use multiple connection objects, but the subject does warrant further investigation.

That brought me to dilemma, I hope our database experts would see this thread, paging szlamany, techgnome, mendhak and the others!!!

My answer would be, it depends upon the architecture of your application. You cannot decide the architecture based upon what you want or feel is the best way to do something that is merely a fraction of the overall architecture.

Example: If you have a web application wherein each page will be making multiple calls, and this of course will be accessed by several people per second, then it makes sense to use an instance of a connection rather than relying upon connection pooling.

In an other scenario, like with winforms applications where you have a single user on a single machine, there isn't much of a need for the instance.

dee-u...

I answered your thread in the DB section

http://www.vbforums.com/showthread.p...17#post2325117

dee-u...

I made another post in the thread in the DB section...