Basic Instincts: Dynamic Data Entry With XML Literals

發行項
09/10/2019

Basic Instincts

Dynamic Data Entry With XML Literals

Beth Massi

Dynamically Loading XAML
Obtaining the Metadata
Generating UI Based on Metadata
Dynamic Data Entry

Wouldn't it be nice to generate all your maintenance screens in your data-driven applications automatically? When I say "maintenance" I mean all those simple lookup data tables or contact tables. Instead of handing those screens to the junior developer on the team, why not just generate them all at run time based on your object model or database schema? This is a relatively easy task in Windows Presentation Foundation (WPF) using XAML, and with the deep XML support in Visual Basic it becomes a snap to generate user interfaces dynamically.

Dynamically Loading XAML

Dynamically creating WPF user interfaces at run time is easy using Visual Basic XML Literals and XML namespace imports. This is because WPF expresses its user interface elements and layout in XAML and Visual Basic does all the heavy lifting, resolving the XML namespaces in order to create the correct XAML.

Let me show you what I mean. You can load and save XAML at run time using the XamlReader and XamlWriter classes in the System.Windows.Markup namespace. Create a new WPF project and, in the Window1 codebehind, you can write this:

Imports <xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup

Class Window1
  Private Sub Window1_Loaded() Handles MyBase.Loaded
    Dim UI = <Label Name="Label1">This is COOL!</Label>
    Me.Content = XamlReader.Load(UI.CreateReader())
  End Sub
End Class

You'll see the label populate the contents of the window, as shown in Figure 1 .

Figure 1 XAMLReader Loads XAML at Run Time

When you include the WPF namespace imports at the top of the code file, Visual Basic automatically creates the proper XAML you need for it to load at run time. In this example I'm using the default namespace for the controls; however, you can also provide a namespace prefix and change the XAML to use it. This allows you to reserve the default namespace for other XML you may be manipulating in your code:

Imports <xmlns:controls=  "https://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup

Class Window1
  Private Sub Window1_Loaded() Handles MyBase.Loaded
    Dim UI = <controls:Label Name="Label1">      This is COOL!      </controls:Label>
    Me.Content = XamlReader.Load(UI.CreateReader())
  End Sub
End Class

One caveat here is that the XAML you load at run time cannot require compilation, so you cannot specify event handlers on dynamically loaded controls in the XAML directly. You can still add them at run time in the codebehind. For example, you can't do this dynamically, as it will cause a XamlParseException "Must compile XAML file that specifies events:"

Dim UI = <Label
    MouseDown="Label1_MouseDown"
    Name="Label1">
    This is COOL!
  </Label>
Me.Content = XamlReader.Load(UI.CreateReader())

Instead, you can get the reference to the label at run time and explicitly add the event handler in code:

Private Sub Window1_Loaded() Handles Window1.Loaded
  Dim UI = <Label Name="Label1">This is COOL!</Label>
  Dim myLabel As Label = XamlReader.Load(UI.CreateReader())
  Me.Content = myLabel
  AddHandler myLabel.MouseDown, AddressOf Label1_MouseDown
End Sub

Private Sub Label1_MouseDown()
  MsgBox("Mouse Down!")
End Sub

As you can see, there are many possibilities. Of course, dynamically generating a UI isn't unique to WPF. You can do this in Windows Forms as well, but it is an exercise in coding the layout by hand. WPF and Visual Basic make this a breeze because you can define one piece of XAML and construct it from a single LINQ query.

Obtaining the Metadata

Before you can construct the XAML for your UI you're going to need some metadata. You need an easy way to describe how your UI should look based on the table's schema. You can use reflection to look at your object model. But if the model is really simple and maps one-to-one to your database table anyway, you can just ask the database to return the metadata about its tables.

