ADO with SQL Server 2000

[just_a_me]

Background info: I have a VB6 application where I am going to convert it from writing to an Access database to a SQL Server DB instead. I have a stored procedure for inserting records that I was hoping to use.

Problem: In ADO.net it is easy to open your connection, call your stored procedure then close the connection. It seems like all the example code for ADO that I find ends up leaving the connection open the whole time the application is running.

Question: Is there anything wrong with opening and closing my connection each time I call the stored procedure using ADO?

[si_the_geek]

For VB6/ADO, leaving the connection open for the life of the application is fine - just make sure you close it when you have completely finished.

If you want to, you can open/close the connection each time - with connection pooling there shouldn't be a noticeable difference either way.

[techgnome]

Background info: I have a VB6 application...[snip]

Problem: In ADO.net it is easy to o...[snip]

ADO.NET... or ADO? ADO.NET is used in VB.NET ... VB6 still uses ADO (classic if you will).

As for opening the connection and leaving it open... it depends on the app and your architecture... in our system leaving the connection open all the time was unacceptable because of the architecture of our system. So we open when we need data, get the data (or save or what ever) and then close the connection. Some cases we'll open a connection, use it a dozen times, then close it. But for the most part it's process based.

-tg