之前写过《深入.NET DataTable》一文,这里补充对DataTable的Merge()方法的介绍,希望对大家有所帮助。
1)对于具备主键信息的DataTable
首先我们约定,对于dataTable1.Merge(dataTable2),dataTable1我们称为目标表,dataTable2称为合并表。
另外,这里首先考虑目标表与合并表结构一致的情况,并关注表合并之后对行状态造成的影响,对于不一致的情况,后文将有讨论。
下面的示例中,首先构造了2个包含16行数据的DataTable:
(目标表)
Fid | Fval | Rowstate |
0 | A | Unchanged |
1 | A | Unchanged |
2 | A | Unchanged |
3 | A | Unchanged |
4 | B | Modified |
5 | B | Modified |
6 | B | Modified |
7 | B | Modified |
8 | A | Deleted |
9 | A | Deleted |
10 | A | Deleted |
11 | A | Deleted |
12 | A | Added |
13 | A | Added |
14 | A | Added |
15 | A | Added |
(合并表)
Fid | Fval | Rowstate |
0 | X | Unchanged |
1 | Y | Modified |
2 | X | Deleted |
3 | X | Added |
4 | X | Unchanged |
5 | Y | Modified |
6 | X | Deleted |
7 | X | Added |
8 | X | Unchanged |
9 | Y | Modified |
10 | X | Deleted |
11 | X | Added |
12 | X | Unchanged |
13 | Y | Modified |
14 | X | Deleted |
15 | X | Added |
(结果表)
Fid | Fval | Rowstate |
0 | X | Unchanged <--Unchanged =Unchanged |
1 | Y | Unchanged <--Modified =Modified |
2 | X | Unchanged <--Deleted =Deleted |
3 | X | Unchanged <--Added =Modified |
4 | X | Modified <--Unchanged =Modified |
5 | Y | Modified <--Modified =Modified |
6 | X | Modified <--Deleted =Deleted |
7 | X | Modified <--Added =Modified |
8 | X | Deleted <--Unchanged =Modified |
9 | Y | Deleted <--Modified =Modified |
10 | X | Deleted <--Deleted =Deleted |
11 | X | Deleted <--Added =Modified |
12 | X | Added <--Unchanged =Modified |
13 | Y | Added <--Modified =Modified |
14 | X | Added <--Deleted =Deleted |
15 | X | Added <--Added =Added |
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
List<string> l1 = new List<string>();
List<string> l2 = new List<string>();
//
DataTable dt1 = new DataTable();
DataColumn dc= dt1.Columns.Add("fid", typeof(int));
dt1.PrimaryKey = new DataColumn[] { dc };
dt1.Columns.Add("fval", typeof(string));
//
for (int i = 0; i < 16; i++)
{
dt1.Rows.Add(i, "a");
if (Math.Floor(i / 4.0) == 0)
{
dt1.Rows[i].AcceptChanges();
}
else if (Math.Floor(i / 4.0) == 1)
{
dt1.Rows[i].AcceptChanges();
dt1.Rows[i]["fval"] = "b";
}
else if (Math.Floor(i / 4.0) == 2)
{
dt1.Rows[i].AcceptChanges();
dt1.Rows[i].Delete();
}
l1.Add(dt1.Rows[i].RowState.ToString());
}
//
DataTable dt2 = new DataTable();
DataColumn dc2 = dt2.Columns.Add("fid", typeof(int));
dt2.PrimaryKey = new DataColumn[] { dc2 };
dt2.Columns.Add("fval", typeof(string));
//
for (int i = 0; i < 16; i++)
{
dt2.Rows.Add(i, "x");
if (i % 4 == 0)
{
dt2.Rows[i].AcceptChanges();
}
else if (i % 4 == 1)
{
dt2.Rows[i].AcceptChanges();
dt2.Rows[i]["fval"] = "y";
}
else if (i % 4 == 2)
{
dt2.Rows[i].AcceptChanges();
dt2.Rows[i].Delete();
}
l2.Add(dt2.Rows[i].RowState.ToString());
}
//
dt1.Merge(dt2);
string outPut = "";
for (int i = 0; i < dt1.Rows.Count; i++)
{
if (dt1.Rows[i].RowState == DataRowState.Deleted)
{
outPut = dt1.Rows[i][0, DataRowVersion.Original].ToString().PadRight(10) + dt1.Rows[i][1, DataRowVersion.Original].ToString().PadRight(10);
}
else
{
outPut = dt1.Rows[i][0].ToString().PadRight(10) + dt1.Rows[i][1].ToString().PadRight(10);
}
outPut += l1[i].PadRight(10) + "<--" + l2[i].PadRight(10) + "=" + dt1.Rows[i].RowState.ToString();
Console.WriteLine(outPut);
}
2)未包含主键信息的DataTable
(目标表)
Fid | Fval |
1 | a |
(合并表)
Fid | Fval |
1 | a |
(结果表)
Fid | Fval |
1 | a |
1 | a |
代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
DataTable dt = new DataTable();
DataColumn dc = dt.Columns.Add("fid", typeof(int));
//dt.PrimaryKey = new DataColumn[] { dc };
dt.Columns.Add("fval", typeof(string));
//
dt.Rows.Add(1, "a");
dt.AcceptChanges();
//
DataTable dt2 = new DataTable();
DataColumn dc2 = dt2.Columns.Add("fid", typeof(int));
//dt2.PrimaryKey = new DataColumn[] { dc2 };
dt2.Columns.Add("fval", typeof(string));
//
dt2.Rows.Add(1, "a");
dt2.AcceptChanges();
//
dt.Merge(dt2);
这种情况下,合并表中的数据被直接引入到目标表。最终的行数=目标表行数+合并表行数。
3)目标表与合并表结构存在差异
对上面的代码稍加修改:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
DataTable dt = new DataTable();
DataColumn dc = dt.Columns.Add("fid", typeof(int));
//dt.PrimaryKey = new DataColumn[] { dc };
dt.Columns.Add("fval", typeof(string));
//
dt.Rows.Add(1, "a");
dt.AcceptChanges();
//
DataTable dt2 = new DataTable();
DataColumn dc2 = dt2.Columns.Add("fid2", typeof(int));
//dt2.PrimaryKey = new DataColumn[] { dc2 };
dt2.Columns.Add("fval", typeof(string));
//
dt2.Rows.Add(1, "a");
dt2.AcceptChanges();
//
dt.Merge(dt2);
(目标表)
Fid | Fval |
1 | a |
(合并表)
Fid2 | Fval |
1 | a |
(结果表)
Fid | Fval | Fid2 |
1 | a | |
a | 1 |
从上面的结果可知,程序完全按字段名称进行区分,对于目标表中不存在的字段,将从合并表中引入新的字段。
如果目标表包含主键:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
DataTable dt = new DataTable();
DataColumn dc = dt.Columns.Add("fid", typeof(int));
dt.PrimaryKey = new DataColumn[] { dc };
dt.Columns.Add("fval", typeof(string));
//
dt.Rows.Add(1, "a");
dt.AcceptChanges();
//
DataTable dt2 = new DataTable();
DataColumn dc2 = dt2.Columns.Add("fid2", typeof(int));
//dt2.PrimaryKey = new DataColumn[] { dc2 };
dt2.Columns.Add("fval", typeof(string));
//
dt2.Rows.Add(1, "a");
dt2.AcceptChanges();
//
dt.Merge(dt2);
这种情况下,程序尝试生成一个跟上面类似的结果表,但由于主键的非空约束,故程序抛出异常。对于合并表中包含主键的情况,当然会产生一样的异常。
所以,对于设置了主键的情况(无论是其中之一设置了,还是两者都设置了),需要保证主键字段的名称一致。否则DataTable在应用约束时就抛出异常。
plus,首次使用Live Writer写博,没有找到代码折叠的功能。软件功能还是有些待改善的地方,:)