In order to generate a simple UI, you would probably want to obtain, at minimum, the following properties for each column in a table: ColumnName, DataType, MaxLength, and IsPrimaryKey.One way to do this is by creating a stored procedure called GetTableSchema (in this example, I'm using the Northwind database) that uses the information schema views to return this basic schema for any given table (see Figure 2 ).

Figure 2 GetTableSchema

CREATE PROCEDURE dbo.GetTableSchema
  (
  @table varchar(50)
  )    
AS
SELECT 
c.table_name As TableName, 
c.column_name As ColumnName, 
c.data_type As DataType, 
c.character_maximum_length As MaxLength,
  COALESCE (
  ( SELECT 
    CASE cu.column_name
      WHEN null THEN 0
      ELSE 1
    END
  FROM information_schema.constraint_column_usage cu
  INNER join information_schema.table_constraints ct
  ON ct.constraint_name = cu.constraint_name
  WHERE 
  ct.constraint_type = 'PRIMARY KEY' 
  AND ct.table_name = c.table_name
  AND cu.column_name = c.column_name 
  ),0) AS IsPrimaryKey
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE @table = t.table_name and 
  (t.table_type = 'BASE TABLE' and not 
  (t.table_name = 'dtproperties') and not 
  (t.table_name = 'sysdiagrams'))
ORDER BY c.table_name, c.ordinal_position

A simple and strongly typed way to access this stored procedure is to add a new LINQ to SQL classes item to your project, expand the Stored Procedures node in the Server Explorer for the database, and drag the above procedure onto the methods pane. Next, you need to create an object on the design surface called TableSchema that contains the same properties as the fields returned from the stored procedure. For this example, I plan to edit the Northwind customer table, so you can add that to the model as well. Once that's set up, in the properties for the GetTableSchema method, set the result type to the TableSchema class.

Generating UI Based on Metadata

Now that you have the metadata, you're almost ready to write a query to generate the XAML at run time. You can generate the entire Window content or just parts of it by using a ContentControl. This way you can easily lay out the static elements of the Window and indicate where the dynamic content should be placed.

For this example, using the grid panel to lay out the form, create a simple textbox and Find and Save buttons at a fixed area at the top of the Window. Under that, place a ContentControl onto the Window and name it something like DynamicContent (see Figure 3 ). You should now have XAML that looks similar to that illustrated in Figure 3 .

Figure 3 Defining the Window

<Window x:Class="Window1"
  xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"
  xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
  Title="Window1"  Name="Window1" SizeToContent="WidthAndHeight" >
  <Grid Name="MainGrid"  >
    <Grid.RowDefinitions>
      <RowDefinition Height="10*" />
      <RowDefinition Height="60*" />
    </Grid.RowDefinitions>
    <StackPanel Name="StackPanel1" Orientation="Horizontal" 
      Margin="3" VerticalAlignment="Top">
      <Label Height="28" Name="Label1" Width="84" 
        HorizontalContentAlignment="Right" FontWeight="Bold">ID</Label>
      <TextBox Height="25" Name="txtSearch" Width="120"></TextBox>
      <Button Height="25" Name="btnFind" Width="75">Find</Button>
      <Button Height="25" Name="btnSave" Width="75">Save</Button>
    </StackPanel>
    <ContentControl Grid.Row="1" Name="DynamicContent" Margin="3" />
  </Grid>
</Window>

In the codebehind, add the appropriate XML namespace imports at the top of the file. Then you can generate the UI by writing a LINQ query in the Window Loaded event handler. You can also add button click handlers for the actual loading and saving of the customer data.

The entire Window class code is shown in Figure 4 . It's important to note that this code embeds a couple of LINQ query expressions to create the labels for the field names, controls, and data bindings using a helper function called GetUIElement that returns the control's XAML as an XElement (see Figure 4 ).

Figure 4 Generating UI Dynamically

Imports <xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup

Class Window1
  Dim db As New NorthwindDataContext
  Dim CustomerData As Customer

  Private Sub Window1_Loaded() Handles MyBase.Loaded
    Dim customerSchema = db.GetTableSchema("Customers").ToList()

    Dim UI = _
      <Grid xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
        xmlns=          "https://schemas.microsoft.com/winfx/2006/xaml/presentation">
        <Grid.ColumnDefinitions>
          <ColumnDefinition Width="100*"/>
          <ColumnDefinition Width="200*"/>
        </Grid.ColumnDefinitions>
        <StackPanel Name="StackLabels" Margin="3">
          <%= From column In customerSchema _
            Where column.IsPrimaryKey = 0 _
            Select <Label
              Height="28"
              Name=<%= column.ColumnName & "Label" %>
              HorizontalContentAlignment="Right">
            <%= column.ColumnName %>:</Label> %>
          </StackPanel>
          <StackPanel Grid.Column="1" Name="StackFields" Margin="3">
            <%= From column In customerSchema _
              Where column.IsPrimaryKey = 0 _
              Select GetUIElement(column) %>
          </StackPanel>
      </Grid>

    Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader)

  End Sub

  Private Function GetUIElement(ByVal column As TableSchema) As XElement
    Select Case column.DataType
      Case "datetime", "int", "smallint", "money"
        Return <TextBox
          Height="28"
          Name=<%= "txt" & column.ColumnName %>
          Text=<%= "{Binding Path=" & column.ColumnName & "}" %>/>
      Case "bit"
        Return <CheckBox
          HorizontalContentAlignment="Left"
          Name=<%= "chk" & column.ColumnName %>
          IsChecked=<%= "{Binding Path=" & column.ColumnName & "}" %>>
          <%= column.ColumnName %>
          </CheckBox>
      Case "image"
        Return <Image
           Height="150"
           Width="150"
           Stretch="Fill"
           HorizontalAlignment="Left"
           Name=<%= "img" & columnInfo.ColumnName %>
           Source=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
      Case Else
        Return <TextBox
          Height="28"
          Name=<%= "txt" & column.ColumnName %>
          MaxLength=<%= column.MaxLength %>
          Text=<%= "{Binding Path=" & column.ColumnName & "}" %>/>
    End Select
  End Function

  Private Sub btnFind_Click() Handles btnFind.Click
    If Me.txtSearch.Text <> "" Then
      Me.CustomerData = (From cust In db.Customers _
        Where cust.CustomerID = Me.txtSearch.Text).FirstOrDefault()

      Me.DataContext = Me.CustomerData
    Else
      Me.DataContext = Nothing
    End If
  End Sub

  Private Sub btnSave_Click() Handles btnSave.Click
    If Me.DataContext IsNot Nothing Then
      Try
        db.SubmitChanges()
        MsgBox("Saved")

      Catch ex As Exception
        MsgBox(ex.ToString)
      End Try
    End If
  End Sub
