Creating a Dynamic Data-Driven User Interface (Part 3)
By Scott Mitchell
Creating a Dynamic, Data-Driven User Interface
This article is one in a series of articles on building a web application that supports dynamic, data-driven user interfaces.
Part 1 - provides an overview of flexible,
dynamic data models and user interfaces, and looks at building the necessary database tables for implementing a dynamic, data-driven user interface.
Part 2 - creates the administrative interface for defining the dynamic
data model attributes.
Part 3 - builds the screens for managing the fixed and dynamic client attributes.
Part 4 - discusses and implements enhancements to the dynamic, data-drive user interface.
Introduction
This article is the third installment of a four-part series that examines how to build a data-driven web applications that offers dynamic
user interfaces. Over the past two articles we created a sample web application that allows for numerous law firms to log in to the site and
manage their clientele. The application's data model contains a Clients table that defines the fixed attributes for a client -
ClientId, CustomerId, FirstName, LastName, and so on. All law firms have these fixed
attributes available to them. Each law firm can also define dynamic attributes. For example, a law firm that specializes in personal injury might
need to capture client information like type of injury, whether the injury occurred on a job site, and so forth. 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, while
Part 2 showed how to allow customers (law firms) to define their custom client
attributes. In this installment we create the web pages for managing clients. This includes two pages: one page to create new clients and manage
their fixed attributes, and a second page to manage their custom attributes. Read on to learn more!
Getting Started...
Our web application models client information using three database tables:
Clients - captures fixed client attributes, which are attributes common to all clients across all law firms. This includes
attributes like ClientId, CustomerId, FirstName, and LastName.
DynamicAttributesForClients - captures the set of custom client attributes for each law firm.
Part 2 looked at how to create a web interface for creating and managing these custom
attributes.
DynamicValuesForClients - this table captures the values of the custom client attributes for a particular client.
For instance, if a law firm has three custom client attributes - Date of Injury, Injured On Job Site, and Cannot Work Because of Injury - a particular
client might have three records in DynamicValuesForClients, storing the values 4/1/2008, True, and False for the three custom client
attributes.
We need to create a web-based user interface for customers to use to managing their clients. This includes the ability to add a new client to the
system, delete a client, and edit a client's fixed and custom attributes. Ideally, this interface would seamlessly meld the fixed and custom attributes,
making it look like there was no difference between the universal attributes (ClientId, FirstName, etc.) and the custom ones
(Date of Injury, Injured On Job Site, etc.). However, this adds more complexity. For the time being I'm going to make a very simple user interface
that separates the fixed and custom attributes into two separate pages. On one page users can create new clients by specifying the values for their
fixed attributes. Moreover, they can edit these fixed attributes and delete clients. To manage a client's custom attributes the user will need to visit
a second page, which enumerates the custom attributes, loads the current client's information, and allows this data to be edited. (With a little work
you could meld the fixed and custom attributes into a single, unified page.)
Creating a New Client
The demo application (available for download at the end of this article) has been updated to include a new page named ~/Customers/Default.aspx.
This page uses a DetailsView and SqlDataSource control to allow visitors to add a new client to the system. As aforementioned, when adding a new client
to the system the user is prompted for just the fixed data attributes, after which a new record is added to the fixed data model table, Clients.
I started building this page by adding a SqlDataSource control with the following INSERT statement:
Note that the user will be prompted to enter values for the FirstName, LastName, and Email parameters.
The CustomerId value is based on what customer (law firm) the currently logged on user is associated with. In Part 2 we built a
Helpers class with a method named GetCustomerIdForLoggedOnUser that returns the CustomerId value for the
currently logged on user. The value for the CustomerId parameter is set in the DetailsView control's ItemInserting event:
Protected Sub dvAddClient_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles dvAddClient.ItemInserting
e.Values("CustomerId") = Helpers.GetCustomerIdForLoggedOnUser()
End Sub
After creating and configuring the SqlDataSource, I bound it to the DetailsView and configured the DetailsView to support inserting. This DetailsView is used
solely for adding new clients; in the next section we'll add a GridView to the page to display the clientele. To have the DetailsView permanently
render its inserting interface I set its DefaultMode property to Insert. Finally, I customized the DetailsView control's
inserting interface to include validation controls for the FirstName, LastName, and Email fields.
The following screen shot shows the DetailsView control when viewed through a browser.
Editing and Deleting Clients
Along with the DetailsView control the ~/Customers/Default.aspx page also includes a GridView that lists the customer's clientele.
The GridView lists only the fixed attribute values, and these values are editable from the GridView interface. When a client is edited from the
GridView the SqlDataSource control's UpdateCommand is sent to the database. The UPDATE statement used here simply
updates the corresponding record in the Clients table; it does not modify any of the custom attribute values for the client.
The GridView also includes the ability to delete clients. The SqlDataSource's DELETE statement deletes the corresponding record
from the Clients table. If there are any custom attribute values for the client then there will be records in the
DynamicValuesForClients table. When defining the data model in Part 1 we created a foreign key constraint between the
Clients.ClientId and DynamicValuesForClients.ClientId columns, and instructed the relationship to cascade deletes.
If you are following along by creating your own web application and did not configure the constraint to cascade deletes you will get an exception
if you attempt to delete a client that has custom attribute values. To remedy this you can either configure the constraint to cascade deletes or
proactively delete the corresponding custom attribute values before deleting the client. This can be done through a stored procedure.
The GridView allows the visitor to only edit the fixed client attributes. To manage the custom attributes the visitor must go to another page.
To help the user reach this page I added a HyperLinkField to the GridView as its far right column. This renders a hyperlink that points to
~/Customers/ClientCustomAttributes.aspx?ID=ClientID, where ClientID is the value of the ClientId column
of the client whose custom attributes are to be viewed or edited.
The following screen shot shows the GridView in action. Note that the Edit and Delete buttons enable the visitor to edit a client's fixed attribute
values and delete a client. The View/Edit Custom Attributes link, when clicked, whisks the user to a second page where they can view and edit the
selected client's custom attributes.
The Challenges Behind Building the Custom Client Attributes User Interface
Because each customer can define their own custom client attributes, the ~/Customers/ClientCustomAttributes.aspx page, which displays
a user interface for viewing and editing these attributes, needs to be able to dynamically generate the user interface for the attributes defined for
the customer to which the currently logged on user is associated with. The good news is that ASP.NET enables page developers to programmatically add
Web controls to the page's control hierarchy. In other words, we can write code to programmatically construct TextBoxes, CheckBoxes, and other
controls so as to dynamically generate the appropriate user interface for the customer's own unique client attributes. In this article we will skim
over the code for dynamically adding controls to a page; for a more in-depth look see Dynamic
Controls in ASP.NET and Creating Dynamic Data Entry User Interfaces.
Generating the appropriate user interface is one challenge. Another is displaying the custom attribute values for the client being edited. Yet another
challenge is saving these custom attribute values back to the database. These challenges are explored over the next two sections.
Creating the Custom Client Attribute UI and Loading the Client's Current Values from the Database
When programmatically adding Web controls to an ASP.NET page it is essential that the controls are added to the page on each and every page visit.
This includes the first page visit and all subsequent postbacks. For the demo application this user interface is created during the Init event.
In the Page_Init event handler I get all custom client attributes from the DynamicAttributesForClients table for the currently logged on
custom and LEFT JOIN that with the DynamicValuesForClients table, which returns the values (if any) for the custom client
attributes for the client whose ClientId value was passed through the querystring.
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
'Create the custom attributes user interface
Using myConnection As New SqlConnection
myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("LawFirmConnectionString").ConnectionString
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection
myCommand.CommandText = "SELECT a.DynamicAttributeId, a.DataTypeId, a.AttributeName, v.DynamicValue " & _
"FROM DynamicAttributesForClients a " & _
"LEFT JOIN DynamicValuesForClients v ON a.DynamicAttributeId = v.AttributeId AND v.ClientId = @ClientId " & _
"WHERE a.CustomerId = @CustomerId " & _
"ORDER BY a.SortOrder"
myCommand.Parameters.AddWithValue("@ClientId", Request.QueryString("ID"))
myCommand.Parameters.AddWithValue("@CustomerId", Helpers.GetCustomerIdForLoggedOnUser())
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader
While myReader.Read()
Dim DynamicAttributeId As Guid = CType(myReader("DynamicAttributeId"), Guid)
Dim DataTypeId As DataTypeIdEnum = CType(Convert.ToInt32(myReader("DataTypeId")), DataTypeIdEnum)
Dim AttributeName As String = myReader("AttributeName").ToString()
Dim AttributeValue As Object = myReader("DynamicValue")
AddCustomAttribute(DynamicAttributeId, DataTypeId, AttributeName, AttributeValue)
End While
myReader.Close()
myConnection.Close()
End Using
End Sub
After the custom attributes (and the client's corresponding values) the results are enumerated and the appropriate set of controls are created.
The ~/Customers/ClientCustomAttributes.aspx page includes a Table Web control named CustomUITable. This Table has
a row programmatically added for each custom client attribute. Each row includes two columns that contain:
The AttributeName value (i.e., the text, "Date of Injury").
The user interface associated with the attribute's data type.
The method AddCustomAttribute adds a two-column row to the CustomUITable table for each custom client attribute.
Private Sub AddCustomAttribute(ByVal DynamicAttributeId As Guid, ByVal DataTypeId As DataTypeIdEnum, ByVal AttributeName As String, ByVal AttributeValue As Object)
'Add a row to CustomUITable
Dim tr As New TableRow
'Add the name as the left cell
Dim tdName As New TableCell
tdName.Text = AttributeName
tdName.VerticalAlign = VerticalAlign.Top
tr.Cells.Add(tdName)
'Add the UI as the right cell
Dim UIControls As List(Of Control) = CreateCustomAttributeUI(DynamicAttributeId, DataTypeId, AttributeValue)
Dim tdUI As New TableCell
tdUI.VerticalAlign = VerticalAlign.Top
For Each ctrl As Control In UIControls
tdUI.Controls.Add(ctrl)
Next
tr.Cells.Add(tdUI)
CustomUITable.Rows.Add(tr)
End Sub
The CreateCustomAttributeUI method generates the Web controls for the user interface for the current custom client attribute.
This method returns a List of Control instances (each Web control in ASP.NET derives from the
Control class).
the appropriate user interface for each custom client attribute.
Recall that each attribute is assigned a data type, which dictates the type of information expected from the user. We defined four data types
in Part 1. The following list enumerates these data types and specifies the user interface the demo application uses for each:
String - a multi-line TextBox Web control.
Boolean - a CheckBox Web control.
Numeric - a TextBox Web control and a CompareValidator that ensures that the user's input is a valid numeric value.
Date - a TextBox Web control and a CompareValidator that ensures that the user's input is a valid date value.
The user interface generation code for each data type is hard-coded in the CreateCustomAttributeUI method. An alternative approach
would be to model the data types as classes in the web application that were responsible for their own UI rendering.
This is the ideal approach, but it is easier to see and understand what's happening with the UI creation hard-coded in the code-behind, which is why
I've gone with that approach here. In addition to creating the UI, the CreateCustomAttributeUI method also assigns the current value
in the database to the Web control. For example, if there's a custom client attribute named "Date of Injury" and the edited client has a value of
4/1/2008 for this attribute, the CreateCustomAttributeUI method both creates the TextBox and CompareValidator controls associated with
this UI and assigns the Text property of the TextBox to "4/1/2008".
The CreateCustomAttributeUI method and helper methods called from within follow.
Private Function CreateCustomAttributeUI(ByVal DynamicAttributeId As Guid, ByVal DataTypeId As DataTypeIdEnum, ByVal AttributeValue As Object) As List(Of Control)
Dim ctrls As New List(Of Control)
Select Case DataTypeId
Case DataTypeIdEnum.String
'Use a TextBox
Dim stringValue As String = String.Empty
If Not Convert.IsDBNull(AttributeValue) Then
stringValue = AttributeValue.ToString()
End If
Dim tb As New TextBox
tb.ID = GetID(DynamicAttributeId)
tb.Text = stringValue
tb.TextMode = TextBoxMode.MultiLine
tb.Columns = 50
tb.Rows = 5
ctrls.Add(tb)
Case DataTypeIdEnum.Boolean
'Use a checkbox
Dim checkedState As Boolean = False
If Not Convert.IsDBNull(AttributeValue) Then
checkedState = Convert.ToBoolean(AttributeValue)
End If
Dim cb As New CheckBox
cb.ID = GetID(DynamicAttributeId)
cb.Checked = checkedState
ctrls.Add(cb)
Case DataTypeIdEnum.Numeric
'Use a TextBox
Dim doubleValue As String = String.Empty
If Not Convert.IsDBNull(AttributeValue) Then
doubleValue = Convert.ToDouble(AttributeValue)
End If
Dim tb As New TextBox
tb.ID = GetID(DynamicAttributeId)
tb.Columns = 5
tb.Text = doubleValue
ctrls.Add(tb)
'And a numeric CompareValidator
ctrls.Add(CreateDataTypeCheckCompareValidator(DynamicAttributeId, ValidationDataType.Double, "Invalid numeric value."))
Case DataTypeIdEnum.Date
'Use a TextBox
Dim dateValue As String = String.Empty
If Not Convert.IsDBNull(AttributeValue) Then
dateValue = Convert.ToDateTime(AttributeValue).ToShortDateString()
End If
Dim tb As New TextBox
tb.ID = GetID(DynamicAttributeId)
tb.Columns = 10
tb.Text = dateValue
ctrls.Add(tb)
'And a date CompareValidator
ctrls.Add(CreateDataTypeCheckCompareValidator(DynamicAttributeId, ValidationDataType.Date, "Invalid date value."))
End Select
Return ctrls
End Function
Private Function CreateDataTypeCheckCompareValidator(ByVal DynamicAttributeId As Guid, ByVal DataType As ValidationDataType, ByVal ErrorMessage As String) As CompareValidator
Dim cv As New CompareValidator
cv.ID = "CompVal_" & GetID(DynamicAttributeId)
cv.ControlToValidate = GetID(DynamicAttributeId)
cv.Display = ValidatorDisplay.Dynamic
cv.Operator = ValidationCompareOperator.DataTypeCheck
cv.Type = DataType
cv.ErrorMessage = ErrorMessage
Return cv
End Function
Private Function GetID(ByVal DynamicAttributeId As Guid) As String
Return DynamicAttributeId.ToString().Replace("-", "_")
End Function
A couple of comments before we move on. First, note the each Web control added to the page has its ID property set. This is done because
later, when we update the database with the user's input, we need to be able to programmatically access the controls. Therefore, the ID is set
to the primary key of the DynamicAttributesForClients table, DynamicAttributeId. This column is a uniqueidentifier,
which is equivalent to a Guid type in the .NET Framework. The Guid class has a ToString() method that turns the
value into a string representation like 37146444-9d4c-4306-bc4a-fdab87911015. However, the hyphens cause problems with the client-side script emitted
by the CompareValidator controls. Therefore, I created and use the GetID method when assigning a control's ID value. The
GetID method converts the Guid to a string and then replaces all hyphens with underscores.
Another thing to take note of is that when working with the AttributeValue we need to first see if it is equivalent to a database NULL
value before assigning the value to the control. This is because there may not be a corresponding record in DynamicValuesForClients for all
of the custom client attributes. For example, if a client has just been created but has not yet had any custom attributes defined then the SELECT
query used to return the custom client attributes will have a NULL for the custom attribute values.
The following screenshot shows the ~/Customers/ClientCustomAttributes.aspx page when visited for a client that does not yet have any
custom attributes values specified. This customer - Hutz and Hutz - has five custom client attributes defined:
Birthdate (Date)
Employed (Boolean)
Married (Boolean)
Number of Years at Current Job (Numeric)
Reason for Law Suit (String)
Note that each custom attribute's user interface is specific to its data type. The Boolean attributes render as CheckBoxes, while the String attribute
renders as a multi-line TextBox.
Saving the Client's Custom Attribute Values
At this point the ~/Customers/ClientCustomAttributes.aspx page displays the user interfaces for the custom client attributes and includes
the currently edited client's current values. The last piece of the puzzle is to update changes to the client's custom attribute values. The
~/Customers/ClientCustomAttributes.aspx page includes an Update button that, when clicked, updates the client's custom attribute values
and then redirects the user back to the Manage Clients page (i.e., ~/Customers/Default.aspx).
Our first order of business is to get the values the user entered into the various custom attribute user interfaces. This is accomplished by
querying the database to retrieve the list of custom client attributes for the client and then enumerating the attributes and using the
FindControl(controlId) method to programmatically access the control and get its value.
In the following code snippet (which is found in the Update Button's Click event handler) gets the custom client attributes from the
database, loops through them and for each attribute it calls the GetValueForCustomAttribute method to retrieve the user's input into the user interface.
We'll examine this method momentarily.
'Get the custom attributes for this customer
Dim myCommand As New SqlCommand
myCommand.Connection = myConnection
myCommand.Transaction = myTransaction
myCommand.CommandText = "SELECT a.DynamicAttributeId, a.DataTypeId " & _
"FROM DynamicAttributesForClients a " & _
"WHERE a.CustomerId = @CustomerId "
myCommand.Parameters.AddWithValue("@CustomerId", Helpers.GetCustomerIdForLoggedOnUser())
Dim myReader As SqlDataReader = myCommand.ExecuteReader
Dim AttributeValues As New Dictionary(Of Guid, SqlParameter)
While myReader.Read()
Dim DynamicAttributeId As Guid = CType(myReader("DynamicAttributeId"), Guid)
Dim DataTypeId As DataTypeIdEnum = CType(Convert.ToInt32(myReader("DataTypeId")), DataTypeIdEnum)
AttributeValues(DynamicAttributeId) = GetValueForCustomAttribute(DynamicAttributeId, DataTypeId)
End While
myReader.Close()
The values returned from the GetValueForCustomAttribute method are stored in a Dictionary object named
AttributeValues. A Dictionary object is useful for storing a collection of items that are indexed by some value other
than an ordinal number. In this case I want to have a collection of attributes, accessible by AttributeId (a Guid value), whose values are
SqlParameter objects with the appropriate value entered by the user. Therefore, I created a Dictionary object with
keys of type Guid and values of type SqlParameter - Dim AttributeValues As New Dictionary(Of Guid, SqlParameter).
(If this doesn't make too much sense it should become clearer once we look at the code later on in the Click event handler that actually updates the database.)
The GetValueForCustomAttribute method returns a SqlParameter object with a ParameterName value of
@DynamicValue and appropriately set Value and DbType properties. If the user does not enter any value, the
SqlParameter's Value is set a database NULL value (DBNull.Value). Moreover, the DbType property
is set according to the data type of the custom client attribute. The user interface used for a particular custom client attribute is retrieved via
the FindControl(controlId) method (namely, CustomUITable.FindControl(controlId)). Recall that when creating
the user interface controls for the custom client attributes we set the ID of the control to the value of the
DynamicAttributeId column, formatted using the GetID method. This same logic is used to programmatically find the control so
that the value entered by the user can be retrieved.
That
Private Function GetValueForCustomAttribute(ByVal DynamicAttributeId As Guid, ByVal DataTypeId As DataTypeIdEnum) As SqlParameter
Dim userInputParam As New SqlParameter
userInputParam.ParameterName = "@DynamicValue"
userInputParam.Value = DBNull.Value
Dim ctrlId As String = GetID(DynamicAttributeId)
Dim ctrl As Control = CustomUITable.FindControl(ctrlId)
Select Case DataTypeId
Case DataTypeIdEnum.String
Dim tb As TextBox = CType(ctrl, TextBox)
userInputParam.DbType = Data.DbType.String
If Not String.IsNullOrEmpty(tb.Text) Then
userInputParam.Value = tb.Text.Trim()
End If
Case DataTypeIdEnum.Boolean
Dim cb As CheckBox = CType(ctrl, CheckBox)
userInputParam.Value = cb.Checked
userInputParam.DbType = Data.DbType.Boolean
Case DataTypeIdEnum.Numeric
Dim tb As TextBox = CType(ctrl, TextBox)
userInputParam.DbType = Data.DbType.Double
If Not String.IsNullOrEmpty(tb.Text) Then
userInputParam.Value = tb.Text.Trim()
End If
Case DataTypeIdEnum.Date
Dim tb As TextBox = CType(ctrl, TextBox)
userInputParam.DbType = Data.DbType.Date
If Not String.IsNullOrEmpty(tb.Text) Then
userInputParam.Value = tb.Text.Trim()
End If
End Select
Return userInputParam
End Function
Like with the code used to create the user interface, logic used in the GetValueForCustomAttribute method to retrieve the user's
value is hard-coded in the ASP.NET code-behind class.
Once we have all of the custom client attribute values loaded into the AttributeValuesDictionary object we are ready
to update the database. Each custom client attribute value is stored as a record in the DynamicValuesForClients table. If there are
five custom attributes each client whose custom attribute values have been saved will have five corresponding records in
DynamicValuesForClients. When saving a client's custom attribute values we may need to:
Insert records into DynamicValuesForClients - if the client has not yet had any custom attribute values specified, or if
a new custom client attribute has been added to the system, then there is no corresponding record in DynamicValuesForClients for a
particular attribute's value. Therefore we'll need to add a new record to DynamicValuesForClients.
Update records in DynamicValuesForClients - if a client already has a record in DynamicValuesForClients for
a particular custom attribute, updating the attribute value requires updating the corresponding database record.
What approach we take depends on whether there already exists a record in the DynamicValuesForClients table for the
ClientId and AttributeId pair. I created a stored procedure named lawfirm_AddOrUpdateDynamicValueForClient
to handle this decision. This makes the ASP.NET code very simple: just loop through the AttributeValuesDictionary object
and for each item call the lawfirm_AddOrUpdateDynamicValueForClient stored procedure, passing in the ClientId, AttributeId,
and the value for that attribute. (Because the items in the AttributeValuesDictionary object are SqlParameter objects we
can add them to the SqlCommand's Parameters collection using myCommand.Parameters.Add(AttributeValues(attributeId)).)
For Each AttributeId As Guid In AttributeValues.Keys
myCommand.CommandText = "lawfirm_AddOrUpdateDynamicValueForClient"
myCommand.CommandType = Data.CommandType.StoredProcedure
myCommand.Parameters.Clear()
myCommand.Parameters.AddWithValue("@ClientId", Request.QueryString("ID"))
myCommand.Parameters.AddWithValue("@AttributeId", AttributeId)
myCommand.Parameters.Add(AttributeValues(AttributeId))
myCommand.ExecuteNonQuery()
Next
The lawfirm_AddOrUpdateDynamicValueForClient stored procedure follows. It is pretty simple - it checks to see if there is already a record
for the ClientId and AttributeId pair passed in. If so, then the record already exists in the table so an UPDATE
statement is used; if not, the record needs to be added, so an INSERT statement is used instead.
IF EXISTS(SELECT 1 FROM DynamicValuesForClients WHERE ClientId = @ClientId AND AttributeId = @AttributeId)
-- Record exists, so update
UPDATE DynamicValuesForClients SET
DynamicValue = @DynamicValue
WHERE ClientId = @ClientId AND AttributeId = @AttributeId
ELSE
-- Record does not exist, so insert
INSERT INTO DynamicValuesForClients(ClientId, AttributeId, DynamicValue)
VALUES(@ClientId, @AttributeId, @DynamicValue)
That's it! I encourage you to download the code demo at the end of this article and closely examine the code in the page. One thing you'll notice
is that the entire set of SQL statements in the Update Button's Click event handler are wrapped up in a transaction. This technique
ensures that the set of INSERT and/or UPDATE statements that occur as each custom client attribute value is updated
are treated as an atomic operation. For more on implementing transactional support in your .NET code, see
Maintaining Database Consistency with Transactions.
Looking Forward...
We now have a fully functional dynamic, data-drive web application! Customers can log on to the site and define custom client attributes and manage
values for those custom attributes. Of course, there is much room for improvement and some lingering questions, like what happens if a customer
redefines an existing custom attribute, and how can we extend the system to include more data types, and how do we add extra attributes to these
data types, such as indicating that a particular string field is required or that a date value must be between some range of legal values?
Part 4 discusses questions like these.
