Home

Introduction to Data Relationships

   

Fundamentals of Data Relationships

 

Introduction

A relational database is an application in which information flows from one object to another. To illustrate this, imagine you are creating a database for a car rental company (actually our Bethesda Car Rental application). When processing an order, the user would have to select a car and the customer who would rent it. To make this possible, one one hand you must create a special relationship between the list that holds the cars and the object used to process orders, on the other hand you must create a relationship between the list of customers and the order processor.

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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        DataSet dsStudents;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      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("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}

Practical LearningPractical Learning: Introducing Data Relationships

  1. Start Microsoft Visual Studio
  2. Create a new Windows Application named BCR1
  3. To add a new form to the project, in the Solution Explorer, right-click BCR1 -> Add -> Windows Form...
  4. Set the Name to OrderProcessing and press Enter
  5. To add a new form to the application, in the Solution Explorer, right-click BCR1 -> Add -> Windows Form...
  6. Set the Name to Employees and click Add
  7. From the Data section of the Toolbox, click DataSet and click the form
  8. Click Untyped Dataset and click OK
  9. In the Properties window, change the following characteristics:
    DataSetName: Employees
    (Name): dsEmployees
  10. Click Tables and click its button
  11. Click Add and change the following characteristics:
    TableName: Employee
    (Name) tblEmployee
  12. Click Columns and click its button
  13. In the Columns Collection Editor, click Add change the characteristics as follows:
    ColumnName: EmployeeID
    (Name): colEmployeeID

Using Unique Values

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 the previous lesson, we saw that, to create unique values for a column, you could set its Unique property to True. Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
        }
    }
}

Once this property is set, the user must remember to specify the value of that column, otherwise, the record would not be created. In some cases, the user may not have the right value at the time of data entry or the user may simply be confused.

To assist you with specifying the unique value of a column, you can ask the compiler to take care of this. To make this happen, you can set an incremental value on the column. To support this, the DataColumn class is equipped with a Boolean property named AutoIncrement. The default value of this property is False. When this property is set to True, when the user decides to add a new record, the compiler would increment the current value of the column. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
}

By default, if the DataColumn.AutoIncrement property is set to True, before any record is created on the table, the initial value of the column is set to 0 and that would be the first value of the column. If you want, you can start the records with another value. To support this, the DataColumn class is equipped with a property named AutoIncrementSeed, which is a Long integral type. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
}

By default, if the AutoIncrement property of the DataColumn class is set to True, when a record is being created, the value of the column would be incremented by 1. If you want it to be incremented by another value, you can use the AutoIncrementStep property. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  Type.GetType("System.Int32"));
    colStudentID.Unique = true;
    colStudentID.AutoIncrement = true;
    colStudentID.AutoIncrementSeed = 1000;
    colStudentID.AutoIncrementStep = 5;
}

Practical LearningPractical Learning: Introducing Data Relationships

  1. While the EmployeeID member is selected, in the Properties list, double-click AutoIncrement to set its value to True
  2. Click AutoIncrementSeed and type 1
  3. Click Add continuously and create the following columns:
     
    AllowDBNull ColumnName (Name) Expression
    False EmployeeNumber colEmployeeNumber  
      FirstName colFirstName  
    False LastName colLastName  
      EmployeeName colEmployeeName LastName + ', ' + FirstName
      Title colTitle  
  4. Click Close and click Close
  5. Display the Order Processing form
  6. From the Data section of the Toolbox, click DataSet and click the form
  7. Click Untyped Dataset and click OK
  8. In the Properties window, change the following characteristics:
    DataSetName: RentalOrders
    (Name): dsRentalOrders
  9. Click Tables and click its button
  10. Click Add and change the following characteristics:
    TableName: RentalOrder
    (Name) tblRentalOrder
  11. Click Columns and click its ellipsis button
  12. In the Columns Collection Editor, click Add twice and create the following columns:
     
    ColumnName (Name) Additional Properties
    RentalOrderID colRentalOrderID AutoIncrement: True
    AutoIncrementSeed: 1000
    DateProcessed colDateProcessed DataType: System.DateTime
  13. Click Close

Relationship Constraints

 

Introduction

 

In a relation, a constraint is a rule that would specify 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 visually create a constraint, display the Table Collection Editor. In the Members list, click the name of the table. In the Properties list, click the Constraints list and click its ellipsis button:

To visually create a constraint, display the Table Collection Editor. In the Members list, click the name of the table. In the Properties list, click the Constraints list and click its ellipsis button

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

Unique Constraints

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:

 

