基于WPF LINQ的动态数据模型

Categories: LINQ, WPF
Comments: 27 Comments
Published on: 2008 年 09 月 22 日

[原文作者]: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。

27 Comments - Leave a comment
  1. seo software说道:

    Thanks for the tips. Interesting however seems like there is a few exhausting paintings ahead for me!

  2. binary option说道:

    I think this is one of the most significant information for me. And i’m glad reading your article. But should remark on some general things, The site style is ideal, the articles is really great

  3. Appreciate it for sharing the information with us.

  4. quoted on Colon Cleanse Home Remedies说道:

    Colon Cleanse Health…

    […]we like to honor other sites on the web, even if they aren’t related to us, by linking to them. Below are some sites worth checking out[…]…

  5. I think this is one of the most important information for me. And i am glad reading your article. But want to remark on few general things, The web site style is ideal, the articles is really excellent

  6. You made some respectable factors there. I seemed on the web for the problem and found most people will associate with with your website.

  7. I like the valuable information you provide in your articles. I’ll bookmark your weblog and check again here frequently. I’m quite certain I will learn plenty of new stuff right here! Best of luck for the next!

  8. Best super deals说道:

    Hi there! Do you use Twitter? I’d like to follow you if that would be ok. I’m definitely enjoying your blog and look forward to new updates.

  9. I don’t normally respond to posts but I will in this case. :)

  10. quoted on install drupal themes说道:

    Blog Maker…

    […]follow the other links to read more[…]…

  11. psychic powers说道:

    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.

  12. hunting blinds说道:

    Very good read – do you guys have a Twitter I can follow?

  13. bull blinds说道:

    Awesome post – keep up the good work.

  14. Ganar Dinero说道:

    Hi there, You have done an excellent job. I’ll certainly digg it and personally recommend to my friends. I am sure they will be benefited from this website.

  15. I don’t even know how I ended up here, but I thought this post was good. I do not know who you are but definitely you are going to a famous blogger if you aren’t already ;) Cheers!

  16. Andree Sauders说道:

    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!

  17. portable sinks说道:

    Excellent read, I just passed this onto a friend who was doing a little research on that.

  18. Limewire说道:

    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

  19. Gaylord Vergo说道:

    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.

  20. online说道:

    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

  21. 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!

  22. malware说道:

    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

  23. Kisha Toppins说道:

    I often read your blog admin try to discover it very fascinating. Thought it was about time i show you , Sustain the truly fantastic work

  24. I discovered your site through research engine a few moment ago, and luckily, this is the only details I was searching for the last hours

  25. Greetings, You write some good blogs. I examine back the following always to learn if you have kept as much as date. I concept you possibly can desire to know, as soon as I click your RSS feed it re-directs me to one more website.

Leave a comment


Welcome , today is 星期五, 2017 年 05 月 26 日