WPF Datagrid将第二个数据库表显示为ComboBox选项 [英] WPF Datagrid Showing Second DB Table As ComboBox Options

问题描述

我在数据库中添加了第二个表，其中包含我想在组合框中看到的不同选项，这些选项针对新列中datagrid的每一行.每个选项都有一个与之关联的键，该键与主表中的一列匹配，该键的值介于0到8之间.我有2个问题，其中一个已经解决:

I have added a second table to my database which contains the different options i want to see in a combobox for each row of my datagrid in a new column. Each option has a key associated with it that matches up to a column in my main table, which is a value from 0 to 8. I have 2 problems, one of which i have resolved:

1. 当网格加载时，我希望组合框列显示与每一行的[Status]键相对应的[Description].这正在工作.

1. When the grid loads, i want the combobox column to show the [Description] that corresponds to each row's [Status] key. This is working.

我希望用户选择[Description]单元格并在下拉菜单中打开所有可能的[Description]选项，以便他可以更改项目的状态.

I want the user to select the [Description] cell and have a dropdown open all of the possible [Description] options so that he can change the item's status.

例如，如果某个组件处于保留状态，则用户需要在网格中选择该组件，然后在状态列的下拉列表中选择保留".

For example, if a component goes on hold, the user needs to select that component in the grid and select "On Hold" in the dropdown of the status column.

状态可以正确显示，但下拉菜单中不会显示CNCComponentStatus表中的8个选项.我敢打赌我没有在SqlDataAdapter中用第二张表正确填充数据集.那或者我没有正确使用SelectedItem/ItemSource/SelectedValuePath.

The status correctly shows, but the dropdown doesn't populate with the 8 options from the CNCComponentStatus table. I am willing to bet i am not filling the dataset with the second table correctly in the SqlDataAdapter. That or i am not correctly using SelectedItem/ItemSource/SelectedValuePath correctly.

这是网格的屏幕截图这是我的数据结构

Here is a screenshot of the grid Here is my data structure

这些是状态"选项:

这是数据网格的xaml(仅相关部分):

Here's the xaml for the datagrid (only the pertinent parts):

<DataGrid Name="dataGrid1" ItemsSource="{Binding Path=test_3DimensionalDB}" AutoGenerateColumns="False">
<DataGridTemplateColumn x:Name="StatusColumn" Header="Status" Width="*" IsReadOnly="False">
    <DataGridTemplateColumn.CellTemplate>
        <DataTemplate>
            <TextBlock x:Name="cboStatus" Text="{Binding Path=Description, Mode=TwoWay}"/>
        </DataTemplate>
    </DataGridTemplateColumn.CellTemplate>
    <DataGridTemplateColumn.CellEditingTemplate>
        <DataTemplate>
            <ComboBox x:Name="StatusCombo" IsEditable="True" SelectedValuePath="{Binding Status}" DisplayMemberPath="{Binding Description}" />
        </DataTemplate>
    </DataGridTemplateColumn.CellEditingTemplate>
</DataGridTemplateColumn>

这是获取数据的方法:

 Private Sub SelectQuery(ByVal company As String, ByVal status As String, ByVal component As String)

    Dim com As String = "SELECT tmfCNCComponent_threed.[CNCComponentKey]
    ,tmfCNCComponent_threed.[CompanyID]
    ,tmfCNCComponent_threed.[JobNumber]
    ,tmfCNCComponent_threed.[LogNumber]
    ,tmfCNCComponent_threed.[Customer]
    ,tmfCNCComponent_threed.[DueDate]
    ,tmfCNCComponent_threed.[JobLeader]
    ,tmfCNCComponent_threed.[CADProgrammer]
    ,tmfCNCComponent_threed.[Salesperson]
    ,tmfCNCComponent_threed.[CNCProgrammer]
    ,tmfCNCComponent_threed.[ComponentDescription]
    ,tmfCNCComponent_threed.[ComponentFilePath]
    ,tmfCNCComponent_threed.[Material]
    ,tmfCNCComponent_threed.[ComponentSizeX]
    ,tmfCNCComponent_threed.[ComponentSizeY]
    ,tmfCNCComponent_threed.[ComponentSizeZ]
    ,tmfCNCComponent_threed.[QuantityShown]
    ,tmfCNCComponent_threed.[QuantityMirror]
    ,tmfCNCComponent_threed.[UpdateTime]
    ,tmfCNCComponent_threed.[Status]
    ,tmfCNCComponent_threed.[ProgStarted]
    ,tmfCNCComponentStatus_threed.[Description]
    FROM [test_3DimensionalDB].[dbo].[tmfCNCComponent_threed]
    INNER JOIN tmfCNCComponentStatus_threed
    ON tmfCNCComponent_threed.Status = tmfCNCComponentStatus_threed.CNCComponentStatusKey 
    WHERE [ComponentDescription] " & component & " 'trode%' AND [CompanyID]='" & company & "' AND [Status]" & status & "ORDER BY [UpdateTime] DESC"

    Dim Adpt As New SqlDataAdapter(com, con)
    con.Open()
    Dim ds As New DataSet()
    Adpt.Fill(ds, "dbo.tmfCNCComponent_threed")

    dataGrid1.ItemsSource = ds.Tables("dbo.tmfCNCComponent_threed").DefaultView

    con.Close()

    RowCount()
    searchBox.Clear()