End Class

When you run the application this time, you are going to see all the customer fields in the UI (see the example in Figure 5 ). Remember that the GetTableSchema stored procedure will work for any table contained in the database, so you could even abstract this further. For instance, what if you just want one single Window that can edit any maintenance table in the database, no matter what the schema?

Figure 5 Customer Fields in the UI

Dynamic Data Entry

The previous example is still tied to the object model of Customer to load and edit the data. Even though the UI is dynamic, the data entry is not. What you probably want is a more abstracted form so that when you modify the database schema of any of the maintenance tables, you don't have to update your object model and recompile all the code.

One way to achieve this is to load and edit a simple untyped DataTable at run time. This works well using XML literals again, this time to generate the SELECT and UPDATE statements for the SqlDataAdapter. One thing to note is that this technique relies on the database to enforce any validation rules. This is why I would only use this type of dynamic form to edit very simple tables (such as maintenance or lookup tables). However, I suggest you play with the WPF Binding.ValidationRules property to declaratively add validation rules to the controls.

You can create a typed DataTable for the TableSchema metadata as well, which eliminates the need for LINQ to SQL classes in the project. To do this, just right-click on the project, add a new item, and select DataSet. Name it TableSchemaDataSet and drag the GetTableSchema stored procedure onto the design surface from the Server Explorer. This automatically creates a typed DataTable for you with no fuss. Rename the DataTable to TableSchema and save it.

Now you need to load an untyped DataTable into the dynamically generated UI to edit the data. You don't want to make any assumptions about the schema of the table you're editing except that there is a primary key of some sort. DataTables and DataSets work well with WPF, but there are a couple of things you need to set up manually since you're loading this all at run time.

A simple thing to do is to set up a public property called TableName on the form to hold the name of the table you want to edit. This can be set easily from calling code. Then create some private class-level variables to reference the ADO.NET objects you need. The XAML markup of the Window for this example is exactly the same as before. Figure 6 shows the class-level variables you need and the TableName property set to a default value of Shippers.

Figure 6 Set Variables and Properties for Dynamic Data Entry

Imports <xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml">
Imports System.Windows.Markup
Imports System.Data.SqlClient
Imports System.Data

Partial Public Class Window2
  'This is the metadata table we created in the DataSet Designer
  Private TableSchema As New TableSchemaDataSet.TableSchemaDataTable
  'ADO.NET objects used to load and save the table we're editing
  Private TableDataAdapter As New SqlDataAdapter
  Private TableConnection As New _
          SqlConnection(My.Settings.NorthwindConnectionString)
  Private Table As DataTable
  'This is the key field used in searching for a row in this example
  Private PKField As TableSchemaDataSet.TableSchemaRow

  'This property can be set before the Form.Show() to edit any table
  Private m_tableName As String = "Shippers"
  Public Property TableName() As String
    Get
      Return m_tableName
    End Get
    Set(ByVal value As String)
      m_tableName = value
    End Set
  End Property
  End Class

