How to merge two datatables in c# (without any common identifier)?8

Question

I am trying to merge two data table dt and dt4. I want to merge 2 data tables, having different schemas, and no common identifier together. Becuase the first data table dt contains some values.I get dt count on x. Based on that count I have to fetch some value table and stored in dt4. if if dt contains 3 row value then x=3 and dt4 contains 3 values. After these process I have to merge dt and dt4 and displayed as one table. While using the given below code getting one error. The error is : "'column' argument cannot be null." Help me to find a proper soluion. Thank you.

Code:

protected void ddlCircle_SelectedIndexChanged(object sender, EventArgs e)
{
    ShadingAnalysisDataSetTableAdapters.tbl_CadEngineersTeamTableAdapter cd;
    cd = new ShadingAnalysisDataSetTableAdapters.tbl_CadEngineersTeamTableAdapter();
    DataTable dt = new DataTable();
    dt = cd.GetAvailableData(ddlCircle.SelectedValue); // Getting details of unassigned site

    int x, y;

    DataTable dt3 = new DataTable();
    dt3 = cd.GetTeam();
    y = dt3.Rows.Count;

    x = dt.Rows.Count; // counting the unassinged sites

    DataTable dt2 = new DataTable();
    dt2 = cd.GetAssignTeam(x);           //Getting team based on count

    string[] arr = new string[dt2.Rows.Count];
    int i = 0;
    foreach (DataRow r in dt2.Rows)
    {
        arr[i] = r["Team"].ToString(); // assigning available team to array
        i++;
    }

    string[] strArr = new string[x+1]; // another array to copy arr values.

    i = 0; int j = 0;
    while (j <= x)
    {
        strArr[j]=  arr[i] ; // copying the arr[] values into strArr[] based on count.
        i++;
        j++;

        if (i == y)
        {
            i = 0;
        }
    }
     DataTable dt4 = new DataTable();
     dt4.Columns.Add("Team");

     foreach (string s in strArr)
       {
          dt4.Rows.Add(s); // Converting string array strArr[] to data table dt4
        }

     dt.Merge(dt4);  // error poppup here. Error : 'column' argument cannot be null.
     GridView2.DataSource = dt;
     GridView2.DataBind();
}

dt contain

State  District  SiteID  SiteName
-----  --------  ------  --------
Sate1  District1  1001     A
Sate2  District2  1002     B
Sate3  District3  1003     C

dt4 contain

Team
-----
Team1
Team2
Team3

I need a final output as:

State  District  SiteID  SiteName  Team
-----  --------  ------  --------  -----
Sate1  District1  1001     A       Team1
Sate2  District2  1002     B       Team2
Sate3  District3  1003     C       Team3

without any common column and not even single condition!!! brother what are you doing.. This will be very nasty if some how you merge these datatable (by using loops etc.) Because what will happn if someone delete or add data in a table..How will you co relate with other table! — yogi970, Oct 29 '14 at 11:46
@yogi970 : I have getting dt4 value based on dt count. So there is no issues while add or delete data — Vipin, Oct 29 '14 at 12:01

score 0 · Answer 1 · answered Oct 29 '14 at 11:07

0

Do you have relationship. If you have primary key ID in First Table and you can add ID column in Second Table

See here MSDN Article Merge Datatables

answered Oct 29 '14 at 11:07

Manish Goswami

863
10
27

score 0 · Answer 2 · edited May 23 '17 at 12:13

0

If you add a relationship between the 2 datatables, like a common identifier - you would be able to extract the common columns between datatables automatically using Linq.

Something like the following;

var commonColumns = dt1.Columns.OfType<DataColumn>().Intersect(dt2.Columns.OfType<DataColumn>(), new DataColumnComparer());
        DataTable result = new DataTable();

        dt1.PrimaryKey = commonColumns.ToArray();

        result.Merge(dt1, false, MissingSchemaAction.AddWithKey);
        result.Merge(dt2, false, MissingSchemaAction.AddWithKey);