End Sub

感谢您的宝贵时间，非常抱歉.

Thanks for your time, sorry for the extreme detail.

由于我绑定不正确，这就是我尝试填充组合框的方法.虽然我不太了解如何只显示一列而不是DefaultView.另外，由于其保护级别，无法访问"StatusCombo"吗?

Here is how i would attempt to populate the combobox since i am not binding properly. Although i don't quite understand how to show only one column instead of the DefaultView. Also, "StatusCombo" is inaccessible due to it's protection level?

    Dim com2 As String = "SELECT * FROM tmfCNCComponentStatus_threed"
    Dim AdptStatus As New SqlDataAdapter(com2, con)
    AdptStatus.Fill(ds, "dbo.tmfCNCComponentStatus_threed")
    StatusCombo.ItemsSource = ds.Tables("dbo.tmfCNCComponentStatus_threed").DefaultView

我认为，

推荐答案

这就是组合框的外观.不需要x:Name属性.我怀疑是否也需要IsEditable.

Here's what that combobox should end up looking like, I think. The x:Name property isn't needed. I doubt that IsEditable is needed either.

<DataGridTemplateColumn.CellEditingTemplate>
    <DataTemplate>
        <ComboBox 
            IsEditable="True" 
            SelectedValuePath="Status" 
            DisplayMemberPath="Description" 
            SelectedValue="{Binding Status}"
            />
    </DataTemplate>
</DataGridTemplateColumn.CellEditingTemplate>

