Board index » Visual Studio » Multiple Connection Objects within VB Code

Visual Studio135
What is everyones' opinion on writing VB code and having every

subroutine/function instantiating a new instance of a connection object and

opening the connection to the database?



I'm currently supporting a VB6 program that has about 25+ of these within

the code. Of course, the servername, username/password are all hard-coded.

Not even a global variable.



To me, this is just sloppy and injudicious programming. I can't imagine

creating a connection to the database similar to a stateless environment

over and over again -- especially with desktop client/server applications,

unless specifically required.



Am I the only one that feels this way?


-

Actually, if each routine closes the connection (as it should), the

connection pooling mechanism will effectively manage the number of open

connections for you. It's not all that terrible. Yes, the objects get built,

populated, used and torn down a lot (which can add overhead), but it deals

with some cases where more than one connection is needed at once.



hth



--

____________________________________

William (Bill) Vaughn

Author, Mentor, Consultant

Microsoft MVP

www.betav.com

Please reply only to the newsgroup so that others can benefit.

This posting is provided "AS IS" with no warranties, and confers no rights.

__________________________________



"DavidM" <spam@spam.net>wrote in message



-

Bill,



Isn't it true that transactions (.BeginTrans, .CommitTrans, etc.) are

per-connection? If this is true, can I implement multiple transactions by

maintaining multiple connections? And would it be possible to accomplish

pseudo-nested transactions (in certain cases where the data in the two+

transactions are orthogonal)?



Or are transactions "bigger" than connections in the sense that they are

database-wide regardless of the number of connections open or opened? And

what is the impact of connection pooling on transaction scenarios?



Your knowledge is greatly valued.



- Jim Rodgers









"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com>wrote in message



-

Consider that if you take this route, you might end up blocking one

connection with a pending transaction on another.



--

____________________________________

William (Bill) Vaughn

Author, Mentor, Consultant

Microsoft MVP

www.betav.com

Please reply only to the newsgroup so that others can benefit.

This posting is provided "AS IS" with no warranties, and confers no rights.

__________________________________



"Jim Rodgers" <noway@jose.Net>wrote in message



-

Thanks, Bill.



Do you or one of your fellow authors have a book or article that you would

recommend covering "connections, transactions, and pooling."



I may have experienced a blocked connection and did not understand what was

wrong. And are transactions the only way for one connection to block

another? Are there other basic pitfalls like this related to multiple

connections?



I am using ADO (not ADO.NET). I see my opportunities for technical

enlightenment getting smaller in the rear view window.



Cheers,



Jim Rodgers







"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com>wrote in message



-

hi every one, i think depending on my perception that we can use just one

connection and make it public if we have just to access one database located

on the server(i use this for SQL Server 2000 ), and in other case if we need

to manage a operations on tables(directly on tables or using views or not) we

can use transaction that'll have an atomic existance and will take end after

the coneciton.committran!

i use to work ths way using visual basic 6 with SQL Server 2000 on windows

2000 Professional and Server!

-