Question for DataTables Replation in a DataSet?

Can anyone explain what the relations in DataSet do and what it for? I have run the code below with and without the relation snippet, and I see not different. Can you tell me what the relation in a DataSet doing?

protectedvoid Page_Load(object sender,EventArgs e)

{

//1. Db Connection

string myConnString ="Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI";

SqlConnection myConn =newSqlConnection(myConnString);

myConn.Open();

//2. DataSet

DataSet ds =newDataSet();

//3. Adapter command and fillin dataSet

//--1). Customer SQL Adapter--

string sSQL ="Select CustomerID, Phone From Customers Where CustomerID ='VINET'";

SqlDataAdapter myCommand;

myCommand =newSqlDataAdapter(sSQL, myConn);

myCommand.Fill(ds,"Customers");

//--2) Orders SQL Adapter--

sSQL ="Select o.OrderID, o.CustomerID, o.OrderDate, o.RequiredDate, o.Freight, o.ShippedDate From Customers c, Orders o Where c.CustomerID = o.CustomerID AND o.CustomerID ='VINET' ";

myCommand =newSqlDataAdapter(sSQL, myConn);

myCommand.Fill(ds,"Orders");

myConn.Close();

//4. Relation snippet

DataColumn dc1 = ds.Tables["Customers"].Columns["CustomerID"];

DataColumn dc2 = ds.Tables["Orders"].Columns["CustomerID"];

DataRelation dRel = new DataRelation("CustomerOrder", dc1, dc2);

ds.Relations.Add(dRel); //add relation to DataSet and bind data

GridView1.DataSource = ds.Tables["Customers"].DefaultView;

GridView1.DataBind();

GridView2.DataSource = ds.Tables["Orders"].DefaultView;

GridView2.DataBind();

}

[3725 byte] By [ColinTo] at [2007-12-22]
# 1

Relationships allow you to navigate parent child records:

MSDN Library:

The DataSet, which is an in-memory cache of data retrieved from a data source, is a major component of the ADO.NET architecture. The DataSet consists of a collection of DataTable objects that you can relate to each other with DataRelation objects. You can also enforce data integrity in the DataSet by using the UniqueConstraint and ForeignKeyConstraint objects. For further details about working with DataSet objects, see Using DataSets in ADO.NET.

Whereas DataTable objects contain the data, the DataRelationCollection allows you to navigate though the table hierarchy. The tables are contained in a DataTableCollection accessed through the Tables property.

.NET Framework Class Library

DataSet.Relations Property

Get the collection of relations that link tables and allow navigation from parent tables to child tables.

Namespace: System.Data
Assembly: System.Data (in system.data.dll)

DMan1 at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2

I believe that the relations are pretty much the relations if not exactly like the SQL table relationships (PK/FK etc...)

So in the snippet relation example, you are creating a relationship between 2 tables, customers and orders and the relationship being the CustomerID

so a customer ID is unique, and any customer can order any number of products.

Each customer will have many orders.

ahmedilyas at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3
Thanks DMan1 !
ColinTo at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 4
Thanks ahmedilyas !
ColinTo at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 5
I see now. You know what the 2nd query confuss me( sSQL = "Select o.OrderID, o.CustomerID, o.OrderDate, o.RequiredDate, o.Freight, o.ShippedDate From Customers c, Orders o Where c.CustomerID = o.CustomerID AND o.CustomerID ='VINET' "; )

I thought it had relationship already in the 2nd query (Where c.CustomerID = o.CustomerID ). Now I really think the "where" condition is unnecessary. The both queries (like "select * from Customers" and "select * from Orders" ) should connect using the DataRelation is enough, agree?

ColinTo at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 6

good question and my SQL is a bit rough but yes the WHERE clause is not a "relationship" joint but rather a more filtered approached for the results.

the relationship really comes in use when you are inserting data into the database

ahmedilyas at 2007-8-30 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...

.NET Development

Site Classified