基于WPF LINQ的动态数据模型

[原文作者]:Beth Massi

[原文链接]:Dynamic Data Entry with WPF and LINQ

   上一节我讲到用XML编写动态WPF UI, 尽管这里的UI是动态生成的,但仍有一处不尽人意的地方,就是我们采用的是一个具体的对象customer(来源于LINQ to SQL classes)。 如果想要我们的应用程序既能够动态生成WPF UI 又能动态地编辑处理数据库里的任何表数据,就需要进一步参数化程序代码 ——而不仅限于customer。 这样只需要修改数据库表的定义而不用更新对象模块和重新编译代码。

   实现方法是试图在运行时加载,处理一个简单的无类型的(或者称作通用类型)DataTable,并运用XML literals特性 –为SqlDataAdapter生成SELECT和UPDATE语句。要注意一件事情,这个过程必须依赖数据库验证规则。因此这种方式只能用于处理非常简单的表(缩略语.维护表)。

 
   我需要创建一个强类型(指定类型)的DataTable包含表的定义,用来取代LINQ to SQL模板。这个datatable存储了字段的元数据(字段名,类型,等等)以便我们对表的操作。右键点击项目工程,添加新模板,选择DataSet template。我将它命名为TableSchemaDataSet,然后拖拽Server Explorer-〉Data Connection –〉Northwind Database下的存储过程GetTableSchema(这个已经在前面章节中生成好了)到DataSet 设计器上,自动为我们生成所需的DataTable,重命名为TableSchema,并保存。

 
   接下来精彩的部分。因为我们并不想知道当前表的定义,我们会加载一个通用类型的DataTable生成一个动态的WPF 窗口。DataTable和DataSet都建好了,还有两个步骤需要手动设置。

   首先定义一个公共属性(Public Property)存放表名,同时设置默认值为“Shippers”,然后创建一批私有变量引用所需的ADO.NET对象。

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

 


    现在UI定义好了,然后设置主键字段(TableSchemaDataRow 对象)以便我们点击Find按钮时运用到Update和Select操作。

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

 

Private Sub SetPrimaryKey()    这样我们就可以通过设置TableName,动态生成UI,通过Find找到想要的数据,并可以修改保存数据。而且可以不用重新编译修改数据库表结构。
 

    ‘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

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

 

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

 

    然后我们可以在Loaded事件处理函数中编写逻辑代码,加载元数据,创建和加载XAML显示UI,再然后就是处理TableDataAdaper上的UpdateCommand。

《基于WPF LINQ的动态数据模型》有27个想法

  1. Pingback: install drupal themes
  2. It is unrealistic correctly to expect a psychic to always have the suitable answers. However, in fairness if they’re not able to check out what lies ahead for you, they should be honest and show you they can’t see anything.

  3. Awesome post, identical good points. Just being here has improved my noesis and I am look forward to it equally a very good info hub, I will pronounce there are hundreds of posts to actually take a flavor at. For example this office and the replies. I must enounce that I got the virtually of the info from the replies here. Points were really talking nearly the experience here. I love to be the part of this forum and amplification some good information. Thanks!

  4. Wonderful beat ! I wish to apprentice while you amend your website, how could i subscribe for a blog website? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast provided bright clear idea

  5. Its like you read my mind! You seem to know so much about this, like you wrote the book in it or something. I think that you can do with a few pics to drive the message home a little bit, but other than that, this is wonderful blog. A great read. I’ll certainly be back.

  6. Magnificent beat ! I wish to apprentice while you amend your web site, how can i subscribe for a blog site? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast offered bright clear concept

  7. You really make it seem so easy with your presentation but I find this topic to be really something which I think I would never understand. It seems too complex and very broad for me. I am looking forward for your next post, I’ll try to get the hang of it!

  8. Definitely believe that which you said. Your favorite justification appeared to be on the net the easiest thing to be aware of. I say to you, I definitely get annoyed while people think about worries that they plainly don’t know about. You managed to hit the nail upon the top and defined out the whole thing without having side effect , people could take a signal. Will likely be back to get more. Thanks

发表评论