How bad is opening and closing a SQL connection for several times? What is the exact effect?

ADO. NET has connection pooling, e.g. When you close a connection, it's not really completely closed, but "recycled" if you ask for a new connection with exactly the same connection string.

ADO. NET has connection pooling, e.g. When you close a connection, it's not really completely closed, but "recycled" if you ask for a new connection with exactly the same connection string. Nevertheless - if you already know ahead of time that you'll have to call these five Fill methods one by one, I would definitely recommend opening the connection reading all five data tables from the database closing the connection again right away It's accepted best practice to do it this way, it doesn't hurt you - so just do it!

:-) Marc PS: Connection pooling in ADO. NET of course only works if you haven't turned it off! :-) It's on by default - you'd have to explicitly disable it.

Performance There are many reasons that you may want to open and close connections. You have to decide where the best trade-off is for your use. You can do both: use an open connection for a period of time and/or a set number of transactions, then close it and open a new one.

Opening and closing SQL connections is expensive when compared to other simple tasks in the database. But, if your actual task is already time consuming, the extra overhead may not be noticed (if you are already hiding the wait period of the actual task - so the user does not start randomly clicking things - like retry). Test Case: You can measure your difference by writing two versions of a test query.

Select any simple SQL task (needs to be the same in each version). In version one, do it with a single constant open connection outside the loop, looping thru your simple task X number of times.In the second, do it with the opening and closing of the connection inside the loop. Change the X number of times to match your usage and expectations.

That should give you a real good feel for the impact on your system. Hope that helps you understand the basics... Jack.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.