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

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!!

Contents hide

Answers:

Method 1

Method 2

Method 3

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);
}

Method 2

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.

Method 3

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating