Need some guidance on .NET connection pooling

[Guest]

Hi all,

Merry Christmas!

I have an application that reads records off an Excel spreadsheet and
imports them into an Oracle database.

What I have in my application is a subroutine that opens a database
connection, sets up the command and parameters, and executes a stored
procedure in the database. At the end of the subroutines I close the
connection.

As my application reads through the Excel records (there are about 2000
records), the above subroutine is called, which means the connection opens
and closes for 2000 times.

Is this the correct way to import the 2000 records with connection pooling?
Or am I really supposed to have opened the connection outside that
subroutine, let that subroutine run 2000 times, and then close the
connection? I know this might be a stupid question but as a beginner I
really need an answer...thank you!

 

[Sahil Malik [MVP C#]]

Well .. 2000 records isn't that much, so I wouldn't worry too much anyway.
But opening closing 2000 records on oracle is no big deal as conn. pooling
wouldn't make much of a difference. But when it comes to Excel, even if the
provider implements pooling, you may still run into file access issues - I'd
recommend keep the conn open at Excel and .. if it is a highly concurrent
production system, open/close connections on oracle.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Guest]

Sahil,

Thank you very much for your response!

Well .. 2000 records isn't that much, so I wouldn't worry too much anyway.
But opening closing 2000 records on oracle is no big deal as conn. pooling
wouldn't make much of a difference. But when it comes to Excel, even if the
provider implements pooling, you may still run into file access issues - I'd
recommend keep the conn open at Excel and .. if it is a highly concurrent
production system, open/close connections on oracle.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________