Dynamic Data Entry with WPF and LINQ

Dynamic Data Entry with WPF and LINQ

  • Comments 22

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!

Leave a Comment
  • Please add 6 and 3 and type the answer here:
  • Post
  • Hi Railton,

    The only thing that won't work with SQL Compact is the GetTableSchema stored procedure. SQL Compact doesn't support stored procs so you'll need another way to get the metadata, like storing it in another table.

    HTH,

    -B

  • Hi,

    Thanks for the steer, however I don't think my skill are deep enough to convert your code :(

    Thanks again,

    Railton

  • Hi Railton,

    Just create a table that contains the metadata you need, i.e. TableName, ColumnName, Type, Length, IsPrimaryKey and fill it with the information about each table. Then just query that instead. It's more maintance becuause you need to make sure the schema and the metadata are in sync but it's the easiest approach for SQL Compact.

    HTH,

    -B

  • Hi,

    Thanks for the reply, I'm Ok with the concept of storing meta-data in my own tables and retrieving it via sql.  

    Looking at the code there are quite a few references to the stored procedure and its result set, the area I think I will struggle is converting these references to use the data returned from the SQL.  

    Hope this makes sense! :)

    Railton.

  • Hi Railton,

    There should only be one reference to the GetTableSchema stored proc. All you need to do is change the query on the TableAdapter on the TableSchema dataset. Just right-click and select Configure and then you can change the query to select from your metadata table instead. For more info on configuring TableAdapter queries: http://msdn.microsoft.com/en-us/library/kda44dwy.aspx

    HTH,

    -B

  • Hi,

    I've just got round to looking at this again.  I have managed to get it to work but I had to create a new tableadapter and change the declarations so they included ce for example sqlcedataadapter.

    I also found that I get an error on the XamlReader.Load if the column names have a space in them.

    Thanks again for your help.

    Railton.

  • Hello! Beth Massi. I really appreciate all your efforts for helping us to learn in vb. Actually I'm just new in wpf and the two wpf video series help me a lot to learn on it. May i request if you could upload your video more on wpf same as the previous with 26 video. Many Thanks!!

Page 2 of 2 (22 items) 12