"Too Much Persistence"

This problem regarding an ASP.NET 1.1 app, written by another developer, recently came my way:

After searching for a person to add to the [List], it does not successfully perform any subsequent searches.

It's like it is storing part of the old search and that is why the new one isn't working. It does not reset unless you log out of ABC Online completely and go back in. Even after that, you have to keep logging all the way out and back in to do more than one search.

I realized that this matter probably involved ASP.NET Session because the problem was resolved by logging out of the app which destroyed the user's session. From scant past experience I also knew that this app made generous use of session.

I fired up the app and did indeed receive non-deterministic results. If I performed a wide open search with no criteria I would receive a result set of 800 rows. If I specified a last name, I would get back a few rows, which was expected behavior. If I then removed all search criteria and searched again I would not receive 800 rows but instead would receive the previous search results of just a few rows. If I then specified another criteria, such as first name, I received even more confusing and inconsistent results. I ran the Oracle procedure and it returned the expected results. Hmmmm. Time to crack open the code.

userSearchAdapter.SelectCommand.Parameters("I_DIVISION_ID").Value = divisionID
If userType.Length > 0 Then
userSearchAdapter.SelectCommand.Parameters("I_USER_TYPE").Value = userType
End If
If firstName.Length > 0 Then
userSearchAdapter.SelectCommand.Parameters("I_FIRST_NAME").Value = firstName
End If
If lastName.Length > 0 Then
userSearchAdapter.SelectCommand.Parameters("I_LAST_NAME").Value = lastName
End If
If title.Length > 0 Then userSearchAdapter.SelectCommand.Parameters("I_TITLE").Value = title
End If
UserSearchDataSet.Clear()
userSearchAdapter.Fill(UserSearchDataSet)

The code above came from a search method that's part of a search object that is stored in session. The search object is instantiated on first use. I could see from the call to DataSet.Clear that the results were being reset before each call to the Fill method. Setting some debug breakpoints and watches confirmed this, but unexpected results were returned from the database call so stale data wasn't the issue. Observing the values of the OracleParameters finally revealed the problem.

The values of the parameters were only set if the length of its corresponding argument was greater than 0. This meant that once the value of a parameter was set, it wouldn't be reset to null. It could only be set to another value with a length greater than 0. Because this search object was stored in session these parameter values persisted across postbacks. This logic bug explained why searches produced expected results until a user tried to clear search criteria and initiate a new, fresh search.

My solution was predictably overkill. (Bite me- I like to be thorough.) I stuck the following just above the first line of the code above.

' Because this object is persisted in session, its parameters values are constant between postbacks. This characteristic
' causes non-deterministic results because the parameters values, once set, were never unset due to the if logic
' surrounding each parameter. The For loop below resets the values of each parameters before a search so that each
' search begins with a fresh set of parameters.
For Each param As OracleClient.OracleParameter In userSearchAdapter.SelectCommand.Parameters
If param.Direction = ParameterDirection.Input AndAlso Not param.Value Is Nothing AndAlso Not param.Value.Equals(System.DBNull.Value) Then
param.Value = System.DBNull.Value
End If
Next

I fault not the developer but the folks that supposedly QA'd this application. I suppose that they just performed one search and concluded that this functionality was perfect. This application has been in production for almost 6 months and it has taken them this long to discover the problem.