Advertisment

Master Detail Forms in ASP.NET 2.0

author-image
CIOL Bureau
Updated On
New Update

Master detail forms have been one of the major requirements in business applications. Since business logic in databases have to be enforced to meet performance and scalability requirements, most applications follow the norm of having data stored in master detailed format. The most complex issue in creating reports is the linking of the two controls so that they are synchronized whenever the data in the first control is changed when it is selected. With the desktop applications it has been somewhat a little better because the data is persisted in the application through out and it is easy to retrieve any time. The most daunting task is persisting the data in the first control and then refreshing the related data in the second control in a web page.

Advertisment

Earlier the issues related to web pages was that the data had to be retrieved and populated on the second control while the selected data on the first control had to be persisted because the selected row was the only indicator to show the relationship between the first and second control. And it was more daunting in the case of grid controls because the selected row was unable to be persisted on a post back of the page.

With the advent of ASP.NET 2.0 development has been made better and the complex tasks like the one explained above has also been taken care of and it is now a breeze to develop complex master detailed grid reports. And the interesting part is that this complex requirement has been taken care of without even writing a single line of code.

We will now proceed with a walk through where this feature of master-detail report with grids is developed. But before that, I will explain what is master-detail and what is unique about that. Let us see with an example demonstrated with the Northwind database found in SQL Server.

Advertisment

Now let us see the two tables Categories and Products. Open up Query Analyzer in SQL Server and then connect to the Northwind database.

use northwind



select * from dbo.Categories


select * from dbo.Products

Now you will see that the table Categories contains the category id, category name and description. The table Products contains the product details as well as the category id. That means the tables are related with the category id.

Advertisment

Now we would like to query the data in the products table that is related to a particular item in the Categories Table. Let us say that we want to see all the items in the Product table that is related to the item 'Beverages' in the Categories table.So the query has to be written like this below to achieve the desired result.

select Productname,unitprice,unitsinstock from dbo.products where exists (select * from Categories where categoryName = 'Beverages' and Categories.CategoryID = Products.CategoryID )

Now let us visualize how the report has to be written for this kind of query. There has to be 2 grids. The first grid will show all the data from the Categories table so that when the user selects any row in the Categories table, the corresponding details in the Products table has to be displayed and also the row that was selected in the Categories table should be preserved so that the user will know the category for which the products grid is displayed.

Advertisment

Let us do the following. We will be using Visual Studio .NET 2005 Beta 1 and the .NET Framework 2.0 to create this requirement. We will create a web application and then create the report on the default page.

Open Visual Studio.



On the File menu, click 'NewWeb' Site.The New Web Site dialog box appears.

Under Project Types, click the programming language you prefer to work in. Let us use C#. But this doesn't make any difference for now because we are not going to write any code and show how ASP.NET has removed the complexity for us. The programming language that is chosen will be the default for the Web site, but ASP.NET 2.0 allows to set the programming languages for each page individually. Now pages developed with different languages can be assembled to make a web site.Under Visual Studio installed templates, click ASP.NET Web Site. Enter the location of the website in the dialog box and then press OK.

Advertisment

Visual Studio creates the folder and a new page named

Default.aspx.

Now we will have to create a connection using the Server Explorer. This connection will be used for the grids. Now we will create a connection to SQL Server. If the Server Explorer tab is not visible in Visual Studio, in the View menu, click Other Windows, and then click Server Explorer.

In Server Explorer, right-click Data Connections and choose Add Connection.



The Connection Properties dialog box is displayed.


Enter or select the name of your SQL Server.


Select Use a specific user ID and password.


Enter a user name and password.


Select Save my password.


Select Northwind as the database.


Click Test Connection, and when you are sure that it works, click OK.


When you are finished, click OK.






Advertisment

The connection is added to Server Explorer. This completes the creation of the connection to the SQL Server.

Now we will show the details of the 2 tables on the same page. The first grid will populate data from the Categories table and when any row is selected the corresponding details from the Products table will be displayed in the second grid.

Open the default page and then switch to the Design view.

Advertisment

Type Master-Detail Page in the page and format the text as a heading. Or if you prefer you can drag and drop a label on the page and then make the appropriate decorations.

