Three Master-Details Related Grids on the Same Form

• Download demo - 212 KB
• Download source code - 276 KB

Introduction

This is a working example of a master-details DataGridView on the same form. There are three entities that are in parent-child relations: Department, Group, Student. Department has Groups, Group has Students. The group name is unique in the context of the department, but is not unique in the context of the whole university. Sometimes, a student may be transferred from one group to another or even from one department to another. Therefore, it would be nice if the administrator could have the possibility to determine each department group explicitly when he/she changes a student's group. I decided to expand on the group name (code, somehow) to an explicit group name = Department abbreviation + group name (code, somehow). Also it would be comfortable for the user when the students are filtered by the current Group and displayed in the Students grid, and the Groups are filtered by the current Department and displayed in the Groups grid. The user may change the group's department and the student's group using the combobox in the grid. All these points are covered in the article presented.

Background

Our approach uses ADO.NET objects: DataSet, DataTable, DataRelation, DataColumn, and DataRow. And these WinForms objects: DataGridView, DataGridViewTextBoxColumn, DataGridViewComboBoxColumn. BindingSource is also used.

Using the Code

For a start, we create a form with three data grids and the respective binding sources for them on it (see the image below).

We name them gridDepartments, gridGroups, and gridStudents, respectively. Then, we create some storage where the form data will be kept. For these goals, I prefer System.Data.DataSet to any other object because it is well documented and well known for me. And, what is more, it has a relational nature. So we create a data structure that stores three entities and the two relations between them using stuff from the System.Data namespace.

Here is the declaration:

/// <summary>
/// Data set that collect all form data tables, relations
/// </summary>
private DataSet FormData = new DataSet();
/// <summary>
/// Relation that links department (parent) and group (child) entities
/// </summary>
private DataRelation DepartmentGroupRelation;
/// <summary>
/// Relation that links group (parent) and student (child) entities
/// </summary>
private DataRelation GroupStudentRelation;
/// <summary>
/// Department entity
/// </summary>
private DataTable DepartmentTable = new DataTable(DEPARTMENT_TABLE);
/// <summary>
/// Group entity
/// </summary>
private DataTable GroupTable = new DataTable(GROUP_TABLE);
/// <summary>
/// Student entity
/// </summary>
private DataTable StudentTable = new DataTable(STUDENT_TABLE);

Here is the implementation:

//load tables' schema from DataBase, this stuff just calls 
//DataAdapter.FillSchema method with target DataTable
//and SchemaType.Source paremeters
base.LoadFormDataSchema(DepartmentTable, DepartmentCmds);
base.LoadFormDataSchema(GroupTable, GroupCmds);
base.LoadFormDataSchema(StudentTable, StudentCmds);

//add tables to data set
FormData.Tables.AddRange(new DataTable[] { DepartmentTable, 
                             GroupTable, StudentTable });

//add relations between tables
DepartmentGroupRelation = FormData.Relations.Add(DEPARTMENT_GROUP_RELATION,
    DepartmentTable.Columns[DEPARTMENT_ID], 
    GroupTable.Columns[GROUP_DEPARTMENT], true);
GroupStudentRelation = FormData.Relations.Add(GROUP_STUDENT_RELATION,
    GroupTable.Columns[GROUP_ID], 
    StudentTable.Columns[STUDENT_GROUP], false);

To know more about EntityEditorForm.LoadFormDataSchema(DataTable, ISelectCommandProvider), you can my article about a data table editing framework.

The next line of code solves the problem with the 'wide' name for groups. We use a virtual column that is formed from the group name and the department abbreviation. The department abbreviation we receive from the parent Department table uses the DEPARTMENT_GROUP_RELATION relation. I prefer this solution to an using an additional table field. Because an additional field will cause problems with data integrity (group abbreviation may be changed, for example).

//add virtual columns
DataColumn wideName = GroupTable.Columns.Add(GROUP_WIDENAME, typeof(string),
    "Parent(" + DEPARTMENT_GROUP_RELATION + ")." + 
    DEPARTMENT_ABBREVIATION + " + ' ' + " + GROUP_CODE);
