Fundamentals of Data Relationships

Introduction

A relational database is an application in which information flows from one object, such as a table, to another, such as a table. This means that, to get a relational database, you should create some relationships among the tables of your database.

The DataSet and the other data-based classes of the System.Data namespace provide all the functionalities you need to create relationships among objects.

Consider the following starting points of a data set:

using System;
using System.Data;
using System.Web.Mvc;

namespace RedOakHighSchool.Controllers
{
    public class StudentsController : Controller
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        DataSet dsStudents;

        public StudentsController()
        {
            colStudentID = new DataColumn("student-id",
                                      Type.GetType("System.Int32"));

            colUsername = new DataColumn("username", Type.GetType("System.String"));

            tblStudents = new DataTable("student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            dsStudents = new DataSet("red-oak-high-school");
            dsStudents.Tables.Add(tblStudents);
        }

        // GET: Students
        public ActionResult Index()
        {
            return View();
        }
    }
}

Parent and Child Tables

In order to create a relationship, you need two tables. One table would hold the original data. That table is referred to as the parent. That table would provide its data to another table. The table that receives data is referred to as a child table.

The table that acts as the parent must have a way to uniquely identify each record. In previous lessons (Lesson 3), we already saw how to apply the uniqueness to a column by setting its Unique property to True. We also saw that, to assist you with specifying the unique value of a column, you can ask the compiler to automatically generate an incrementing number for a column. This is done by applying the AutoIncrement Boolean property to a DataColumn column.

Introduction to Relationship Constraints

In a data relationship, a constraint is a rule that specifies how some data is created or provided to a table. For example, a constraint can provide the means by which some values of a column are entered. Another type of constraint can create a rule that controls the types or ranges of values that can be accepted for a column; non-acceptable values would be rejected. There are various types of constraints and most are supported in the DataSet system.

To support constraints, the DataTable class is equipped with a property named Constraints, which is an object of type ConstraintCollection.

Primary Keys

Introduction

A unique constraint is a rule that states that the value of one column or the combination of values of some columns must be unique among the records of a table. The unique constraint can involve only one column as we saw for the Unique property of a column. For example, if you are creating a list of usernames for the students of a school, you would not want two students to have the same username. Here is an example:

When creating the table, you can set up a unique constraint so that duplicate values would be rejected. In this case, every time the user enters a new value, the compiler (actually an interpreter) would check if that value exists already. If so, the user would be informed and must take appropriate actions to correct it. Here are examples:

A unique constraint can also involve more than one column. Using a unique constraint, you can set a rule so that each combination of two or more columns would produce a unique value.

To support unique constraints, the System.Data namespace provides a class named UniqueConstraint. Therefore, to programmatically create a unique constraint, you can declare a variable to type UniqueConstraint and initialize it with one of its many constructors. If you want to specify only the column that will hold unique values, you can use the following constructor:

public UniqueConstraint(DataColumn column);

This method expects the variable name of a column as argument. After creating the constraint variable, you can add it to the Constraints collection of the table. To support this, the ConstraintCollection class is equipped with a method named Add that comes in many versions. If you had created a UniqueContraint object and you want to add it, you can use the following syntax of the method:

public void Add(Constraint constraint);

This version expects a Constraint-derived object as argument. Here is an example:

using System;
using System.Data;
using System.Web.Mvc;

namespace RedOakHighSchool.Controllers
{
    public class StudentsController : Controller
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        DataSet dsStudents;

        UniqueConstraint cnsUniqueUsername;

        public StudentsController()
        {
            colStudentID = new DataColumn("student-id",
                                      Type.GetType("System.Int32"));

            colUsername = new DataColumn("username", Type.GetType("System.String"));

            tblStudents = new DataTable("student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            cnsUniqueUsername = new UniqueConstraint(colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            dsStudents = new DataSet("red-oak-high-school");
            dsStudents.Tables.Add(tblStudents);
        }

        // GET: Students
        public ActionResult Index()
        {
            return View();
        }
    }
}

If you create a unique constraint using the UniqueConstraint(DataColumn column) constructor, a default name would be assigned to it. If this is the first constraint, it would be named Constraint1 and the names would be incremental. If you want, you can provide your own name. To do this, you can use the following constructor of the UniqueConstraint class:

public UniqueConstraint(string name, DataColumn column);

The first argument is the name of the unique constraint. Here is an example:

using System;
using System.Data;
using System.Web.Mvc;

namespace RedOakHighSchool.Controllers
{
    public class StudentsController : Controller
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        DataSet dsStudents;

