Possible connection leaking causing "System.Data.SqlClient.SqlException: Timeout expired" error in SQL Server?

My application requires a user to log in and allows them to edit a list of things. However, it seems that if the same user always logs in and out and edits the list, this user will run into a "System.Data.SqlClient.SqlException: Timeout expired." error. I've read a comment about it possibly caused by uncommitted transactions. And I do have one going in the application.

I'll provide the code I'm working with and there is an IF statement in there that I was a little iffy about but it seemed like a reasonable thing to do.

I'll just go over what's going on here, there is a list of objects to update or add into the database. New objects created in the application are given an ID of 0 while existing objects have their own ID's generated from the DB. If the user chooses to delete some objects, their IDs are stored in a separate list of Integers. Once the user is ready to save their changes, the two lists are passed into this method. By use of the IF statement, objects with ID of 0 are added (using the Add stored procedure) and those objects with non-zero IDs are updated (using the Update stored procedure). After all this, a FOR loop goes through all the integers in the "removal" list and uses the Delete stored procedure to remove them. A transaction is used for all this.

Publ开发者_高级运维ic Shared Sub UpdateSomethings(ByVal SomethingList As List(Of Something), ByVal RemovalList As List(Of Integer))
Using DBConnection As New SqlConnection(conn)
    DBConnection.Open()
    Dim MyTransaction As SqlTransaction
    MyTransaction = DBConnection.BeginTransaction()
    Try
        Using MyCommand As New SqlCommand()
            MyCommand.Transaction = MyTransaction
            MyCommand.CommandType = CommandType.StoredProcedure

            For Each SomethingItem As Something In SomethingList
                MyCommand.Connection = DBConnection
                If SomethingItem.ID > 0 Then
                    MyCommand.CommandText = "UpdateSomething"
                Else
                    MyCommand.CommandText = "AddSomething"
                End If
                MyCommand.Parameters.Clear()
                With MyCommand.Parameters
                    If MyCommand.CommandText = "UpdateSomething" Then
                        .Add("@id", SqlDbType.Int).Value = SomethingItem.ID
                    End If
                    .Add("@stuff", SqlDbType.Varchar).Value = SomethingItem.Stuff
                End With
                MyCommand.ExecuteNonQuery()
            Next

            MyCommand.CommandText = "DeleteSomething"
            For Each ID As Integer In RemovalList
                MyCommand.Parameters.Clear()
                With MyCommand.Parameters
                    .Add("@id", SqlDbType.Int).Value = ID
                End With
                MyCommand.ExecuteNonQuery()

            Next
        End Using
        MyTransaction.Commit()
    Catch ex As Exception
        MyTransaction.Rollback()
        'Exception handling goes here   '
    End Try

End Using
End Sub

There are three stored procedures used here as well as some looping so I can see how something can be holding everything up if the list is large enough.

I'm using Visual Studio 2008 to debug and am using SQL Server 2000 for the DB.

Edit: I still seem to be getting this error. I've even removed the whole transaction thing and I still encounter it. At this point, I'm assuming there is some kind of leak happening here. I've tried not using the USING statements and explicitly tell the command and connection to dispose itself but no dice. Memory usage by SQL Server also increases quite a bit if this method is called a lot in a short period of time.

I've read that increasing the CommandTimeout property of the SQLCommand would help. I'm wondering if there are any big disadvantages or consequences from doing so.

---

I would suggest using the following, that way Dispose will always be called and be Rolledback in every non-committed case.

using (SqlConnection sqlCn = new SqlConnection())
{
   using (SqlTransaction myTrans = sqlCn.BeginTransaction())
   {
   ...
   myTrans.Commit();
   }
}

Also, I don't believe you need to make a new SqlCommand for every execution. Just maintain the same one and update the CommandText and Parameters.

---

If you have a large number of commands, you may want to build them all before opening the connection. After you start the transaction and open the connection, spin through and execute them.

You probably want to use TransactionScope

Using _tx as New System.Transactions.TransactionScope(<add your own timeout here>)

 'Do all your sql work' 

 If _noErrors Then
  _tx.Complete()
 End If

End Using 

With the transaction scope, you can set a timeout of up to 20 minutes without modifying server settings.

---

I believe I have managed to solve the problem. I have modified the application so that unnecessary calls to the database are not made (i.e. unchanged objects do not need to be updated again) and increased the CommandTimeout property for the SQLCommand object. So far, no problems.

Big thanks for suggestions too.