merging tables in code behind

Question

4.50/5 (2 votes)

See more:

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 15-Apr-12 20:07pm

danait25

Add a Solution

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..
....?!?@.....

2 solutions

Solution 1

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.

Posted 17-Apr-12 7:21am

Maciej Los

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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

VJ Reddy · Accepted Answer · 2012-04-17T07:45:00

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