In the Loaded event handler you can now load the metadata, create and load the XAML to display the UI just like before, and then set the UpdateCommand on the TableDataAdapter. Figure 7 shows the Loaded event handler and the code that generates the XAML.

Figure 7 Generate the Dynamic Data Entry UI

Private Sub Window1_Loaded() Handles MyBase.Loaded
  Try
    'Get the schema of the database table we want to edit
    Dim taSchema As New _
        TableSchemaDataSetTableAdapters. TableSchemaTableAdapter
    taSchema.Fill(Me.TableSchema, Me.TableName)

    'Create the DataTable that will hold the record we're editing
    Me.Table = New DataTable(Me.TableName)
    Me.Title = Me.TableName
    Me.LoadUI() 
    Me.SetPrimaryKey()
    Me.SetUpdateCommand()

  Catch ex As Exception
    MsgBox(ex.ToString)
    Me.Close()
  End Try
End Sub

Private Sub LoadUI() _
  Dim UI = _
    <Grid xmlns:x="https://schemas.microsoft.com/winfx/2006/xaml"
      xmlns="https://schemas.microsoft.com/winfx/2006/xaml/presentation"
      Name="Grid1">
      <Grid.ColumnDefinitions>
        <ColumnDefinition Width="100*"/>
        <ColumnDefinition Width="200*"/>
      </Grid.ColumnDefinitions>
      <StackPanel Name="StackLabels" Margin="3">
        <%= From column In Me.TableSchema _
          Where column.IsPrimaryKey = 0 AndAlso _
            column.DataType <> "timestamp" _
            Select <Label 
            Height="28"
            Name=<%= column.ColumnName & "Label" %>
            HorizontalContentAlignment="Right">
          <%= column.ColumnName %>:</Label> %>
        </StackPanel>
        <StackPanel Grid.Column="1" Name="StackFields" Margin="3">
          <%= From column In Me.TableSchema _
                Where column.IsPrimaryKey = 0 AndAlso _
                column.DataType <> "timestamp" _
            Select GetUIElement(column) %>
        </StackPanel>
      </Grid>

  Me.DynamicContent.Content = XamlReader.Load(UI.CreateReader())
End Sub

Private Function GetUIElement(ByVal columnInfo As _
    TableSchemaDataSet.TableSchemaRow) As XElement
  Select Case columnInfo.DataType.ToLower
    Case "datetime", "int", "smallint", "money"
      Return <TextBox
        Height="28"
        Name=<%= "txt" & columnInfo.ColumnName %>
        Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
    Case "bit"
      Return <CheckBox
        HorizontalContentAlignment="Left"
        Name=<%= "chk" & columnInfo.ColumnName %>
        IsChecked=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>>
        <%= columnInfo.ColumnName %>
        </CheckBox>
    Case "image"
      Return <Image
         Height="150"
         Width="150"
         Stretch="Fill"
         HorizontalAlignment="Left"
         Name=<%= "img" & columnInfo.ColumnName %>
         Source=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
    Case Else
      Return <TextBox
        Height="28"
        Name=<%= "txt" & columnInfo.ColumnName %>
        MaxLength=<%= columnInfo.MaxLength %>
        Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
  End Select
End Function

Now that you have the XAML UI generated, set the primary key field (which is a TableSchemaDataRow object) so that you can use it in your UPDATE statement as well as in the SELECT query when the user clicks the Find button on the form. Generally primary keys are surrogate keys (like auto-incrementing integers) and mean nothing to the user, so instead you may want to create another public property that captures the search field name.

Assuming that every table has only one primary key field defined you can use the indexer 0 on the query, which returns the first of the sequence:

  Private Sub SetPrimaryKey()
    'Grab the Primary Key column of the table we want to 
    ' edit so we can use it in the search
    Me.PKField = (From column In Me.TableSchema _
                  Where column.IsPrimaryKey = 1)(0)
  End Sub

To create the SELECT statement, use XML literals again, but this time without creating XML. Instead you can create a string by calling the XElement's .Value property. You then can create the SqlCommand and fill the untyped DataTable with the results. Setting the window's DataContext sets up the data binding to the fields that were specified when the XAML was generated. Figure 8 shows the code that generates the SELECT statement and sets the DataContext to the results.

