Master/Detail in a single form
This article examines:
This article uses the following technologies:
· Master/Detail registers;
· Master/detail with GridView and DetailsView.
Visual Studio 2005, C#, ASP.NET and SQL Server.
As soon as I started the development of my first project in .NET for the Web, I came across with a situation that, although easily resolved in the desktop model, I did not yet have a satisfactory answer for in the Web environment.
The question was: “How to present data of the master/detail type in a single form, in a simple and practical manner for the users”. The models which I came across always referred to different forms to execute this task, that is, a form to keep the data of the master register and another to report the details, which, operationally, did not please the users much.
This article presents the solution which I found for such a situation, uniting design and functionality in a single form. Without wasting any more time, let us move on to what matters.
Creating the project
Create a Web site project in the Visual Studio 2005, through the menu File>New> Web Site. Select the item ASP.NET Web site; in Language, select Visual C# and click OK (Figure 1). Open the page Default.aspx and modify to the Design mode.
Figure 1. Creating a new Web Site
Creating the database
In the Solution Explorer, right click over the App_Data folder and select the Add New Item option. Select the SQL Database item, name it “masterdetail.mdf” and click over Add (Figure 2). After the creation of the database, we will include three tables.
Figure 2. Creating the database of the application
Gain access to the Database Explorer, right click over the option Tables and select the Add New Table item. Include the fields according to Figure 3.
Figure 3. Discipline Table of the database
Store the table with the name of “Discipline”. Expand the Tables option, right click over the Discipline table and select the Show Table Data option. Include the following data: “Portuguese”, “English”, “Math” and “Science”.
Using the same technique described previously, create a table called “Teacher” and include the fields according to Figure 4. Remember to configure the key field to Is Identity.
Figure 4. Teacher Table of the database
And, finally, create a new table and include the fields according to Figure 5. Also configure the key field to Is Identity. Store the table with the name “TeacherDiscipline”.
Figure 5. TeacherDiscipline Table of the database
Gain access to the Table Designer> Relationships menu. In the Foreign Key Relationships form click Add. Open Tables and Colunns Specification and create the relationship between the tables Discipline and TeacherDiscipline. Also create the relationship between the tables Teacher and TeacherDiscipline.
In the relationship FK_TeacherDiscipline_Teacher modify the INSERT and UPDATE Specification/Delete Rule property to Cascade and click the Close button. This will make a removal or an update in a master table to affect the related registers in the other table. After the creation of tables we will have the database structure shown in Figure 6.
Figure 6. Structure of the database od the application
Configuring the connection with the Teacher table
Add a SqlDataSource to the form and modify its name to “SqlTeacher”. In the Smart Tag, gain access to the option Configure Data Source, where the database connection setup form will be displayed.
Select the masterdetail.mdf from the list of available connections and click in Next. Select Yes, save this connection as, inform the name of the connection as “ConnectionString” and click in Next.
In Configure the Select Statement, check the Specify columns from table or view option and in Name, select the Teacher table. In Colunns, check the TeacherID and Name fields.
Click over Advanced, check the Generate INSERT, DELETE and UPDATE statements option and click OK. Conclude the wizard.
Drawing the Master form
Double click over a blank space of the page and create an operation attribute for the class, this attribute will be used to control the operation which is being performed by the user:
public partial class _Default : System.Web.UI.Page
static String operation;
Also add a DetailsView. Through the control’s Smart Tag, gain access to the Auto Format option and change the form’s formatting to Professional.
Still in DetailsView gain access to Choose Data Source and select SqlTeacher. Check the options: Enable Inserting, Enable Editing and Enable Deleting. Modify the following properties of the DetailsView1: AllowPaging to true, in PagerSettings>Mode choose NextPreviousFirstLast and, in Width, type “300px”.
Double click over the ModeChanging event and type the following code:
The former code saves the operation which the user is performing. This data will be used by the DataBound method to make the using of the example operational.
Return to the design mode and, in the DataBound event of the DetailsView, type the code in Listing 1.
Listing 1. Code of the DataBound event of the DetailsView
if (DetailsView1.PageCount == 0)
if (operation == "Insert")
DetailsView1.PageCount - 1;
This code is divided into two parts, where, in the first part, we change the DetailsView1 mode into Insert, in case there are no records in the table. In the second part, we change the DetailsView1 mode into Edit, just after the inclusion of a new teacher, so that the user can include disciplines related to that same teacher.
Configuring the connection with the TeacherDiscipline table
Return to the design mode and add another SqlDataSource to the form, giving it the name “SqlTeacherDiscipline”. Perform the same previous technique and select the DisciplineTeacherID, TeacherID and DisciplineID fields from the TeacherDiscipline table.
Click the Where button and configure the according to that illustrated in Figure 7, where we will add a parameter for the query.
Figure 7. Parameter configuration
After that, click the Add button and then OK. Click the Advanced button, check the Generate INSERT, DELETE and UPDATE statements option and click OK. Conclude the wizard.
Gain access to the InsertQuery property of the SqlTeacherDiscipline and configure the insertion parameters in agreement to Figure 8.
Figure 8. Configuring the insertion parameter
Obtaining the name fo the discipline
Access the Smart Tag of the SqlTeacherDiscipline, select the Configures Data Source option and click the Next button. In the Configure the Select Statement page, select Specify a custom SQL statement or stored procedure and click Next. Modify the SQL command into the code in Listing 3. Click the Next button, Next and, after that, Finish.
Listing 3. Modifying the SQL command of the SqlTeacherDiscipline
INNER JOIN Discipline ON
WHERE (TeacherDiscipline.TeacherID = @TeacherID)
Configuring the connection with the Discipline table
Add another SqlDataSource to the form, giving it the name “SqlDiscipline”. Carry out the same previous technique and select the SubjectID and Description fields of the Discipline table.
Including data about the disciplines
Click over the Smart Tag of the DetailsView and select the Add New Field option. In the editor, select the field of the TemplateField type in Choose a field type and in Header text type “Discipline”. Click OK.
Select the Edit Fields option in the Smart Tag, modify the order of the Discipline field so that it appears before the DetailsView commands (Figure 9) and, after that, change the InsertVisible property into False. Close the editor clicking OK.
Figure 9. Modifying the order of the fields in the DetailsView
Drawing the Detail form
Select the Edit Templates option in the Smart Tag of the DetailsView. In Display, select field - Discipline. Add a GridView to the ItemTemplate container. Modify the formatting of GridView to Professional.
In Choose Data Source, select SqlTeacherDiscipline. Click Edit Columns in the Smart Tag and remove the TeacherID and DisciplineID columns. Check the options: Enable Paging and Enable Sorting.
In the EditItemTemplate container, add a DetailsView and modify the DefaultMode property into Insert. Modify the format to Professional and, in Choose Data Source, select SqlTeacherDiscipline.
Click Edit Fields and remove the TeacherID and Description columns. Select the DisciplineID field and click Convert this field into TemplateField. Check the Enable Inserting option.
Click Edit Templates of the DetailsView and, in the Display box, select InsertItemTemplate. Erase the content of the container and insert a DropDownList. Select SqlDiscipline for the Choose Data Source of the control. In the Select a data field to display in DropDownList box, select Description and, in Select a data field the value in DropDownList, choose DisciplineID. Click OK to close the editor.
Click Edit DataBindings and, in Bound to, select DisciplineID. Click End Template Editing until the closing of the editor of templates.
Add a GridView just below the DetailsView (inside the EditItemTemplate container). Select the Professional format and, in Choose Data Source, select SqlTeacherDiscipline. Click Edit Columns, remove the TeacherID and DisciplineID columns and click OK. Check the options: Enable Paging, Enable Sorting and Enable Deleting (Figure 10).
Figure 10. EditItemTemplate container Layout
Close the editor (of the DetailsView1), clicking End Template Editing. Our form will have to be similar to Figure 11. Now, we can execute the project and verify the results obtained.
Figure 11. Form Layout
Running the example
When executing the example, for the first time, we verify that the form is presented in the register inclusion mode, since, the Teacher table is empty. Type the name of a teacher and click Insert as Figure 12 shows.
Figure 12. New register inclusion form
Notice that the form changes into edit mode, so that we can relate the disciplines ministered by the teacher in question (Figure 13).
Figure 13. Disciplines of the teacher
After including the desired disciplines and clicking the Update of the master form, we have the following situation, illustrated in Figure 14.
Figure 14. Final form
We must highlight that the inclusion of the disciplines, for a certain teacher, will only be possible after that same teacher has been stored in the Teacher table, since we need the TeacherID duly generated for us to include the related disciplines.
This article presented the use of Master/Detail relationships in the development of Web pages, using a single form. See you next time.