First Name MI Last Name Username
Frank   Adams fadams
Fannie H Adams fadams
Virginie   Mengue vmengue
Christine M Chambers cchambers
Cynthia P Chambers cchambers
Carlton   Chambers cchambers
Alexis   Leandro aleandro

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:

First Name MI Last Name Username Valid
Frank   Adams fadams fadams
Fannie H Adams fadams fhadams
Virginie   Mengue vmengue vmengue
Christine M Chambers cchambers cmchambers
Cynthia P Chambers cchambers cphambers
Carlton   Chambers cchambers cchambers
Alexis   Leandro aleandro aleandro

A unique constraint can also involve more than one column. For example, if you are creating a list of members of a club, it could be confusing to have two members with the exact same full name. You could allow same last names. As long as the first names are different, there would be less or no confusion. Using a unique constraint, you can set a rule so that each combination of a first and last name would be different.

To visually create a unique constraint, display the Table Collection Editor and create the desired columns for a table. In the Members list, click the desired table. In the Properties list, click the ellipsis button of the Constraints field. In the Constraints Collection Editor, click Add -> Unique Constraint, give it a name, click the check box of the column that will hold unique values. Here is an example:

Unique Constraint

If the unique constraint will involve more than one column, click the check box of each. After providing the necessary information, click OK.

To support unique constraints, the System.Data namespace provides the UniqueConstraint class. 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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        DataSet dsStudents;
 
        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colStudentID = new DataColumn("ColumnID",
                                      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(colUsername);
            tblStudents.Constraints.Add(cnsUniqueUsername);

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}

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:

private void Exercise_Load(object sender, EventArgs e)
{
    colStudentID = new DataColumn("ColumnID",
                                  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("Students");
    dsStudents.Tables.Add(tblStudents);
}

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

The Primary Key

To create a scenario where data flows from one list to another, we saw that the first list must hold the records that would be supplied to other lists. In the first list, each record must be distinguishable from the others. One way you can take care of this is to create what is referred to as a primary key.

To visually create a primary key, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click the arrow of the PrimaryKey combo box and click the left button of the column. Here is an example:

Primary Key

Like a unique constraint, a primary key can involve more than one column. In this case, you would click the gray button of each of the columns that would be involved in the combination. After doing this, you can click Close.

In reality, a primary key is a unique constraint. Therefore, if you create a primary key as just described, the studio would configure it behind the scenes. Otherwise, you can explicitly create a primary key as a unique constraint. To do this, in the Members list of the Tables Collection Editor, click the desired table. In the Properties list, click Constraints and click its ellipsis button. In the Constraints Collection Editor, click Add -> Unique Constraint. In the Unique Constraint dialog box, accept the default name or change it. In the Columns list, click the check box of the column that will be the primary key, and click the Primary Key check box. Here is an example:

Primary Key Unique Constraint

If the primary key involves more than one column, click the check box of each. After making the selections, click OK.

To assist you with programmatically creating 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
);

Here is an example:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

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

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

            colStudentID = new DataColumn("ColumnID",
                                      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);

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

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
        }
    }
}
   

 

 
 
 

	

Practical LearningPractical Learning: Creating a Primary Key

  1. While the RentalOrder table is still selected in the Members list, in the RentalOrder Properties list, click Constraints and click its button
  2. In the Constraints Collection Editor, click Add -> Unique Constraint
     
  3. Set the Name to PKRentalOrders
  4. Click the check box of RentalOrderID and click the Primary Key check box
     
  5. Click OK and click Close
  6. Click Columns and create the following columns:
     
    ColumnName (Name) Other Properties
    RentalOrderID colRentalOrderID  
    DateProcessed colDateProcessed  
    EmployeeID colEmplID DataType: System.Int32
    EmployeeName colEmployeeName  
    CustomerID colCustID DataType: System.Int32
    VehicleID colVehicleID DataType: System.Int32
    Condition colCondition  
    MileageStart colMileageStart DataType: System.UInt32
    MileageEnd colMileageEnd DataType: System.UInt32
  7. Click Close
  8. While the RentalOrder table is selected, in the RentalOder Properties list, click PrimaryKey and click the arrow of its combo box.
    Notice the check box on RentalOrderID
     
    Tables Collection Editor
  9. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Vehicle
    (Name) tblVehicle
  10. Click Columns and click its button
  11. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    VehicleID colCarID AutoIncrement: True
    AutoIncrementSeed: 1
    TagNumber colTagNumber AllowDBNull: False
    Unique: True
    Make colMake  
    Model colModel  
    Doors colDoors DataType: System.UInt16
    Category colCategory  
  12. Click Close
  13. While the Vehicle table is selected, in the Properties list, click Constraints and its button
  14. Click Add -> Unique Constraint
  15. Set the Name to PKVehicles
  16. Click the check box of VehicleID and click the Primary Key check box:
     
  17. Click OK and click Close
  18. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Customer
    (Name) tblCustomer
  19. Click Columns and click its button
  20. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    CustomerID colCustomerID AutoIncrement: True
    AutoIncrementSeed: 1
    CustomerName colCustomerName AllowDBNull: False
    Address colAddress  
  21. Click Close
  22. Click Constraints and click its button
  23. Click Add -> Unique Constraint
  24. Set the Name to PKCustomers
  25. Click the check box of CustomerID and click the Primary Key check box
  26. Click OK and click Close
  27. In the Tables Collection Editor, click Add and change the following characteristics:
    TableName: Employee
    (Name) tblEmployee
  28. Click Columns and click its button
  29. In the Columns Collection Editor, click Add continuously and create the following columns:
     
    ColumnName (Name) Additional Properties
    EmployeeID colEmployeeID AutoIncrement: True
    AutoIncrementSeed: 1
    FirstName colFirstName AllowDBNull: False
    LastName colLastName  
    Title colTitle  
  30. Click Close
  31. Click Constraints and click its button
  32. In the Constraints Collection Editor, Click Add -> Unique Constraint
  33. Set the Name to PKEmployees
  34. Click the check box of EmployeeID and click the Primary Key check box
  35. Click OK and click Close

