Defining a schema

• We begin by creating a table schema by hand.
• Later, we will draw the schema from a database table.
• There are multiple ways to do anything we show below, particularly creating things.
• Example is in DataSet\DtBasic

static DataSet ds;
static DataTable inventory;
static DataTable items;

private static void CreateDataTables()
{
    ds = new DataSet("fred");

    CreateInventoryTable();
    CreateItemTable();
    AddTableConstraint();
    PopulateTables();
}


private static void CreateInventoryTable()
{
    inventory = new DataTable("Inventory");
    ds.Tables.Add(inventory);

    DataColumn dc;

    dc = new DataColumn();
    dc.ColumnName = "id";
    dc.DataType = typeof(Int32);
    dc.ReadOnly = true;
    dc.AllowDBNull = false;
    dc.Unique = true;
    dc.AutoIncrement = true;
    dc.AutoIncrementSeed = 100;
    dc.AutoIncrementStep = 10;
    inventory.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "itemId";
    dc.DataType = typeof(Int32);
    dc.AllowDBNull = false;
    inventory.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "owner";
    dc.DataType = typeof(string);
    dc.AllowDBNull = false;
    inventory.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "qty";
    dc.DataType = typeof(Int32);
    dc.AllowDBNull = false;
    inventory.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "price";
    dc.DataType = typeof(decimal);
    dc.AllowDBNull = false;
    inventory.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "totalPrice";
    dc.Expression = "price * qty";
    inventory.Columns.Add(dc);

    inventory.PrimaryKey = new DataColumn[]{ inventory.Columns["id"] };
}

private static void CreateItemTable()
{
    items = new DataTable("Items");
    ds.Tables.Add(items);

    DataColumn dc;

    dc = new DataColumn();
    dc.ColumnName = "id";
    dc.DataType = typeof(Int32);
    dc.ReadOnly = true;
    dc.AllowDBNull = false;
    dc.Unique = true;
    dc.AutoIncrement = true;
    dc.AutoIncrementSeed = 100;
    dc.AutoIncrementStep = 10;
    items.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "name";
    dc.DataType = typeof(string);
    dc.AllowDBNull = false;
    dc.Unique = true;
    items.Columns.Add(dc);

    dc = new DataColumn();
    dc.ColumnName = "color";
    dc.DataType = typeof(string);
    dc.AllowDBNull = true;
    items.Columns.Add(dc);

    items.PrimaryKey = new DataColumn[]{ items.Columns["id"] };

}

• Primary key columns should have AllowDBNull = false.
• If only a single column key , then Unique should be true.
• If a multi-column key, then Unique should be false.
• These will be set for you automatically when you specify the primary key on the table.

dt.PrimaryKey = new DataColumn[]{ col1, col2, ... };

Adding rows

Here is how to add rows to a table.

static string[] itemNames = { "widget",  "freeble", "thingie", 
                              "whatzit", "doodad",  "hack",   "munge" };
static string[] itemColors = { "red",    "blue",    "blue", 
                               "red",    "green",   "green",  "green" };

static string[] ownerNames = { "fred",   "fred",   "fred", 
                               "ted",    "ted",    "ted" };
static string[] ownedNames = { "widget", "freeble", "thingie", 
                               "widget", "freeble", "thingie" };

private static void PopulateTables()
{
    Dictionary<string,int> idMap = new Dictionary<string,int>();

    DataRow row;

	// adding item definitions
    for ( int i=0; i<itemNames.Length; ++i )
    {
        row = items.NewRow();
        row["name"] = itemNames[i];
        row["color"] = itemColors[i];
        items.Rows.Add(row);

        idMap[(string)row["name"]] = (int)row["id"];
    }

    Random rnd = new Random();

	// adding inventory records
    for ( int i = 0; i<ownerNames.Length; ++i )
    {
        row = inventory.NewRow();
        row["owner"] = ownerNames[i];
        row["itemId"] = idMap[ownedNames[i]];
        row["qty"] = rnd.Next(1, 10);
        row["price"] = new decimal(rnd.Next(50, 1000) / 100.0);
        inventory.Rows.Add(row);
    } 
}

Don't get thrown off by the use of the Dictionary. When we are adding rows to the items table, we generate ids for the items. We use idMap to store the relation between item names and item ids. When we then build the inventory table, we need to create the foreign key reference from an inventory row back to an item. Because our arrays of source data use item names, we use the dictionary to map the name to the id that is needed for the foriegn key reference.

Here is how to access rows from a table:

private static void PrintTables()
{
    PrintTable(items);
    PrintTable(inventory);
}


private static void PrintTable(DataTable dt)
{
    Console.WriteLine("Table {0}: {1} rows.", dt.TableName, dt.Rows.Count);
    PrintTableConstraints(dt);

     foreach ( DataColumn col in dt.Columns )
Console.Write("{0}\t",col.ColumnName);
    Console.WriteLine();

    foreach (DataRow row in dt.Rows)
    {
        //foreach (object val in row.ItemArray)
        //    Console.Write("{0}\t", val);
        //for (int i = 0; i < row.ItemArray.Length; ++i)
        //    Console.WriteLine("{0}\t", row[i]);
        foreach (DataColumn col in dt.Columns)
            Console.Write("{0}\t", row[col]);
        Console.WriteLine();
    }
}

Constraints

• A DataTable can have constraints.
• Constraints are not enforced unless the DataSet.EnforceConstraints property is true.

Unique constraints

• A UniqueConstraint can be put on a column to force it to have unique values.
• A UniqueConstraint will be added automatically if you set the Unique property on a a DataColumn to true.
• You can also create a UniqueConstraint object and add it to the Constraints collection of a DataTable.

