Introduction
I first started my interaction with databases with MS Access, like most people, and of course, I discovered VBA. OK... I have a background in C, C++, a little of C#, and a couple of other languages, and I thought I could try something new. VBA was so easy to use and understand, and I developed a pretty complex database with a back-end file (data file) and a front-end (distributable interface file) in no time. I used linked tables at first, and with the growing complexity of the database came the problems. First, MS Access took control over the data, recording things when I didn't want them to be changed (for example, calling the move next record command or simply closing a form after changing the data). The second was the speed of the application decreasing due to the network issues.
Thanks to a thing called recordsets, I solved my problem. I built a class in MS Access that allowed me to do two things. It allowed me to do whatever I wanted with the data and was incredibly easy to use. It also allowed me to use snapshot images of the data that boosted the speed of my application over the network. This class I created also did a kind of fake binding of the data and the form controls. By doing this, I regained all control over my data, which avoided the problems of "unwanted recording and data changes" (if you have used MS Access, you know what I'm talking). But the DAO and ADO recordsets are still relatively slow since they need a constant connection. In an already slow network, more than three people connecting at the same time to the database made it sluggish and painful to use.
So I started to study ADO.NET and ... what I discovered was not pretty; Connections, DataAdapters, DataSets, DataTables, DataRows, DataColumns...whhoowww. I was getting dizzy. I asked myself if there was something as simple to use as the DAO or ADO recordset. I couldn't find any. The other thing about ADO.NET is that most articles talk about using wizards that auto-build extensive code specifical to a data format. What if the database structure changes? What do I have to do? Rebuild the form completely???
Based on this, I started building this simple library that simulates a recordset, and another that performs a fake binding...but that's another story.
Notes
I don't understand much of ADO.NET. In fact I can't even begin to grasp the amount of features and all the possibilities that it offers, and I may even be saying some really dumb things here.
If you are looking for a miracle solution...forget it. The concept is to allow you to make a simple and basic usage of the ADO.NET features.
If you want to have a very simple database interface layer, this is the place to be.
The main concept
The main concept of these two libraries is to act like middle tiers between the database (tables and relations) and the interface.
The beauty of this is that if you want to change some structural things about your database file (let's say that you want to migrate the technology of your database from MS Access to MS SQL Server), you will only have to change the middle layer(s) without having to change the entire application.
The other great aspect of this is the reduction of the amount of wizard generated code. Making a change in auto generated code is sometimes painful. "I didn't write this. What is this?"
The ADO.NET RecordSet Class
Basically, the recordset works like a pointer to an array. It must offer search capabilities as well as creation, deletion, and saving functionalities. For example, to keep track of where we are, we need to have an integer variable that stores our current position. This index is the key to our recordset, and based on its value, we will run through the data. These are some of the variables used by this class:
Private conn As OleDb.OleDbConnection
Private dadapter As OleDb.OleDbDataAdapter
Private cmdbld As OleDb.OleDbCommandBuilder
Private select_command As OleDb.OleDbCommand
Private insert_command As OleDb.OleDbCommand
Private delete_command As OleDb.OleDbCommand
Private update_command As OleDb.OleDbCommand
Private dset As DataSet
Private dtable As DataTable
Private drow As DataRow
Private index As Long
Private newRow As Boolean
Private found As Boolean
Private SQLQuery As String
Private foundrecords_counter As Long
Private operation_finished As Boolean = False
To use the recordset, we have to proceed like we would with the DAO or ADO recordset.
- Open the connection
- Open the recordset
- Do all the operations needed
- Close the recordset
Opening the connection
The ADO.NET Recordset class has a Connection function which simply stores the path to the database file and the provider to use (and other parameters you might think useful), and tests the connection by opening and closing it.
Function Connection(ByVal constr As String)
Try
conn.ConnectionString = constr
conn.Open()
conn.Close()
Connection = True
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + _
MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, _
"ADO.NET RecordSet Library Error")
Connection = False
End Try
End Function
Opening and closing the recordset
When the recordset is opened, the index variable is set to 0 (the beginning of the recordset), else it is set to -1 (BOF - Beginning of File, meaning that no record is being pointedat). The SQLstr indicates the SQL query for our table (e.g.: "SELECT * FROM OurTable"). fields is used to retrieve the names of the columns in the table (see ahead). The newRow is used as a flag (see ahead).
To open the recordset, we simply do this:
Function OpenRecordSet(ByVal SQLstr As String)
Try
dadapter = New OleDb.OleDbDataAdapter(SQLstr, conn)
dadapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
dadapter.MissingMappingAction = MissingMappingAction.Passthrough
dadapter.Fill(dset)
fields = dset.Tables(0).Columns
If dset.Tables(0).Rows.Count() = 0 Then
index = -1
Else
index = 0
End If
newRow = False
OpenRecordSet = True
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + _
MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, _
"ADO.NET RecordSet Library Error")
OpenRecordSet = False
End Try
End Function
To close the recordset, we simply do this:
Function CloseRecordSet() As Boolean
Try
dset.Dispose()
dadapter.Dispose()
conn.Dispose()
CloseRecordSet = True
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + _
MsgBoxStyle.ApplicationModal + _
MsgBoxStyle.OkOnly, "ADO.NET RecordSet Library Error")
CloseRecordSet = False
End Try
End Function
So to use this class, we just have to do something like this:
- create and initialize our variable (named, for example "
rs")
rs.Connection("parameters of the connection go here")
rs.OpenRecordSet("the SQL Query that defines our table")
This is great, but how do I retrieve/set values in a recordset?
Let's see how we can do this.
Retrieving/setting values of a recordset
To return/set the value of a field in the recordset, we create a property called FieldValue and indicate the name or index of the column to return/set. For example, one of the overloaded implementations of this property is like this:
Property FieldValue(ByVal columnName As String)
Get
Try
FieldValue = dset.Tables(0).Rows(index).Item(columnName)
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + _
MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, _
"ADO.NET RecordSet Library Error")
End Try
End Get
Set(ByVal Value)
Try
If newRow Then
drow.Item(columnName) = Value
Else
dset.Tables(0).Rows(index).Item(columnName) = Value
End If
Catch ex As Exception
MsgBox("Error: " & ex.Message, MsgBoxStyle.Critical + _
MsgBoxStyle.ApplicationModal + MsgBoxStyle.OkOnly, _
"ADO.NET RecordSet Library Error")
End Try
End Set
End Property
As you can see, the newRow flag is used here to identify if we are changing an existing record or creating a new one.
Nice, but how do I navigate through the recordset?
Let's see how to do the navigation...
Navigating the recordset
The navigation and searching through the recordset is done using the value of the index. So if we want to go back and forth in the recordset, we simply manipulate that variable.
To navigate the recordset, we build the Move<direction> set of functions.
This is the MoveFirst example:
Sub MoveFirst()
index = 0
End Sub
Flags on a recordset
Flags are a very important aspect in recordsets. They give you indications on the state of the recordset after a navigation or a search. These indications are usually boolean values that tell you, for example, if you have reached the end of a recordset, or if a search (Find<word> set of functions) was successful or not.
An example of this flag is the BOF flag. This flag is true when the beginning of the recordset has been reached (before the recordset, where there is no data)
You can get this flag as true, for example, if you continuously do a MovePrevious command and pass beyond the first position.
ReadOnly Property BOF() As Boolean
Get
If index < 0 Then
BOF = True
Else
BOF = False
End If
End Get
End Property
The example
I believe the example is very straightforward and easy to understand.
It simply uses some commands of the recordset and some flags, and shows you how this library is used and how easy the recordset functionalities are.
Conclusion
In this article, you have seen how to create a simple recordset class to navigate through data in a database. As for data binding in a form, well...that'll be the next chapter.
Hope this helps. Cheers!
