How to merge multiple datatables (every datatable return one row) in one row

Question

I have multiple data tables : i want to merge them (My P.K : emp_num)

var tblEmp_data = new DataTable();//one row
var tblEmp_time = new DataTable();//one row
var tbl_emp_mission = new DataTable();//one row

tblEmp_data = GetEmpInfo(empNum);
tblEmp_time = GetEmpTime(empNum, start_period, end_period);
tbl_emp_mission = GetEmpMission(empNum, start_period, end_period);

tblEmp_data.Merge(tblEmp_time, false, MissingSchemaAction.Add);
tblEmp_data.AcceptChanges();
tblEmp_data.Merge(tbl_emp_mission, false, MissingSchemaAction.Add);
tblEmp_data.AcceptChanges();

Now i get the data on multiple rows rather than one row ! I want the data on one row ? how to do this ?

Note: i want all the columns allow null except the primary key so i avoid this exception :

failed to enable constraints. one or more rows contain values violating non-null, unique, or foreign-key constraints

Edited:

The third table which cause the problem:

public static DataTable GetEmpMission(int empNum, DateTime start_period, DateTime end_period)
        {
            using (IfxConnection con = new IfxConnection(ConfigurationManager.ConnectionStrings["tl"].ToString()))
            {
                DataTable dt = new DataTable();
                StringBuilder cmdTxt = new StringBuilder();
                cmdTxt.Append(" SELECT COUNT(emp_num)  ");
                cmdTxt.Append(" FROM hs_mission WHERE emp_num = ? AND from_date BETWEEN ? AND ?  ");

                using (var myIfxCmd = new IfxCommand(cmdTxt.ToString(), con))
                {

                    myIfxCmd.CommandType = CommandType.Text;
                    if (con.State == ConnectionState.Closed)
                    {
                        con.Open();
                    }
                    myIfxCmd.Parameters.Add("emp_num", IfxType.SmallInt);
                    myIfxCmd.Parameters.Add("start_period", IfxType.Date);
                    myIfxCmd.Parameters.Add("end_period", IfxType.Date);

                    myIfxCmd.Parameters[0].Value = ((object)empNum) ?? DBNull.Value;
                    myIfxCmd.Parameters[1].Value = ((object)start_period.Date) ?? DBNull.Value;
                    myIfxCmd.Parameters[2].Value = ((object)end_period.Date) ?? DBNull.Value;

                    using (IfxDataReader dr = myIfxCmd.ExecuteReader())
                    {
                        dt.Load(dr);
                        dt.Columns.Add("emp_num", typeof(Int32));
                        dt.Rows[0]["emp_num"] = empNum;
                        dt.AcceptChanges();
                    }

                }
                con.Close();
                con.Dispose();
                return dt;


            }

        }

Return data like this :

column1            emp_num 

 0                   6762

and throw exception :

failed to enable constraints. one or more rows contain values violating non-null, unique, or foreign-key constraints

you have three tables can you give a example like data in all those three table and the format for the 4th table? you have provided for only one. — Ratna, May 25 '13 at 07:16

score 9 · Accepted Answer · edited May 23 '17 at 10:30

9

So empNum is the key column which all tables share? It sounds as if you could use my MergeAll:

public static DataTable MergeAll(this IList<DataTable> tables, String primaryKeyColumn)
{
    if (!tables.Any())
        throw new ArgumentException("Tables must not be empty", "tables");
    if(primaryKeyColumn != null)
        foreach(DataTable t in tables)
            if(!t.Columns.Contains(primaryKeyColumn))
                throw new ArgumentException("All tables must have the specified primarykey column " + primaryKeyColumn, "primaryKeyColumn");

    if(tables.Count == 1)
        return tables[0];

    DataTable table = new DataTable("TblUnion");
    table.BeginLoadData(); // Turns off notifications, index maintenance, and constraints while loading data
    foreach (DataTable t in tables)
    {
        foreach (DataColumn col in t.Columns) 
            col.ReadOnly = false; // this might be required in your case
        table.Merge(t); // same as table.Merge(t, false, MissingSchemaAction.Add);
    }
    table.EndLoadData();

    if (primaryKeyColumn != null)
    {
        // since we might have no real primary keys defined, the rows now might have repeating fields
        // so now we're going to "join" these rows ...
        var pkGroups = table.AsEnumerable()
            .GroupBy(r => r[primaryKeyColumn]);
        var dupGroups = pkGroups.Where(g => g.Count() > 1);
        foreach (var grpDup in dupGroups)
        { 
            // use first row and modify it
            DataRow firstRow = grpDup.First();
            foreach (DataColumn c in table.Columns)
            {
                if (firstRow.IsNull(c))
                {
                    DataRow firstNotNullRow = grpDup.Skip(1).FirstOrDefault(r => !r.IsNull(c));
                    if (firstNotNullRow != null)
                        firstRow[c] = firstNotNullRow[c];
                }
            }
            // remove all but first row
            var rowsToRemove = grpDup.Skip(1);
            foreach(DataRow rowToRemove in rowsToRemove)
                table.Rows.Remove(rowToRemove);
        }
    }

    return table;
}

