Connection pooling? 1

[RedLion]

Hello,

Can someone explain to me what is meant with connection pooling? And where you should think about when using a connection?

Thanks,

Charl

 

[chiph]

Connection Pooling is usually refered to when talking about COM+.

COM+ is smart enough to recognize requests for database connections, and if it has one in it's pool where the connect string is an exact match, it will give the caller one from the pool, rather than creating one from scratch. This is much faster.

Once it detects that a caller is finished with a connection, it will move the connection object to it's internal pool for possible later use. It's possible to set the maximum number of connections held by the pool (in order to comply with licensing agreements, for example), and it's also possible to set the number of initial connections so that growing the pool is less likely to happen.

==============

Due to recent things I've learned about COM+, I plan to stay away from it if possible. It turns out that COM+ components that use ADO access are automatically promoted to using "Serialized" database access, which causes locking problems (we have 1.2 million+ hits a day on our site, and this is a concern).

If you don't want to use COM+, you can write your own pool manager in .NET. All you'd do is write a pool manager object which is a singleton (it stores an instance of itself internally in order that there always be only one copy of itself in existance). You'd then add an internal collection of connection objects, and add public methods to provide one upon request, and for clients to return them to the pool.

Chip H.

 

[RedLion]

Thanks Chip H.

As I can make up out of your response the best way is to always use the same connection string to connect to a database?

What and where is the best way to store the connection-string (for use with ado.net) in a config file, ini-file, registry (also thinking about security issues because the password is set in the connection string)?

Thanks,

Charl

 

[chiph]

Yes - the connect string must be an identical match. You can store it in Active Directory, another LDAP-style directory system (like Novell NDS), the registry, or an INI file. I wouldn't worry about encrypting it or anything, since if a bad guy can read your registry you're hosed anyway.

Chip H.

 

[RedLion]

Thanks again Chip H.!

We go further,
Is there a difference between connecting to a database with ole db or using an ODBC-connection? And if so, what are the differences?

Thanks,

Charl

 

[chiph]

ODBC was Microsoft's first stab at creating a database-independent access layer. It's actually their implementation of an industry-standard, but I can't recall the name of it anymore (haven't used ODBC since I did OS/2 and 16-bit Windows development!)

OLEDB came later, after COM became a big hit. It's a little more inefficient than ODBC (since it uses COM), but makes up for it by being more intelligent about how it does things.

In the .NET framework, OLEDB maps to unmanaged code, and has to go through the COM InterOp services, so it will be much slower than the SQL Server access. They're supposed to be working on a Managed Oracle driver, but I haven't been out to the Oracle Technology Network site to see if a beta is available yet.

Chip H.

 

[Guest_imported]

Oracle BETA driver was released last week ;-)