To read the article online, visit http://www.4GuysFromRolla.com/articles/082708-1.aspx

Creating a Dynamic Data-Driven User Interface (Part 2)

By Scott Mitchell


Introduction


This article is the second installment of a four-part series that examines how to build a data-driven web applications that offers dynamic user interfaces. Over the course of this article series we will build a complete and functional web application with a dynamic, data-driven user interface. Specifically, the demo application is a fictional website used by numerous law firms to manage their clientele.

The application uses both a fixed and dynamic data model for law firms to manage their clients. The Clients table contains the fixed attributes for a client and is composed of columns like ClientId, CustomerId, FirstName, and LastName. All law firms have these fixed attributes available to them. The dynamic data model allows each law firm to define custom attributes for their clientele. For example, a law firm that specializes in personal injury might need to capture client information that is not needed for a law firm that specializes in family law. The custom client attributes for each law firm are stored in a database table named DynamicAttributesForClients.

Part 1 examined the scope of the project and created the data model. In this installment we create the web pages used by the law firms to define the custom client attributes. Read on to learn more!

Getting Started...


When we left off in Part 1 our web application consisted solely of a database in the App_Data folder that implemented both our application's data model and the schema necessary for the SqlMembershipProvider. In the demo available for download at the end of this article you'll find that I have added many user interface aspects, including:
  • A master page that implements a CSS-based website design created by John Zaitseff. For more information on this design, see http://www.opendesigns.org/preview/?template=1700.
  • A site map file (Web.sitemap) with links to the site's various sections.
  • A login page, where visitors can log in to the website.
  • A Customers folder that has been configured so that only authenticated users can visit the ASP.NET pages within. (For more information on how to restrict certain web pages from anonymous users check out the User-Based Authorization tutorial in my Forms-Based Authentication, Membership, and Roles tutorial series.) In this folder you'll find the page DefineClientAttributes.aspx, which is where a customer can create and manage custom client attributes.
This article examines the DefineClientAttributes.aspx page in detail. This web page enables the visitor to add new custom client attributes as well as edit and delete existing attributes.

Determining What Customer the Currently Logged On User is Associated With


When visting The DefineClientAttributes.aspx page we need to list the custom client attributes. Because these attributes are specific to a particular customer we need to determine what customer the currently logged on user is associated with. This information is stored in the ExtendedUserInfo table, which we created in Part 1.

There are several scenarios where we'll need to determine the currently logged on user's associated CustomerId value. For instance, we need to determine the CustomerId value in DefineClientAttributes.aspx when listing the custom attributes and when adding a new custom attribute to the database. Similarly, will need the CustomerId value when generating the dynamic, data-driven user interface, as we need to know what attributes to display in the UI. Therefore, let's create a helper method that returns the CustomerId value for the currently logged on user.

I have created such a method in a class named Helpers, which is located in the App_Code folder. This class has a single shared function, GetCustomerIdForLoggedOnUser, which can next to the database and queries the ExtendedUserInfo table, returning the currently logged on users associated CustomerId value. The code for this class follows:

Imports System.Data.SqlClient

Public Class Helpers
   'Returns the CustomerId associated w/the currently logged on user
   Public Shared Function GetCustomerIdForLoggedOnUser() As Guid
      Dim UserIdValue As Guid = Guid.Empty
      If Membership.GetUser() IsNot Nothing Then
         UserIdValue = Membership.GetUser().ProviderUserKey
      End If

      Using myConnection As New SqlConnection
         myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("LawFirmConnectionString").ConnectionString

         Dim myCommand As New SqlCommand
         myCommand.Connection = myConnection
         myCommand.CommandText = "SELECT CustomerId FROM ExtendedUserInfo WHERE UserId = @UserId"
         myCommand.Parameters.AddWithValue("@UserId", UserIdValue)

         myConnection.Open()
         Dim customerId As Guid = CType(myCommand.ExecuteScalar(), Guid)
         myConnection.Close()

         Return customerId
      End Using
   End Function
End Class

The currently logged on user's UserId value is obtained by Membership.GetUser().ProviderUserKey. The Membership.GetUser() method returns information about the currently logged on user. The ProviderUserKey property retrieves the UserId value. This value is used for the value of the @UserId parameter in the above SELECT statement.

Viewing a Customer's Dynamic Client Attributes


The DefineClientAttributes.aspx page includes a GridView control that lists the dynamic client attributes that belong to the customer associated with the currently logged in user. This GridView is populated via a SqlDataSource control named CustomAttributesDataSource. This data source control's SelectCommand property is assigned to the following SELECT query:

