codecandle logo




 

C# - ADO.NET - DataRelation - Creating Data Relation between DataTable instances


Related: C# Tutorial

Date: 23.08.2011

In C#, in order to create relationional Data structure parallel to the structure of physical Database data relation of Tables, instance of DataRelation Class can be used. A child Table in SQL physical database can references to the Parent Table with constraint type foreign key. A basic example is Region Table and Territories Table of Northwind Sample Database which is available with SQL Server 2000. Region Table as Parent Table with primary key RegionID contains one ID per region entry. The child Table Territories references to the RegionID with foreign key constrain. As Region Table is referenced column as primary key entity with its RegionID, every record of child Table Terriotories needs to have a valid regionID value which is defined in Parent Table.

Region Table and Territories Table is related to each other in term "one to many relational". More than one record-row can have same regionID to share.

C# side the codebehind, when the two Table is stored in DataSet to work in memory via codeside: two Tables and the relation structure-entity can be produced with DataRelation Class.

Example follows the following steps.
1. Queries SQL server: fills the DataSet instance with two Table Region and Territories with DataAdaptor.,
2. Creates the foreign key and primary key relation between two DataTable instance which is on RegionID column.
3. Loops through the Region column to retrieve four ID entry belongs to the Region Table.
4. For Every region ID, loops through within parent as 2nd loop to obtain rows of Child Table with GetChildRows method.

Creating Data Relation between DataTable instances example:



using System.Data.SqlClient;
using System.Data;

namespace NS_x
{
class Program
{
static void Main(string [] args)
{
//create and intialize a SQL connection object
SqlConnection o_SQLconnection = new SqlConnection();
//provide connection details
o_SQLconnection.ConnectionString = "Server=Nebraska2;Database=Northwind;integrated
security=SSPI"
;

//create a SQL data adaptor instances to fill dataset
//provide SQL query command and connection instance to adaptor instances
SqlDataAdapter o_SQLDataAdaptor_regions = new SqlDataAdapter("select * from
Region"
, o_SQLconnection);
SqlDataAdapter o_SQLDataAdaptor_territories = new SqlDataAdapter("select * from
Territories"
,o_SQLconnection);


//create and intialize a dataset instance for data storage
DataSet o_ds_retrievedData = new DataSet();

//retrieve the data into dataset instance according to provided SQL query for data adaptor
//add regions table to the dataset instance
o_SQLDataAdaptor_regions.Fill(o_ds_retrievedData, "RegionsOfCompany");
//add territories table to the dataset instance
o_SQLDataAdaptor_territories.Fill(o_ds_retrievedData, "TerritoriesofCompany");

//display results: loop within dataset instance for row instances
Console.WriteLine("Regions of Northwind Co.");
Console.WriteLine(".....................................");
foreach (DataRow tempRow in o_ds_retrievedData.Tables["RegionsOfCompany"].Rows)
{
//display records of Parent Table
Console.WriteLine("id: " + tempRow["RegionID"] + " RegionName: " +
tempRow["RegionDescription"].ToString());
}



//create and initialize a datarelation instance
DataRelation o_DRelation_TerritoriesofRegions = o_ds_retrievedData.Relations.Add
("TerritoriesofReigon",
o_ds_retrievedData.Tables["RegionsOfCompany"].Columns["RegionID"],
o_ds_retrievedData.Tables["TerritoriesofCompany"].Columns["RegionID"]);


//loop through the parent table to retrieve parent records which are referenced in
child table

foreach (DataRow o_DRow_RegionRecord in o_ds_retrievedData.Tables["RegionsOfCompany"].Rows)
{
//Iteration
Console.WriteLine("Territories of Region with ID: " + o_DRow_RegionRecord["RegionID"].ToString());
//loop within child rows which references to the Parent Table per one entry value
of Parent

foreach (DataRow o_DRow_TerritoryRecord in o_DRow_RegionRecord.GetChildRows(o_DRelation_TerritoriesofRegions))
{
Console.WriteLine("RegionID: " + o_DRow_TerritoryRecord["RegionID"].ToString()
+ " Territory: " + o_DRow_TerritoryRecord["TerritoryDescription"].ToString());
}

}


}
}

}

C# - ADO.NET - DataRelation - Creating Data Relation between DataTable instances



Area: articles

Reference resources: unknown node
Tags: C# - ADO.NET - DataRelation - Creating Relation between DataTable instances

Catagory layer 1: Programming
Catagory layer 2: Languages

Code layer 1: Languages
Code layer 2: csharp
Code layer 3: C# 1.0
Code layer 4: ado.net
Code layer 5: datarelation-creating-relation-between-datatable-instances-(fetching-referencers-rows)

Reader layer 1: Beginner

Sector layer 1: C# Programmer

Fuzzy layer 1: how to create relation between two Table instance in C#
Fuzzy layer 2: how to retrieve rows of Child Table which references to the Parent Table with foreign Key

Nodes: Languages\C# (1)
  • comment icon
  • Comments Section
 
  • 08.02.2014 01:16:51 post by aUyod

KHcNs

  • 26.01.2013 20:54:54 post by Hdewney

good article

  • 26.01.2013 20:52:03 post by Sam Seven

This? was helpful to understand Creating Data Relation! Thank you for posting this.

  • 22.01.2013 07:17:09 post by Alias Name

Comment...

  • Post your Comment:


Chat section


[ Copy this | Start New | Full Size ]

CodeCandle

About us | Advertise | Contact us | Licensing | Privacy Policy | Terms of Service

© 2010 CodeCandle. All rights reserved.