Have a look at a previous question about this Full outer join, on 2 data tables, with a list of columns

edited May 23 '17 at 12:13

Community

1
1

answered Oct 29 '14 at 11:10

Mez

4,666
4
29
57

Without any common idenitfier how can I merge two data table. – Vipin Oct 29 '14 at 11:12
Why would you want to merge 2 data tables, having different schemas, and no common identifier together? – Mez Oct 29 '14 at 11:16
To get your desired output, add an ID column to each data table, with an identity increment. In your loop you can add 1 for the first record, 2 for the second etc. This would be done for both tables... Then use my logic to merge them together. – Mez Oct 29 '14 at 11:18
I want to merge 2 data tables, having different schemas, and no common identifier together. Becuase the first data table dt contains some values.I get dt count on x. Based on that count I have to fetch some value table and stored in dt4. if if dt contains 3 row value then x=3 and dt4 contains 3 values. After these process I have to merge dt and dt4 and displayed as one table. – Vipin Oct 30 '14 at 03:48
So in that case why don't you get the first table, iterate the records, and then update each record with missing column values that you require? – Mez Oct 30 '14 at 13:55

score 0 · Accepted Answer · answered Oct 31 '14 at 11:22

The given code is working perfectly. Thank you for all valuable comments.

Code :

protected void ddlCircle_SelectedIndexChanged(object sender, EventArgs e)
{
    ShadingAnalysisDataSetTableAdapters.tbl_CadEngineersTeamTableAdapter cd;
    cd = new ShadingAnalysisDataSetTableAdapters.tbl_CadEngineersTeamTableAdapter();
    DataTable dt = new DataTable();
    dt = cd.GetAvailableData(ddlCircle.SelectedValue); // Getting details of unassigned site

    int x, y;

    DataTable dt3 = new DataTable();
    dt3 = cd.GetTeam();
    y = dt3.Rows.Count;

    x = dt.Rows.Count; // counting the unassinged sites

    DataTable dt2 = new DataTable();
    dt2 = cd.GetAssignTeam(x);           //Getting team based on count

    string[] arr = new string[dt2.Rows.Count];
    int i = 0;
    foreach (DataRow r in dt2.Rows)
    {
        arr[i] = r["Team"].ToString(); // assigning available team to array
        i++;
    }

    string[] strArr = new string[x+1]; // another array to copy arr values.

    i = 0; int j = 0;
    while (j <= x)
    {
        strArr[j]=  arr[i] ; // copying the arr[] values into strArr[] based on count.
        i++;
        j++;

        if (i == y)
        {
            i = 0;
        }
    }
     DataTable dt4 = new DataTable();
     dt4.Columns.Add("Team");

     foreach (string s in strArr)
       {
          dt4.Rows.Add(s); // Converting string array strArr[] to data table dt4
        }
 //Adding new data table for merging two table details

        DataTable dt7 = new DataTable();

        //create columns and copy data from dt:

        dt7 = dt.Copy();

        foreach (DataColumn column in dt4.Columns)
        dt7.Columns.Add(column.ColumnName, typeof(string));


        //copy data from dt4:

        foreach (DataRow row in dt4.Rows)
        {
            dt7.Rows[dt4.Rows.IndexOf(row)][5] = row[0];
        }    

        GridView3.DataSource = dt7;
        GridView3.DataBind();

    }

score -2 · Answer 4 · edited Oct 30 '14 at 09:19

You don't have relationship with table dt and table dt4.

Problem::You are facing this error because you dont have any relation between these two data tables.

I think you better start by adding relationship first. If you have primary key ID in dt and you can add ID column in dt4 with the relevant ID.

So first create a column in both tables which is common and then on basis of that relationship you can use DataTable.Merge method directly

How to merge two datatables in c# (without any common identifier)?8

4 Answers4