SQL Server Connections - Best Practice

[MacDaddy]

This may be a daft question so apologies in advance but I'm pretty new to VB.NET and connecting to SQL Server.

What is considered best practice when building an app that needs to connect frequently to a SQL Server DB to fetch, write, delete and update records?

Should a connection be opened at app startup (a connection at start is opened to validate the user) and left open for subsequent transactions or should a connection be opened, closed and disposed of each time a read/write to the DB is required?

Or is it purely a matter of personal preference?

Thanks in advance,

Paul

[techgnome]

Generally speaking, the idea is that connections should only be open for as long as necessary to do what needs to be done to the database. ADO.NET is designed to operate primarily in a disconnected state... you open a connection, get your data and close the connection. The app does what it needs to do on the data in memory. then you open the connection, update the database, close the connection.

That said....
Let's say you have to select data from three table when your app starts.
Do not do this:

open
select
close

open
select
close

open
select
close


Do it like this instead

open
select
select
select
close

Does that help?

-tg

[Jenner]

Yes, if you have multiple quick operations, keep the connection open until done. This is bad for example:

For i = 1 To 100
    Using con As New SQLConnection(constring)
        con.Open
        Dim cmd As New SQLCommand(strSQL, con)
        cmd.ExecuteNonQuery()
    End Using
Next

The main reason is because the database server takes time to open and shut down connections and it can generally only have a certain number open at a time.

[MacDaddy]

Oh yes, fair comment, if there are multiple queries to be executed one after another then it goes without saying that opening/closing a connection each time is a bit daft.

The primary reason for the question was whether or not to open a connection and keep it in that state until the app is terminated for use by whatever process the user starts, obviously from the answers given that's not the way to go so thanks for your help, much appreciated.

Cheers,

Paul