DataRelation Class (System.Data)

A DataRelation is used to relate two DataTable objects to each other through DataColumn objects. For example, in a Customer/Orders relationship, the Customers table is the parent and the Orders table is the child of the relationship. This is similar to a primary key/foreign key relationship. For more information, see Navigating DataRelations (ADO.NET).

Relationships are created between matching columns in the parent and child tables. That is, the DataType value for both columns must be identical.

Relationships can also cascade various changes from the parent DataRow to its child rows. To control how values are changed in child rows, add a ForeignKeyConstraint to the ConstraintCollection of the DataTable object. The ConstraintCollection determines what action to take when a value in a parent table is deleted or updated.

When a DataRelation is created, it first verifies that the relationship can be established. After it is added to the DataRelationCollection, the relationship is maintained by disallowing any changes that would invalidate it. Between the period when a DataRelation is created and added to the DataRelationCollection, it is possible for additional changes to be made to the parent or child rows. An exception is generated if this causes a relationship that is no longer valid.

Note:
Data corruption can occur if a bi-directional relation is defined between two tables. A bi-directional relation consists of two DataRelation objects that use the same columns, with the parent-child roles switched. No exception is raised when the DataRelation objects are saved; however, data corruption can occur.

DataRelation objects are contained in a DataRelationCollection, which you can access through the Relations property of the DataSet, and the ChildRelations and ParentRelations properties of the DataTable.

Examples

The following example creates a new DataRelation and adds it to the DataRelationCollection of a DataSet.

Visual Basic

Private Sub CreateRelation()
    ' Get the DataColumn objects from two DataTable objects 
    ' in a DataSet. Code to get the DataSet not shown here.
    Dim parentColumn As DataColumn = _
        DataSet1.Tables("Customers").Columns("CustID")
    Dim childColumn As DataColumn = DataSet1.Tables( _
        "Orders").Columns("CustID")

    ' Create DataRelation.
    Dim relCustOrder As DataRelation
    relCustOrder = New DataRelation( _
        "CustomersOrders", parentColumn, childColumn)

    ' Add the relation to the DataSet.
    DataSet1.Relations.Add(relCustOrder)
End Sub

private void CreateRelation() 
{
    // Get the DataColumn objects from two DataTable objects 
    // in a DataSet. Code to get the DataSet not shown here.
    DataColumn parentColumn = 
        DataSet1.Tables["Customers"].Columns["CustID"];
    DataColumn childColumn = 
        DataSet1.Tables["Orders"].Columns["CustID"];
    // Create DataRelation.
    DataRelation relCustOrder;
    relCustOrder = new DataRelation("CustomersOrders", 
        parentColumn, childColumn);
    // Add the relation to the DataSet.
    DataSet1.Relations.Add(relCustOrder);
}

Inheritance Hierarchy

System..::.Object
System.Data..::.DataRelation

Thread Safety

This type is safe for multithreaded read operations. You must synchronize any write operations.

Platforms

Windows 7, Windows Vista, Windows XP SP2, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP Starter Edition, Windows Server 2008 R2, Windows Server 2008, Windows Server 2003, Windows Server 2000 SP4, Windows Millennium Edition, Windows 98, Windows CE, Windows Mobile for Smartphone, Windows Mobile for Pocket PC, Xbox 360, Zune

The .NET Framework and .NET Compact Framework do not support all versions of every platform. For a list of the supported versions, see .NET Framework System Requirements.

Version Information

.NET Framework

Supported in: 3.5, 3.0, 2.0, 1.1, 1.0

.NET Compact Framework

Supported in: 3.5, 2.0, 1.0

XNA Framework

Supported in: 3.0, 2.0, 1.0

Reference

DataRelation Members

System.Data Namespace

DataRelationCollection

UniqueConstraint

ParentRelations

Tags :

Community Content

JohnGrove

Getting joined rows using the DataRelation

The DataRelation object is a great way to join two DataTables embedded in a DataSet.

In this small example, you will see the DataRelation:
1.) Join two DataTables
2.) Pull up information from its adjoined row.

We will use the NWind.mdb for this demonstration. We will have Three controls on the form, a comboBox and a DataGridView and a Label.

The Access file can be found here:
http://www.microsoft.com/downloadS/details.aspx?FamilyID=c6661372-8dbe-422b-8676-c632d66c529c&displaylang=en

We will use two tables from Northwind: Customers, Orders.

The comboBox will be populated with CustomerName from the Customer table. We use the CustomerID for purposes of data binding. Upon each selection of the comboBox we will load the corresponding joined DataRows into a new DataTable and bind the DataGridView. Notice, we show just a few of the columns. We could have shown them all if we wanted.

Sometimes users want to know how to pull up information from the child back to the parent. We show this in an optional cell content click event. The user clicks in a cell and the address label becomes populated with the address of the parent table.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private DataSet ds;
private DataTable customers, orders;
private OleDbConnection cn;
private OleDbConnectionStringBuilder sb = new OleDbConnectionStringBuilder();

public Form1()
{
InitializeComponent();
ds = new DataSet();
sb.Provider = "Microsoft.Jet.OLEDB.4.0";
sb.DataSource = Path.Combine(Application.StartupPath, "NWind.mdb");
cn = new OleDbConnection(sb.ConnectionString);
FillTables();
}

private void FillTables()
{
customers = FillTable("SELECT * FROM Customers");
orders = FillTable("SELECT * FROM Orders");
AddRelations();
comboBox1.DataSource = customers;
comboBox1.DisplayMember = "ContactName";
comboBox1.ValueMember = "CustomerID";
comboBox1.SelectedIndexChanged += new EventHandler(comboBox1_SelectedIndexChanged);
comboBox1.SelectedIndex = 1;
}

private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
dataGridView1.DataSource = null;
String customerID = comboBox1.SelectedValue.ToString();
DataRow custRow= customers.Rows.Find(customerID);
compFile = new DataTable();
compFile.Columns.Add(new DataColumn("OrderID", typeof(Int32)));
compFile.Columns.Add(new DataColumn("OrderDate", typeof(DateTime)));
foreach (DataRow row in custRow.GetChildRows("Customers_Orders"))
{
compFile.Rows.Add(row.Field<Int32>("OrderID"),
row.Field<DateTime>("OrderDate"));
}
dataGridView1.DataSource = compFile;
}

/// <summary>
/// Helper method
/// </summary>
private DataTable FillTable(String sql)
{
DataTable table = new DataTable();
using (OleDbDataAdapter da = new OleDbDataAdapter(sql, cn))
{
da.Fill(table);
}
return table;
}

/// <summary>
/// Joins the two tables
/// </summary>
private void AddRelations()
{
customers.TableName = "Customers";
customers.PrimaryKey = new DataColumn[] { customers.Columns[0] };
orders.TableName = "Orders";
orders.PrimaryKey = new DataColumn[] { orders.Columns[0] };
ds.Tables.Add(customers);
ds.Tables.Add(orders);

//Add relation
ds.Relations.Add("Customers_Orders",
ds.Tables["Customers"].Columns["CustomerID"],
ds.Tables["Orders"].Columns["CustomerID"],
true);
}

private void dgComp_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
Int32 orderID = (Int32)dataGridView1["OrderID", e.RowIndex].Value;
DataRow row = ds.Tables["Orders"].Rows.Find(orderID);
DataRow parentRow = row.GetParentRow("Customers_Orders");
lblAddress.Text = parentRow.Field<String>("Address");
}
}
}

Hopefully this small example will help you to navigate across joined DataTables using the DataRelation.

Tags : datarelation