Open the toolbox and you will see that the controls have been grouped together. This makes it easier to pick the controls according to the category. Expand the Data group and then drag a GridView control onto the page. When the GridView is dropped you will see a smart tag panel. This is one of the new features in Visual Studio .NET 2005 and helps a lot in completing tasks related to that control.

In the smart tag panel, in the Connect to Data Source list click and then configure a data source for the GridView control.



From the Select a data source list, select "< New Data Source >".

Select Database.



Click OK.

From the Choose a data connection list, select the connection that was created and stored earlier in the walkthrough.

Click Next.

From the Name list under Table or View Options, select Categories.



In the Columns box, check CategoryID and CategoryName.


Click Next and then click Finish.


Select the GridView control and in the smart tag panel, choose Edit Columns.


The Fields dialog box is displayed.


Under Available Fields, open the Command Field node, choose Select, and then click Add to add it to the Selected fields list.


In the Selected Fields list, select the new button and then in the property grid, set its SelectText property to Details.


Click OK to close the Fields dialog box.


A new column with a Details hyperlink is added to the grid.


Select the GridView control and in the Properties window, ensure its DataKeyNames property is set to
CategoryID.








This specifies that when you select a row in the grid, ASP.NET can find the key of the currently displayed Categories record in a known location. Earlier this was a tedious task in ASP with a lot of programming to be done. Now this is achieved with just a few clicks.

Now let us preview the GridView in the browser and see how it looks like. Press F5 and you will be prompted whether you want to add the 'Web.Config' file to the project. Go ahead and say yes to it and soon you will see the browser window with the Categories table displayed. This completes the first step.Now we will go to the second step i.e. display a GridView for the Products table and configure it in such a way that only the details for the selected item in the Categories Grid is displayed.

The next step is to add the 'DetailsView' control that will display the detail records -- the products associated with the selected category. The 'DetailsView' control will use a different SQL query to get its data, so it requires a second data source control.We will configure a query to display related records.

From the Data group of the Toolbox, drag a 'DetailsView' control on the page.Now we will configure it to use a second data source control.

From the Select a data source list, select < New Data Source >.



Select Database.


Click OK.

In the Choose a data connection list, click the connection you created and stored earlier in the walkthrough.

Click Next.



From the Name list under Table or View Options, select Products.


In the Columns box, check Productname, unitprice and unitsinstock


Click WHERE.

The Add WHERE Clause dialog box is displayed:

From the Column list, select CategoryID.



From the Operator list, select =.


From the Source list, select Control.


Under Parameter Properties, in the Control ID list, select GridView1. The query for the second grid will get its parameter value from the selection in the first grid.


Click Add and then click OK to close the Add WHERE Clause dialog box.


Click Next.


In the Preview page, click Test Query.


The wizard displays a dialog box that prompts you for a value to use in the WHERE clause.


Type any numeric value in the box and click OK. Let us enter 3 for example.


The products records for category 3 are displayed.


Click Next and then click Finish.


In the smart tag panel, check Enable Paging.


This will allow you to scroll through individual product records.










By default the 'DetailsView' control allows to page through records by clicking a page number. Only one record is displayed at a time and this control is new in ASP.NET 2.0.Now let us preview how this looks like.

Press CTRL+F5 to run the page.



In the grid, select a category.


The DetailsView control displays a product associated with that category.


It is possible to navigate through the DetailsView control with the pager links.


In the grid, select a different category.


The products for that category are displayed in the 'DetailsView' control. Now it is seen that the master-detail report is achieved through the 2 controls.It is seen that only one record is displayed at a time for the selected category in the 'DetailsView' control and certainly it doesn't look right. We would like to see all the details at a time. To do this we will repeat the procedure as explained above but with the 'GridView' control.Now it is seen that the right view is achieved. When a single row is selected in the Categories table the corresponding details from the Products table is displayed in the lower grid.

Figure 1. Data from the Categories table seen when the page is loaded. This is the master form.

The final result is seen in the image below. The first image shows the categories table. When a row is selected the details are seen in the second image. Note that the selected row is seen in a different colour which is definitely the big selling point of the report.        

Figure 2. When a row is selected in the categories table the corresponding details are seen in the detail form.

tech-news