A Master-Detail Solution Using Single Input Controls

Introduction

This is my first article here, even though I have used the wealth of knowledge on this great site many times. So please bear with me.

**This is a disclaimer to let everyone know no matter how much I know, I am always learning!!! So comments, questions, moans will all be happily accepted!!!**

The Problem

The problem this article aims to address involves creating a Master-Detail view of data using single input controls (such as TextBoxes, DateTimePickers, etc.) instead of multiple input controls such as ComboBoxes and ListBoxes.

Here's a quick shot of what I am trying to accomplish. This is how the program is before a row is selected in the currently displayed DataGrid:

And this is the program after a row is selected in the currently displayed DataGrid:

Basically, the program pulls a list of jobs from a database based on who their job is with and the specific date. The TextBoxes to the left and below the DataGrid are updated to show the selected Job and Client Info when a user selects one of the scheduled jobs (the ones with no titles are blank jobs generated by the program for scheduling, and are ignored as part of the Master-Detail relationship).

Here's the simplified Stored Procedure used to fill the DataGrid:

GetJobByDateAndArtist (@ArtistID bigint, @Date datetime)
AS
SELECT 
Job.JobStartTime, Job.JobTitle, Job.ClientID 
FROM 
Job
WHERE 
Job.ArtistID = @ArtistID AND Jobs.JobDate = @Date

And here are the Stored Procedures to fill the TextBoxes:

This is for the client textboxes:

GetClientInfoByID(@ClientID bigint)
AS
SELECT
*
FROM
Clients
WHERE 
Clients.ClientID = @ClientID

This is for the job details textboxes:

GetAJobsDetails(@JobID bigint)
AS
SELECT
*
FROM
Job
WHERE 
Job.JobID = @JobID

So far, all of the Master-Detail solutions I have run across would involve something like listing the clients in a datagrid or list type control and then having a second datagrid show the selected client's appointments. They usually just set up a relationship in the dataset and the program does the rest.

The problem is, in my program, Jobs are the most important item for the user to see, and the user only wants to see the client details for the specific job selected.

To minimize the load on the system, the program is designed to only pull one client record at a time.

Now, since the Clients table is actually the parent table of the Job table, but the jobs datagrid is the main focus of the program, using Relationships only seemed to crash my code.

The Solution

To begin with, we set up some preliminary stuff including:

1. Add three separate SqlDataAdapters (one for the jobs, one for the client's info, and one for the job's details).
2. Generate a Strongly-Typed DataSet (VS automation rocks!).
3. Add a DataView object (for sorting purposes) and link it to the GetJobsByArtistAndDate table of the DataSet.
4. Add a DataGrid control and link the DataSource properties to the DataView object.
5. Add the TextBox controls and linking their DataBindings to their respective columns of the GetClientInfoByID and GetAJobDetails tables in the DataSet.

Now on to coding some functionality:

The next thing I did was to use VS to create an event handler for the MouseDown event of my DataGrid holding the Appointments.

This is how the method looked after it was done:

private void WeeklyDataGrids_MouseDown(object sender, System.Windows.Forms.MouseEventArgs e)
{
    if (((DataGrid)sender).HitTest(e.X,e.Y).Row >= 0)
    {
        this.dataSet11.GetAJobDetails.Clear();
        this.dataSet11.GetClientInfoByID.Clear();
        if (((double)this.CurrentDailyDataView[((DataGrid)
                    sender).HitTest(e.X,e.Y).Row]["JobTotalHours"]) != 0)
        {
            this.GetClientByIDSqlDataAdapter.SelectCommand.Parameters["@ClientID"].Value =
                            this.CurrentDailyDataView[(
                            (DataGrid)sender).HitTest(e.X,e.Y).Row]["ClientID"].ToString();
            this.GetClientByIDSqlDataAdapter.Fill(this.dataSet11.GetClientInfoByID);
            this.GetJobDetailsSqlDataAdapter.SelectCommand.Parameters["@JobID"].Value = 
                            this.CurrentDailyDataView[(
                            (DataGrid)sender).HitTest(e.X,e.Y).Row]["JobID"].ToString();
            this.GetJobDetailsSqlDataAdapter.Fill(this.dataSet11.GetAJobDetails);        
        }
    }
}

Details

I would like to go over some key points of this method:

if (((DataGrid)sender).HitTest(e.X,e.Y).Row >= 0)

This first line simply tests to make sure we're not selecting the column headers of the DataGrid.

this.dataSet11.GetAJobDetails.Clear();
this.dataSet11.GetClientInfoByID.Clear();

These two lines are the most important since TextBox controls and other single input controls are bound automatically to the first available record of a DataTable. If we never cleared the tables in memory, the method would continue to add the records we want, but continue to show the first record we selected.

if (((double)this.CurrentDailyDataView[(
     (DataGrid)sender).HitTest(e.X,e.Y).Row]["JobTotalHours"]) != 0)

The program automatically fills in the DataView object (linked to the GetJobsByArtistAndDate table of the DataSet) with unscheduled job slots. If we just selected one of these unscheduled jobs, the JobID returned would not match a current job in the database and would crash the code. This "if" test prevents this by checking that the currently selected record's JobTotalHour column is not zero (in other words, it's a scheduled job).

this.GetClientByIDSqlDataAdapter.SelectCommand.Parameters["@ClientID"].Value = 
   this.CurrentDailyDataView[((DataGrid)sender).HitTest(e.X,e.Y).Row]["ClientID"].ToString();

And:

this.GetJobDetailsSqlDataAdapter.SelectCommand.Parameters["@JobID"].Value = 
 this.CurrentDailyDataView[((DataGrid)sender).HitTest(e.X,e.Y).Row]["JobID"].ToString();

Both snippets above do the same thing. They each fill in the GetClientDetailsByID and GetJobDetails SelectCommand parameters using their respective columns of the DataRow in the DataView corresponding to the selected row of the active DataGrid.

((DataGrid)sender)

This little code snippet is what I call "smart coding", which can save you some time and minimize the code you have to write. Basically, all it does is casts the sender parameter of the method to a DataGrid object, which allows us to then access any properties of the DataGrid object (since we know that it was a DataGrid that sent the event).

This does a very important thing since my program actually contains 10 separate DataGrids for two different people for every day of the work week, with jobs for each day loaded in each DataGrid. If I were to use:

Person1MondayJobsDataGrid.HitTest(e.X,e.Y)

It would work fine for person 1's Monday's job's DataGrid, but the other 9 would each have to have their own event handler (that's 54 extra lines of code that's not really needed!!) By casting the sender variable to a DataGrid type, I can reuse the same event handler for every DataGrid that I need this functionality for.

Conclusion

In order to create a Master-Detail relationship using textboxes or other single input controls, a separate DataTable is needed, and each time a record on the master DataGrid is selected, the DataTable has to be cleared and filled with only one record for the textboxes to bind to the correct record.

History

• Nov 25, 2005 - Posted article.
• Nov 29,2005 - Added screenshots.
• Nov 29, 2005 - Revised code to use a DataView object to bind to the DataGrid controls.
• Nov 29, 2005 - Changed event trapped for the DataGrid controls from the CurrentCellChanged event to the MouseDown event (made for quicker response).
• Nov 29, 2005 - Fixed problem with the article's text.
• Nov 29, 2005 - Organized each section to better explain the problem, solution, and details.
• Nov 29, 2005 - Removed any unneeded stuff from the article (thanks John).
• Nov 29,2005 - Added History section.