How to go through two tables to create another table with common values

Question

First Datatable is dt

var dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("First Name");
   dt.Rows.Add(1,"name1");
   dt.Rows.Add(6,"name6");
   dt.Rows.Add(4,"name4");

The second table is dt2

var dt2 = new DataTable();
                dt2.Columns.Add("ID");
                dt2.Columns.Add("First Name");
                dt2.Columns.Add("Last Name");
                dt2.Columns.Add("Birthday");

       dt2.Rows.Add(1,"name1", "lastName1", 01.01.1991);
       dt2.Rows.Add(2,"name2", "lastName2", 02.02.1992);
       dt2.Rows.Add(3,"name3", "lastName3", 03.03.1993);
       dt2.Rows.Add(4,"name4", "lastName4", 04.04.1994);
       dt2.Rows.Add(5,"name5", "lastName5", 05.05.1995);
       dt2.Rows.Add(6,"name6", "lastName6", 06.06.1996);

in the third DataTable dt3, I want to get those values where ID is the same

result:

ID   Name   Birthdate
1    name1  01.01.1991
4    name4  04.04.1994
6    name6  06.06.1996

how to go through the DataTable's in c# ?

Why are you not doing this in SQL? Why are you using data tables for your data types? — Oded, Mar 15 '12 at 20:33
If you can use LINQ please have a look at this example. http://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp — Kaf, Mar 15 '12 at 20:36

score 1 · Answer 1 · answered Mar 15 '12 at 20:37

1

Write an SQL Query or a Stored Procedure such that it joins the Two tables like you have depicted. Now use that Query for your DataTable with in .Net. You will get what you need.

answered Mar 15 '12 at 20:37

Shankar Narayana Damodaran

68,075
43
96
126

score 1 · Accepted Answer · answered Mar 15 '12 at 20:50

Unfortunately, there's no easy way, AFAIK, to join the 2 tables and get the third table automagically unless you are willing to write some code....

You can join them using Linq first:

var common = from c in dt.AsEnumerable()
             join x in dt2.AsEnumerable() on c.Field<string>("ID") equals x.Field<string>("ID")
             select new object[] { c["ID"],c["First Name"], x["Birthday"] };

And now you can create the destination table with the schema you want:

DataTable dt3 = new DataTable();
dt3.Columns.Add("ID");
dt3.Columns.Add("Name");
dt3.Columns.Add("Birthdate");
foreach (var item in common)
   dt3.LoadDataRow(item.ToArray(),true);

score 0 · Answer 3 · answered Mar 15 '12 at 20:34

0

Are you adding them to an actual database with a defined schema or just trying to do this in memory? If memory, I would add these to a DataSet which you can then use to filter the criteria becuase in the dataset, you can define their relationship.

answered Mar 15 '12 at 20:34

Craig Trombly

464
2
9

score -1 · Answer 4 · edited Jun 20 '20 at 09:12

-1

You might be looking for adding Relationship between your tables. Check this link.

I guess you will need the DataTables to be in the same DataSet (you can add the DataTables into DataSet).

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 15 '12 at 20:32

Arun

2,493
15
12

1

This is more of a comment than an answer. – RQDQ Mar 15 '12 at 20:35
@RQDQ You want me to post the complete solution instead? you are a funny guy! – Arun Mar 15 '12 at 20:36

How to go through two tables to create another table with common values

4 Answers4