Am I Comforting ADO.NET Connections?

Question

Here is an example of my code in DAL. All calls to the database. Stored procedures are structured in this way, and there is no embedded SQL.

Friend Shared Function Save(ByVal s As MyClass) As Boolean Dim cn As SqlClient.SqlConnection = Dal.Connections.MyAppConnection Dim cmd As New SqlClient.SqlCommand Try cmd.Connection = cn cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "proc_save_my_class" cmd.Parameters.AddWithValue("@param1", s.Foo) cmd.Parameters.AddWithValue("@param2", s.Bar) Return True Finally Dal.Utility.CleanupAdoObjects(cmd, cn) End Try End Function 

Here is the factory connection (if I use the correct term):

 Friend Shared Function MyAppConnection() As SqlClient.SqlConnection Dim cn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("MyConnectionString").ToString) cn.Open() If cn.State <> ConnectionState.Open Then ' CriticalException is a custom object inheriting from Exception. Throw New CriticalException("Could not connect to the database.") Else Return cn End If End Function 

Here is the Dal.Utility.CleaupAdoObjects () function:

 Friend Shared Sub CleanupAdoObjects(ByVal cmd As SqlCommand, ByVal cn As SqlConnection) If cmd IsNot Nothing Then cmd.Dispose() If cn IsNot Nothing AndAlso cn.State <> ConnectionState.Closed Then cn.Close() End Sub 

I get a lot of "Timed out". The waiting period expires before the operation is completed or the server does not respond. "Error messages reported by users. The DAL application opens a connection, reads or saves data, and closes it. No connections are ever left open - intentionally!

There is nothing obvious on Windows 2000 Server that hosts SQL Server 2000 that indicates a problem. Nothing in the event logs and nothing in the SQL Server logs.

Timeouts happen randomly - I can't play. This happens at the beginning of the day, and only 1 to 5 users in the system. This also happens with approximately 50 users in the system. Most connections to SQL Server through Performance Monitor for all databases were about 74.

Timeouts occur in code that stores and reads from the database in different parts of the application. The stack trace does not indicate one or two violating DAL functions. This has happened in many different places.

Is my ADO.NET code capable of leaking connections? I stumbled a bit, and I read that if the connection pool is full, it could happen. However, I am not setting up explicit connection pooling. I even tried to increase the connection timeout in the connection string, but timeouts happen long before the value of 300 seconds (5 minutes):

 <add name="MyConnectionString" connectionString="Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;Connection Timeout=300;"/> 

I already have a complete loss of what causes these timeout problems. Any ideas are welcome.

EDIT: This is a WinForms application.

Answer 1

From here :

Unlike Finalize, developers must explicitly call Dispose to free up unmanaged resources. In fact, you must explicitly call the Dispose method on any object that implements it to free up any unmanaged resources for which the object may contain references.

SqlConnection , SqlCommand , SqlDataReader , etc. all implement IDisposable . If you enable all these instances in the Using block, Dispose will be called automatically, your connections will be closed, and you will not have to worry about such problems. Launch the reflector and take a look at yourself: ( SqlConnection.Dispose )

 protected override void Dispose(bool disposing) { if (disposing) { this._userConnectionOptions = null; this._poolGroup = null; this.Close(); } this.DisposeMe(disposing); base.Dispose(disposing); } 

It also makes the code shorter in that you do not need to manually add a finally block to clean up ADO.NET objects.

 Using connection As SqlConnection("your connection string") Using command As New SqlCommand("your sql", connection) connection.Open() Using dataReader As SqlDataReader = command.ExecuteReader() 'Your stuff here End Using End Using End Using 

Using the Using approach forces you to support local ADO.NET objects, which is good for me.

Answer 2

One way to check for connection leaks is to add max pool size to the connection string, for example:

 "integrated security=SSPI;server=MyHost;Max Pool Size=1;" 

In development, I usually run this option (or size 2 if the application uses two connections at the same time).

Answer 3

You should always disconnect the connection, regardless of its status:

  'If cn IsNot Nothing AndAlso cn.State <> ConnectionState.Closed Then cn.Close() If cn IsNot Nothing Then cn.Dispose() 

I'm not sure if this can lead to your timeouts, but this is certainly an improvement.

Answer 4

How are your stored procedures performed outside of your application?

What if you moved 'return true' from a try / finally block to Save ()?

Monitor DB connections in a perforated monitor to see if they are growing.

But the first thing I would look at is your sprocs themselves - do they access tables in a constant order or can they be started in locks? For example, if proc1 manages table 1 and then table2, and proc2 gets to table2 and then to table1, you might run into lock problems.

Answer 5

Is this a Windows application or a web application?

Do timeouts occur with simple stored procedures or just with more complex ones?

Have you tried running sql profile tracing to find out if any queries are really time consuming?

Also, have you tried converting to the "using" syntax, which ensures that objects are closed and removed properly?