Master Detail Report: the use of labels vs. grids; Forms Authentication

• Download source - 3.88 Kb

Introduction

The customer/employee information center provides an individual the opportunity to view their records online to look up order status, invoices, bills and other personal information. In each case, the SQL running in the background retrieves the customer/employee record by matching their logon with that stored in the database.

In our example, we are using the self-information center concept to demonstrate the ability to create a more natural business view in ASP.NET using a combination of labels and DataGrid. The typical textbook example uses a drop down box to select the correct report/form, then provides the details in a DataGrid. The resulting form is awkward, and does not always reflect what the user would see on a business form for orders, sales, etc.

Many different approaches are available to handle the data in this type of application. I chose to use the DataSet/cached DataView to demonstrate the use of row indexes. In our example, the SQL returns one row only -- index 0 -- the one that matches the employee or customer logon. We could also have returned many rows, then browsed through the rows (each representing an employee/customer) with a button that increased the row index by +1. A variety of state methods would preserve the current row index from click to click.

We automated the record retrieval process by capturing the user logon using Forms Authentication. In this example, we match the value in User.Identity.Name with the appropriate column containing the logon stored in a customer/employee record. This enables the system to return only those records belonging to the person logging on.

The example displays personal information about the employee (Master) using labels and their sales (detail) using a DataGrid. The employees can browse their sales, sort the sales by customer, etc. As mentioned earlier, the screen can also serve as a template with simple modifications that would permit a supervisor to view sales by employee.

Solution Overview

The solution requires three essential steps:

• An employee logs on to the system using Forms Authentication, and the logon is stored in User.Identity.Name.

  Note: Forms Authentification is a topic in its own right and beyond the scope of this tutorial. However, a sample logon and registration page is available.

• The Master section of the Master Detail report is created by:
  1. Matching User.Identity.Name with a value stored in a employee/customer table.
  2. Information is retrieved and placed in a cached DataView.
  3. The data is displayed using labels.
• The Detail section of the report is retrieved with a custom query (just for fun), and presented in a DataGrid. Many alternatives are available to retrieve the data other than using a custom SQL statement including the creation of an appropriate view, stored procedure, etc.

Pre-code Steps

The Northwind Employees table used in the demonstration must be modified to add a column to store the logon. For this example, we used the name u_UserName, varchar (30).

In the demonstration, we have not added any error checking to keep it simple...

Using the Code

At this point, we can focus our attention on the ASP.NET code that creates the Master Detail Report. Comments have been included to explain the coding:

