Click here to Skip to main content
16,020,669 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
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
Posted
Comments
Hendra Yudha P 17-Apr-12 3:52am    
why you do not do it in SQL rather than having to do it in code behind?
danait25 17-Apr-12 4:47am    
the reason is that I'm using access database (since it's a pretty small DB) and accessing data with sql statments in the dataadapter functions.
The "arrived" table is actually a view (I've added a new dataadapter that merge 2 tables from access) and since it's a view -I can't manage to create a NEW view that merge the OLD view with the "people" table..
....?!?@.....

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:
SQL
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.
 
Share this answer
 
Comments
danait25 18-Apr-12 0:25am    
I need to open a different connection string? or the tables are datatables ?
I don't get it - how can I use it in my code?
Maciej Los 18-Apr-12 17:08pm    
Yes, you need to create new OleDbConnection and use OleDbDataReader object to Load() data into DataTable. After that: DataGridView1.DataSource = myDataTable. If my solution is helpful, mark it as "Solved". You can mark more than one answer.
The Merge method of DataTable can be used for this purpose as shown below:
VB
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
 
Share this answer
 
Comments
danait25 18-Apr-12 3:49am    
worked great, it's so much faster now!
VJ Reddy 18-Apr-12 4:02am    
You're welcome and thank you for the response.
Maciej Los 18-Apr-12 17:10pm    
My 5 too!
VJ Reddy 18-Apr-12 19:41pm    
Thank you, losmac.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900