wideName.ColumnMapping = MappingType.Hidden;

Next, we do grid initialization. It consists of data binding and grid columns installation. This is the point of main trick. The BindingSource object implements the current table row manager and it can be assigned to DataGridView.DataSource. Also, the DataGridView.DataMember property can be set to a relation name. As a result, the grid displays all the children of the current row in the binding source using the data member relationship. And each time the current row in BindingSource changes, the grid displays the children of the next parent. The code below implements this feature:

// data binding.
//department binding data source
bsrcDepartments.DataSource = FormData;
bsrcDepartments.DataMember = DEPARTMENT_TABLE;
gridDepartments.DataSource = bsrcDepartments;

// We set bsrcDepartment (bounding source object) as data source to make
// groups list filtered by current department in gridGroups
bsrcGroups.DataSource = bsrcDepartments;
bsrcGroups.DataMember = DEPARTMENT_GROUP_RELATION;
gridGroups.DataSource = bsrcGroups;

// Again we set bsrcGroup (bounding source object) as data source to make
// students list filtered by current group in gridGroups
bsrcStudents.DataSource = bsrcGroups;
bsrcStudents.DataMember = GROUP_STUDENT_RELATION;
gridStudents.DataSource = bsrcStudents;

And now is exactly the time when we may add columns to our grids. But we have to disable auto generation of grid columns before data binding. So, insert the code lines below before the //data binding line:

gridDepartments.AutoGenerateColumns = 
   gridGroups.AutoGenerateColumns =
     gridStudents.AutoGenerateColumns = false;

We add columns to each grid (you can get the full code in the article sources).

//add columns manually 
AddDepartmentGridColumns(gridDepartments);
AddGroupGridColumns(gridGroups);
AddStudentGridColumns(gridStudents);

Here, we see only separate the parts from these methods. This is the addition of the ID column:

DataGridViewTextBoxColumn idColumn = new DataGridViewTextBoxColumn();
idColumn.Name = idColumn.DataPropertyName = DEPARTMENT_ID;
idColumn.HeaderText = DEPARTMENT_ID; // [TEXT]
idColumn.ValueType = typeof(Int64);
idColumn.Frozen = true;
idColumn.Visible = true;
idColumn.ReadOnly = true;
grid.Columns.Add(idColumn);

Make it read only by using the idColumn.ReadOnly = true; code line. And, this is the column with the combobox that allows to choose a certain group. The combo uses an expression column of the GroupTable data table to display a 'wide' group name for the selection.

// this is combobox column for wide group name changing. Wide group name consists 
// of department abbreviation and group title to distinguish groups with same title
// ("first group", for instance) of different departments. In that way user takes 
// possibility to change department of certain student, not group only, in more 
// understandable manner. 
DataGridViewComboBoxColumn groupColumn = new DataGridViewComboBoxColumn();
groupColumn.Name = groupColumn.DataPropertyName = STUDENT_GROUP;
groupColumn.HeaderText = STUDENT_GROUP;   // [text]
groupColumn.DataSource = GroupTable;
groupColumn.DisplayMember = GROUP_WIDENAME;
groupColumn.ValueMember = GROUP_ID;
groupColumn.Name = GROUP_WIDENAME;
gridStudents.Columns.Add(groupColumn);

Let's parse this code block in detail.

• groupColumn.DataPropertyName = STUDENT_GROUP;: The value of the column depends on the STUDENT_GROUP table field. And inversely, all combobox value changes reflect on the STUDENT_GROUP field (column with name 'Group', Int64, ID reference to row in GroupTable).
• groupColumn.DataSource = GroupTable;: This property indicates the source of strings to display in the combobox.
• groupColumn.ValueMember = GROUP_ID;: This indicates the key column name in the data source that will be used for comparison with the DataPropertyName field value to find a correspondence.
• groupColumn.DisplayMember = GROUP_WIDENAME;: It indicates the column name in the data source from which the combobox will get strings for the presentation of key values determined by the ValueMember property.