        UniqueConstraint cnsUniqueUsername;

        public StudentsController()
        {
            colStudentID = new DataColumn("student-id",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.AutoIncrementStep = 5;

            colUsername = new DataColumn("username", Type.GetType("System.String"));

            tblStudents = new DataTable("student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            dsStudents = new DataSet("red-oak-high-school");
            dsStudents.Tables.Add(tblStudents);
        }

        // GET: Students
        public ActionResult Index()
        {
            return View();
        }
    }
}

If the unique constraint is a combination of columns, create them in an array before adding them.

A Primary Key Constraint

When creating a table for a relational database, you must designate a special column whose records would be unique among the other records of the same table. Such a colum or field is referred to as a primary key. This means that a primary key.

To let you create a primary key, the UniqueConstraint class is equipped with the following constructor:

public UniqueConstraint(DataColumn column, bool isPrimaryKey);

In this case, the second argument is passed as true or false. If passed as true, the column specified as the first argument would be treated as the primary key. If you use this constructor, a default name would be given to the constraint. If you want to specify a name, you can use the following constructor:

public UniqueConstraint(string name,
                        DataColumn column,
                    	bool isPrimaryKey);

Creating Records

As mentioned already, if you specify a column as a primary key, when creating a record, you must provide a value for that record, and that value must be unique. As we saw previously, if you want, you can make the compiler generate a unique incrementing value for the column. Otherwise, you must make sure that the column receives a value.

Finding a Record

Some of the operations you perform on records require that you find aparticular record you want to use. If you had created a table that has a primary key, which means the column has unique values, to assist you with finding a record, the DataRowCollection class provides a method named Find. It is overloaded with two versions. One of the versions uses the following syntax:

public DataRow Find(Object key);

This method expects the value of a primary key column as argument. This method requires that the table that calls it (actually the table that owns the collection of records on which this method is called) have a primary key. If that table doesn't have a primary key, the compiler will throw a MissingPrimaryKeyException exception. If the column contains the value passed as argument, the method returns true.

If you had applied an integer type to a column, which is the recommended approach, the argument of this method would be that value. In an ASP.NET MVC application, many of the methods of a controller class expects an integer argument. This would be the value of the primary key column.

Checking the Existence of a Record

On a typical table, you may want to find out whether it contains a certain record. To assist you with this, the DataRowCollection class is equipped with a method named Contains and that is overloaded with two versions. One of the versions uses the following syntax:

public bool Contains(Object key);

This method expects a value of the primary key column. This method too can be called only on a table that has a primary key. If not, the compiler would throw a MissingPrimaryKeyException exception. If the DataRowCollection.Contains() method had returned true, you can then call the DataRowCollection.Find() method to produce the intended record.

Foreign Keys

A Foreign Key Constraint

For a parent list to supply its information to another list, in such a child list, you must create a column that would correspond to the primary key of the parent table. This column of the child list is called a foreign key.

To support foreign keys, the System.Data namespace provides a class named ForeignKeyConstraint. The ForeignKeyConstraint class is derived from the Constraint class. To programmatically create a foreign key, declare a variable of type ForeignKeyConstraint and initialize it with one of its six constructors. If you want to specify (only) the names of the primary key and the foreign key columns, you can use the following constructor:

public ForeignKeyConstraint(DataColumn parentColumn,
                            DataColumn childColumn);

Here is an example:

using System;
using System.Data;
using System.Web.Mvc;

namespace RedOakHighSchool.Controllers
{
    public class StudentsController : Controller
    {
        DataColumn colGenderID;
        UniqueConstraint PK_GenderID;
        DataColumn colGender;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataColumn colStudentGenderID;
        DataTable tblStudents;

        DataSet dsStudents;

        UniqueConstraint cnsUniqueUsername;
        ForeignKeyConstraint FK_GenderID;

        public StudentsController()
        {
            colGenderID = new DataColumn()
            {
                ColumnName = "gender-id",
                DataType = Type.GetType("System.Int32"),
                AutoIncrement = true,
                AutoIncrementSeed = 1,
                AutoIncrementStep = 1
            };

            colGender = new DataColumn("sex", Type.GetType("System.String"));

            tblGenders = new DataTable("gender");
            tblGenders.Columns.Add(colGenderID);
            tblGenders.Columns.Add(colGender);

            colStudentID = new DataColumn("student-id", Type.GetType("System.Int32"))
            {
                Unique = true,
                AutoIncrement = true,
                AutoIncrementSeed = 1000,
                AutoIncrementStep = 5
            };

            colUsername = new DataColumn("username", Type.GetType("System.String"));
            colStudentGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));