The Foreign Key

For a parent list to supply its information to another list, the child list must have a column that would serve as a relay. This means that, in the 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 visually create a foreign key, in the Tables Collection Editor, you can click the ellipsis button of the Constraints field. In the Constraints Collection Editor, you can click Add -> Foreign Key Constraint. In Foreign Key Constraint:

  • Accept or change the Name
  • In the Parent Table combo box, select the name of the table that holds the primary key
  • If everything is configured fine, after selecting the table that holds the primary key, the box under Key Columns and the box under Foreign Key Columns should have the names of the right columns already. Otherwise, you should click the box under Key to reveal its combo box, then click the arrow of that combo box to select the primary key column of the parent table
  • Click the box under Foreign Key Columns and, in its combo box, select the name of the foreign key in the current table

Here is an example:

Foreign Key

In the same way, you can create the other foreign keys for your table. The list of foreign keys would appear in the Members list of the Constraints Collection Editor.

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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataColumn colStudentGenderID;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        ForeignKeyConstraint FKGenderID;

        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

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

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

            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.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);

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

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

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);
        }
    }
}

Using Data Relationships

 

Introduction

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 visually create a relationship in a data set, first select the DataSet object. In the Properties window, click Relations and click its ellipsis button. In the Relations Collection Editor, to create a relationship, click Add. In the Relation dialog box, you can complete the text boxes and combo boxes using the same options as when creating a foreign key.

If you had previously created a(the) foreign key(s) in your table, the relationship(s) would automatically be created and configured so you do not have to recreate it(them).

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.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Exercise1
{
    public partial class Exercise : Form
    {
        DataColumn colGenderID;
        UniqueConstraint PKGenderID;
        DataColumn colGender;
        DataColumn colStudentGenderID;
        DataTable tblGenders;

        DataColumn colStudentID;
        DataColumn colUsername;
        DataTable tblStudents;
        UniqueConstraint cnsUniqueUsername;

        ForeignKeyConstraint FKGenderID;

        DataRelation relSchool;
        DataSet dsStudents;

        public Exercise()
        {
            InitializeComponent();
        }

        private void Exercise_Load(object sender, EventArgs e)
        {
            colGenderID = new DataColumn("GenderID", Type.GetType("System.Int32"));
            colGenderID.AutoIncrement = true;
            colGenderID.AutoIncrementSeed = 1;
            colGenderID.AutoIncrementStep = 1;

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

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

            colStudentID = new DataColumn("ColumnID",
                                      Type.GetType("System.Int32"));
            colStudentID.Unique = true;
            colStudentID.AutoIncrement = true;
            colStudentID.AutoIncrementSeed = 1000;
            colStudentID.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);

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

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

            dsStudents = new DataSet("Students");
            dsStudents.Tables.Add(tblStudents);
            dsStudents.Tables.Add(tblGenders);

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

There are many other ways you can create a relationship.

Data Binding

Once a relationship exists between two tables, you can use that relationship to have the information flow from one list to another. To support this, the visual controls of the .NET Framework are equipped with various properties, including DataSource and DisplayMember.

The DataSource property specifies the name of the data set variable that holds the database. The DisplayMember property specifies the name of the column from the table that has the value to be displayed.

 
 
   
 

Previous Copyright © 2005-2014, FunctionX Next