Advisory Services   |   IT Solutions   |   Free Articles   |   Training   |   Resource Center

• Download source Code - 3k

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 employee 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 3 essential steps:

• An employee logons to the system using Forms Authentication, and the logon is stored in User.Identity.Name.
  
  Note: Forms Authentication is a topic in its own right, & 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 & 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 retrieving 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.

 	'--------------------------------------------------------------
         ' 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
    '-----------------------------------------------------------------------