Title: Bind a DataGrid to a DataSet with a data relation in C#

This example shows how you can bind a DataGrid control to a DataSet that contains two tables that are related with a data relation. The data relation lets the DataGrid allow you to click on links to jump from one table to the related records in the other. For example, the picture on the right shows the data in the TestScores table for the Ben Beecher record in the Students table.

To make this post a bit easier to read, it's broken into sections.

• MakeStudents
• MakeTestScores
• Form_Load
• Navigation

When it starts, the program creates a DataSet. It then calls the MakeStudents and MakeTestScores methods to create the Students and TestScores tables. The following code shows the MakeStudents method.

// Make the Students table. private void MakeStudents(DataSet data_set) { // Make the Students table. DataTable dt_students = new DataTable("Students"); data_set.Tables.Add(dt_students); // Add columns to the DataTable. dt_students.Columns.Add("FirstName", System.Type.GetType("System.String")); dt_students.Columns.Add("LastName", System.Type.GetType("System.String")); dt_students.Columns.Add("StudentId", System.Type.GetType("System.Int32")); // Make all columns required. for (int i = 0; i < dt_students.Columns.Count; i++) { dt_students.Columns[i].AllowDBNull = false; } // Make FirstName + LastName the primary key. DataColumn[] pk_cols = { dt_students.Columns["FirstName"], dt_students.Columns["LastName"] }; dt_students.PrimaryKey = pk_cols; // Make StudentId require uniqueness. dt_students.Constraints.Add( new UniqueConstraint( dt_students.Columns["StudentId"])); // Add items to the table. dt_students.Rows.Add(new object[] { "Ann", "Archer", 1 }); dt_students.Rows.Add(new object[] { "Ben", "Beecher", 2 }); dt_students.Rows.Add(new object[] { "Cindy", "Carter", 3 }); dt_students.Rows.Add(new object[] { "Dean", "Dent", 4 }); dt_students.Rows.Add(new object[] { "Erma", "Edwards", 5 }); }

This method creates a new DataTable and adds it the DataSet it received as a parameter. (You'll see how the DataSet is created shortly.)

The code then adds columns to the DataTable, makes all of the columns required, makes the combination FirstName + LastName the table's primary key, and creates a uniqueness constraint to ensure that every record has a different StudentId.

The method finishes by creating some records.

The following code shows the MakeTestScores method.

// Make the TestScores table. private void MakeTestScores(DataSet data_set) { // Make the TestScores table. DataTable dt_testscores = new DataTable("TestScores"); data_set.Tables.Add(dt_testscores); // Add columns to the DataTable. dt_testscores.Columns.Add("StudentId", System.Type.GetType("System.Int32")); dt_testscores.Columns.Add("TestNumber", System.Type.GetType("System.Int32")); dt_testscores.Columns.Add("Score", System.Type.GetType("System.Int32")); // Make all columns required. for (int i = 0; i < dt_testscores.Columns.Count; i++) { dt_testscores.Columns[i].AllowDBNull = false; } // Make StudentId + TestNumber the primary key. DataColumn[] pk_cols = { dt_testscores.Columns["StudentId"], dt_testscores.Columns["TestNumber"] }; dt_testscores.PrimaryKey = pk_cols; // Make test scores for each student. Random rand = new Random(); foreach (DataRow row in data_set.Tables["Students"].Rows) { object[] values = new object[3]; values[0] = row["StudentId"]; // Make 10 records. for (int test = 1; test <= 10; test++) { values[1] = test; values[2] = rand.Next(70, 100); dt_testscores.Rows.Add(values); } } }

This method is somewhat similar to the MakeStudents method. It creates the DataTable, adds it to the DataSet, defines its columns, makes the columns required, and creates a primary key. Note that a primary key enforces uniqueness, so this table cannot have duplicate StudentId + TestNumber values.

Next the method loops through the rows in the Students table. For each Students record, the method creates 10 random test scores.

The program's most interesting code is contained in the following Form_Load event handler.

private void Form1_Load(object sender, EventArgs e) { // Make the DataSet. DataSet data_set = new DataSet("Students Data"); // Make the Students table. MakeStudents(data_set); // Make the TestScores table. MakeTestScores(data_set); // Make the data relation. // The DataGrid uses this for navigation. // This also defines a foreign key constraint. DataColumn parent_col = data_set.Tables["Students"].Columns["StudentId"]; DataColumn child_col = data_set.Tables["TestScores"].Columns["StudentId"]; DataRelation id_relation = new DataRelation( "Test Scores", parent_col, child_col); data_set.Tables["Students"].ChildRelations.Add(id_relation); // Bind the DataGrid to the DataSet. dgStudents.DataSource = data_set; }

This code creates the DataSet named "Students Data" and passes it to the MakeStudents and MakeTestScores methods to make the tables.

It then creates a data relation between the Students.StudentId and TestScores.StudentId columns. To do that, it gets references to those columns, uses them to create a DataRelation, and adds the relation to the Students table's ChildRelations collection.

Adding the relation does three things. First, and most obviously, it adds the relation to the Students table. That allows the DataGrid to provide navigation for the columns. I'll explain that in the next section.

Second, this adds the relation to the TestScores table's ParentRelations collection.

Finally, this creates a foreign key constraint between the two columns. That makes the DataSet prevent you from creating a TestScores record that uses a StudentId value that isn't already in the Students table. (Run the progfram and use the DataGrid to try it and see what happens.)

That's all there is to the example's code. This section explains how you can use the DataGrid to navigate through the DataSet.

When you start the program, the DataGrid looks like this:

Click the + to expand the DataSet's tables so you see this:

Now you can click on one of the tables to expand it. The TestScores table just shows a list of its data, so it isn't very interesting. If you click on the Students table, you see this:

If you click the + next to a Students record, the DataGrid expands to show the child relationships that are defined for that record.

Finally, if you click the Test Scores link, the DataGrid opens the child records for the Students record.

At this point, the DataGrid knows the Students parent record. If you click on the bottom of the DataGrid to add a new row to the TestScores table, the DataGrid automatically fills in the parent record's StudentId value. (Although you can change it if you like.)

The following picture highlights some of the DataGrid control's navigational features.

The DataGrid has the following navigational features:

• It displays the DataSet's name in the upper left.
• It displays the parent table's name below the DataSet's name.
• It displays the parent record's data to the right of the parent table's name.
• Click the Back arrow to move back to the previous view, which showed the parent table's records.
• Click the Show/Hide button to show or hide the DataSet name and the parent record's data.

This isn't a very fancy interface for managing a complicated database, but it does allow you to navigate from parent records to child records. It also allows you to add, edit, and delete records in all of the tables. It may not be fancy enough to give to users, but it might be all you need to manage a simple database yourself.

Download the example to experiment with it and to see additional details.