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 TextBox
es, DateTimePicker
s, etc.) instead of multiple input controls such as ComboBox
es and ListBox
es.
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 TextBox
es 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 TextBox
es:
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:
- Add three separate
SqlDataAdapter
s (one for the jobs, one for the client's info, and one for the job's details). - Generate a Strongly-Typed DataSet (VS automation rocks!).
- Add a
DataView
object (for sorting purposes) and link it to the GetJobsByArtistAndDate table of the DataSet
. - Add a
DataGrid
control and link the DataSource
properties to the DataView
object. - 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 DataGrid
s 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.