About row IDs. The link in the Master-Details relation is ensured by a pair of keys: primary (parent entity) and foreign (child entity). It is a common practice to use negative auto-incremented identification digits for IDs under editing mode (when data exists in the memory of a single work station only). It ensures ID originality for new rows under editing, and allows to avoid excessive calls to the server. And, when rows' insertion to a database occurs, the rows get a real ID. I.e., the row key is changed. But, it is inadmissible for parent-child relations. It does not care if you use GUID IDs, but I prefer traditional digital IDs to GUIDs. A database based on digital IDs is four times larger than one based on GUIDs (GUID size is 16 bytes instead of 4 bytes for a digit). Therefore, we give real values to row IDs when a row is created.

//add originality support
//we have to do this to support master-details relations
//between three tables.
DepartmentTable.TableNewRow += 
  new DataTableNewRowEventHandler(SomeTable_TableNewRow);
GroupTable.TableNewRow += 
  new DataTableNewRowEventHandler(SomeTable_TableNewRow);
StudentTable.TableNewRow += 
  new DataTableNewRowEventHandler(SomeTable_TableNewRow);

...
            
/// <summary>
/// Auto assignment of new row's fields 
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void SomeTable_TableNewRow(object sender, DataTableNewRowEventArgs e)
{
    DetermineRowID(e.Row, 0, ((DataTable)sender).TableName);
}

/// <summary>
/// gives id for row
/// </summary>
/// <param name="row">row that requires id</param>
/// <param name="idColumnIndex">id column index</param>
/// <param name="entityName">entity name</param>
/// <remarks>
/// It determines undetermined rows only skipping determined
/// (id is equal or more than zero or is null)
/// </remarks>
protected static void DetermineRowID(DataRow row, 
                      int idColumnIndex, string tableName)
{
    Int64 idCurrent = (DBNull.Value == row[idColumnIndex]) ? -1 : 
                       Convert.ToInt64(row[idColumnIndex]);
    if (idCurrent < 0)
    //it must be 'undefined'. use SetupVirtualIDColumn
    {
        row[idColumnIndex] = SequenceNumberManager.GetNextID(
            tableName,
            DataLayer.DataLayer.DefaultDataSource);
    }
}

SequenceNumberManeger.GetNextID uses the default data source and the Stored Procedure in it to obtain a free ID for the entity. This Stored Procedure returns an absolutely unique ID, because it increases the current ID for the entity with each call.

IF EXISTS (SELECT * FROM dbo.sysobjects 
WHERE id = object_id(N'[dbo].[GetNextID]') AND 
           OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[GetNextID]
RETURN
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Major League
-- Create date: 14/09/2009
-- Description:    gets sequence number for table
-- =============================================
CREATE 
--ALTER
PROCEDURE [dbo].[GetNextID] 
    -- Table name
    @TableName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Declare the return variable here
    DECLARE @NextID bigint;     --Return value

    --if table does not exist we just return NULL. It must exist.
    IF EXISTS(SELECT * FROM dbo.sequence WHERE 
                       table_name = @TableName) 
    BEGIN
        -- get next sequence number
        SELECT @NextID = (sequence_value + 1) FROM 
           dbo.sequence WHERE table_name = @TableName;
        UPDATE dbo.sequence SET sequence_value = @NextID 
           WHERE table_name = @TableName;
    END
    ELSE
    BEGIN
        -- set next sequence to first number
        SET @NextID = 1;
        INSERT INTO sequence(table_name, sequence_value) 
               VALUES(@TableName, @NextID);
    END

    -- Return the result of the function
    SELECT @NextID

END
GO

At this point, we have a form with three grids in Master-Details relations.

Conclusion

Any comments, remarks, and suggestions about the article topic are very welcome!

History

• 23 September 2009: First version.