如何在一行中合并多个数据表(每个数据表返回一行)

本文关键字：数据表 一行 返回 合并 | 更新日期: 2023-09-27 18:14:28

我有多个数据表:我想合并它们(我的 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();

现在我得到多行数据，而不是一行!我想把数据放在一行?怎么做呢?

注意:我希望所有列都允许null，除了主键，所以我避免这个例外:

failed to enable constraints. one or more rows contain values violating non-null, unique, or foreign-key constraints
<标题>编辑:

导致问题的第三个表:

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;

            }
        }

返回如下数据:

column1            emp_num 
 0                   6762

和抛出异常:

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

如何在一行中合并多个数据表(每个数据表返回一行)

所以empNum是所有表共享的键列?听起来好像你可以用我的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;
}

然后这样使用:

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

如果我理解正确的话，您在三个数据表中拥有数据，并且每个表都有您希望在结果表中拥有的一列(或多列)。基本上，每个表包含单行，您希望合并这些列。

在本例中，我只需创建一个新列，并复制该值:

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

这样使用:MergeColumns_SO16666297(tblEmp_data,tblEmp_time,new string[]{"emp_num"});

我的第二个想法是，创建一个TransferObjects类，其中包含您需要的所有字段，并从各个表中复制值。

如果主键列在所有数据表中具有相同的名称，则应该在一行中获得数据

    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;
    }
相关文章：