' Add the appropriate Namespaces for our project 

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

    <script runat="server">

    '--------------------------------------------------------------
    ' Create & manage the cache that handles the Master section
    '--------------------------------------------------------------
    
        '---------------------------------------------------------
        'con for Connection  'dad for DataAdapter 'dst for DataSet
        'dvw for DataView    'cmd for command     'lbl for label
        '---------------------------------------------------------
    
         Dim MyCmd as String
    
         Sub Page_Load ( s As Object, e As EventArgs )
    
            If Not Page.IsPostBack Then
                 BindGrid( )
            End If
    
            ' Create our Variables
            Dim conNorthwind As SqlConnection
            Dim dadEmployees As SqlDataAdapter
    
            Dim dstEmployees As DataSet
            Dim dvwEmployees As DataView
    
            ' Set up the DataView cache
            dvwEmployees = Cache( "Employees" )
            If dvwEmployees Is Nothing Then
              conNorthwind = New SqlConnection( "server=(local);_
         database=Northwind;trusted_connection=true" )
              dadEmployees = New SqlDataAdapter( "Select * From Employees _
                 where u_UserName = '" & User.Identity.Name & "' ", conNorthwind )
    
              dstEmployees = New DataSet()
              dadEmployees.Fill( dstEmployees, "Employees" )
    
              dvwEmployees = dstEmployees.Tables( "Employees" ).DefaultView()
              Cache( "Employees" ) = dvwEmployees
            End If

    ' Display the information using labels
              lblEmpID.Text = dvwEmployees( 0 ).Row( "EmployeeID" )
              lblFirstName.Text = dvwEmployees( 0 ).Row( "FirstName" )
              lblLastName.Text = dvwEmployees( 0 ).Row( "LastName" )
              lblPhone.Text = dvwEmployees( 0 ).Row( "HomePhone" )
              lblEmail.Text = dvwEmployees( 0 ).Row( "EmailAddress" )
              lblNotes.Text = dvwEmployees( 0 ).Row( "Notes" )
    
          End Sub

Step 1

We create a connection object that specifies the database that we wish to use. The DataAdapter specifies the work to be done, i.e., execute our SQL statement using the connection object.

The name conNorthwind refers to the connection object, and is set to the Northwind database using a trusted connection. In the connection string "server=(local);database=Northwind...", you will need to replace the word local with the name of your server, and Northwind with the name of the database where your data resides.

Step 2

We specify the DataSet, fill it with the requested data, name the DataView and cache the retrieved rows.

Step 3

We display the data by selecting the row at index 0. Since only one row is retrieved, we do not specify a looping mechanism to loop through the rows. Again, we also do not specify any error trapping, e.g., row boundary checks, but would definitely add these to a production example.

then ...

 '--------------------------------------------------------------
     ' Begin the Datagrid section that will handle Report Details
     '--------------------------------------------------------------


    ' Set up Page Index functions
     '--------------------------------------------------------------
      Sub DataGrid_SetPage(Sender As Object, e As DataGridPageChangedEventArgs)
          DataGrid1.CurrentPageIndex = e.NewPageIndex
          BindGrid()
      End Sub

      Sub DataGrid_IndexChanged(sender As Object, e As EventArgs)
         End Sub


     ' Set up application to handle sorting
     '---------------------------------------------------------------
        Property Sort_Field() As String
         Get
             Dim obj As Object = ViewState("Sort_Field")
             If obj Is Nothing Then
                 Return String.Empty
             End If
             Return CStr(obj)
         End Get

         Set(ByVal Value As String)
             ViewState("Sort_Field") = Value
         End Set
        End Property

     Sub DataGrid_Sort(Sender As Object, e As DataGridSortCommandEventArgs)
         DataGrid1.CurrentPageIndex = 0
         Sort_Field = e.SortExpression
         BindGrid()
     End Sub


     '---------------------------------------------------------------
     ' Bind the Datagrid
     '---------------------------------------------------------------
      Sub BindGrid()

        ' Create a custom SQL statement to get the data
         Dim CmdText As String

         If Sort_Field = String.Empty Then
             CmdText = "select * from Northwind.dbo.Orders" & _
               " where exists (Select * From Employees where " & _
               "Employees.EmployeeID = Orders.EmployeeID and " & _
               "Employees.u_UserName = '" & User.Identity.Name & _
               "' ) order by CustomerID"
             MyCmd = CmdText
         Else
             CmdText = "select * from Northwind.dbo.Orders" & _
                " where exists (Select * From Employees where " & _
                "Employees.EmployeeID = Orders.EmployeeID and " & _
                "Employees.u_UserName = '" & User.Identity.Name & _
                "' ) order by " & Sort_Field
             MyCmd = CmdText
         End If

         'Set the SQLDataAdapter to connect to the Northwind Order table
         ' & use our custom SQL Statement
         Dim conNorthwind2 As New SqlConnection("server=(local);" & _
                 "database=Northwind;trusted_connection=true")
         Dim dadOrders As New SqlDataAdapter(MyCmd, conNorthwind2)

         'Create dstOrders (the Order Dataset), fill it, and bind it.
         Dim dstOrders As New DataSet()
         dadOrders.Fill(dstOrders)

         DataGrid1.DataSource = dstOrders
         DataGrid1.DataBind()

     End Sub


'-----------------------------------------------------------------------
'End of Script, Begin HTML Section Below
'-----------------------------------------------------------------------

How to use it

The source code ZIP file includes an ASP.NET page that we have pre-tested on our server... however, there are some changes that must be made prior to using the code:

1. Alter the Employees table to add the u_UserName column.
2. Set up your own logon page, or use the one identified earlier.
3. The Sales-Report.aspx page should be placed in your web directory.
4. A spacer gif called shim.gif (enclosed in ZIP) should be placed in the images subdirectory.

Conclusion

The application enables a user to logon, and view a Master Detail report of their personal information and the orders that he or she has taken. Master level data is placed in labels to create a natural looking form, and Detail level data is placed in a DataGrid.

The example is for purposes of illustration only... there are many ways to retrieve the data from a SQL Server database. Downloading the data to the client side allows a number of employees to work with their sales without maintaining an active cursor.

We have tried to balance an overview with the necessary details to build this site. From experience, we probably were too detailed for some, and not enough for others.