在代码后面合并表格 [英] merging tables in code behind

问题描述

在用户选择过滤器以显示相关详细信息之后，我有2个需要在运行时合并的数据表.
一个表(人员")具有人员信息(ID，名称，电话号码等.)
另一个表(到达")具有到达信息(id，日期，商店名称.)

我正在寻找一种合并两个表格的方式，以便在某个特定日期与到达有关，我可以看到到达的详细信息，但是即使他们没有看到人员的详细信息，我仍然会看到那天没到.
例如:

人
id fname lname电话
91约翰·史密斯5544
92金太阳3455
93伊迪丝卡贝尔5477

到达
自动编号ID到达日期商店名称
1 92 16.4.12巴厘岛
2 92 13.4.12永远
3 93 15.4.12永远


现在，如果我要过滤并查看谁在4月16日到达，则需要查看以下内容:

id fname lname电话到达日期商店名称
91约翰·史密斯5544
92 kim sun 3455 16.4.12巴厘岛
93伊迪丝卡贝尔5477

我现在正在使用2个数据表.一个从人"表中选择所有人的详细信息，另一个从我选择的日期中选择所有到达的人.
之后，我用于..合并，但是由于大约有1500人，因此加载数据网格大约需要20秒，而且让用户等待的时间太多了..

您可以建议其他方法吗?

thanx

Hi,

I have 2 datatables that I need to merge on runtime, after the user choose a filter to show the relevant details.
one table ("people") has people info (id, name, phone num, etc..)
the other table ("arrived") has arrival info (id, date, storename..)

I''m looking for a way to merge both tables where I''ll be able to see the arrival details if it''s relevant for a certain date, but I''ll still see the people details even if they didn''t arrive that day.
example:

PEOPLE
id fname lname phone
91 john smith 5544
92 kim sun 3455
93 edith cabel 5477

ARRIVED
autonum id arrival_date storename
1 92 16.4.12 bali
2 92 13.4.12 forever
3 93 15.4.12 forever


now, if I want to filter and see who arrived on april 16th , I need to see this:

id fname lname phone arrival_date storename
91 john smith 5544
92 kim sun 3455 16.4.12 bali
93 edith cabel 5477

I''m using now 2 datatables. one select all people details from people table, and the other select all people that arrived on the date I selected.
after that I use for.. next to merge, but since there''re about 1500 people it takes the datagrid about 20 seconds to load and it''s too much time to let the user wait..

can you suggest other ways to do that?

thanx

推荐答案

您也可以在运行时在SQL中合并"表.它比您的方法快得多.
使用以下代码作为数据表的来源:

You can "merge" the tables at run time in SQL too. It''s much, much faster then your method.
Use below code as a source of datatable:

SELECT P.fname, P.lname, P.phone, A.arrival_date, A.storename
FROM ARRIVED AS A LEFT JOIN PEOPLE AS P ON A.id = P.id
WHERE A.arrival_date = #2012/4/16#

希望对您有所帮助.

I hope it will be helpful.

DataTable的Merge方法可用于此目的，如下所示:

The Merge method of DataTable can be used for this purpose as shown below:

Private Sub Main()
    Dim people As New DataTable()
    people.Columns.Add("Id", GetType(Integer), Nothing)
    people.Columns.Add("Name", GetType(String), Nothing)
    people.PrimaryKey = New DataColumn() {people.Columns("Id")}

    people.Rows.Add(91, "John")
    people.Rows.Add(92, "Kim")
    people.Rows.Add(93, "Edith")

    Dim arrived As New DataTable()
    arrived.Columns.Add("AutoNum", GetType(Integer), Nothing)
    arrived.Columns.Add("Id", GetType(Integer), Nothing)
    arrived.Columns.Add("ArrivalDate", GetType(DateTime), Nothing)
    arrived.Columns.Add("StoreName", GetType(String), Nothing)

    arrived.Rows.Add(1, 92, New DateTime(2012, 4, 16), "Bali")
    arrived.Rows.Add(2, 92, New DateTime(2012, 4, 13), "Forever")
    arrived.Rows.Add(3, 93, New DateTime(2012, 4, 15), "Forever")

    Dim dataView As DataView = arrived.DefaultView
    dataView.RowFilter = "ArrivalDate=#4/16/2012#"

    Dim peopleArrived As DataTable = people.Copy()
    peopleArrived.Merge(dataView.ToTable(), False, MissingSchemaAction.Add)

    PrintTable(people,"People")
    PrintTable(arrived,"Arrived")
    PrintTable(peopleArrived,"People Arrived")
End Sub

Public Shared Sub PrintTable(table As DataTable, title as String)
    Console.WriteLine("--------------{0}------------",title)
    For Each row As DataRow In table.Rows
        For Each col As DataColumn In table.Columns

            Console.Write("{0,-15}", row(col.ColumnName).ToString())
        Next
        Console.WriteLine()
    Next
End Sub
'--------------People------------
'91             John
'92             Kim
'93             Edith
'--------------Arrived------------
'1              92             4/16/2012 12:00:00 AMBali
'2              92             4/13/2012 12:00:00 AMForever
'3              93             4/15/2012 12:00:00 AMForever
'--------------People Arrived------------
'91             John
'92             Kim            1              4/16/2012 12:00:00 AMBali
'93             Edith

这篇关于在代码后面合并表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！