SQL Connections and .NET

[avarga82]

My question is about opening & closing SQL connections in .NET.

My app is made up of 3 main parts: A code library, a control library, and the main application. All three are separate projects. In each, I'm using a global connection. My code for the public connection is as follows:

Public Function p_cn() As SqlClient.SqlConnection
If m_cn.State = ConnectionState.Closed Then
m_cn.Open()
End if
Return m_cn
End Function

I obviously want to minimize the number of connections that are opened & closed in my app, and I don't see a better way other than a global connection. Also, should I be opening and closing the connection for each transaction? This doesn't sound logical to me, but it seems that even with a global variable that I never explicity close, is still closing and reopening. I'm just trying to minimize the resources used on the SQL Server.

Any ideas?

 

[jebenson]

In general, it is a good idea to open the connection when needed and close it immediately when it is no longer needed.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[SBendBuckeye]

If you are using DataReaders, you are responsible for opening and closing the connection. If you use DataAdapters, they will leave the connection in the state in which they found. Good Luck!

If you are doing a lot of SQL Server access, check out the MS Enterprise Library, it can simplify things a lot for you.

Have a great day!

j2consulting@yahoo.com