SELECT DynamicAttributesForClients.DynamicAttributeId, DynamicAttributesForClients.DataTypeId, DynamicAttributesForClients.AttributeName, DynamicAttributesForClients.SortOrder, DynamicAttributeDataTypes.DataTypeName

FROM DynamicAttributesForClients
   INNER JOIN DynamicAttributeDataTypes ON
      DynamicAttributesForClients.DataTypeId = DynamicAttributeDataTypes.DataTypeId

WHERE (DynamicAttributesForClients.CustomerId = @CustomerId)

ORDER BY DynamicAttributesForClients.SortOrder, DynamicAttributesForClients.AttributeName

Note that it returns all records from the DynamicAttributesForClients table for a particular CustomerId value. It brings back the name of the data type for each attribute by joining on the DynamicAttributeDataTypes table. Finally, it sorts the results by the SortOrder column in ascending order, breaking ties by sorting alphabetically on the AttributeName column.

We need to specify a value for the @CustomerId parameter. This can be done by creating an event handler for the SqlDataSource control's Selecting event. The Selecting event fires each time the data source control goes executes its SelectCommand against the database. The following event handler code assigns the value of the @CustomerId parameter to the CustomerId value associated with the currently logged on user:

Protected Sub CustomAttributesDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles CustomAttributesDataSource.Selecting
   e.Command.Parameters("@CustomerId").Value = Helpers.GetCustomerIdForLoggedOnUser()
End Sub

The following screenshot shows the GridView when viewed through browser. As you can see, the GridView lists the custom client attributes for the appropriate customer.

The customer's  current dynamic client attributes.

For more information on using the Selecting event to programmatically specify values for the parameters in the SelectCommand see the Examining the Data Source Control's Events article in my Accessing and Updating Data in ASP.NET article series.

Adding a New Dynamic Client Attribute


In addition to viewing the dynamic client attributes, visitors need to be able to add new attributes. This can be accomplished in a number of ways. One of the easiest ways to insert data into a database from a web page is to use a DetailsView control in tandem with a data source control that is configured to support inserting. The CustomAttributesDataSource data source control includes an InsertCommand property that specifies the INSERT statement to use to add a new record to the DynamicAttributesForClients table:

INSERT INTO [DynamicAttributesForClients] ([CustomerId], [DataTypeId], [AttributeName], [SortOrder])
VALUES (@CustomerId, @DataTypeId, @AttributeName, @SortOrder)

With this INSERT statement in place we can configure the DetailsView to support inserting. Moreover, the DetailsView can be permanently rendered in insert mode by setting its DefaultMode property to Insert.

The values for the new attribute's DataTypeId, AttributeName, and SortOrder need to be specified by the visitor. The CustomerId value, however, is based on the currently logged on user and can be populated programmatically via the GetCustomerIdForLoggedOnUser method in the Helpers class. To set the CustomerId value create an event handler for the DetailsView control's ItemInserting event. This event fires at the start of the inserting workflow and provides an opportunity for developers to customize the values that will be used when adding a new record. Simply set the CustomerId value to the value returned by the GetCustomerIdForLoggedOnUser method:

Protected Sub dvAddAttribute_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles dvAddAttribute.ItemInserting
   e.Values("CustomerId") = Helpers.GetCustomerIdForLoggedOnUser()
End Sub

By default the DetailsView's inserting interface is composed of a TextBox control. While a vanilla TextBox is sufficient for some inputs, it is less than ideal for those inputs that require some sort of validation logic or would be best captured using an alternative data entry Web control. For example, the DataTypeId field is a foreign key to the DynamicAttributeDataTypes table. The inserting interface should let the visitor choose the new attribute's data type from a drop-down list rather than requiring them to type in an ID value. Likewise, the inserting interfaces for the AttributeName and SortOrder inputs should include validation logic since these are both required fields.

the following screenshot shows the DetailsView control in action. Validation controls are present to ensure that the user enters a name and sort order for each new attribute, and that this sort order is a numeric value between 1 and 100.

A visitor may add a new custom attribute.

For more information on inserting data using ASP.NET's data source controls be sure to read Inserting Data. For more on how to customize the DetailsView control's inserting interface, see Adding Validation Controls to the Editing and Inserting Interfaces and Customizing the Data Modification Interface.

