Dynamic Data Entry with WPF and LINQ

In my last post on this subject I explored creating WPF UI's dynamically using XML literals. The one part that bugged me a bit was that even though the UI was dynamic, we were using a fixed object model of our customer (using LINQ to SQL classes). I wanted to augment this code a bit more so that we could not only dynamically generate the WPF UI but also dynamically edit any maintenance table in our database -- not just customer. What I really wanted to enable is if we modify the database schema of any of our maintenance tables then we don't have to update our object model and recompile our code.

To achieve this I decided to explore loading and editing a simple untyped DataTable at runtime. This worked well using XML literals again -- this time to generate the SELECT and UPDATE statements for the SqlDataAdapter. One thing to note, 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 (aka. maintenance tables).

For this example I decided to create a typed DataTable for my TableSchema instead as well, eliminating the need for LINQ to SQL classes in the project. If you recall this is used to hold the column metadata (ColumnName, DataType, etc.) for the table we want to edit. I just right-clicked on my project to add a new item and selected DataSet. I named it TableSchemaDataSet and then just simply dragged the GetTableSchema stored procedure (which we added to the Northwind database in the last post) onto the design surface. This automatically creates a typed DataTable for us with no fuss. I renamed the DataTable to TableSchema and saved it.

Now for the fun part. We need to load an untyped DataTable into our dynamically generated WPF form because we don't want to make any assumptions about the schema of the table we're editing (except that there is a primary key of some sort). DataTables and DataSets work well with WPF but there are a couple things we need to set up manually since we're loading this all at runtime. (Writing the code to load and save the DataTable makes me REALLY appreciate the DataSet designer and the code that it generates for you).

First I set up a Public property on the form to hold the name of the table we want to edit called TableName and set the default to "Shippers" this time. Then I created some private class level variables to reference the ADO.NET objects we'll need. (Take a look at the last post for the XAML markup of the Window, it's exactly the same for this example.)

Imports <xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation">
Imports <xmlns:x="http://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

In the Loaded event handler we can now load the metadata, create and load our XAML to display our UI just like before, and then set our UpdateCommand on the TableDataAdapter.

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="http://schemas.microsoft.com/winfx/2006/xaml"
                 xmlns="http://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 Else
            Return <TextBox
                       Height="28"
                       Name=<%= "txt" & columnInfo.ColumnName %>
                       MaxLength=<%= columnInfo.MaxLength %>
                       Text=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>
    End Select
End Function

Now that we've got the UI defined I'll set the primary key field (which is a TableSchemaDataRow object) so that we can use this in our 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. Since there is only one primary key field on a table, I use the FirstOrDefault() extension method 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).FirstOrDefault()
End Sub

To create the SELECT statement, notice that I'm once again using XML literals but this time I'm not creating XML. Instead I'm creating a string by calling the XElement's .Value property. We then can create the SqlCommand and fill our untyped DataTable with the results. Setting the WPF form's DataContext sets up the data binding to the fields we specified when we generated the XAML above.

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

We want to be able to edit and save this record so we need to also generate an UPDATE command. For this example I'm only setting up the UpdateCommand on the SqlDataAdapter because we're creating a form that just edits records, but it's easy enough to create Delete and Insert commands as well. Here I'm using XML Literals again (of course! ;-)).

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 is that when I create the UpdateCommand, I'm assuming 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 we can simply set the TableName property of this form and it will dynamically generate the UI, load the data when we click the Find button, and allow us to save our changes back to the database. Try modifying your database table's schema and running it again without recompiling. Slick.

I've uploaded this project onto CodeGallery with both forms (this one and the one we did in the last post) so have a look. You'll need to download the Northwind database here first.

Enjoy!