Merging 2 datatables in to 1 datatable with same number of rows.

Question

How can i merge two Datatables into the same row. I am using different stored procedures to get data into datasets. In asp.net using c#, i want to merge them so there are same number of rows as table 1 with an added column from table 2.

For example:

DataTable table1 = dsnew.Tables[0];
DataTable table2 = dsSpotsLeft.Tables[0];
table1.Merge(table2);

This is fetching me 4 rows instead of 2 rows. What am i missing here? Thanks in advance!!

Could you describe the two tables? A list of fieldnames in both may suffice — Bob Kaufman, Sep 27 '12 at 19:14
So, you're pulling data from 2 tables via stored procs into DataTable objects, then attempting to merge the data. Can you do it in the stored procs? — Nick Vaccaro, Sep 27 '12 at 19:29

score 5 · Answer 1 · edited Apr 10 '17 at 02:05

5

You cannot use the method Merge in this case, instead you should create new DataTable dt3, and then add columns and rows based on the table 1 and 2:

var dt3 = new DataTable();

var columns = dt1.Columns.Cast<DataColumn>()
                  .Concat(dt2.Columns.Cast<DataColumn>());

foreach (var column in columns)
{
    dt3.Columns.Add(column.ColumnName, column.DataType);
}

//TODO Check if dt2 has more rows than dt1...
for (int i = 0; i < dt1.Rows.Count; i++)
{
    var row = dt3.NewRow();
    row.ItemArray = dt1.Rows[i].ItemArray
                       .Concat(dt2.Rows[i].ItemArray).ToArray();

    dt3.Rows.Add(row);
}

edited Apr 10 '17 at 02:05

Jeremy Thompson

61,933
36
195
321

answered Sep 28 '12 at 06:28

cuongle

74,024
28
151
206

Concat is static method of String.Concat – ShujatAli Sep 18 '14 at 08:05
@shujatAli: Well, it's also a method on Array, please take a look MSDN – cuongle Sep 18 '14 at 08:09
@CoungLe Please take a look at msdn public methods of array http://msdn.microsoft.com/en-us/library/system.array(v=vs.110).aspx – ShujatAli Sep 18 '14 at 09:18

score 1 · Accepted Answer · answered Sep 27 '12 at 19:26

Without knowing more about the design of these tables, some of this is speculation.

What it sounds like you want to perform is a JOIN. For example, if you have one table that looks like:

StateId, StateName

and another table that looks like

EmployeeId, EmployeeName, StateId

and you want to end up with a result set that looks like

EmployeeId, EmployeeName, StateId, StateName

You would perform the following query:

SELECT Employee.EmployeeId, Employee.EmployeeName, Employee.StateId, State.StateName
FROM Employee
INNER JOIN State ON Employee.StateId = State.StateId

This gives you a resultset but doesn't update any data. Again, speculating on your dataset, I'm assuming that your version of the Employee table might look like the resultset:

EmployeeId, EmployeeName, StateId, StateName

but with StateName in need of being populated. In this case, you could write the query:

UPDATE Employee
SET Employee.StateName = State.StateName
FROM Employee
INNER JOIN State ON Employee.StateId = State.StateId

Tested in SQL Server.

score 1 · Answer 3 · edited May 23 '17 at 11:54

Assuming you have table Category and Product related by CategoryID, then try this

var joined = from p in prod.AsEnumerable()
             join c in categ.AsEnumerable()
             on p["categid"] equals c["categid"]
             select new
             {
                 ProductName = p["prodname"],
                 Category = c["name"]
             };

var myjoined = joined.ToList();

Sources

That was a LINQ solution. You can also loop through the first datatable and add columns from the second datatable

Merging 2 datatables in to 1 datatable with same number of rows.

3 Answers3

Linked

Related