MSDN Magazine - October 2008 - (Page 29) 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 = SELECT * FROM WHERE = .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 Figure 9 Generate the UPDATE Statement Private Sub SetUpdateCommand() 'Set the UpdateCommand so that we can save edited records in the table Dim cmdText = UPDATE SET <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso _ column.DataType "timestamp" _ Select = @ <%= If(Me.TableSchema.Rows.IndexOf(column) .Value %> WHERE = @ <%= From column In Me.TableSchema _ Where column.IsPrimaryKey = 0 AndAlso _ column.DataType = "timestamp" _ Select AND = @ .Value %> .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 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. 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: 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. 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. msdnmagazine.com October 2008 29 http://code.msdn.microsoft.com/dynamicWPF http://blogs.msdn.com/bethmassi http://www.msdnmagazine.com
For optimal viewing of this digital publication, please enable JavaScript and then refresh the page. If you would like to try to load the digital publication without using Flash Player detection, please click here.