Advance programmers only

hi ,
I am using ASP.NET with SQL Server. I have a function ABC() which creates,open ,and then dispose sqlserver's connection .
I am using ABC() twice in one .aspx page ,my friend who build this function argued that this function will open only one connection in everypage ,no matter how many times we call function ABC() in a single page ,while i denies .
Please tell me ,because our whole company database acces relies on this single ABC() function.

Thanks in AdvanceYou don't need an advanced programmer for this one. Open() and Close() rely on Connection Pooling, and you'd be surprised how many connections "open" but really share one that's already opened.

If you have a function that's ABC(), you should really have a means of OpenConnection() and CloseConnection() available that calls the Connection.Dispose() method to clear out your memory also.

You should be ok to open/close the connection a few times in an aspx page.|||::while i denies .

Read the documentation :-)

::my friend who build this function argued that this function will open only one connection in
::everypage

Your friend is wrong.

You are wrong.

:-)

ABC opens, uses, then disposes the method.

Now, if you did a decent job with the connection string (a.k.a. as astandard connection string), then basically SQL Connections are pooled. This means when YOU close the SQL Connection, it will NOT be closed, but will go back into the pool, ready for reuse (and being closed a couple of minutes later).

The idea behind this is - rightly - that opening a NEW connection is pretty slow (password authentication, setting up streams etc.). So, when you open / close connections often, keeping it around is much more efficient. Sadly, managing this from an application's point of view is hard (has to be obeyed everywhere etc.), so this went into the system. Connection Pooling was part of the infrastructure for a long time before .NET came around.

So, when this is in place, then basically ABC () may NOT OPEN a connection, but return one already stored. Basically for this the connection string has to be identical :-) Note that this pool is cross page if the connection strings are identical - which is why I sy your friend is wrong. Because the one connection you "opened" further up in the page may be reused right now by another page, resulting in your page openring a second connection and expanding the pool.

NOW - when you have Transactions enabled on the bpage, and the page is thus running under COM# guidance, then things are a little harder. Because COM+ spawns a transaction, a disposed connection can ntot go back into the general pool befor ethe page completes - it thus stays bound to the page (actually to the transaction context the page runs in). So the second call to ABC () would not get A connection back (note the emphasis on "A"), but THE connection - the same connection, as it basically is unused at the moment and still bound to the transactional context.

To read up in the dcumentation:

* For the non COM+ cas: look for "Connection pooling".
* For the COM+ case - well, read the COM+ documentation. Note that ServicedComponent subclasses just implement COM+ for .NET, so you better go back to the original COM+ documentation if you want to get details of inner workings. A third party book is strongly advisable.

::Please tell me ,because our whole company database acces relies on this single ABC()
::function.

Given that the reuse of existing connections is totally transparent and does not change anything on the level of your appplication, could you elaborate how you think this can break your application?