Problem while nesting DataTable

Question

Hello. I am trying to achieve nesting in DataTable, i.e. a column of DataTable is a DataTable. My code is something like this:

DataTable table = new DataTable();
DataColumn column = new DataColumn("Qualifications", System.Type.GetType("System.Data.DataTable"));
table.Columns.Add(column);

I am getting a runtime error message at line # 2, that says "Column requires a valid DataType". What could be the reason?

Answer 1

I would say that what you are attempting to achieve is not possible using the way you specified. To achieve a relationship between an entity and several sub-entities use a one-to-many relationship between one table and another table.

This means you have two separate tables, call them for instance TableOne and TableMany. In TableOne put all the fields that describe your entity, and make sure to have a primary key. In TableMany put all the fields that describe your sub-entities, and include a field which is of the type of the TableOne primary key field, which relates each sub-entity to its owning entity.

For examle:

TableOne:
    int          PrimaryKey
    nvarchar(50) Name

TableMany:
    int          ForeignKey
    nvarchar(50) Qualification
    int          Grade

TableOne sample content:
    PrimaryKey     Name
    1              Alice
    2              Bob
    3              Charlie

TableMany sample content:
    ForeignKey     Qualification    Grade
    1              Driving          100
    1              Acting           60
    1              Singing          30
    2              Driving          40
    2              Piloting         90
    2              Snowboarding     80
    3              Dancing          70
    3              Tennis           30

Example Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace datatests
{
    class Program
    {
        static void Main(string[] args)
        {
            Program p = new Program();
            p.Main();
        }

        DataSet dataSet;
        DataTable TableOne, TableMany;
        DataRelation OneToManyRelation;

        void Main()
        {
            dataSet = new DataSet();

            TableOne = new DataTable();
            var TableOnePK = TableOne.Columns.Add("PrimaryKey", typeof(int));
            TableOne.Columns.Add("Name", typeof(string));

            TableMany = new DataTable();
            var TableManyFK = TableMany.Columns.Add("ForeignKey", typeof(int));
            TableMany.Columns.Add("Qualification", typeof(string));
            TableMany.Columns.Add("Grade", typeof(int));

            dataSet.Tables.Add(TableOne);
            dataSet.Tables.Add(TableMany);

            TableOne.Constraints.Add("PK", TableOnePK, true);
            OneToManyRelation = new DataRelation("OneToMany", TableOnePK, TableManyFK);

            TableOne.ChildRelations.Add(OneToManyRelation);

            // Populate TableOne with sample data.
            AddTableOneRow(1, "Alice");
            AddTableOneRow(2, "Bob");
            AddTableOneRow(3, "Charlie");

            // Populate TableMany with sample data.
            AddTableManyRow(1, "Driving", 100);
            AddTableManyRow(1, "Acting", 60);
            AddTableManyRow(1, "Singing", 30);
            AddTableManyRow(2, "Driving", 40);
            AddTableManyRow(2, "Piloting", 90);
            AddTableManyRow(2, "Snowboarding", 80);
            AddTableManyRow(3, "Dancing", 70);
            AddTableManyRow(3, "Tennis", 30);

            var parentRow=TableOne.Rows[0];
            var childRows = parentRow.GetChildRows(OneToManyRelation);
            Console.WriteLine("Data for record key #{0}, Name={1}", 
                parentRow["PrimaryKey"], 
                parentRow["Name"]);
            Console.WriteLine("Qualifications:");
            foreach (DataRow childRow in childRows)
            {
                Console.WriteLine("    {0}: {1}", 
                    childRow["Qualification"], 
                    childRow["Grade"]);
            }
        }

        private void AddTableManyRow(int fk, string qual, int grade)
        {
            var newRow = TableMany.NewRow();
            newRow["ForeignKey"] = fk;
            newRow["Qualification"] = qual;
            newRow["Grade"] = grade;
            TableMany.Rows.Add(newRow);
        }

        private void AddTableOneRow(int key, string name)
        {
            var newRow = TableOne.NewRow();
            newRow["PrimaryKey"] = key;
            newRow["Name"] = name;
            TableOne.Rows.Add(newRow);
        }
    }
}

Sample output:

Data for record key #1, Name=Alice
Qualifications:
    Driving: 100
    Acting: 60
    Singing: 30