Command Objects
The command object classes (OleDbCommand, SqlCommand, OdbcCommand, and OracleCommand) define database commands. The command can be a SQL query, or some nonquery 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 (an SQL statement).
The command object’s Parameters collection contains parameter objects that define any values needed to execute the command text. For example, the following code creates an OleDbCommand object to execute the 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(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnAdd.Click ' Open the connection. Dim conn_people As New 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 data. ShowValues(conn_people) ' Close the connection. conn_people.Close() conn_people.Dispose() 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.
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 code builds and initializes a DataSet from scratch. It starts by creating a new DataSet object named Scores. It creates a DataTable named Students and adds it to the DataSet object’s Tables collection.
Next, the code uses the DataTable object’s Columns.Add method to add FirstName, LastName, and StudentId columns to the table. It then sets the StudentId column’s Unique property to True to make the DataSet prohibit duplicated StudentId values.
The program then makes an array of DataColumn objects containing references to the FirstName and LastName columns. It uses the array to create a UniqueConstraint and adds it to the table’s Constraints collection. This makes the DataSet ensure that each record’s FirstName/LastName pair is unique.
Similarly, the program creates the TestScores table, gives it StudentId, TestNumber, and Score columns, and adds a uniqueness constraint on the StudentId/TestNumber pair of columns.
Next, the code adds a relationship linking the Students table’s StudentId column and the TestScores table’s StudentId column.
The program then adds some Students records and some random TestScores records.
Finally, the program attaches the DataSet to a DataGrid control to display the result. The user can use the DataGrid to examine and modify the data just as if it had been loaded from a database.
Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load ' Make the DataSet. Dim scores_dataset As New DataSet("Scores") ' Make the Students table. Dim students_table As DataTable = _ scores_dataset.Tables.Add("Students") ' Add columns to the Students table. students_table.Columns.Add("FirstName", GetType(String)) students_table.Columns.Add("LastName", GetType(String)) students_table.Columns.Add("StudentId", GetType(Integer)) ' Make the StudentId field unique. students_table.Columns("StudentId").Unique = True ' 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)) ' Make the TestScores table. Dim test_scores_table As DataTable = _ scores_dataset.Tables.Add("TestScores") ' Add columns to the TestScores table. test_scores_table.Columns.Add("StudentId", GetType(Integer)) test_scores_table.Columns.Add("TestNumber", GetType(Integer)) test_scores_table.Columns.Add("Score", GetType(Integer)) ' Make the combined StudentId/TestNumber unique. Dim studentid_testnumber_score_columns() As DataColumn = { _ test_scores_table.Columns("StudentId"), _ test_scores_table.Columns("TestNumber") _ } test_scores_table.Constraints.Add( _ New UniqueConstraint(studentid_testnumber_score_columns)) ' 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")) ' Make some student data. students_table.Rows.Add(New Object() {"Art", "Ant", 1}) students_table.Rows.Add(New Object() {"Bev", "Bug", 2}) students_table.Rows.Add(New Object() {"Cid", "Cat", 3}) students_table.Rows.Add(New Object() {"Deb", "Dove", 4}) ' Make some random test scores. Dim score As New Random For id As Integer = 1 To 4 For test_num As Integer = 1 To 10 test_scores_table.Rows.Add( _ New Object() {id, test_num, score.Next(65, 100)}) Next test_num Next id ' Attach the DataSet to the DataGrid. grdScores.DataSource = scores_dataset End Sub The following table describes the DataSet object’s most useful properties.
| Property | Purpose |
|---|---|
| CaseSensitive | Determines whether string comparisons inside DataTable objects are case-sensitive. |
| DataSetName | The DataSet object’s name. Often, you don’t need to use this for much. If you need to use the DataSet object’s XML representation, however, this determines the name of the root element. |
| DefaultViewManager | Returns a DataViewManager object that you can use to determine the default settings (sort order, filter) of DataView objects you create later. |
| EnforceConstraints | Determines whether the DataSet should enforce constraints while updating data. For example, if you want to add records to a child table before the master records have been created, you can set EnforceConstraints to False while you add the data. You should be able to avoid this sort of problem by adding the records in the correct order. |
| HasErrors | Returns True if any of the DataSet object’s DataTable objects contains errors. |
| Namespace | The DataSet's namespace. If this is nonblank, the DataSet object’s XML data’s root node includes an xmlns attribute as in <Scores xmlns=”my_namespace”>. |
| Prefix | Determines the XML prefix that the DataSet uses as an alias for its namespace. |
| Relations | A collection of DataRelation objects that represent parent/child relations among the columns in different tables. |
| Tables | A collection of DataTable objects representing the tables stored in the DataSet. |
Open table as spreadsheet 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.
| Method | Purpose |
|---|---|
| AcceptChanges | Accepts all changes to the data that were made since the data was loaded, or since the last call to AcceptChanges. When you modify a row in the DataSet, the row is flagged as modified. If you delete a row, the row is marked as deleted but not actually removed. When you call AcceptChanges, new and modified rows are marked as Unchanged instead of Added or Modified, and deleted rows are permanently removed. |
| Clear | Removes all rows from the DataSet object’s tables. |
| Clone | Makes a copy of the DataSet including all tables, relations, and constraints, but not including the data. |
| Copy | Makes a copy of the DataSet including all tables, relations, constraints, and the data. |
| GetChanges | Makes a copy of the DataSet containing only the rows that have been modified. This method’s optional parameter indicates the type of changes that the new DataSet should contain (added, modified, deleted, or unchanged). |
| GetXml | Returns a string containing the DataSet object’s XML representation. |
| GetXmlSchema | Returns the DataSet object’s XML schema definition (XSD). |
| HasChanges | Returns True if any of the DataSet object’s tables contains new, modified, or deleted rows. |
| Merge | Merges a DataSet, DataTable, or array of DataRow objects into this DataSet. |
| ReadXml | Reads XML data from a stream or file into the DataSet. |
| ReadXmlSchema | Reads an XML schema from a stream or file into the DataSet. |
| RejectChanges | Undoes any changes made since the DataSet was loaded or since the last call to AcceptChanges. |
| WriteXml | Writes the DataSet object’s XML data into a file or stream. It can optionally include the DataSet object’s schema. |
| WriteXmlSchema | Writes the DataSet object’s XSD schema into an XML file or stream. |
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 contain 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.
| Property | Purpose |
|---|---|
| CaseSensitive | Determines whether string comparisons inside the DataTable are case-sensitive. |
| ChildRelations | A collection of DataRelation objects that define parent/child relationships where this table is the parent. For example, suppose the Orders table defines order records and contains an OrderId field. Suppose that the OrderItems table lists the items for an order and it also has an OrderId field. One Orders record can correspond to many OrderItems records, all linked by the same OrderId value. In this example, Orders is the parent table and OrderItems is the child table. |
| Columns | A collection of DataColumn objects that define the table’s columns (column name, data type, default value, maximum length, and so forth). |
| Constraints | A collection of Constraint objects represent restrictions on the table’s data. A ForeignKeyConstraint requires that the values in some of the table’s columns must be present in another table (for example, the Addresses record’s State value must appear in the States table’s StateName column). A UniqueConstraint requires that the values in a set of columns must be unique within the table (for example, only one Student record can have a given FirstName and LastName pair). |
| DataSet | The DataSet object that contains this DataTable. |
| DefaultView | Returns a DataView object that you can use to view, sort, and filter the table’s rows. |
| HasErrors | Returns True if any of the DataTable object’s rows contains an error. |
| MinimumCapacity | The initial capacity of the table. For example, if you know you are about to load 1000 records into the table, you can set this to 1000 to let the table allocate space all at once instead of incrementally as the records are added. That will be more efficient. |
| Namespace | The DataTable object’s namespace. If this is nonblank, the DataTable object’s XML records’ root nodes include an xmlns attribute as in <Students xmlns=”my_namespace”>. |
| ParentRelations | A collection of DataRelation objects that define parent/child relationships where this table is the child. See the description of the ChildRelations property for more details. |
| Prefix | Determines the XML prefix that the DataTable uses as an alias for its namespace. |
| PrimaryKey | Gets or sets an array of DataColumn objects that define the table’s primary key. The primary key is always unique and provides the fastest access to the records. |
| Rows | A collection of DataRow objects containing the table’s data. |
| TableName | The table’s name. |
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:
<pfx:Students xmlns:pfx="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.
| Method | Purpose |
|---|---|
| AcceptChanges | Accepts all changes to the table’s rows that were made since the data was loaded or since the last call to AcceptChanges. |
| Clear | Removes all rows from the table. |
| Clone | Makes a copy of the DataTable, including all relations and constraints, but not including the data. |
| Compute | Computes the value of an expression using the rows that satisfy a filter condition. For example, the statement tblTestScores.Compute (“SUM(Score)“, “StudentId = 1”) calculates the total of the tblTestScores DataTable object’s Score column where the StudentId is 1. |
| Copy | Makes a copy of the DataTable including all relations, constraints, and data. |
| GetChanges | Makes a copy of the DataTable containing only the rows that have been modified. This method’s optional parameter indicates the type of changes that the new DataSet should contain (added, modified, deleted, or unchanged). |
| GetErrors | Gets an array of DataRow objects that contain errors. |
| ImportRow | Copies the data in a DataRow object into the DataTable. |
| LoadDataRow | This method takes an array of values as a parameter. It searches the table for a row with values that match the array’s primary key values. If it doesn’t find such a row, it uses the values to create the row. The method returns the DataRow object it found or creates. |
| NewRow | Creates a new DataRow object that matches the table’s schema. To add the new row to the table, you can create a new DataRow, fill in its fields, and use the table’s Rows.Add method. |
| RejectChanges | Undoes any changes made since the DataTable was loaded or since the last call to AcceptChanges. |
| Select | Returns an array of DataRow objects selected from the table. Optional parameters indicate a filter expression that the selected rows must match, sort columns and sort order, and the row states to select (new, modified, deleted, and so forth). |
The DataTable object also provides several useful events, which are listed in the following table.
| Event | Purpose |
|---|---|
| ColumnChanged | Occurs after a value has been changed in a row. |
| ColumnChanging | Occurs when a value is being changed in a row. |
| RowChanged | Occurs after a row has changed. A user might change several of a row’s columns and ColumnChanged will fire for each one. RowChanged fires when the user moves to a new row. |
| RowChanging | Occurs when a row is being changed. |
| RowDeleted | Occurs after a row has been deleted. |
| RowDeleting | Occurs when a row is being deleted. |
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.
| Property | Purpose |
|---|---|
| HasErrors | Returns True if the row’s data has errors. |
| Item | Gets or sets one of the row’s item values. Overloaded versions of this property use different parameters to identify the column. This parameter can be the column’s zero-based index, its name, or a DataColumn object. An optional second parameter can indicate the version of the row so, for example, you can read the original value in a row that has been modified. |
| ItemArray | Gets or sets all of the row’s values by using an array of generic Objects. |
| RowError | Gets or sets the row’s error message text. |
| RowState | Returns the row’s current state: Added, Deleted, Modified, or Unchanged. |
| Table | Returns a reference to the DataTable containing the row. |
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 11-27, the third row has RowError set to Missing registration.
Figure 11-27: The DataGrid control marks a DataRow that has a nonblank RowError.
The following table describes the DataRow object’s most useful methods.
| Method | Purpose |
|---|---|
| AcceptChanges | Accepts all changes to the row that were made since the data was loaded or since the last call to AcceptChanges. |
| BeginEdit | Puts the row in data-editing mode. This suspends events for the row, so your code or the user can change several fields without triggering validation events. BeginEdit is implicitly called when the user modifies a bound control’s value and EndEdit is implicitly called when you invoke AcceptChanges. Although the row is in edit mode, it stores the original and modified values, so you can retrieve either version, accept the changes with EndEdit, or cancel the changes with CancelEdit. |
| CancelEdit | Cancels the current edit on the row and restores its original values. |
| ClearErrors | Clears the row’s column and row errors. |
| Delete | Deletes the row from its table. |
| GetChildRows | Returns an array of DataRow objects representing this row’s child rows as specified by a parent/child data relation. |
| GetColumnError | Returns the error text assigned to a column. |
| GetParentRow | Returns a DataRow object representing this row’s parent record as specified by a parent/child data relation. |
| GetParentRows | Returns an array of DataRow objects representing this row’s parent records as specified by a data relation. |
| HasVersion | Returns True if the row has a particular version (Current, Default, Original, or Proposed). For example, while a row is being edited, it has Current and Proposed versions. |
| IsNull | Indicates whether a particular column contains a NULL value. |
| RejectChanges | Removes any changes made to the row since the data was loaded or since the last call to AcceptChanges. |
| SetColumnError | Sets error text for one of the row’s columns. If a column has an error message, then a DataGrid control displays a red circle containing a white exclamation point to the left of the column’s value as an error indicator. In Figure 11-27, the second row’s second column has a column error set. If you hover the mouse over the error indicator, a tooltip displays the error’s text. |
| SetParentRow | Sets the row’s parent row according to a data relation. |
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.
| Property | Purpose |
|---|---|
| AllowDBNull | Determines whether the column allows NULL values. |
| AutoIncrement | Determines whether new rows automatically generate auto-incremented values for the column. |
| AutoIncrementSeed | Determines the starting value for an auto-increment column. |
| AutoIncrementStep | Determines the amount by which an auto-incrementing column’s value is incremented for new rows. |
| Caption | Gets or sets a caption for the column. Note that some controls may not use this value. For example, the DataGrid control displays the column’s ColumnName, not its Caption. |
| ColumnMapping | Determines how the column is saved in the table’s XML data. This property can have one of the values Attribute (save the column as an attribute of the row’s element), Element (save the column as a subelement), Hidden (don’t save the column), and SimpleContent (save the column as XmlText inside the row’s element). If a column is hidden, the DataGrid control doesn’t display its value. See the text following this table for an example. |
| ColumnName | Determines the name of the column in the DataTable. Note that data adapters use the column name to map database columns to DataSet columns, so, if you change this property without updating the table mapping, the column will probably not be filled. |
| DataType | Determines the column’s data type. Visual Basic raises an error if you change this property after the DataTable begins loading data. Visual Basic supports the data types Boolean, Byte, Char, DateTime, Decimal, Double, Int16, Int32, Int64, SByte, Single, String, TimeSpan, UInt16, UInt32, and UInt64. |
| DefaultValue | Determines the default value assigned to the column in new rows. |
| Expression | Sets an expression for the column. You can use this to create calculated columns. For example, the expression Quantity * Price makes the column display the value of the Quantity column times the value of the Price column. |
| MaxLength | Determines the maximum length of a text column. |
| Namespace | The column’s namespace. If this is nonblank, the rows’ XML root nodes include an xmlns attribute as in <StudentId xmlns= ”my_namespace”>12</StudentId>. |
| Ordinal | Returns the column’s index in the DataTable object’s Columns collection. |
| Prefix | Determines the XML prefix that the DataColumn uses as an alias for its namespace. For example, if Namespace is my_namespace and Prefix is pfx, then a row’s StudentId field might be encoded in XML as <pfx:StudentId xmlns:pfx=”my_namespace”>12</ pfx:StudentId>. |
| ReadOnly | Determines whether the column allows changes after a record is created. |
| Table | Returns a reference to the DataTable containing the column. |
| Unique | Determines whether different rows in the table can have the same value for this column. |
The following example defines the Students table’s XML column mappings. 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. Note that you cannot use the SimpleContent ColumnMapping if any other column has a ColumnMapping of Element or SimpleContent.
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> The following code makes the FirstName and LastName columns elements of the Students rows, and it makes the StudentId an attribute:
students_table.Columns("FirstName").ColumnMapping = MappingType.Element students_table.Columns("LastName").ColumnMapping = MappingType.Element students_table.Columns("StudentId").ColumnMapping = MappingType.Attribute The following shows the resulting records:
<Students Student> <FirstName>Art</FirstName> <LastName>Ant</LastName> </Students> <Students Student> <FirstName>Bev</FirstName> <LastName>Bug</LastName> </Students> <Students Student> <FirstName>Cid</FirstName> <LastName>Cat</LastName> </Students> <Students Student> <FirstName>Deb</FirstName> <LastName>Dove</LastName> </Students>
DataRelation
ADataRelation 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.
| Property | Purpose |
|---|---|
| ChildColumns | Returns an array of DataColumn objects representing the child columns. |
| ChildKeyConstraint | Returns the ForeignKeyConstraint object for this relation. You can use this object to determine the relation’s behavior when the program updates, deletes, or modifies the values used in the relationship. For example, if the StudentId field links the Students and TestScores tables and you delete a Students record, you can use this object to make the database automatically delete any corresponding TestScores records. |
| ChildTable | Returns a DataTable object representing the relation’s child table. |
| DataSet | Returns a reference to the DataSet containing the relation. |
| Nested | Determines whether the child data should be nested within parent rows in the DataSet's XML representation. See the text following this table for more detail. |
| ParentColumns | Returns an array of DataColumn objects representing the parent columns. |
| ParentKeyConstraint | Returns the UniqueConstraint object for this relation. This object requires that the values in the parent’s columns are unique within the parent table. |
| ParentTable | Returns a DataTable object representing the relation’s parent table. |
| RelationName | Determines the relation’s name. |
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> 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. If you set the TestScores.StudentId column’s ColumnMapping value to Hidden, you can remove the redundant values and get the following result:
<Students> <FirstName>Deb</FirstName> <LastName>Dove</LastName> <StudentId>4</StudentId> <TestScores> <TestNumber>1</TestNumber> <Score>81</Score> </TestScores> <TestScores> <TestNumber>2</TestNumber> <Score>68</Score> </TestScores> ... </Students>
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. That would prevent the program from creating an Addresses record where State is XZ.
-
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. That would prevent the program from creating two Employees records with the same FirstName and LastName.
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.
| Property | Purpose |
|---|---|
| AcceptRejectRule | Determines the action taken when the AcceptChanges method executes. This value can be None (do nothing) or Cascade (update the child fields’ values to match the new parent field values). |
| Columns | Returns an array containing references to the constraint’s child columns. |
| ConstraintName | Determines the constraint’s name. |
| DeleteRule | Determines the action taken when a row is deleted. This value can be Cascade (delete the child rows), None (do nothing), SetDefault (change child field values to their default values), or SetNull (change child field values to NULL). |
| RelatedColumns | Returns an array containing references to the constraint’s parent columns. |
| RelatedTable | Returns a reference to the constraint’s parent table. |
| Table | Returns a reference to the constraint’s child table. |
| UpdateRule | Determines the action taken when a row is updated. This value can be Cascade (update the child rows’ values to match), None (do nothing), SetDefault (change child field values to their default values), or SetNull (change child field values to NULL). |
The following code makes a foreign key constraint relating the Students.StudentId parent field to the TestScores.StudentId child field:
scores_dataset.Relations.Add( _ "Student Test Scores", _ students_table.Columns("StudentId"), _ test_scores_table.Columns("StudentId"))
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 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.
' 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)) 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.
| Property | Purpose |
|---|---|
| Columns | Returns an array of DataColumn objects representing the columns that must be unique. ConstraintName determines the name of the constraint. |
| IsPrimaryKey | Returns True if the columns form the table’s primary key. |
| Table | Returns a reference to the DataTable that contains the constraint. |
EAN: N/A
Pages: 417