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="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
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="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 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!