SelectedValuePath 和 DisplayMemberPath 是字符串.它们是填充 ComboBox 的数据行中的列的名称(如果使用常规C#类实例填充它，则它们将是item类的属性的名称).您试图将这些属性绑定到您没有的视图模型的属性，但是即使您拥有一个视图模型，也不会拥有那些属性.但是，什么是绑定以及绑定的概念一开始可能很奇怪.

SelectedValuePath and DisplayMemberPath are strings. They're the names of columns in the data rows that populate the ComboBox (if you populate it with regular C# class instances, they would instead be the names of properties of the item class). You were trying to bind those properties to properties of a viewmodel you don't have, but even if you did have one, it wouldn't have those properties. But the concept of what a binding is and what it does can be weirdly slippery at first.

我几乎告诉过您 DisplayMemberPath更好.

I almost told you to read the documentation on SelectedValuePath, but the documentation has been revised and is now essentially meaningless. DisplayMemberPath is better.

第二:您应该有一个视图模型.缺少视图模型会使一切变得更加困难.但是您没有视图模型.

Second: You should have a viewmodel. The lack of a viewmodel makes everything more difficult. But you don't have a viewmodel.

您需要做的是从数据库中获取所需的内容，并将其放在组合框可以绑定到的某个位置的某个集合中.

What you need to do is get what you want from the database, and put it in some kind of collection somewhere that the combobox can be bound to.

我们缺少一个视图模型来充当该集合的自然宿主，我们将通过在窗口的资源"中创建一个CollectionViewSource来做到这一点.

Lacking a viewmodel to act as a natural home for that collection, we'll do that by creating a CollectionViewSource in your window's Resources.

<Window
    x:Class="blah blah"
    ...stuff...
    >
    <Window.Resources>
        <CollectionViewSource
            x:Key="StatusItems"
            />
    </Window.Resources>

我们将使用它来填充组合框，如下所示:

And we'll use that to populate the combobox like so:

<ComboBox 
    x:Name="StatusCombo" 
    SelectedValuePath="Status" 
    DisplayMemberPath="Description" 
    ItemsSource="{Binding Source={StaticResource StatusItems}}"
    />

但是我们的CollectionViewSource为空.因此，我们必须以某种方式填充它.每当您执行数据库查询时，我们都会这样做.

But our CollectionViewSource is empty. So we'll have to populate it somehow. We'll do that whenever you do the database query stuff.

Dim statusCVS As CollectionViewSource = FindResource("StatusItems")

Dim com2 As String = "SELECT * FROM tmfCNCComponentStatus_threed"
Dim AdptStatus As New SqlDataAdapter(com2, con)
AdptStatus.Fill(ds, "dbo.tmfCNCComponentStatus_threed")

Dim statusRows = ds.Tables("dbo.tmfCNCComponentStatus_threed").Rows
Dim statuses As New List(Of Object)

For Each row As DataRow In statusRows
    statuses.Add(New With {
            .Status = CInt(row("CNCComponentStatusKey")),
            .Description = CStr(row("Description"))
        })
Next

statusCVS.Source = statuses

如果可以的话，这会比循环更好:

This would be nicer than the loop, if it works:

Dim statuses = From row In ds.Tables("dbo.tmfCNCComponentStatus_threed")
               .Rows.Cast(Of DataRow)
               Select New With {
                    .Status = CInt(row("CNCComponentStatusKey")),
                    .Description = CStr(row("Description"))
                }

statusCVS.Source = statuses

好的，现在我们有了这部分:

OK, now we have this part:

    SelectedValue="{Binding Status}"

我猜想 test_3DimensionalDB (您的DataGrid实际在填充吗?)必须有一些列是组合中Status ID值的外键，而我猜想它可能是称为状态.

I'm guessing that test_3DimensionalDB (is your DataGrid actually populating?) must have some column that's a foreign key to the Status id value in the combo, and I'm guessing that it might be called Status.

所以我们要发生的事情是:假设您有两个项目:

So what we want to happen is: Say you have two items:

item 0:
    Status = 1
    Description = Dog
item 1:
    Status = 2
    Description = Cat

DisplayMemberPath ="Description" 表示项目0将显示为"Dog"，即其Description属性的值.出于相同的原因，项目1将显示为猫".

DisplayMemberPath="Description" means that item 0 will be displayed as "Dog", the value of its Description property. Item 1 will be displayed as "Cat" for the same reason.

SelectedValuePath ="Status" 意味着当绑定将SelectedValue设置为 2 时，组合框将浏览其item集合以查找具有状态属性等于 2 ，然后选择该项目.如果我们设置 SelectedValuePath ="Fred" ，它将查找具有名为 Fred 等于 2 的属性的项目.

SelectedValuePath="Status" means that when the binding sets SelectedValue to 2, the combobox will look through its items collection to find the item that has a Status property equal to 2, and select that item. If we set SelectedValuePath="Fred", it would look for an item that had a property named Fred equal to 2.

同样，如果用户自己更改选择，则采用另一种方式:假设用户选择项目0，因此ComboBox会查询其自己的 SelectedValuePath ，看到状态"并获取所选项目的状态属性值(如果有).对于项目0，其为 1 .然后，组合框会将 1 的值分配给其自己的 SelectedValue 属性.

Likewise, if the user changes the selection himself, it goes the other way: Let's say the user selects item 0, so the ComboBox consults its own SelectedValuePath, sees "Status", and gets the Status property value (if any) of the selected item. For item 0, that's 1. Then the combobox would assign that value of 1 to its own SelectedValue property.

SelectedValue 上的绑定随后将收到有关 SelectedValue 已更改的通知，它将获取新值并更新绑定到的数据库行列(也称为在这种情况下为状态".

The Binding on SelectedValue will then receive a notification that SelectedValue has changed, and it will get the new value and update the database row column it's bound to (also named "Status", in this case).

Binding 是一个粘着在做东西的对象.这不是任务"的花哨词.

The Binding is an object that sticks around and does stuff. It's not a fancy word for "assignment".

SelectedValue="{Binding Status}"

这篇关于WPF Datagrid将第二个数据库表显示为ComboBox选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！