Master Detail

[rakgol_a]

Hi,

We are using a grid(XtraGrid by Devepress) that ChildRelations required
the datasource to support DataTable ChildRelations for master detail relationships, how add ChildRelations to SQLDataTable?

Qoute from the Grid help file

"This document explains how you can implement master-detail relationships for a DataTable using the methods provided by the data source.

ADO .NET allows you to implement master-detail relationships between data tables. The key object in ADO .NET is the DataTable which encapsulates a two-dimensional table of the in-memory data. You can define a master-detail relationship between two tables via the parent table's DataTable.ChildRelations property. If you bind the grid control to a DataTable, which contains master-detail relationships or to a DataView which refers to such a table, the grid can obtain the relationships and the only thing you need to do is to assign pattern Views for representing particular relationships in the XtraGrid. "

We are using dotConnect for Oracle, SQL Server and Postgres.

Regards,
Alfred

[Shalex]

Here is a classic implementation of master-detail relationship:

Code: Select all

      using (SqlConnection connection = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=test;Integrated Security=True")) {

        SqlDataTable deptTable = new SqlDataTable("select * from dept", connection);
        SqlDataTable empTable = new SqlDataTable("select * from emp", connection);

        deptTable.Open();
        empTable.Open();

        DataSet ds = new DataSet();
        ds.Tables.Add(deptTable);
        ds.Tables.Add(empTable);

        DataRelation deptEmpRel = new DataRelation("dept_emp", deptTable.Columns["deptno"], empTable.Columns["deptno"]);
        //empTable.ParentRelations.Add(deptEmpRel);
        deptTable.ChildRelations.Add(deptEmpRel);

        BindingSource deptLink = new BindingSource();
        deptLink.DataSource = deptTable;

        BindingSource empLink = new BindingSource();
        empLink.DataSource = deptLink;
        empLink.DataMember = "dept_emp";

        deptGrid.DataSource = deptLink;
        empGrid.DataSource = empLink;
      }

In addition to classic master-detail relationship, the SqlDataTable component is capable of establishing a relationship where details data is not cached on the client. Every time the current position in the master table is changed, the client SqlDataTable requests the appropriate portion of data from the server. This goal is achieved using the ParentDataRelation class. The query-based master-detail relationship is designed to improve performance of the application and leverage network load.

Code: Select all

    using (SqlConnection connection = new SqlConnection(@"Data Source=.\SqlExpress;Initial Catalog=test;Integrated Security=True")) {
      connection.Open();
      SqlDataTable deptTable = new SqlDataTable("SELECT * FROM dept", connection);
      SqlDataTable empTable = new SqlDataTable("SELECT * FROM emp", connection);
      empTable.ParentRelation.ParentTable = deptTable;
      empTable.ParentRelation.ParentColumnNames = new string[] { "deptno" };
      empTable.ParentRelation.ChildColumnNames = new string[] { "deptno" };
      deptTable.Owner = this;
      empTable.Owner = this;
      deptTable.Open();
      empTable.Open();
      deptDataGrid.DataSource = deptTable;
      empDataGrid.DataSource = empTable;
    }

Here is a script that creates tables that are used in the sample:

Code: Select all

CREATE TABLE Dept(
		DEPTNO int PRIMARY KEY NOT NULL,
		DNAME varchar(14) NULL,
		LOC varchar(13) NULL);

CREATE TABLE Emp(
		EMPNO int PRIMARY KEY NOT NULL,
		ENAME varchar(10) NULL,
		JOB varchar(9) NULL,
		MGR int NULL,
		HIREDATE datetime NULL,
		SAL float NULL, 
		COMM float NULL, 
		DEPTNO int REFERENCES dept(DEPTNO));

[rakgol_a]

Can one create SqlDataTable child relation in design-time than in code?

"....
DataRelation deptEmpRel = new DataRelation("dept_emp", deptTable.Columns["deptno"], empTable.Columns["deptno"]);
deptTable.ChildRelations.Add(deptEmpRel);
..."

[Shalex]

You can get master-detail relationship in 2 ways in design time using the Dept and Emp tables:

• Our DataSet Wizard (Tools | SQL Server | DataSet Wizard) detects relations between selected tables automatically. In this case you need to set the DataSource property to DataSet, the DataMember property to Dept in the first GridControl. And in the second GridControl: the DataSource property to DataSet, the DataMember property to the Dept.FK__Emp__DEPTNO relation of this Dataset.
• You may create master-detail relationship without using DataSet. Just create 2 SqlDataTables: deptDataTable ("select * from Dept") and empDataTable ("select * from Emp"). Set the ParentRelation property of a child table (empDataTable) in design time. As a result, you will get the master-detailed implementation used in the second sample from my previous post where details data is not cached on the client.