Don't Forget to Remove the Type Attribute for Declarative Parameters of Type uniqueidentifier
The SqlDataSource control's Configure Data Source wizard makes it easy to automatically generate INSERT, UPDATE, and DELETE statements for a database query. However, one issue that you must keep an eye out for is that the parameters generated by the wizard for columns of type uniqueidentifier have their Type property is set to Object. If this scenario unfolds when attempting to insert, edit, or delete data an exception will be thrown with the message: "Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query."

To fix this problem simply remove the Type attribute from the parameter's declarative markup. In other words, manually modify the parameter syntax from:

<asp:SqlDataSource ...>
   <InsertParameters>
      <asp:Parameter Name="CustomerId" Type="Object" />
      ...
   </InsertParameters>
</asp:SqlDataSource>

To: <asp:SqlDataSource ...>
   <InsertParameters>
      <asp:Parameter Name="CustomerId" />
      ...
   </InsertParameters>
</asp:SqlDataSource>

Refer to Inserting with a SqlDataSource Using uniqueidentifier Parameters for more information on this bug and workaround.

Editing and Deleting Dynamic Client Attributes


Currently a single SqlDataSource control is used for selecting and inserting data into the DynamicAttributesForClients table. We can extend this control to also handle editing and deleting. Start by configuring the SqlDataSource control's UpdateCommand and DeleteCommand properties as follows:

-- The DeleteCommand property
DELETE FROM [DynamicAttributesForClients]
WHERE [DynamicAttributeId] = @DynamicAttributeId

-- The UpdateCommand property
UPDATE [DynamicAttributesForClients] SET
   [AttributeName] = @AttributeName,
   [SortOrder] = @SortOrder
WHERE [DynamicAttributeId] = @DynamicAttributeId

Note that the UPDATE statement does not allow for an attribute's data type to be modified. This restriction is in place because once an attribute is in use and has values for clients changing the data type can cause the existing values to become corrupt. Consider a custom attribute named "Reason for Law Suit" of type String. Imagine that a value for this attribute has been specified for most of the clients for a particular law firm. Now imagine that the attribute type is changed to Boolean. When displaying information for a particular client how would the system translate a value like "Suing for damages to automobile" - a perfectly valid value when the attribute had a data type of String - a Boolean value?

Another potential issue can arise if the attribute's name can be modified, which is allowed in this demo application. Consider a Boolean attribute named "Active Client," and that dozens of clients have this value set to either True or False. Now imagine that a user changes the name of this attribute from "Active Client" to "Inactive Client."

One option would be to allow a user to modify the data type (or name) for any attribute that does not have associated values. I leave this as an exercise to the interested reader.

Once these command properties have been set you can configure the GridView control to support built-in editing and deleting. Doing so adds a CommandField to the GridView control. Like with the DetailsView, we need to customize the GridView's editing interface to include the necessary validation logic for the AttributeName and SortOrder inputs and a drop-down list for the DataTypeId field.

That's all there is to it. Because the data shown in the grid is already filter based on the currently logged on user's CustomerId value there is no need to include this field in the UPDATE or DELETE statements. The following screenshot shows the customized editing interface and action.

The GridView enables visitors to  edit the name and sort order for existing custom attributes.

Assuming you configured the foreign key constraint between the DynamicAttributesForClients.DynamicAttributeId and DynamicValuesForClients.AttributeId columns to cascade deletes when a visitor deletes a custom client attribute all associated values for that custom attribute are automatically deleted. If you do not configure the foreign key constraint to cascade deletes then a foreign key constraint violation will occur if a visitor attempts to delete a custom client attribute that has associated values. This will result in a Yellow Screen of Death unless you proactively prohibit visitors from deleting attributes that have associated values or if you explicitly delete the associated values prior to deleting the attribute itself.

Looking Forward...


At this point we have created a data model and built the shell of a website to allow to log in and manage their custom client attributes. In the next installment we will turn our attention to the most complex task in building dynamic, data-driven user interfaces: programmatically constructing the user interface based on dynamic attributes defined by the customers.

Until then... Happy Programming!

  • By Scott Mitchell


    Attachments


  • Download the code used in this article

    Further Reading


  • Creating a Dynamic Data-Driven User Interface (Part 1)
  • Forms-Based Authentication, Membership, and Roles Tutorials (includes VB & C# versions!)
  • Examining ASP.NET 2.0's Membership, Roles, and Profile
  • Accessing and Updating Data in ASP.NET
  • Article Information
    Article Title: ASP.NET.Creating a Dynamic, Data-Driven User Interface (Part 2)
    Article Author: Scott Mitchell
    Published Date: August 27, 2008
    Article URL: http://www.4GuysFromRolla.com/articles/082708-1.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers