Browse Query-based Master-detail Relationship

[malinsky]

Hello,

please help me. I create Query-based Master-detail Relationship without connect to any datagrid etc.. How browse all master records with his child rows. I try foreach rows but childs rows stay for first master row. Sorry I'm C# novice. Thanks for help! Pavel

Code: Select all

        private void button1_Click(object sender, EventArgs e)
        {
            // create Query-based Master-detail Relationship
            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();

            // browse all master rows with child rows
            foreach (DataRow dataRow in deptTable.Rows)
            {
                // data from master row 
                foreach (DataColumn dataColumn in deptTable.Columns)
                {
                    richTextBox1.AppendText(dataRow[dataColumn].ToString() + "\t");
                }

                // data from child rows of current master row stay on first master row
                richTextBox1.AppendText(String.Format("empTable.RecordCount: {0} \n\n", empTable.RecordCount));
                foreach (DataRow dataRowChild in empTable.Rows)
                {
                    foreach (DataColumn dataColumnChild in empTable.Columns)
                    {
                        richTextBox1.AppendText(dataRowChild[dataColumnChild].ToString() + "\t");
                    }
                    richTextBox1.AppendText("\n");
                }
                richTextBox1.AppendText("\n");
            }
            richTextBox1.AppendText("");
        }

[Pinturiccio]

In your code you just display a new line and don't change the position. In the example with DataGridView, position is changed in the Dept table when selecting a new row. You won't get the required result without using DataGridView.
To get the required result you should use DataSet and create the Relation object. The example below shows how this can be done:

Code: Select all

private void button1_Click(object sender, EventArgs e)
{
    SqlDataTable dt = new SqlDataTable();
    SqlDataAdapter deptAdapter = new SqlDataAdapter("SELECT * FROM dept", connection);
    SqlDataAdapter empAdapter = new SqlDataAdapter("SELECT * FROM emp", connection);
    DataSet dataSet = new DataSet();
            
    deptAdapter.Fill(dataSet, "Dept");
    empAdapter.Fill(dataSet, "Emp");

    DataTable deptTable = dataSet.Tables["Dept"];
    DataTable empTable = dataSet.Tables["Emp"];
                    
    DataRelation Relation = deptTable.ChildRelations.Add(deptTable.Columns["deptno"], empTable.Columns["deptno"]);
    DataRow[] arrRows;

    foreach (DataRow dataRow in deptTable.Rows)
    {
        foreach (DataColumn dataColumn in deptTable.Columns)
        {
            richTextBox1.AppendText(dataRow[dataColumn].ToString() + "\t");
        }
        richTextBox1.AppendText("\n");

        arrRows = dataRow.GetChildRows(Relation);

        for (int i = 0; i < arrRows.Length; i++)
        {
            foreach (DataColumn dataColumnChild in empTable.Columns)
            {
                richTextBox1.AppendText(arrRows[i][dataColumnChild].ToString() + "\t");
            }
            richTextBox1.AppendText("\n");
        }
        richTextBox1.AppendText("-----------------------------------------\n\n");
    }
}

This result can also be achieved using the BindingSource and CurrencyManager classes.

[malinsky]

Hello,

thank you for answer. In other words, the use of query based on relationship via ParentRelation is not possible without binding to visual components? Without visual components is needed use full fetch master and all child rows to client and work with data snapshot of all potentional child rows in memory? I tried the similar uses like in Delphi with master-detail TQuery...

Thanks

[Pinturiccio]

We have changed your example and now it is pretty much the same as your initial example. To change the position in the deptTable table we used the Devart.Data.DataLink type. For more information, please refer to http://www.devart.com/dotconnect/sqlser ... aLink.html

When we select values for a new row from the deptTable table, we change the position in the DataLink object and data corresponding to the row from the deptTable table are selected to the empTable table.

Code: Select all

private void button1_Click(object sender, EventArgs e)
{
    // create Query-based Master-detail Relationship
    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();

    DataLink dl = new DataLink();
    dl.Owner = this;
    dl.DataSource = deptTable;
    dl.Synchronized = true;
            
    for (int i = 0; i < deptTable.RecordCount; i++)
    {
        dl.Position = i;

        // data from master row
        foreach (DataColumn dataColumn in deptTable.Columns)
        {
            richTextBox1.AppendText(deptTable.Rows[i][dataColumn].ToString() + "\t");
        }
        richTextBox1.AppendText(String.Format("empTable.RecordCount: {0} \n\n", empTable.RecordCount));

        // data from child rows of current master row stay on first master row
        foreach (DataRow dataRowChild in empTable.Rows)
        {
            foreach (DataColumn dataColumnChild in empTable.Columns)
            {
                richTextBox1.AppendText(dataRowChild[dataColumnChild].ToString() + "\t");
            }
            richTextBox1.AppendText("\n");
        }
        richTextBox1.AppendText("\n");
    }
    richTextBox1.AppendText("");
}

[malinsky]

Hello,

that's exactly what I needed to know, great!

Thanks!