To work on a record set, you must
communicate what list of values, such as a table or else, would be considered in the set. To specify a table as
the source of data, pass it, as a string, as the first argument to the Recordset.Open()
method. This can be done as follows: Private Sub btnRecordset_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRecordset.Click
Dim rstPeople As ADODB.RecordsetClass
rstPeople = New ADODB.RecordsetClass
rstPeople.Open("Persons", . . .)
rstPeople.Close()
End Sub
This specifies that the source of data will be a table
named Persons.
|
The Connection to a
Record Set |
|
When creating a recordset, you must specify how the
connection to the database would be established. To support this, the
RecordsetClass class is equipped with a property named ActiveConnection,
which is of type String. To specify this property, you can first create a ConnectionClass object
using the techniques we reviewed in previewed lessons. After defining the
object, you can pass it as the second argument to the RecordsetClass.Open()
method. This can be done as follows: Private Sub btnRecordset_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRecordset.Click
Dim rstPeople As ADODB.RecordsetClass
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
rstPeople = New ADODB.RecordsetClass
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)
rstPeople.Open("Persons", conADO)
rstPeople.Close()
End Sub
This creates a connection to a database named People
and uses it for the new RecordsetClass object.
In many environments, a
database will need to be accessed by more than one computer. This means
that, when creating a RecordsetClass object, you need to keep different
factors in mind. For example, you (actually your user) may be accessing a
record or a series of records at the same time with someone else from
another computer. In some
cases, there may usually be only one person using a database and there
might occasionally be someone else but unlikely, although possible. In
some other cases, such as on the Internet or in a big enterprise, there
might be many people accessing, or trying to access, a database, or a
specific set of records, at the same time.
Imagine you are working on a large database such as a
bank application that has thousands or millions of records (such as
thousands or millions of customers). If you want to perform an operation
on the customers, you may have to deal with many or all records. You may
also have to deal with the fact that other people are accessing the same
records like you, at the same time.
Normally, some
operations don't require you to have access to all records, at least not
all the time. When working on records, thus when creating a RecordsetClass
object, you can specify a way to isolate a range of records and deal only
with that range. The range of records that you select is called a cursor.
Because a cursor plays a tremendous role in a record
set, there are different options when using it. To support these options,
the ADODB namespace is equipped with an enumeration named CursorTypeEnum.
There are various types of cursors:
- A static cursor holds a constant set of
records. Suppose you create a record set and open it. Also suppose
that either you only or other people besides you are working on the
same record set. You get to a record and start viewing it (or even
working on it). After using that record, you move to another record,
and you can do this back and forth as you wish. Suppose that, while
doing this back and forth navigation (we will learn later on how to
programmatically navigate through a record set), another person has
accessed a record that is part of your record set and made a change.
If using a static cursor, every time you visit the record set, it
shows the same records the way they were when you opened the record
set. It would not show the changes that have taken place. This is why
it is called a static cursor. A static cursor is appropriate if you
are not interested to know what changes have taken place ever since
you opened the record set.
A static cursor is represented with the ADODB.CursorTypeEnum.adOpenStatic
value.
- A cursor is referred to as
forward-only if it allows you to move forward through the records.
Here is how it works. Suppose that you create a Recordset object and
specify its cursor. Suppose that, while using the record set, you get
to a record that was set as the start point of your cursor. Also,
suppose that either you only or other people besides you are working
on the same record. If you make a change on the current record, the
other people will be notified. If other people make a change on the
current record, you also would know. After using that record, you move
to the next. With the forward-only cursor, you cannot move back to a
record you left already. This means that, even if you are still
working on the record set, if there are changes performed on a record
you left behind (for example, if another person who is working on the
same record changes something on a record that you passed already),
you cannot know and you cannot find out because you cannot go back to
a record left behind. If this becomes a necessity, you can close the
cursor and re-open it. A forward-only cursor is appropriate if you
don't need to navigate back and forth among the records of a record
set. Because of the way it works, if you access the RecordCount
property of a forward-only cursor, it would produce -1.
This type of cursor is represented by the ADODB.CursorTypeEnum.adOpenForwardOnly
value.
- A cursor is called dynamic if it detects
and shows all changes that are occurring in a record set, whether the
changes are caused by you or by other people who are accessing the
record set at the same time. This type of cursor is appropriate if you
want to know, live, what is going on with the record set you are
working with.
To specify a dynamic cursor, use the ADODB.CursorTypeEnum.adOpenDynamic
value.
- A key set cursor creates and saves a key
for each record that has been modified since the record set was
opened. If you access the record, the key is used to check the data of
the record set.
A key set cursor is created using the ADODB.CursorTypeEnum.adOpenKeyset
value.
If you don't specify a cursor, the ADODB.CursorTypeEnum.adOpenUnspecified
value is used as the default cursor. Otherwise, to specify a cursor, pass
the desired value as the third argument to the RecordsetClass.Open()
method. Here is an example:
Private Sub btnRecordset_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRecordset.Click
Dim rstPeople As ADODB.RecordsetClass
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
rstPeople = New ADODB.Recordset
Dim fldEach As ADODB.Field
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)
rstPeople.Open("Persons", _
conADO, _
ADODB.CursorTypeEnum.adOpenDynamic)
rstPeople.Close()
End Sub
This specifies a dynamic cursor on the recordset.
Imagine that, after creating a record set and working
on it, you want to control who else can have access to the records of the
set you are using. To exercise this control, you can create a
"lock". This allows you, for example, to prevent other people
from changing the records until you have finished with them. To support
locking, the ADODB namespace is equipped with an enumeration named
LockTypeEnum, which defines various options:
- When a computer connects to a database, its user may need to make
changes to various records at the same time, such as deleting a range
of records or changing many records at the same time (such as giving a
raise to many employees), instead of making one change, then another,
then another. For this type of scenario, when the user accesses the
records, instead of monopolizing them and waiting for the user to
finish an operation that could take long, you can download the records
on the user's computer, and disconnect the user from the database. The
user would then make the necessary changes. When the user is ready to
commit the changes, you can then reconnect to the data source and
submit the changes. This type of lock is referred to as batch
optimistic.
This lock is supported through the ADODB.LockTypeEnum.adLockBatchOptimistic value
- You may have a database that a few different people access at the
same time. If the database is small enough, which is the case for
restricted environment, the likelihood of two people editing or
updating the same record (at the same time) may be low. In this case,
you can indicate that you want to lock the record only when necessary.
In this case, you use what is referred to as optimistic locking.
This lock is implemented using the ADODB.LockTypeEnum.adLockOptimistic value
- The above two options assume that you would lock many records to
apply the indicated scenarios. If you prefer to lock one record at a
time, you can use what is referred to as pessimistic locking.
This lock is
done using the ADODB.LockTypeEnum.adLockPessimistic value
- The above three scenarios allow a user to edit and/or update the
records that are included in the set. In some cases, you may want to
prevent any editing or update on the records while the set is being
accessed. In this case, you can set the records to read-only.
This lock can be set using the ADODB.LockTypeEnum.adLockReadOnly
value
If you don't want to specify the
type of lock system to use on a record set, use the ADODB.LockTypeEnum.adLockUnspecified
value, which is the default value. Otherwise, to specify the lock
type, pass the desired value as the fourth argument to the RecordsetClass.Open()
method. Here is an example:
Private Sub btnRecordset_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnRecordset.Click
Dim rstPeople As ADODB.RecordsetClass
Dim conADO As ADODB.ConnectionClass = New ADODB.ConnectionClass
rstPeople = New ADODB.Recordset
Dim fldEach As ADODB.Field
conADO.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source='C:\Programs\People.mdb'", Nothing, Nothing, 0)
rstPeople.Open("Persons", _
conADO, _
ADODB.CursorTypeEnum.adOpenDynamic, _
ADODB.LockTypeEnum.adLockOptimistic)
rstPeople.Close()
End Sub
This uses the adLockOptimistic as the type of lock
used on the recordset.
|
