Workshop

for RuBoard

This workshop will help reinforce the concepts covered in today's lesson.

Quiz

1:

How are system-assigned keys supported in ADO.NET?

A1:

Automatically generated keys are supported in two primary ways. First, the DataColumn class exposes AutoIncrement , AutoIncrementStep , and AutoIncrementSeed properties that when set enable ADO.NET to generate incremental numeric values in the DataColumn . These values are often used to relate tables through a foreign key constraint or data relation. Second, you can write your own code to assign values to keys as rows are inserted; for example, by using the NewGuid method of the Guid structure.

2:

In what ways can expressions be used for a DataColumn ?

A2:

Expressions, through the Expression property, can be used to create calculated columns based on a combination of literal values, numeric and string values, and even aggregate functions and scalar functions such as ISNULL and SUBSTRING . In addition, expressions can be used to refer to both parent and child rows to perform rollups of data.

3:

How does a primary key differ from a unique constraint on a DataTable ?

A3:

A primary key is actually just an instance of a unique constraint and is represented by a UniqueConstraint object in the ConstraintCollection of the table. The IsPrimaryKey property of the UniqueConstraint object will be set to True when this is the case. The PrimaryKey property of the DataTable can be used to set the primary key, which automatically creates the unique constraint.

4:

What is the relationship between a foreign key constraint and a data relation?

A4:

A foreign key constraint is used to ensure that data in a child table conforms to the primary key value of a parent table. To that end, foreign key constraints are about data integrity. Data relations, on the other hand, enable programmatic navigation of a DataSet through methods such as GetChildRows and the ChildRelations collection. However, these two concepts are related in that creating a data relation automatically creates a foreign key constraint, although the reverse isn't true.

Exercise

Q1:

To get some practice in working with the structure of a DataSet , write some code in a console application similar to that shown in Listing 4.5 to retrieve the Titles table and its associated Reviews table. Then create a data relation to relate the tables and use it to traverse Titles and the child Reviews . Also ensure that the primary keys of both tables are set and that no one can change them.

A1:

One possible solution is as follows :

 Dim dr, child As DataRow Dim con As New SqlConnection(connect) Dim da As New SqlDataAdapter("usp_GetTitlesReviews", con) da.SelectCommand.CommandType = CommandType.StoredProcedure Dim ds As New DataSet("TitlesReviews") ' Get the data da.MissingSchemaAction = MissingSchemaAction.AddWithKey da.Fill(ds) Dim reviews As DataTable = ds.Tables(0) Dim titles As DataTable = ds.Tables(1) ' Make the PKs read only reviews.Columns("ReviewId").ReadOnly = True Titles.Columns("ISBN").ReadOnly = True ' Setup the relationship ds.Relations.Add("FK_ISBN", titles.Columns("ISBN"), reviews.Columns("isbn")) ' Traverse the tables For Each dr In titles.Rows   Console.WriteLine(dr.Item("ISBN").ToString())   Dim children() As DataRow   children = dr.GetChildRows("FK_ISBN")   For Each child In children     Console.WriteLine("  " & child.Item("ReviewText").ToString())   Next Next 

In this code snippet, the usp_GetTitlesReviews stored procedure returns the Reviews as the first result set and the Titles as the second. The MissingSchemaAction property is set to AddWithKey to ensure that the primary key information is returned. For ease of use, the reviews and titles variables are set to refer to the appropriate tables before setting the primary key columns to read-only and creating the DataRelation . With the relationship in place, the code then traverses the titles table and finds the related reviews with the GetChildRows method.

for RuBoard


Sams Teach Yourself Ado. Net in 21 Days
Sams Teach Yourself ADO.NET in 21 Days
ISBN: 0672323869
EAN: 2147483647
Year: 2002
Pages: 158
Authors: Dan Fox

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net