Creating and Using a DataRelation Object

Chia sẻ: Tuan Nghia | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

121
lượt xem 16
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Creating and Using a DataRelation Object In this section, you'll learn how to create a DataRelation object to define a relationship between two DataTable objects that hold some rows from the Customers and Orders tables

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Creating and Using a DataRelation Object

Creating and Using a DataRelation Object In this section, you'll learn how to create a DataRelation object to define a relationship between two DataTable objects that hold some rows from the Customers and Orders tables. As you know, the CustomerID column of the child Orders table is a foreign key that links to the CustomerID column of the parent Customers table. Once you've created a DataRelation, you can use the GetChildRows() method of a DataRow object in the parent DataTable to obtain the corresponding DataRow objects from the child DataTable. By "corresponding," I mean the rows that have matching values in the foreign key DataColumn objects. You can also use the GetParentRow() method of a DataRow in the child DataTable to obtain the corresponding DataRow in the parent DataTable. Before creating and adding a DataRelation to a DataSet, you first need a DataSet. The following example creates and populates a DataSet with two DataTable objects named customersDT and ordersDT; notice that the top two rows from the Customers table along with the corresponding rows from the Orders table are retrieved: SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "SELECT TOP 2 CustomerID, CompanyName " + "FROM Customers " + "ORDER BY CustomerID;" + "SELECT OrderID, CustomerID " + "FROM Orders " + "WHERE CustomerID IN (" + " SELECT TOP 2 CustomerID " + " FROM Customers " + " ORDER BY CustomerID" + ")"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); mySqlDataAdapter.Fill(myDataSet); mySqlConnection.Close(); myDataSet.Tables["Table"].TableName = "Customers"; myDataSet.Tables["Table1"].TableName = "Orders"; DataTable customersDT = myDataSet.Tables["Customers"]; DataTable ordersDT = myDataSet.Tables["Orders"];
You'll see how to create a DataRelation that defines a relationship between the customersDT and ordersDT DataTable objects next. Note You'll find all the code examples shown in this section in the CreateDataRelation.cs program. Creating the DataRelation The DataRelation constructor is overloaded as follows: DataRelation(string dataRelationName, DataColumn parentDataColumn, DataColumn childDataColumn) DataRelation(string dataRelationName, DataColumn[] parentDataColumns, DataColumn[] childDataColumns) DataRelation(string dataRelationName, DataColumn parentDataColumn, DataColumn childDataColumn, bool createConstraints) DataRelation(string dataRelationName, DataColumn[] parentDataColumns, DataColumn[] childDataColumns, bool createConstraints) DataRelation(string dataRelationName, string parentDataTableName, string childDataTableName, string[] parentDataColumnNames, string[] childDataColumnNames, bool nested) where • dataRelationName is the name you want to assign to the RelationName property of your DataRelation. • parentDataColumn and parentDataColumnsare the DataColumn objects in the parent DataTable. • childDataColumn and childDataColumns are the DataColumn objects in the child DataTable. • createConstraints indicates whether you want a UniqueConstraint added to the parent DataTable and a ForeignKeyConstraint added to the child DataTable automatically (the default is true). • parentDataTableName and childDataTableName are the names of the parent and child DataTable objects. • parentDataColumnNames and childDataColumnNames contain the names of the DataColumn objects in the parent and child DataTable objects. • nested indicates whether the relationships are nested. The following example creates a DataRelation object named customersOrdersDataRel:
DataRelation customersOrdersDataRel = new DataRelation( "CustomersOrders", customersDT.Columns["CustomerID"], ordersDT.Columns["CustomerID"] ); The name assigned to the RelationName property of customersOrdersDataRel is CustomersOrders, the parent DataColumn is customersDT.Columns["CustomerID"], and the child DataColumn is ordersDT.Columns["CustomerID"]. Next, customersOrdersDataRel must be added to myDataSet. You access the DataRelation objects in a DataSet object through its Relationships property. The Relationships property returns an object of the DataRelationCollection class, which is a collection of DataRelation objects. To add a DataRelation object to the DataRelationCollection object of a DataSet, you call the Add() method through the Relationships property of your DataSet. The following example uses the Add() method to add customersOrdersDataRel to myDataSet: myDataSet.Relations.Add( customersOrdersDataRel ); The Add() method is overloaded, and you can also use the following version of the Add() method to create and add a DataRelation object to myDataSet: myDataSet.Relations.Add( "CustomersOrders", customersDT.Columns["CustomerID"], ordersDT.Columns["CustomerID"] ); This example does the same thing as the two earlier examples. The first parameter to the Add() method is a string containing the name you want to assign to the RelationName property of the DataRelation. The second and third parameters of the relationship are the DataColumn objects from the parent and child DataTable objects. Examining the Constraints Created by the DataRelation By default, when you create a DataRelation, a UniqueConstraint and ForeignKeyConstraint are automatically added to your parent and child DataTable
objects. You can get the UniqueConstraint from a DataRelation using its ParentKeyConstraint property. For example: UniqueConstraint myUC = customersOrdersDataRel.ParentKeyConstraint; You can view the properties of the myUC UniqueConstraint object using the following code: Console.WriteLine("Columns:"); foreach (DataColumn myDataColumn in myUC.Columns) { Console.WriteLine("" + myDataColumn); } Console.WriteLine("myUC.ConstraintName = " + myUC.ConstraintName); Console.WriteLine("myUC.IsPrimaryKey = " + myUC.IsPrimaryKey); Console.WriteLine("myUC.Table = " + myUC.Table); The output from this code is as follows: Columns: CustomerID myUC.ConstraintName = Constraint1 myUC.IsPrimaryKey = False myUC.Table = Customers You can get the ForeignKeyConstraint from a DataRelation using its ChildKeyConstraint property. For example: ForeignKeyConstraint myFKC = customersOrdersDataRel.ChildKeyConstraint; You can view the properties of myFKC using the following code: Console.WriteLine("myFKC.AcceptRejectRule = " + myFKC.AcceptRejectRule); Console.WriteLine("Columns:"); foreach (DataColumn myDataColumn in myFKC.Columns) { Console.WriteLine(""+ myDataColumn); } Console.WriteLine("myFKC.ConstraintName = " + myFKC.ConstraintName); Console.WriteLine("myFKC.DeleteRule = " + myFKC.DeleteRule); Console.WriteLine("RelatedColumns:");
foreach (DataColumn relatedDataColumn in myFKC.RelatedColumns) { Console.WriteLine("" + relatedDataColumn); } Console.WriteLine("myFKC.RelatedTable = " + myFKC.RelatedTable); Console.WriteLine("myFKC.Table = " + myFKC.Table); Console.WriteLine("myFKC.UpdateRule = " + myFKC.UpdateRule); The output from this code is as follows: myFKC.AcceptRejectRule = None Columns: CustomerID myFKC.ConstraintName = CustomersOrders myFKC.DeleteRule = Cascade RelatedColumns: CustomerID myFKC.RelatedTable = Customers myFKC.Table = Orders myFKC.UpdateRule = Cascade The DeleteRule and UpdateRule properties are set to Cascade by default. Because DeleteRule is set to Cascade, when you delete a DataRow in the parent DataTable, then any corresponding DataRow objects in the child DataTable are also deleted. Because UpdateRule is set to Cascade, when you change the DataColumn in the parent DataTable on which the ForeignKeyConstraint was created, then the same change is also made in any corresponding DataRow objects in the child DataTable. You'll learn more about this later in the section "Issues When Updating the Primary Key of a Parent Row." Navigating DataRow Objects in the Parent and Child DataTable Objects To navigate the DataRow objects in related DataTable objects, you use the GetChildRows() and GetParentRows() methods of a DataRow. Using the GetChildRows() Method You use the GetChildRows() method to get the related child DataRow objects from the parent DataRow. For example, the following code displays the parent DataRow objects from the customersDT DataTable and their related child DataRow objects from the ordersDT DataTable: foreach (DataRow customerDR in customersDT.Rows)
{ Console.WriteLine("\nCustomerID = " + customerDR["CustomerID"]); Console.WriteLine("CompanyName = " + customerDR["CompanyName"]); DataRow[] ordersDRs = customerDR.GetChildRows("CustomersOrders"); Console.WriteLine("This customer placed the following orders:"); foreach (DataRow orderDR in ordersDRs) { Console.WriteLine("OrderID = " + orderDR["OrderID"]); } } The output from this code is as follows: CustomerID = ALFKI CompanyName = Alfreds Futterkiste This customer placed the following orders: OrderID = 10643 OrderID = 10692 OrderID = 10702 OrderID = 10835 OrderID = 10952 OrderID = 11011 CustomerID = ANATR CompanyName = Ana Trujillo Emparedados y helados This customer placed the following orders: OrderID = 10308 OrderID = 10625 OrderID = 10759 OrderID = 10926 Using the GetParentRow() Method You use the GetParentRow() method to get the parent DataRow from the child DataRow. For example, the following code displays the first child DataRow from ordersDT and its related parent DataRow from customersDT: DataRow parentCustomerDR = ordersDT.Rows[0].GetParentRow("CustomersOrders"); Console.WriteLine("\nOrder with OrderID of " + ordersDT.Rows[0]["OrderID"] + " was placed by the following customer:"); Console.WriteLine("CustomerID = " + parentCustomerDR["CustomerID"]);
The output from this code is as follows: Order with OrderID of 10643 was placed by the following customer: CustomerID = ALFKI