Title: Use SQL queries to display master-detail data in C#

This example uses SQL queries to load master-detail data only when it is needed. For quite a while now, the .NET-ish way to manipulate data from a database has been to load it into a DataSet stored in memory and then work with it there. You can display the data, bind it to controls, change values, and otherwise fiddle with it. When you're done making any changes, you save the changes back into the database. (More recently, you can use the entity framework to work with the data as if it is stored in objects instead of a database, but the way data is fetched and changes are moved back into the database is similar.)

That approach works well if you only need to work with small amounts of data and if multiple users don't try to work with the same data at the same time. However, what if you have a huge database? For example, if the database contains billing records for 1 million customers. Or what if two users load and modify the same data? How do you know which changes to save into the database? In those cases, loading all of the data into a DataSet doesn't work very well.

Master-detail data also compounds the problem with the .NET-ish approach because it requires you to load all of the data from both the master and detail tables even if you'll never use much of the detail data.

This example demonstrates a technique I've used in projects where these situations may occur. The idea is to use SQL queries to load only the data that you need. You then display it directly in controls on the form. You could probably use data binding to make display easier, but the technique shown here is reasonably straightforward and makes it easier to know exactly what's happening.

When the program starts, it uses the following code to create a database connection and build a list of students form the database's Addresses table.

private OleDbConnection Conn; // Build the list of students. private void Form1_Load(object sender, EventArgs e) { // Prepare the connection. string connect_string = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=Students.mdb;" + "Mode=Share Deny None"; Conn = new OleDbConnection(connect_string); // List the students. ListStudents(); }

At the class level, the code defines an OleDbConnection object. The form's Load event handler initializes that object with a connection string. It then calls the following ListStudents method to make the list of students.

// List the students in cboStudents. private void ListStudents() { string query = "SELECT StudentId, LastName, FirstName " + "FROM Addresses " + "ORDER BY LastName, FirstName"; OleDbCommand cmd = new OleDbCommand(query, Conn); Conn.Open(); using (OleDbDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Student student = new Student( (int)reader.GetValue(0), (string)reader.GetValue(1), (string)reader.GetValue(2)); cboStudents.Items.Add(student); } } Conn.Close(); }

This method creates an OleDbCommand object to execute a query that selects the StudentId, FirstName, and LastName fields from the Addresses table. It opens the database connection, executes the command, and loops through the results. For each returned record, the method create a Student object and adds it to the ComboBox named cboStudents.

The following code shows the Student class.

public class Student { public long StudentId; public string FirstName, LastName; public Student(long student_id, string first_name, string last_name) { StudentId = student_id; FirstName = first_name; LastName = last_name; } // Help the ComboBox display the student's name. public override string ToString() { return LastName + ", " + FirstName; } }

This class simply holds a student's StudentId, FirstName, and LastName values. It provides a constructor to make initializing a new object easy. It also overrides the ToString method so a ListBox or ComboBox will display a Student object's last and first names.

That's the end of the code that loads the master student data when the program starts. When you select a student from the ComboBox, the following code displays that student's detail data.

// Display the data for the selected student. private void cboStudents_SelectedIndexChanged( object sender, EventArgs e) { // Get the selected student. Student student = cboStudents.SelectedItem as Student; // Get the rest of the student's address data. string address_query = "SELECT Street, City, State, Zip " + "FROM Addresses WHERE StudentId=" + student.StudentId; OleDbCommand address_cmd = new OleDbCommand(address_query, Conn); Conn.Open(); using (OleDbDataReader reader = address_cmd.ExecuteReader()) { // Get the first (and hopefully last) row. if (reader.Read()) { txtStreet.Text = (string)reader.GetValue(0); txtCity.Text = (string)reader.GetValue(1); txtState.Text = (string)reader.GetValue(2); txtZip.Text = (string)reader.GetValue(3); } } // Get the student's test scores. lstScores.Items.Clear(); string scores_query = "SELECT TestNumber, Score " + "FROM TestScores WHERE StudentId=" + student.StudentId + " " + "ORDER BY TestNumber"; OleDbCommand scores_cmd = new OleDbCommand(scores_query, Conn); int test_total = 0; int num_scores = 0; using (OleDbDataReader reader = scores_cmd.ExecuteReader()) { // Get the next row. while (reader.Read()) { lstScores.Items.Add("Test " + (int)reader.GetValue(0) + ": " + (int)reader.GetValue(1)); test_total += (int)reader.GetValue(1); num_scores++; } } Conn.Close(); // Display the calculated average. if (num_scores == 0) txtAverage.Text = "0"; else txtAverage.Text = (test_total / num_scores).ToString("0.0"); }

This code gets the Student object selected in the ComboBox. It builds a query to get the data in the Students table for that Student object's StudentId. (You could avoid this query by selecting that data in the original query when the program starts and saving the data in the Student object.) The program executes the query and displays the student's address data in text boxes.

Next the program builds a query to get the data in the TestScores table for that Student object's StudentId. The program executes the query and adds the test score data to the lstScores ListBox. It also keeps a running total of the scores and a count of the number of TestScores records.

After it displays the test scores, the program calculates the student's average score and displays it in the txtAverage TextBox.

That's about it. This program saves memory over the DataSet solution in two places. First, it when it starts, the program only loads student names and IDs. The DataSet solution would load all of the Addresses data. If the database contained lots (thousands) of students and the Addresses records were big, the DataSet would use a lot more memory.

Second, the DataSet approach would also load the entire TestScores table when the program starts. If the database contains thousands of students with a dozen or so test scores each, this would take a lot of memory. This example only loads the test scores for the student you select at any given time.

One advantage that the DataSet approach has is that it does all of its data selection at the beginning so the data is loaded and ready to go later when you need it. (The DataSet is also smart enough to not save data that has not been modified, so it doesn't waste time uploading data if you don't make any changes.)

This example fetches a student's data later when you select that student. That requires more round trips to the database and that is generally slower than a single large round trip. However, if you have a large database, you probably don't need to use most of the data anyway. The choice is between selecting everything all at once or selecting a tiny fraction of the database in several queries.

The multiple-query approach also performs its queries when the user selects a student. There's a tiny delay, but the user doesn't notice it. (Actually the first time you select a student, there is a noticeable delay. I think the database is spending time building an execution plan for the query and it caches the plan for later use. There may also be data caching going on here. If anyone has more insight into this, please post a comment.)

This example doesn't let you modify the data and save changes. I'll show how you can do that in a later post.

Of course this only works well if the database is well designed. For more information on designing databases that are flexible and robust, see my book Beginning Database Design Solutions.

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