COMMAND OBJECTS

The command object classes (OleDbCommand, SqlCommand, OdbcCommand, and OracleCommand) define database commands. The command can be a SQL query, or some non-query statement such as an INSERT, UPDATE, DELETE, or CREATE TABLE statement.

The object’s Connection property gives the database connection object on which it will execute its command. CommandText gives the SQL text that the command represents.

The CommandType property tells the database the type of command text the command holds. This can be StoredProcedure (CommandText is the name of a stored procedure), TableDirect (CommandText is the name of one or more tables from which the database should return data), or Text (CommandText is a SQL statement).

The command object’s Parameters collection contains parameter objects that define any values needed to execute the command text.

Example program CommandInsert, which is available for download on the book’s website, uses the following code to create an OleDbCommand object that executes the bolded SQL statement INSERT INTO PeopleNames (FirstName, LastName) VALUES (?, ?). The question marks are placeholders for parameters that will be added later. The code then adds two new OleDbParameter objects to the command’s Parameters collection. When the code invokes the command’s ExecuteNonQuery method, the adapter replaces the question marks with these parameter values in the order in which they appear in the Parameters collection. In this example, the value of txtFirstName.Text replaces the first question mark and txtLastName.Text replaces the second.

Private Sub btnAdd_Click() Handles btnAdd.Click
    ' Make the connect string.
    Dim connect_string As String =
        "Provider=Microsoft.Jet.OLEDB.4.0;" &
        "Data Source=""" & txtDatabase.Text & """;" &
        "Persist Security Info=False"
 
    ' Open a database connection.
    Using conn_people As New OleDb.OleDbConnection(connect_string)
        conn_people.Open()
 
        ' Make a Command to insert data.
        Dim cmd As New OleDbCommand(
            "INSERT INTO PeopleNames (FirstName, LastName) VALUES (?, ?)",
            conn_people)
 
        ' Create parameters for the command.
        cmd.Parameters.Add(New OleDbParameter("FirstName", txtFirstName.Text))
        cmd.Parameters.Add(New OleDbParameter("LastName", txtLastName.Text))
 
        ' Execute the command.
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
 
        ' Show the current values.
        ShowValues(conn_people)
 
        ' Close the connection.
        conn_people.Close()
    End Using
End Sub

The command object’s Transaction property gives the transaction object with which it is associated. See the section “Transaction Objects” earlier in this chapter for more information about transactions.

The command object provides three methods for executing its CommandText. ExecuteNonQuery executes a command that is not a query and that doesn’t return any values. For example, CREATE TABLE, UPDATE, and INSERT statements do not return any values.

ExecuteScalar executes a command and returns the first column in the first row selected. This is useful for commands that return a single value such as SELECT COUNT * FROM Users.

ExecuteReader executes a SELECT statement and returns a data reader object (for example, OleDbDataReader). The program can use this object to navigate through the returned rows of data.

The command object’s two other most useful methods are CreateParameter and Prepare. As you may be able to guess, CreateParameter adds a new object to the command’s Parameters collection. The Prepare method compiles the command into a form that the database may be able to execute more quickly. It is often faster to execute a compiled command many times using different parameter values than it is to execute many new commands.

DataSet

DataSet is the flagship object when it comes to holding data in memory. It provides all the features you need to build, load, store, manipulate, and save data similar to that stored in a relational database. It can hold multiple tables related with complex parent/child relationships and uniqueness constraints. It provides methods for merging DataSet objects, searching for records that satisfy criteria, and saving data in different ways (such as into a relational database or an XML file). In many ways, it is like a complete database stored in memory rather than on a disk.

One of the most common ways to use a DataSet object is to load it from a relational database when the program starts, use various controls to display the data and let the user manipulate it interactively, and then save the changes back into the database when the program ends.

In variations on this basic theme, the program can load its data from an XML file or build a DataSet in memory without using a database. The program can use controls bound to the DataSet to let the user view and manipulate complex data with little extra programming.

The following table describes the DataSet object’s most useful properties.

The DataSet object’s XML properties affect the way the object reads and writes its data in XML form. For example, if the Namespace property is my_namespace and the Prefix property is pfx, the DataSet object’s XML data might look like the following:

<pfx:Scores xmlns:pfx="my_namespace">
  <Students xmlns="my_namespace">
    <FirstName>Art</FirstName>
    <LastName>Ant</LastName>
    <StudentId>1</StudentId>
  </Students>
  <Students xmlns="my_namespace">
    <FirstName>Bev</FirstName>
    <LastName>Bug</LastName>
    <StudentId>2</StudentId>
  </Students>
  ...
  <TestScores xmlns="my_namespace">
    <StudentId>1</StudentId>
    <TestNumber>1</TestNumber>
    <Score>78</Score>
  </TestScores>
  <TestScores xmlns="my_namespace">
    <StudentId>1</StudentId>
    <TestNumber>2</TestNumber>
    <Score>81</Score>
  </TestScores>
  ...
</pfx:Scores>

The following table describes the DataSet object’s most useful methods.

Several of these methods mirror methods provided by other finer-grained data objects. For example, HasChanges returns True if any of the DataSet object’s tables contains changes. The DataTable and DataRow objects also have HasChanges methods that return True if their more limited scope contains changes.

These mirrored methods include AcceptChanges, Clear, Clone, Copy, GetChanges, and RejectChanges. See the following sections that describe the DataTable and DataRow objects for more information.

DataTable

The DataTable class represents the data in one table within a DataSet. A DataTable contains DataRow objects representing its data, DataColumn objects that define the table’s columns, constraint objects that define constraints on the table’s data (for example, a uniqueness constraint requires that only one row may contain the same value in a particular column), and objects representing relationships between the table’s columns and the columns in other tables. This object also provides methods and events for manipulating rows.

The following table describes the DataTable object’s most useful properties.

The DataTable object’s XML properties affect the way the object reads and writes its data in XML form. For example, if the Namespace property is my_namespace and the Prefix property is tbl1, one of the DataTable object’s XML records might look like the following:

<tbl1:Students xmlns:tbl1="my_namespace">
  <FirstName xmlns="my_namespace">Art</FirstName>
  <LastName xmlns="my_namespace">Ant</LastName>
  <StudentId xmlns="my_namespace">1</StudentId>
</pfx:Students>

The following table describes the DataTable object’s most useful methods.

The DataTable object also provides several useful events, which are listed in the following table.

DataRow

A DataRow object represents the data in one record in a DataTable. This object is relatively simple. It basically just holds data for the DataTable, and the DataTable object does most of the interesting work.

The following table describes the DataRow object’s most useful properties.

If a row has an error message defined by its RowError property, the DataGrid control displays a red circle containing a white exclamation point to the left of the row as an error indicator. If you hover the mouse over the error indicator, a tooltip displays the RowError text. In Figure 19-13, the third row has RowError set to “Missing registration.”

FIGURE 19-13: The DataGrid control marks a DataRow that has a nonblank RowError.

Example program MemoryDataSetWithErrors, which is available for download on the book’s website, uses the following code to set errors on the second row’s third column (remember, indexes start at zero) and on the third row. The result is shown in Figure 19-13.

students_table.Rows(1).SetColumnError(2, "Bad name format")
students_table.Rows(2).RowError = "Missing registration"

The following table describes the DataRow object’s most useful methods.

DataColumn

The DataColumn object represents a column in a DataTable. It defines the column’s name and data type, and your code can use it to define relationships among different columns.

The following table describes the DataColumn object’s most useful properties.

Example program MemoryDataSetXmlMappedColumns, which is available for download on the book’s website, uses the following code to define XML column mappings for the Students table. It indicates that the table’s FirstName and LastName columns should be saved as attributes of the row elements, and that the StudentId column should be saved as XmlText.

students_table.Columns("FirstName").ColumnMapping = MappingType.Attribute
students_table.Columns("LastName").ColumnMapping = MappingType.Attribute
students_table.Columns("StudentId").ColumnMapping = MappingType.SimpleContent

The following text shows some of the resulting XML Students records:

<Students FirstName="Art" LastName="Ant">1</Students>
<Students FirstName="Bev" LastName="Bug">2</Students>
<Students FirstName="Cid" LastName="Cat">3</Students>
<Students FirstName="Deb" LastName="Dove">4</Students>

DataRelation

A DataRelation object represents a parent/child relationship between sets of columns in different tables. For example, suppose that a database contains a Students table containing FirstName, LastName, and StudentId fields. The TestScores table has the fields StudentId, TestNumber, and Score. The StudentId fields connect the two tables in a parent/child relationship. Each Students record may correspond to any number of TestScores records. In this example, Students is the parent table, and TestScores is the child table.

The following code defines this relationship. It uses the Students.StudentId field as the parent field and the TestScores.StudentId field as the child field.

' Make a relationship linking the two tables' StudentId fields.
scores_dataset.Relations.Add(
    "Student Test Scores",
    students_table.Columns("StudentId"),
    test_scores_table.Columns("StudentId"))

A DataRelation can also relate more than one column in the two tables. For example, two tables might be linked by the combination of the LastName and FirstName fields.

Most programs don’t need to manipulate a relation after it is created. The DataSet object’s Relations.Add method shown in the previous code creates a relation and thereafter the program can usually leave it alone. However, the DataRelation object does provide properties and methods in case you do need to modify one. The following table describes the DataRelation object’s most useful properties.

Normally, tables are stored separately in a DataSet object’s XML representation, but you can use the Nested property to make the XML include one table’s records inside another’s. For example, suppose that the Students and TestScores tables are linked by a common StudentId field. If you set this relation’s Nested property to True, the XML data would include the TestScores for a student within the Students record, as shown in the following:

<Students>
  <FirstName>Deb</FirstName>
  <LastName>Dove</LastName>
  <StudentId>4</StudentId>
  <TestScores>
    <StudentId>4</StudentId>
    <TestNumber>1</TestNumber>
    <Score>81</Score>
  </TestScores>
  <TestScores>
    <StudentId>4</StudentId>
    <TestNumber>2</TestNumber>
    <Score>68</Score>
  </TestScores>
  ... 
</Students>

Example program MemoryDataSetNestedXml, which is available for download on the book’s website, demonstrates this nested XML structure.

Note that in this representation the TestScores table’s StudentId value is redundant because the same value is contained in the Students element’s StudentId subelement.

Constraints

A constraint imposes a restriction on the data in a table’s columns. DataSets support two kinds of constraint objects:

• ForeignKeyConstraint restricts the values in one table based on the values in another table. For example, you could require that values in the Addresses table’s State field must exist in the States table’s StateName field.
• UniqueConstraint requires that the combination of one or more fields within the same table must be unique. For example, an Employee table might require that the combination of the FirstName and LastName values be unique.

The following sections describe each of these types of constraint objects in greater detail.

ForeignKeyConstraint

In addition to requiring that values in one table must exist in another table, a ForeignKeyConstraint can determine how changes to one table propagate to the other. For example, suppose that the Addresses table has a ForeignKeyConstraint requiring that its State field contain a value that is present in the States table’s StateName field. If you delete the States table’s record for Colorado, the constraint could automatically delete all of the Addresses records that used that state’s name.

The following table describes the ForeignKeyConstraint object’s most useful properties.

UniqueConstraint

If you want to require the values in a single column to be unique, you can set the column’s Unique property to True. This automatically creates a UniqueConstraint object and adds it to the DataTable. The following code shows how a program can make the Students table’s StudentId column require unique values:

students_table.Columns("StudentId").Unique = True

You can use the UniqueConstraint object’s constructors to require that a group of fields has a unique combined value. The following code demonstrates that technique:

' Make the combined FirstName/LastName unique.
Dim first_last_columns() As DataColumn = {
    students_table.Columns("FirstName"),
    students_table.Columns("LastName")
}
students_table.Constraints.Add(New UniqueConstraint(first_last_columns))

This code makes an array of DataColumn objects representing the Students table’s FirstName and LastName fields. It passes the array into the UniqueConstraint object’s constructor to require that the FirstName/LastName pair be unique in the table.

After executing this code, the program could add two records with the same FirstName and different LastNames, or with the same LastName and different FirstNames, but it could not create two records with the same FirstName and LastName values.

The following table describes the UniqueConstraint object’s properties.

Example program MemoryDataSet, which is available for download on the book’s website, defines several uniqueness constraints including a constraint requiring that StudentId be unique and a constraint requiring that the FirstName and LastName combination be unique.