            tblStudents = new DataTable("student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);
            tblStudents.Columns.Add(colStudentGenderID);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            PK_GenderID = new UniqueConstraint("PKGenderID", colGenderID, true);
            tblGenders.Constraints.Add(PK_GenderID);

            FK_GenderID = new ForeignKeyConstraint(colGenderID, colStudentGenderID);
            tblStudents.Constraints.Add(FK_GenderID);

            dsStudents = new DataSet("red-oak-high-school");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);
        }

        // GET: Students
        public ActionResult Index()
        {
            return View();
        }
    }
}

Using Data Relationships

A relational database is an application in which different tables work together so that information in one table can be made available to other tables. To make this possible, you start by creating the tables as we have done above. Each table must have a primary key. As we saw above, to make data from a parent table available to data from a child table, the child table must have a foreign key that would "represent" the information from the parent table. Once the tables and their keys have been created, you can "link" them.

Creating a Relationship

To support relations in a database, the DataSet class is equipped with a property named Relations. The DataSet.Relations property is an object of type DataRelationCollection. The DataRelationCollection class is a collection of objects where each member is of type DataRelation. To create a relationship, declare a variable of type DataRelation and initialize it using one of its six constructors. To specify the primary key and the foreign key, you can use the following constructor:

public DataRelation(string relationName,
                    DataColumn parentColumn,
                    DataColumn childColumn)

The first argument is the name of the relationship. The second argument is the column name of the primary key. The last argument is the column name of the foreign key. After creating the relationship, you can add it to the DataSet.Relations property. To support this, the DataRelationCollection class is equipped with the Add() method that is provided in various versions. One of the versions uses the following syntax:

public void Add(DataRelation relation);

Here is an example

using System;
using System.Data;
using System.Web.Mvc;

namespace RedOakHighSchool.Controllers
{
    public class StudentsController : Controller
    {
        DataColumn colGenderID;
        UniqueConstraint PK_GenderID;
        DataColumn colGender;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataColumn colStudentGenderID;
        DataTable tblStudents;

        DataRelation relSchool;
        DataSet dsStudents;

        UniqueConstraint cnsUniqueUsername;
        ForeignKeyConstraint FK_GenderID;

        public StudentsController()
        {
            colGenderID = new DataColumn()
            {
                ColumnName = "gender-id",
                DataType = Type.GetType("System.Int32"),
                AutoIncrement = true,
                AutoIncrementSeed = 1,
                AutoIncrementStep = 1
            };

            colGender = new DataColumn("sex", Type.GetType("System.String"));

            tblGenders = new DataTable("gender");
            tblGenders.Columns.Add(colGenderID);
            tblGenders.Columns.Add(colGender);

            colStudentID = new DataColumn("student-id", Type.GetType("System.Int32"))
            {
                Unique = true,
                AutoIncrement = true,
                AutoIncrementSeed = 1000,
                AutoIncrementStep = 5
            };

            colUsername = new DataColumn("username", Type.GetType("System.String"));
            colStudentGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));

            tblStudents = new DataTable("student");
            tblStudents.Columns.Add(colStudentID);
            tblStudents.Columns.Add(colUsername);
            tblStudents.Columns.Add(colStudentGenderID);

            cnsUniqueUsername = new UniqueConstraint("UniqueUsername", colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            PK_GenderID = new UniqueConstraint("PKGenderID", colGenderID, true);
            tblGenders.Constraints.Add(PK_GenderID);

            FK_GenderID = new ForeignKeyConstraint(colGenderID, colStudentGenderID);
            tblStudents.Constraints.Add(FK_GenderID);

            dsStudents = new DataSet("red-oak-high-school");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);

            relSchool = new DataRelation("SchoolRelations", colGenderID, colStudentGenderID);
            dsStudents.Relations.Add(relSchool);
        }

        // GET: Students
        public ActionResult Index()
        {
            return View();
        }
    }
}

There are many other ways you can create a relationship.

Records Maintenance and Data Relationships

Editing a Record

Before editing a record, you can locate it by calling the DataRowCollection.Find() method. In an ASP.NET MVC application, the controller class is equipped with an Edit() method that expects as argument the value of a primary column. If the record exists, you can then edit it.

Deleting a Record

Once again, the DataRowCollection.Find() method allows you to locate the record on which you want to act. Once you have such a record, you can delete it.

Previous	Copyright © 2005-2019, FunctionX	Home