Home

Introduction to Record Sets

 

A Record Set Object

 

Introduction

After creating a table, you can fill it with records. You can also create a list (called a query) that selects a few records from a table. The records that a table holds are referred to as a set of records or a record set. To support them as an entity, the ADO library provides an object called a Recordset.

Here are the database and the table we will use:

private void btnPersons_Click(object sender, EventArgs e)
{
    ADOX.CatalogClass catPeople = new ADOX.CatalogClass();
    ADODB.ConnectionClass conPeople = new ADODB.ConnectionClass();
    string strSQL;
    object obj = new object();
            
    catPeople.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source='C:\\Programs\\People.mdb';");
MessageBox.Show("A new Microsoft JET database named People.mdb has been created");

    conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
                   "Data Source='C:\\Programs\\People.mdb'", "", "", 0);
    strSQL = "CREATE TABLE Persons(" +
                     "PersonID COUNTER NOT NULL, " +
                     "FirstName Text(20), " +
                     "LastName Text(20), " +
                     "Gender Text(20));";
    conPeople.Execute(strSQL, out obj, 0);
    MessageBox.Show("A table named Persons has been created in the " +
                    "People.mdb database");
            
    conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " +
                      "VALUES('James', 'Carlton', 'Male');", out obj, 0);
    conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " +
                      "VALUES('Hermine', 'Nguyen', 'Female');", out obj, 0);
    conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " +
                      "VALUES('Paul', 'Yamaguchi', 'Male');", out obj, 0);
    conPeople.Execute("INSERT INTO Persons(FirstName, LastName, Gender) " +
                      "VALUES('Justice', 'Ville', 'Unknown');", out obj, 0);
    MessageBox.Show("New records have been created in the Persons table");
            
    conPeople.Close();
}

This creates a Microsoft JET database named People, followed by a table named Persons, and populates that table with four records.

The Type of Recordset Objects

When creating a record set, you can specify whether to use one or more tables and what record(s) would be included. How the record set is created, how many tables it would have, how many records, and what types of operations can be performed on the records lead to various types of record sets:

  • Table Recordset: If your record set includes only one table, it is referred to a Table record set
  • Dynaset: If a record set includes one or more tables, it is called a Dynaset. This type allows adding, editing, updating, or deleting records that are part of the Recordset object
  • Snapshot: A record set is called a Snapshot if it allows you to view the records of one or more tables, navigating back and forth in the set, but you cannot make changes on the records
  • Forward-Only Recordset: A Recordset object is referred to as Forward-Only if you can view its records without changing them and you can only move forward. This means that, if you get to a record, examine it, and move to the next record, you cannot refer back to a record you left already
  • Dynamic Recordset: If you create a list based on data from one or more tables and allows the users to navigate back and forth with the ability to make changes to records, this type of record set is referred to as Dynamic.

Creating a Recordset Object

Normally, you first create a record set by populating a table with the desired values. To create a Recordset object, start by declaring a variable of type ADODB.Recordset. Here is an example:

private void btnRecordset_Click(object sender, EventArgs e)
{
     ADODB.Recordset rstPeople;
}

Before using the record set, use the new operator to allocate memory for it. Here is an example:

private void btnRecordset_Click(object sender, EventArgs e)
{
     ADODB.Recordset rstPeople;

     rstPeople = new ADODB.Recordset();
}

Opening the Recordset

After declaring the variable, to start creating the recordset, you can open the Open() method of the RecordsetClass class. It's syntax is:

void Open(object Source,
          object ActiveConnection,
          ADODB.CursorTypeEnum CursorType,
          ADODB.LockTypeEnum LockType,
          int Options);

This method takes five arguments.

 

Characteristics of a Record Set

 

The Source

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 void btnRecordset_Click(object sender, EventArgs e)
{
            ADODB.Recordset rstVideos = new ADODB.Recordset();

            rstVideos.Open("Persons", . . .);
}

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 void btnRecordset_Click(object sender, EventArgs e)
{
        ADODB.Recordset rstVideos = new ADODB.Recordset();
        ADODB.Connection conPeople = new ADODB.Connection();
            
        conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source='C:\\Programs\\People.mdb'", "", "", 0);

        rstVideos.Open("Persons", conPeople, . . .);
}

This creates a connection to a database named People and uses it for the new Recordset object.

A Cursor

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 Recordset 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.

To specify a cursor, pass the desired value as the third argument to the RecordsetClass.Open() method. Here is an example:

private void btnRecordset_Click(object sender, EventArgs e)
{
        ADODB.Recordset rstVideos = new ADODB.Recordset();
        ADODB.Connection conPeople = new ADODB.Connection();
            
        conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source='C:\\Programs\\People.mdb'", "", "", 0);

        rstVideos.Open("Persons",
                       conPeople,
                       ADODB.CursorTypeEnum.adOpenDynamic, . . .);
}

This specifies a dynamic cursor on the recordset.

The Lock Type

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

To specify the lock type, pass the desired value as the fourth argument to the RecordsetClass.Open() method. Here is an example:

private void btnRecordset_Click(object sender, EventArgs e)
{
            ADODB.Recordset rstVideos = new ADODB.Recordset();
            ADODB.Connection conPeople = new ADODB.Connection();
            
            conPeople.Open("Provider=Microsoft.Jet.OLEDB.4.0;" +
                           "Data Source='C:\\Programs\\People.mdb'", "", "", 0);

            rstVideos.Open("Persons",
                           conPeople,
                           ADODB.CursorTypeEnum.adOpenDynamic,
                           ADODB.LockTypeEnum.adLockOptimistic, . . .);
}

This uses the adLockOptimistic as the type of lock used on the recordset.

The last argument is used to indicate how the database provider will evaluate the first argument.

 

Home Copyright © 2005-2012 FunctionX