Figure 8 Generate the SELECT Statement

Private Sub btnFind_Click() Handles btnFind.Click
  If Me.txtSearch.Text <> "" Then
    Try
      'Create the SELECT command
      Dim cmdText = <s>
        SELECT * FROM <%= Me.TableName %> 
        WHERE <%= Me.PKField.ColumnName %> = 
          <%= If(Me.PKField.DataType.Contains("char"), _
            "'" & Me.txtSearch.Text & "'", _
            Me.txtSearch.Text) %>
        </s>.Value

      Dim cmd As New SqlCommand(cmdText, Me.TableConnection)
      Me.Table.Clear()
      Me.TableDataAdapter.SelectCommand = cmd
      Me.TableDataAdapter.Fill(Me.Table)

      Me.DataContext = Me.Table
      Dim view = CollectionViewSource.GetDefaultView(Me.Table)
      view.MoveCurrentToFirst()

    Catch ex As Exception
      MsgBox(ex.ToString)
      Me.DataContext = Nothing
    End Try
  Else
    Me.DataContext = Nothing
  End If
End Sub

You want to be able to edit and save this record, too, so you need to generate an UPDATE command. For this example, the code only sets up the UpdateCommand on the SqlDataAdapter because the form just edits records, but it's easy enough to create Delete and Insert commands as well using the same techniques. Figure 9 shows the code that constructs the UPDATE statement and sets up the UpdateCommand:

Figure 9 Generate the UPDATE Statement

Private Sub SetUpdateCommand()
  'Set the UpdateCommand so that we can save edited records in the table
  Dim cmdText = <s>
    UPDATE <%= Me.TableName %> 
    SET <%= From column In Me.TableSchema _
      Where column.IsPrimaryKey = 0 AndAlso _
      column.DataType <> "timestamp" _
      Select <c>
        <%= column.ColumnName %> = @<%= column.ColumnName %>
        <%= If(Me.TableSchema.Rows.IndexOf(column) < _
          Me.TableSchema.Rows.Count - 1, ", ", "") %>
        </c>.Value %>
    WHERE <%= Me.PKField.ColumnName %> = @<%= Me.PKField.ColumnName %>
      <%= From column In Me.TableSchema _
        Where column.IsPrimaryKey = 0 AndAlso _
        column.DataType = "timestamp" _
        Select <c>
          AND <%= column.ColumnName %> = @<%= column.ColumnName %>
          </c>.Value %>
    </s>.Value

  Dim cmd As New SqlCommand(cmdText, Me.TableConnection)
  Dim p As SqlParameter

  For Each column In Me.TableSchema
    If column.IsPrimaryKey = 0 AndAlso column.DataType = "timestamp" Then
    'Note: It's recommended to use a TimeStamp column in your tables for     'concurrency checking
      p = New SqlParameter("@" & column.ColumnName, SqlDbType.Timestamp)
      p.SourceVersion = DataRowVersion.Original
      p.SourceColumn = column.ColumnName
      cmd.Parameters.Add(p)
    Else
      p = New SqlParameter("@" & column.ColumnName, _
        CType([Enum].Parse(GetType(SqlDbType), _
        column.DataType, True), SqlDbType))
      p.SourceColumn = column.ColumnName
      p.SourceVersion = DataRowVersion.Current
      cmd.Parameters.Add(p)
    End If
  Next

  Me.TableDataAdapter.UpdateCommand = cmd
End Sub

One important note here: when the UpdateCommand is created it assumes that concurrency checking is being done with a TimeStamp field (which I've added to my copy of Northwind), but you can also create the long version where it checks original against current values as well.

So now you can simply set the TableName property of this form and it will dynamically generate the UI, load the data when the Find button is clicked, and save changes back to the database. Try modifying your database table's schema and running it again without recompiling the application at all. Slick.

I've uploaded this project onto Code Gallery with both examples, so have a look at code.msdn.microsoft.com/dynamicWPF .

Send your questions and comments to instinct@microsoft.com .

Beth Massi is an Online Content and Community Program Manager on the Visual Studio Community Team at Microsoft. She is responsible for producing content for business application developers and driving community features onto MSDN Developer Centers. Read more about her at blogs.msdn.com/bethmassi .

Contents