Ado. Net series-adding relationships to datatables

Source: Internet
Author: User
ArticleDirectory
    • Ado. Net series-adding relationships to datatables

Ado. Net series-adding relationships to datatablesintroduction

We know that a dataset is an in-memory replica of database. It can
Contain multiple datatables just like a database. In addition you can
Also set relationship between the datatables and navigate through
Relationship. This article shows you how.

Example
Using system;
Using system. Data;
Using system. Data. sqlclient;

Namespace adonetsamples
{
Class sample
{
Static void main (string [] ARGs)
{
// Declare connection, datadapter and Dataset
Sqlconnection CNN;
Sqldataadapter da;
Dataset Ds;

// Create connection
CNN = new sqlconnection ("connection_string_here ");
DA = new sqldataadapter ();
DS = new dataset ();

// Set selectcommand Property
Da. selectcommand =
New sqlcommand ("select * from MERs", CNN );

// Populate the dataset
Da. Fill (DS, "MERs ");
Da. selectcommand. commandtext =
"Select * from orders ";
Da. Fill (DS, "orders ");

// Declare relationship
Datarelation rel =
New datarelation ("custorders ",
DS. Tables [0]. Columns ["customerid"],
DS. Tables [1]. Columns ["mermerid"]);
DS. relations. Add (rel );

// Display values of MERs datatable
Foreach (datarow R in DS. Tables [0]. Rows)
{
Console. writeline (R ["mermerid"]);
Datarow [] childrows = R. getchildrows ("custorders ");
Foreach (datarow Cr in childrows)
{
Console. writeline ("\ t" + Cr ["orderid"]);
}
}
}
}
}

Let's examine the Code:

  • We create connection, dataadapter and dataset objects.
  • We then set selectcommand property of dataadapter to a new sqlcommand instance
  • This command uses the connection we created above. Its commandtext property is set to "select * from MERs"
  • We then fill the dataset with first datatable called MERs
  • We then change the commandtext of this command to "select * from orders"
  • We fill the dataset with second datatable called orders
  • In order to set relationship between two tables we need
    Have a common key field between them. In our case customerid is such
    Field.
  • We then create an instance of datarelation class that represents a relation between tables
  • We pass relation name, parent datacolumn and child datacolumn via its constructor.
  • We then add this datarelation instance to the relations collection of DataSet object.
  • In order to iterate through the Parent and Child rows we use for eachloop as shown.
  • There are two for eachloops. The outer loop scans through
    Parent datarows and the inner loop iterates through the Child datarows.
  • The getchildrows () method of datarow class accepts
    Relation name for which the child rows are to be retrieved. It returns
    Array of detail rows for the given parent row.
Summary

dataset allows you to set relations between its datatables. this is
very much similar to setting relationships between database tables. the
datarelation class represents a single database relationship. you can
Add one or more such relationships to the relations collection of
dataset. once the relation is set you can use getchildrows () method of
individual datarow of parent table to get its detail rows.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.