Then use it in this way:

var tables = new[] { tblEmp_data, tblEmp_time, tbl_emp_mission };
tblEmp_data = tables.MergeAll("empNum");

edited May 23 '17 at 10:30

Community

1
1

answered May 21 '13 at 09:20

Tim Schmelter

450,073
74
686
939

I try this method but i get always `failed to enable constraints. one or more rows contain values violating non-null, unique, or foreign-key constraints` may be one table is empty – Anyname Donotcare May 21 '13 at 09:22
Is this method could bring the whole three rows from the three datatables in one row ? – Anyname Donotcare May 21 '13 at 09:23
@just_name: Yes, that's it's purpose. – Tim Schmelter May 21 '13 at 09:26
I try on two tables instead of one i get the following exception : `System.Data.ReadOnlyException: Column 'over_time_w' is read only.` – Anyname Donotcare May 21 '13 at 09:27
@just_name: Add sample data to your question, then i can test it too. Where do you get the exception, have you used the debugger? – Tim Schmelter May 21 '13 at 09:31
1

@just_name: Maybe you need to modify the code of the method at the line after: `foreach (DataTable t in tables)`. You should make all columns in all tables `ReadOnly=false`. So extend the `foreach`-loop in this way: `foreach (DataTable t in tables) { foreach (DataColumn col in t.Columns) col.ReadOnly = false; table.Merge(t); }` – Tim Schmelter May 21 '13 at 09:36
1

@just_name: Added the method to my answer and modified it accordingly. Search for _" // this might be required in your case"_. If you can confirm it i would update the other answer as well. – Tim Schmelter May 21 '13 at 09:44
My last table which cause the problem : `failed to enable constraints. one or more rows contain values violating non-null, unique, or foreign-key constraints` `column1 emp_num 0 5307` – Anyname Donotcare May 21 '13 at 10:17
3

Sorry, but i cannot help with this little information. Maybe you should edit your question and add more details. Where do you get this exception? What sample data caused this exception? How do you fill the table(e.g. via `DataAdapter`)? – Tim Schmelter May 21 '13 at 10:20
@TimSchmelter Not sure if it was just my datasets, but I also had to add `col.AllowDBNull = true` in the loop where you set the columns to be readable. Otherwise, I was seeing the same error as just_name. – Tieson T. Jul 22 '14 at 00:19
@TimSchmelter : I want to apologize if i interrupt you by my last question. – Anyname Donotcare Nov 22 '16 at 13:10

score 3 · Answer 2 · answered May 30 '13 at 14:27

If I understand correctly, you have the data in the three datatables, and every table has a column(or multiple columns) that you wish to have in the result table. Basically, each of the tables contains a single row, and you wish to merge the columns.

In this case I'd simply create a new column, and copy the value over:

void MergeColumns_SO16666297(DataTable destination, DataTable source, IEnumerable<string> columnsToSkip)
{
if(columnsToSkip==null) columnsToSkip= new List<string>();
foreach(var col in source.Columns.OfType<DataColumn>().Where(col=>!columnsToSkip.Contains(col.ColumnName)))
    {
        var newCol = destination.Columns.Add(col.ColumnName, col.DataType);
        newCol.AllowDBNull = true ;
        destination.Rows[0][newCol] = source.Rows[0][col];
    }
}

Use it like this: MergeColumns_SO16666297(tblEmp_data,tblEmp_time,new string[]{"emp_num"});

My second thought, create a TransferObjects class, that has all the fields you need, and copy the values from the individual tables.

score 2 · Answer 3 · answered May 30 '13 at 19:24

This should get you the data in one row, provided that your primary key column has the same name in all data tables

    private DataTable MergeDataTables(List<DataTable> tables)
    {
        //.
        if (null == tables || false == tables.Any())
        {
            return null;
        }

        //.
        DataTable merged = tables.First();

        //.
        for (int i = 1; i < tables.Count; i++)
        {
            var cur = tables[i];

            //.merge columns
            foreach (DataColumn c in cur.Columns)
            {
                if (-1 == merged.Columns.IndexOf(c.ColumnName))
                {
                    merged.Columns.Add(c.ColumnName, c.DataType);
                }
            }

            //.merge rows
            foreach (DataRow r in cur.Rows)
            {
                merged.ImportRow(r);
            }
        }

        //.
        return merged;
    }

How to merge multiple datatables (every datatable return one row) in one row

Edited:

3 Answers3