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 5 and type the answer here:
  • Post
  • PingBack from http://blogs.msdn.com/bethmassi/archive/2008/06/12/dynamic-ui-with-wpf-and-linq.aspx

  • Great stuff.  One of the great challenges of a UI is validating the data entered.  Not just that it fits in the data table, but that it falls within guidelinles.  Have you considered using real-time compilation of .NET code for validation?  

  • hi can u help  to make crystal report

  • Great stuff Beth!!!

    Call me lazy, but is there any similar C# code around????

  • Very cool, I added this to get images (say from Categories)

    Case "image"

    Return <Image

    Height="150"

    Width="150"

    Stretch="Fill"

    HorizontalAlignment="Left"

    Name=<%= "img" & columnInfo.ColumnName %>

    Source=<%= "{Binding Path=" & columnInfo.ColumnName & "}" %>/>

  • Hi dceanes,

    XML literals are not supported in C# so the code would be different. You'd have to build the XAML manually using the LINQ to XML API directly.

    -B

  • Hi LarryC,

    Cool! Thanks for posting that.

    -B

  • Hi Joel,

    That is definitely one way to run managed code business rules dynamically. It works well if the code changes often. But if you have set validation rules (phone number, SSN, common validators, etc.) and just need a way to hook them up and call them then in WPF you can also take a look at the ValidationRules collection. And in Visual Basic you can also look at the CallByName method.

    HTH,

    -B

  • [原文作者]: Beth Massi [原文链接]: Dynamic Data Entry with WPF and LINQ 上一节我讲到用XML编写动态WPF UI, 尽管这里的UI是动态生成的,但仍有一处不尽人意的地方

  • All of tutorials af Beth Massi are very very cool. I wish to be a very good programmer like Her. I'm autodidact and I try...

    this is my Email : khami_med@hotmail.com

  • Hi,

    I have tried loading this project into my environment but when I try to run it I get a large SQL error message that starts:

    System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

    When executing the following line in Window2.xaml.vb:

    taSchema.Fill(Me.TableSchema, Me.TableName)

    I can see the NorthwindDataSet in the data sources and I can preview the data.

    I am using Visual Basic 2008 Express Edition and SQL  Server 2008 Compact Edition.

    Any idea's on what the issue is?

    Thanks in advance for any help.

    Railton.

  • Hi Railton,

    Did you download and install the Northwind database first?

    http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

  • Hi Beth,

    I tried the download but I am not able to use the NORTHWND.MDF in my environment, I get a similar error to the one I mentioned before when testing the connection.  Could this be because I only have SQL Server 2008 Compact Edition installed?

    Thanks,

    Railton

  • Hi Railton,

    Ah, yes. You need SQL Server or the free SQL Server Express edition: http://www.microsoft.com/express/sql/

    HTH,

    -B

  • Ok, thanks.

    Quick question.  Is it possible to use this technique on the compact edition?

    I am writing an application which uses the compact edition database and I need a dynamic UI for editing the table data because columns on the tables will not always be the same and I don't want to have to modify the application each time.  Your code did exactly what I need so was hoping I could use it.

    Thanks again for your help.

    Railton

Page 1 of 2 (22 items) 12