Title: Display master-detail data in a DataGrid in C#

At design time, I added the example's Contacts.mdb database to the project, set its "Build Action" property to Content, and set its "Copy to Output Directory" property to "Copy if newer." That way the database is copied into the executable directory so it's easy for the program to find it at run time.

When the program starts, it executes the following code to load the data.

// Data adapters for loading data. private OleDbDataAdapter DaAddresses, DaTestScores; // The DataSet to hold the data. private DataSet StudentDataSet; // Load the data. private void Form1_Load(object sender, EventArgs e) { // Compose the connection string. string connect_string = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=Contacts.mdb;" + "Mode=Share Deny None"; // Create a DataAdapter to load the Addresses table. DaAddresses = new OleDbDataAdapter( "SELECT * FROM Addresses", connect_string); // Create a DataAdapter to load the Addresses table. DaTestScores = new OleDbDataAdapter( "SELECT * FROM TestScores", connect_string); // Create and fill the DataSet. StudentDataSet = new DataSet(); DaAddresses.Fill(StudentDataSet, "Addresses"); DaTestScores.Fill(StudentDataSet, "TestScores"); // Define the relationship between the tables. DataRelation data_relation = new DataRelation( "Addresses_TestScores", StudentDataSet.Tables["Addresses"].Columns["ContactID"], StudentDataSet.Tables["TestScores"].Columns["ContactID"]); StudentDataSet.Relations.Add(data_relation); // Bind the DataGrid to the DataSet. dgContacts.DataSource = StudentDataSet; }

This code first declares two OleDbDataAdapter objects to load data from the database's Addresses and TestScores tables. It also declares a DataSet to hold the data when it's loaded into memory.

The form's Load event handler loads the data. First it defines a connect string to connect to the database. It then uses it to create the two data adapters. Their constructors take as parameters the SQL queries to use to get the data and the connect string.

Next the code creates a new DataSet and uses the data adapters to copy data from the database into the DataSet. The strings Addresses and TestScores indicate the table names that the data adapters should give to the data inside the DataSet.

The program then defines the master-detail (or parent/child) relationship between the two tables in the DataSet. The constructor's first parameter is the name that the relationship should have. The other two parameters give the columns in the master and detail tables that should be related. After creating the relationship, the code adds it to the DataSet's Relations collection.

Finally the code sets the DataGrid control's DataSource property to the DataSet and the control automatically displays the DataSet's data.

The DataGrid is actually a pretty smart control. It initially displays the DataSet's two tables. You can click on one to expand it. In the picture at the top of this post, the DataGrid is showing the Addresses table. The relationship lets the control know that this is the master record so it displays a + sign next to each record. If you click that + sign, the control displays a link to that record's corresponding records in the TestScores child table. Click that link to see those records.

The little images in the DataGrid control's upper right corner let you navigate through the DataSet. The DataGrid control also allows you to edit data.

When the program ends, it uses the following code to save any changes to the data.

// Save changes to the data. private void Form1_FormClosing(object sender, FormClosingEventArgs e) { // Use a CommandBuilder to make the INSERT, // UPDATE, and DELETE commands as needed. OleDbCommandBuilder cb_addresses = new OleDbCommandBuilder(DaAddresses); OleDbCommandBuilder cb_testscores = new OleDbCommandBuilder(DaTestScores); // Update the database. try { DaAddresses.Update(StudentDataSet, "Addresses"); DaTestScores.Update(StudentDataSet, "TestScores"); } catch (Exception ex) { MessageBox.Show(ex.Message); } }

This code first creates two OleDbCommandBuilder objects, one for each table. Those objects automatically generate SQL INSERT, UPDATE, and DELETE statements to save changes to the data. The commands are associated with the DataAdapters passed into the builders' constructors.

The program finishes by calling the DataAdapter objects' Update methods to save the changes back to the database.

The DataGrid control is quite powerful. It lets you view and edit records in master-detail relationships. Unfortunately it's not very pretty. It's great if you need a quick-and-dirty method to let you or your power users view and edit data, but it doesn't provide more advanced user interface tools such as combo boxes, numeric controls, and value checking. In later posts, I'll show other ways to display master-detail data.

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