使用多个条件搜索相关表 [英] Searching related tables with multiple criteria

问题描述

我需要允许用户搜索多表(具有多个关系)数据集中的任何条件.输入条件并用户按下搜索按钮后，我想允许用户滚动查看包含每个找到的记录的关键字段的列表框.当在列表框中选择每个键字段时，与该键相关的所有记录都将显示在表单上.有很多表，但我还没有绑定过滤或数据集选择或DataViewManagers起作用.到目前为止，创建所有表的视图并运行以下代码最接近工作.这是我目前所得到的非常简单的版本(仅使用一个搜索条件):

I need to allow users to search on any criteria in a multiple table (with multiple relations) dataset. Once the criteria is entered and the user presses the search button, I''d like to allow the user to scroll through a list box containing a key field of each of the found records. As each of the key fields is selected in the list box, all records tied to that key will show up on the form. There are many tables, and I haven’t gotten binding filtering or dataset selects or DataViewManagers to work. So far, creating a view of all the tables and running the following code comes closest to working. This is a very simplistic version (using only one search criteria) of what I’ve currently got:

Private Sub SearchButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SearchButton.Click
        Me.tblChildForSearchTableAdapter.FillByUser(Me.DsSearch. tblChildForSearch, SearchTextBox.Text)
        ''Create a table of distinct DocIDs
        Dim Parentdt As DataTable
        ''Parentdt = DsSearch.VwAllDocSearch.DefaultView.ToTable(True, "DocID")
        Parentdt = DsSearch. tblChildForSearch.DefaultView.ToTable(True, New String() {"DocID", "IndexNo", "FileNo"})
        ''Get the number of rows in the new table
        Dim parentdtCount As Integer
        parentdtCount = Parentdt.Rows.Count
        ''Copy the rows in parentdt to tblParentForSearch
        For i = 0 To parentdtCount - 1
            DsSearch.tblParentForSearch.ImportRow(Parentdt.Rows(i))
        Next
        ''Create a parent,child relation between tblParentForSearch and tblChildForSearch
        Dim colChildID As DataColumn = DsSearch. tblChildForSearch.Columns("DocID")
        Dim colParentID As DataColumn = DsSearch.tblParentForSearch.Columns("DocID")
    
        Dim dr As DataRelation
        dr = New DataRelation("Parent2Child", colParentID, colChildID)

        ''Set up binding sources
        Dim ParentBindingSource = New BindingSource
        With ParentBindingSource
            .DataMember = "tblParentForSearch"
            .DataSource = DsSearch
        End With
        
        Dim ChildBindingSource = New BindingSource
        With ChildBindingSource
            .DataMember = "Parent2Child"
            .DataSource = ParentBindingSource
        End With
    End Sub

然后，我将listbix绑定到ParentBindingSource，并将datagridview绑定到ChildBindingSource.尽管两个对象都填充了正确的数据，但两者之间没有绑定或连接.我不确定我要去哪里.
我有大约10个子表和1个父表.这是我想出的唯一可以解决的问题.

I then bind a listbix to the ParentBindingSource and a datagridview to the ChildBindingSource. Although both objects fill with the correct data, there’s no binding or connection between the two. I''m not sure where I''m going wrong.
I have about 10 child tables and 1 parent table. This is the only thing I’ve come up with that’s come close to working. Any insight on my code or other ways to accomplish this would be very appreciated!

推荐答案

处理复杂数据时，最好的做法是在数据库端.

首先在数据库上创建主键-外键关系(假设已规范化)
创建一个存储过程，为您提供第一个结果集，以填充您的列表.

创建第二个存储过程，以提供从列表中选择后所需的结果集.

一旦您对这两个过程都能给出正确的结果感到满意，就可以在您的前端开始工作.

用户界面的第一部分应提供需要在搜索按钮上运行的第一个SP的参数.第二个过程将从列表中进行选择，并返回所需的数据.

When working with complex data it is best practice to do most of your work on the database side.

First of all create primary-foreign key relationships on your database(assuming that it is normalised)
Create a stored procedure to give you the first result set to populate your list.

Create a second stored procedure to give the result set required after selection from the list.

Once you are happy that both procedures give the correct results start to work on you front end.

The first part of your user interface should provide the parameters for the first SP that needs to run on your search button. The second procedure would take the selection from the list and return the required data.

这篇关于使用多个条件搜索相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！