Xem mẫu

86 Microsoft ADO .NET 4 Step by Step 6. Test setting linked child fields to NULL when a parent record is deleted. Set the Delete Rule field to SetNull. Select the first row in the Flights field, the one with ID 759. Click the (Flights) Delete button and confirm the delete action. 7. Review the Flights and Legs fields. Although the first row in the Flights field has been removed, its child records in the Legs field remain. However, their FlightID column val-ues have been cleared and are set to NULL. Those records no longer have a parent row. Defining the Update and Delete Rules in a DataRelation: Visual Basic Note This exercise uses the “Chapter 5 VB” sample project and continues the previous exercise in this chapter. 1. Open the source code view for the FlightInfo form. Locate the RefreshConstraints method. The application lets the user alter the rules for adjusting the child table when changes are made to the parenttable.TheRefreshConstraintsroutineupdatesthe relevant constraint with the user’s rule choice. 2. Just after the “Alter its cascade rules” comment, add the following statements: linkConstraint.DeleteRule = CType(DeleteRule.SelectedItem, Data.Rule) linkConstraint.UpdateRule = CType(UpdateRule.SelectedItem, Data.Rule) 3. Runtheprogram.TheUpdateRuleandDeleteRulefieldsarebothsettoNoneby default. This prevents parent records (Flights) from being deleted or having their ID col-umn values changed if related child rows (Legs) exist. 4. Test cascade updates. Set the Update Rule field to Cascade. Select the first row in the Flights field, the one with ID 834. Click the (Flights) Edit button. Use the Edit Flight form that appears to alter the Flight ID value from 834 to another value, such as 759. Click OK on that editor form. 5. Review the Flights and Legs fields. Not only did the first row in the Flights field have its ID value changed to 759 but the FlightID values for the related rows in the Legs field changed to 759 as well. 6. Test setting linked child fields to NULL when a parent record is deleted. Set the Delete Rule field to SetNull. Select the first row in the Flights field, the one with ID 759. Click the (Flights) Delete button and confirm the delete action. 7. Review the Flights and Legs fields. Although the first row in the Flights field has been removed, its child records in the Legs field remain. However, their FlightID column val-ues have been cleared and are set to NULL. Those records no longer have a parent row. Chapter 5 Bringing Related Data Together 87 Summary This chapter demonstrated how individual DataTable instances can be joined together in an ADO.NETDataSet.Eachdatatableobjectincludesmanyfeaturesthatletyouqueryand manipulate the data in its rows. By bringing distinct tables together in a data set, you gain additional features that affect multiple tables simultaneously and, if desired, automatically. The DataRelation class defines the link between columns in two different tables. This class defines only the relationship; it doesn’t enforce the rules of the relationship. Constraint ob-jects, specifically the UniqueConstraint and ForeignKeyConstraint derived classes, impose the data requirements needed to ensure data integrity and data expectations between linked tables. 88 Microsoft ADO .NET 4 Step by Step Chapter5QuickReference To Add a table to a DataSet Link two DataTable objects in a relation-ship Enforce cascade deletes in a parent-child relationship Locate the parent row for a child row Locate the child rows for a parent row Do This Define a DataSet instance. Define a DataTable instance, adding columns and rows as needed. Call the DataSet object’s Tables.Add method, passing it the instance of the DataTable. Define a DataSet instance. Define two DataTable instances, adding columns and rows as needed. Determine which columns from each table will form the relationship link. Add both tables to the DataSet using the Tables.Add method. Create a DataRelation instance, passing instances of the columns to be linked to its constructor. Call the DataSet object’s Relations.Add method, passing it the in-stance of the DataRelation. Locate the DataRelation instance that defines the link relationship. Set the DataRelation object’s DeleteRule to System.Data.Rule. Cascade. Ensure that the tables are linked with a DataRelation. Call the child DataRow object’s GetParentRow method, passing it the name of the DataRelation that defines the link relationship. Ensure that the tables are linked with a DataRelation. Call the parent DataRow object’s GetChildRow method, passing it the name of the DataRelation that defines the link relationship. Chapter 6g Data into Information After completing this chapter, you will be able to: ■■ Return a value that aggregates data from a table column ■■ Add a column that aggregates data from a table, or from its parent or child table ■■ Build an index-based view of a table ■■ Generate a new table based on a projected view of the original table After you have joined DataTable instances together in a DataSet, ADO.NET enables a few more features that let you use those table relationships to analyze and select data. These features build upon some of the single-table functions covered in earlier chapters. This chapter introduces the data-aggregation features included in the ADO.NET Framework, expressions that summarize data across multiple table rows. Although not as powerful as the aggregation features found in relational database systems, the DataTable variations still provide quick access to multirow data summaries. The chapter ends with an introduction to the DataView class, which lets you establish row selection, filtering, and sorting standards for a DataTable. Note The exercises in this chapter all use the same sample project, a tool that demonstrates aggregate and data view features. Although you will be able to run the application after each exercise, the expected results for the full application might not appear until you complete all exercises in the chapter. AggregatingData An aggregation function returns a single calculated value from a set of related values. Averages are one type of data aggregation; they calculate a single averaged value from an input of multiple source values. ADO.NET includes seven aggregation functions for use in expression columns and other DataTable features. ■■ Sum Calculates the total of a set of column values. The column being summed must be numeric, either integral or decimal. ■■ Avg Returns the average for a set of numbers in a column. This function also requires a numeric column. 89 ■■ Min Indicates the minimum value found within a set of column values. Numbers, strings, dates, and other types of data that can be placed in order are all valid for the target column. ■■ Max Like Min, but returns the largest value from the available column values. As with the Min function, most column types will work. ■■ Count Simply counts the number of rows included in the aggregation. You can pass any type of column to this function. As long as a row includes a non-NULL value in that column, it will be counted as 1. ■■ StDev Determines the statistical standard deviation for a set of values, a common measure of variability within such a set. The indicated column must be numeric. ■■ Var Calculates the statistical variance for a set of numbers, another measurement re-lated to the standard deviation. Only numeric columns are supported. These seven data aggregation features appear as functions within ADO.NET expressions. Expressions were introduced in the “Using Expression Columns” section of Chapter 4, “Accessing the Right Data Values.” String expressions form the basis of custom expression col-umns and are also used in selecting subsets of DataTable rows. To aggregate data, use one of the following function formats as the expression string: ■■ Sum(column-name) ■■ Avg(column-name) ■■ Min(column-name) ■■ Max(column-name) ■■ Count(column-name) ■■ StDev(column-name) ■■ Var(column-name) In ADO.NET, aggregates always summarize a single DataTable column. Each aggregate func-tion considers only non-NULL column values. Rows that contain NULL values in the specified column are excluded from the aggregation. For example, if you take the average of a table column with 10 rows, but 3 of those rows contain NULL values in the column being averaged, the function will average only the 7 non-NULL values. This is especially useful with the Count function;itcountsonlythenumberofrowsthathaveanon-NULLvalueforthepassedcolumn name. If all the column values are NULL, or if there are no rows to apply to the aggregation function, the result is NULL (System.DBNull). 90 ... - tailieumienphi.vn