ADO Connection Logic and Common Practice

[JaedenRuiner]

Okay,

I feel I might have jumped ahead of myself, because much like with an Open file, or Large Binary Buffer...or hey, perhaps even in the logic of Object References (as opposed to Instances) I believe I have a fundamental clash with the nature of connections to databases, and I need a quick...crash course.

I create an Object in memory, so when I need to use it I pass its reference to a method and the method uses it.
X = New MyClass()
DoSomething(x)
DoSomethingElse(x)

Simple. Even though DoSomething() has no idea what X means, it understands the Reference or Address to the Instance that was initially stored in X and it accesses the same Instance of MyClass() that DoSomethingElse() accesses even though they have no idea of each other or of the variable passed to them, they just use the reference to that object.

Now, given that "Logic" there, What is the common practice with Database Connections.

I Connect to the Database and that is my "Reference" (forgive my semantics, but I'm trying to get a handle on this and I use the language that correlates).

So everything I want to do with that Database is on That Reference, Right? I mean it appears that if I want to:

• Fill A DataTable from a DataAdapter
• Delete Rows from a Different Table with a SqlCommand
• Update Rows in a Third Table

then I'm supposed to Open and Close 3 uniquely different Connection Objects? That sounds like Creating 3 Instances of the Same Connection. (Like I said i'm going on understanding the logic).

Now I get pooling, but where is the logic of Creating A connection, then destroying it, creating another, destroying it, creating another, detroying it, ad infinitum when it is all being handled in One Database? I would think, logically, if I have for instance, a Data file, and I load it's data into an object, that I've created 1 object. When I need that data, i don't recreate the object I just use the reference to the one I've already created. Logically, I would think that the usage of connections to a database would be similar, and i would create the connection and use that connection until I'm done with the database.

Much like dimming multiple "tmpStrs" in a procedure, is that how database connections are used: the recommended practice being to create a "New Connection" each time you access the database and then once you have what you need or completed whatever task you close that connection? It seems redundant, but I'll admit I've never had to handle Database communication from an Application before, so I'm trying to figure out if the Connection is like dim tmpStr: just a random temporary thing until it goes out of scope.

Thanks

 

[cjard]

Er, I wouldnt draw a parallel between an stack reference to a heap object and a database conenction to a database, but here's a quick rundown of how pooling works:

You make a Connection object (to a db)
A tcp-connection is formed and leased from the pool
You do your work
You close your Connection
The tcp-connection returns to the pool
You form another Connection and open it
The tcp-connection is re-used (it's idle)
You form another Connection immediately while the other is still in use and open it
The tcp-connection is still in use so another is formed and leased from the pool


Ergo, the num of Connections you have is not correlated to the number of tcp connections the db sees. The framework will attempt to get away with the minimum number of tcp-connections needed to support the Connections

You need not know, or care, of the detail*

*unless youre doing per-tcp-connection based transactions, in which case you should open a Connection and then the transaction on it, do all your work, then close the Connection. If youre using MS DIstributed Transaction Coordination you do not need to worry about keeping to the same Connection BUT, DTC is heavyweight!