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 comments about increasing the timeout period but I've also 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.

Public 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
        For Each SomethingItem As Something In SomethingList
            Using MyCommand As New SqlCommand()

                MyCommand.Connection = DBConnection
                If SomethingItem.ID > 0 Then
                    MyCommand.CommandText = "UpdateSomething"
                Else
                    MyCommand.CommandText = "AddSomething"
                End If
                MyCommand.Transaction = MyTransaction
                MyCommand.CommandType = CommandType.StoredProcedure
                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()
            End Using
        Next
        For Each ID As Integer In RemovalList
            Using MyCommand As New SqlCommand("DeleteSomething", DBConnection)
                MyCommand.Transaction = MyTransaction
                MyCommand.CommandType = CommandType.StoredProcedure
                With MyCommand.Parameters
                    .Add("@id", SqlDbType.Int).Value = ID
                End With
                MyCommand.ExecuteNonQuery()
            End Using
        Next

        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. Other users can log in to the system at the same time just fine though.

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