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.
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_rateCheckingConnection.Open()
m_fieldList = New Hashtable
End Sub
Public Sub CloseConnection()
m_rateCheckingConnection.Close()
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
m_rateCheckingConnection.Close()
m_rateCheckingConnection.Open()
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()
m_rateCheckingReader.Read()
End Sub
Public Sub MovePrevious()
' since the records are in descending order now, this is really just a Read to get to next record
m_rateCheckingReader.Read()
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()
m_fieldList.Clear()
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 + "'"
Next
Dim insertSQL = "INSERT INTO MyTable(" + insertFieldList + ") VALUES (" + insertValueList + ")"
Dim conn As SqlConnection = New SqlConnection(m_connStr)
conn.Open()
Dim cmd As SqlCommand = New SqlCommand(insertSQL, conn)
cmd.ExecuteNonQuery()
conn.Close()
' clear the list when done
m_fieldList.Clear()
End Sub
End Class