Foreign key constraints

• You can add a ForeignKeyConstraint to a table.
• ForeignKeyConstraints enforce parent-child relations. The constraint goes on the child table.
• A ForeignKeyConstraint will be added automatically if you create a DataRelation (see below).
• Here is an example of creating a ForeignKeyConstraint by itself.

private static void AddTableConstraint()
{
    ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Item_Inventory",
        items.Columns["id"],
        inventory.Columns["itemId"]);

    fk.DeleteRule = Rule.Cascade;
    fk.UpdateRule = Rule.SetNull;
    fk.AcceptRejectRule = AcceptRejectRule.Cascade;

    inventory.Constraints.Add(fk);
}

• These constraints can be set on multiple columns.
• The DeleteRule and UpdateRule properties control propagation of deletes and updates from parent to child.
• Values are from the Rule enumeration:

Rule	Description
Cascade	Deletes or updates related rows. This is the default.
SetNull	Sets values in the related rows to DBNull.
SetDefault	Sets values in related rows to the default value.
None	Specifies that no action be taken on related rows.

 

• There is also an AcceptRejectRule propertythat deals with the AcceptChanges and RejectChanges methods being called (later). Values are:

Member name	Description
Cascade	Changes are cascaded across the relationship.
None	No action occurs (default).

• A DataTable has a Constraints collection:

  private static void PrintTableConstraints(DataTable dt)
  {
      if (dt.Constraints.Count > 0)
      {
          Console.Write(" -- Constraints: ");
          foreach (Constraint c in dt.Constraints)
              if (c is UniqueConstraint)
              {
                  UniqueConstraint uc = c as UniqueConstraint;
                  Console.Write("[UC: {0} {1} ", uc.ConstraintName, uc.IsPrimaryKey ? "Primary" : "");
                  foreach (DataColumn col in uc.Columns)
                      Console.Write(", {0}", col.ColumnName);
                  Console.Write("] ");
              }
              else if (c is ForeignKeyConstraint)
              {
                  ForeignKeyConstraint fc = c as ForeignKeyConstraint;
                  Console.Write("[FK: {0} {1} ", 
                                fc.ConstraintName, 
                                fc.RelatedTable.TableName);
                  for (int i = 0; i < fc.Columns.Length; ++i)
                      Console.Write(", {0}->{1}", 
                                    fc.Columns[i].ColumnName, 
                                    fc.RelatedColumns[i].ColumnName);
                  Console.Write("] ");
              }
              else
                  Console.Write("[{0}: {1}", c.GetType().ToString(), c.ConstraintName);
  
          Console.WriteLine();
      } 
  }

Relations

• Use a DataRelation to relate one table to another, to navigate between tables, and to return child or parent rows from a related table.
• To create, need to have a name for the relation and an array of one or more DataColumn references for the parent and a similar array for the child. If only one column is involved, just the parent column and the child column need be passed, not an array.

private static void AddRelation()
{
    DataRelation rel = new DataRelation("Item_Inventory",
        items.Columns["id"],
        inventory.Columns["itemId"]);
            
    ds.Relations.Add(rel);
          
    rel.ChildKeyConstraint.AcceptRejectRule = AcceptRejectRule.Cascade;
    rel.ChildKeyConstraint.DeleteRule = Rule.Cascade;
    rel.ChildKeyConstraint.UpdateRule = Rule.SetNull;
}

• Adding a relation causes a UniqueConstraint to be added to the parent table (if only one column is involved).
• Adding a relation causes a ForeignKeyConstraint to be added to the child table.
• You can add a flag, createConstraints, to the c-tor call to prevent these constraints from being added.
• Adding a relation can cause an error if the existing data in the tables is incorrect.
• You can use a relation to traverse from a child row to its parent row, or from a parent row its child rows.

  private static void TraverseRelation()
  {
      DataRelation rel = ds.Relations["Item_Inventory"];
  
      Console.WriteLine("From Item");
      foreach (DataRow row in items.Rows)
      {
          Console.WriteLine("{0} {1} {2}", row["id"], row["name"], row["color"]);
          foreach (DataRow child in row.GetChildRows(rel))
              Console.WriteLine(" -- {0} {1} {2} {3} ", 
                                child["id"], child["owner"], 
                                child["qty"], child["price"]);
      }
  
      Console.WriteLine();
      Console.WriteLine("From Inventory");
      foreach (DataRow row in inventory.Rows)
      {
          Console.WriteLine("{0} {1} {2} {3} ", 
                            row["id"], row["owner"], row["qty"], row["price"]);
          foreach (DataRow parent in row.GetParentRows(rel))
              Console.WriteLine(" -- {0} {1} {2}", 
                                parent["id"], parent["name"], parent["color"]);
      }
  }

DataSet info

private static void PrintDataSetInfo(DataSet ds)
{
    Console.WriteLine("DataSet {0}: enforceConstraints = {1}", 
                      ds.DataSetName, ds.EnforceConstraints);

    Console.Write(" -- {0} tables: ", ds.Tables.Count);

    foreach (DataTable dt in ds.Tables)
        Console.Write("{0}, ", dt.TableName);
    Console.WriteLine();

    Console.Write(" -- {0} relations: ", ds.Relations.Count);

    foreach (DataRelation dr in ds.Relations)
        Console.Write("[{0}: {1} -> {2}], ", 
                      dr.RelationName,dr.ParentTable.TableName,dr.ChildTable.TableName);
    Console.WriteLine();
}