Wednesday, May 15, 2013

Simulating Microsoft Access recordset in ASP.Net with SQL Server (MovePrevious)

Despite all of the problems that Microsoft Access has, there are some things that it can do more conveniently than Microsoft SQL Server.  One of them is that it allows you to go forward and backward on a record set using MoveNext and MovePrevious.  In ASP.Net, using a SqlDataReader, you can only go forwards on a record set.  I was recently converting over an Access database to ASP.Net using SQL Server.  The Access code had a whole lot of places where a table was loaded to the record set, the cursor was moved to the last record with MoveLast, then the code stepped through the set backwards using MovePrevious, then reset itself to the end again with MoveLast.  I wanted to try to duplicate this as closely as possible .

After doing some reading, my first plan was to make a DataSet. Once you load data into a DataSet, you can iterate forwards and backwards.  The main reason I didn't want to do it that way is because you have to load all of the data into the DataSet at once that you might need to iterate through.  In my case, the amount of records I needed would vary depending on the data, and I didn't want to load a lot more than I had to in order to be sure I had all I needed.

What I ended up doing was based on a fairly simple idea.  I just sorted the records in the opposite order so that I could iterate through them forward instead of backward.  When MoveLast is called, I just re-ran the query to reset the DataReader's cursor back to the beginning.

The fancier parts were:

  • I made a class to encapsulate it all so that I could use the same method names that the Access record set uses.  That way there was less code to change, so I was hoping that would lead to less potential errors.
  • Access also lets you insert a record by calling AddNew to get a new record, changing values on the record, and then calling Update.  I added some methods to simulate that as well.
The class is not currently generalized to use different tables, but that shouldn't be too hard. The code is below:

    Dim m_newSqlFieldList As String = ""
    Dim m_newSqlValueList As String = ""

    Private Class AccessSimulator
        Dim m_connStr As String = ""
        Dim m_rateCheckingConnection As SqlConnection
        Dim m_rateCheckingCommand As SqlCommand
        Dim m_rateCheckingReader As SqlDataReader
        Dim m_fieldList As Hashtable
        Public Sub New(connStr As String)
            m_connStr = connStr
            m_rateCheckingConnection = New SqlConnection(m_connStr)

            m_fieldList = New Hashtable
        End Sub
        Public Sub CloseConnection()
        End Sub
        Public Sub MoveLast()
            ' This is to simulate MoveLast on the table
            ' TOP could also  be a select WHERE date goes back a certain amount from a given date

            ' always assume connection is open because the Reader is being used by the calling procedure
            ' it will only be closed if CloseConnection is explicitly called

            Dim sql As String = "SELECT TOP 50 * FROM MyTable ORDER BY ID DESC"
            m_rateCheckingCommand = New SqlCommand(sql, m_rateCheckingConnection)
            m_rateCheckingReader = m_rateCheckingCommand.ExecuteReader()
        End Sub
        Public Sub MovePrevious()
            ' since the records are in descending order now, this is really just a Read to get to next record
        End Sub
        Public Function GetFieldValue(fieldName As String) As String
             ' this is a substitute for the exclamation point when referencing a value of a record
            Return m_rateCheckingReader(fieldName).ToString()
        End Function
        Public Sub AddNew()
        End Sub
        Public Sub StoreValueForInsert(fieldName As String, fieldValue As String)
             ' this is a substitute for the exclamation point when setting a value of a record
            m_fieldList(fieldName) = fieldValue
        End Sub
        Public Sub Update()
            ' in my case, Update is only used on this class after doing an AddNew
            ' so since we aren't actually inserting in the AddNew method, do the insert now, based on fields in the field list
            ' changes would need to be made to also support actually updating records
            Dim insertFieldList As String = ""
            Dim insertValueList As String = ""
            For Each storedField As DictionaryEntry In m_fieldList
                ' if insertFieldList is non-empty, then value list is too
                If insertFieldList <> "" Then
                    insertFieldList = insertFieldList + ", "
                    insertValueList = insertValueList + ", "
                End If
                insertFieldList = insertFieldList + "[" + storedField.Key + "]"
                insertValueList = insertValueList + "'" + storedField.Value + "'"

            Dim insertSQL = "INSERT INTO MyTable(" + insertFieldList + ") VALUES (" + insertValueList + ")"
            Dim conn As SqlConnection = New SqlConnection(m_connStr)
            Dim cmd As SqlCommand = New SqlCommand(insertSQL, conn)

            ' clear the list when done
        End Sub
    End Class