SQLConnection advice required (VB.net) 1

[keithinuk]

Hello
I'm trying to determine best practice regarding connection to my SQL2000 database from a 3-tier application with a minimal user base. By minimal I mean anything from 1 to 5 users. Largest clients may have about 20 users.

At the moment I open a connection at signon time and it is active for the length of the session.

Should I be doing this or should I be establishing a new connection for each database access whether that be retrieval or update ?????

Isn't there a licencing issue as well with multiple connections ?????

Any advice please ?????
Thanks
Keith In case I forget to say "Thank you" I'll say it now - thank you for your help !!!

 

[chiph]

Opening a connection and leaving it open is indeed wasteful of connection licenses. With only 1-5 users, it's not a problem for you today. But if the number of users were to increase (via a corporate acquisition, or increased business) you'd have to buy more licenses. What if another application gets written that runs on the same DB server, same thing would happen.

By writing your ADO.NET application to connect only as needed, more database connections are available on average (peak usage is another concern). Plus, ADO.NET has very good database connection pooling, so as long as the connection string is *identical*, then you'll get a pooled connection the next time you need it, and your app will be much faster.

Please feel free to ask more in the VB.NET forum.

Chip H.

 

[keithinuk]

Thanks for the reply Chip.
Most of our clients are banks in African or the Caribbean so acquisition and by out are unlikely. I take your point though !!!!!
As far as connection pooling is concerned I read a post on a board recently (could have been here) that highlighted pooling as an issue and somebody suggested turning it off in SQL. I didn't get any further than reading the post though.
From what you've said it sounds like a bonus not an issue.

Thanks again for your reply.
Keith In case I forget to say "Thank you" I'll say it now - thank you for your help !!!

 

[SQLSister]

If you are opening and closing connections all through your application, remember to write the connection as a component and instantiate a new instance of it every time rather than coding the connection information throughout your program. It makes a huge differnce when you need to change something about your connection string at a later time.