System.Data之DataTable用法总结

• DataTable.AcceptChanges方法：接受修改，相当于提交数据

• DataTable.Merge方法

/// <summary>
/// 合并表格
/// </summary>
/// <param name="table">源表</param>
public void Merge(DataTable table);
/// <summary>
/// 合并表格
/// </summary>
/// <param name="table">源表</param>
/// <param name="preserveChanges">保留当前数据表中的更改为true</param>
public void Merge(DataTable table, bool preserveChanges);
/// <summary>
/// 合并表格
/// </summary>
/// <param name="table">源表</param>
/// <param name="preserveChanges">保留当前数据表中的更改为true</param>
/// <param name="missingSchemaAction">缺少的操作</param>
public void Merge(DataTable table, bool preserveChanges, MissingSchemaAction missingSchemaAction);

 

MSDN中的例子：注释后的数字与下面4副图对应

DataTable table1 = new DataTable("Items");
DataColumn column = new DataColumn("id", typeof(System.Int32));
column.AutoIncrement = true;
table1.Columns.Add(column);
column = new DataColumn("item", typeof(System.String));
table1.Columns.Add(column);
table1.PrimaryKey = new DataColumn[] { table1.Columns[0] };
DataRow row;
for (int i = 0; i <= 3; i++)
{
	row = table1.NewRow();
	row["item"] = "Item " + i;
	table1.Rows.Add(row);
}
dataGridView1.DataSource = table1;//①

DataTable dt2 = table1.Copy();
dataGridView1.DataSource = dt2;//①

dt2.Merge(table1, true);
dataGridView1.DataSource = dt2;//①
dt2.Merge(table1, false);
dataGridView1.DataSource = dt2;//①

DataTable modifiedTable = table1.Copy();
foreach (DataRow rowModified in modifiedTable.Rows)
{
	rowModified["item"] = rowModified["item"].ToString()
		+ " modified";
}

table1.Rows[0]["item"] = "new Item 0";
table1.Rows[1]["item"] = "new Item 1";
row = table1.NewRow();
row["id"] = 4;
row["item"] = "Item 4";
table1.Rows.Add(row);

DataTable table1Copy = table1.Copy();
dataGridView1.DataSource = table1Copy;//②
dataGridView1.DataSource = modifiedTable;//③

//Merge new data into the modified data.
table1.Merge(modifiedTable, true);
dataGridView1.DataSource = table1;//②
table1.Merge(modifiedTable, false);
dataGridView1.DataSource = table1;//④

table1Copy.Merge(modifiedTable, true);
dataGridView1.DataSource = table1Copy;//②
table1Copy.Merge(modifiedTable, false);
dataGridView1.DataSource = table1Copy;//④

通过MSDN的例子可知：

preserveChanges参数启用的前提是表有主键，当主表（如table1）与源表（modifiedTable）合并时，preserveChanges为True则以主表数据为准，为false则以源表为准（此时源表中存在，但主表中不存在的字段也会追加，只是值为空）

 

示例1：

DataTable dt1 = new DataTable();
dt1.Columns.Add("dt1c1", typeof(string));
dt1.Columns.Add("dt1c2", typeof(int));
dt1.Columns.Add("dt1c3", typeof(double));

dt1.Rows.Add("1", 1, 1.01);
dt1.Rows.Add("2", 2, 1.02);

DataTable dt2 = new DataTable();
//dt2.Columns.Add("dt1c1", typeof(int));//①
dt2.Columns.Add("dt1c1", typeof(string));
dt2.Columns.Add("dt1c2", typeof(int));
dt2.Columns.Add("dt1c4", typeof(double));

dt2.Rows.Add("1", 3, 1.03);
dt2.Rows.Add("2", 4, 1.04);

dt1.Merge(dt2, true);//②
dt1.Merge(dt2, false);//②
dt1.Merge(dt2, true, MissingSchemaAction.Add);//②
dt1.Merge(dt2, true, MissingSchemaAction.AddWithKey);//②
dt1.Merge(dt2, true, MissingSchemaAction.Error);//相当于return，无任何值返回
dt1.Merge(dt2, true, MissingSchemaAction.Ignore);//③
dataGridView1.DataSource = dt1;

①当字段名一致，但字段类型不一致时报错

②修改dt2的dt1c1的属性为string后，代码中结果如下

③MissingSchemaAction对象为Ignore时不合并源表中的字段

示例2：增加主键后

DataTable dt1 = new DataTable();
dt1.Columns.Add("dt1c1", typeof(string));
dt1.Columns.Add("dt1c2", typeof(string));
dt1.Columns.Add("dt1c3", typeof(int));
dt1.Columns.Add("dt1c4", typeof(double));
dt1.PrimaryKey = new DataColumn[] { dt1.Columns[0] };

dt1.Rows.Add("1", "A1", 1, 1.01);
dt1.Rows.Add("2", "A2", 2, 1.02);
dt1.Rows.Add("3", "A3", 3, 1.03);

DataTable dt2 = new DataTable();
dt2.Columns.Add("dt1c1", typeof(string));
dt2.Columns.Add("dt1c2", typeof(string));
dt2.Columns.Add("dt1c3", typeof(int));
dt2.Columns.Add("dt1c5", typeof(double));
dt2.PrimaryKey = new DataColumn[] { dt2.Columns[0] };

dt2.Rows.Add("1", "B1", 11, 10.01);
dt2.Rows.Add("2", "B2", 12, 10.02);
dt2.Rows.Add("4", "B4", 14, 10.04);

①dt1.Merge(dt2, true);

dt1.Merge(dt2, true, MissingSchemaAction.Add);
dt1.Merge(dt2, true, MissingSchemaAction.AddWithKey);
 

dt1.Merge(dt2, true, MissingSchemaAction.Ignore);

dt1.Merge(dt2, true, MissingSchemaAction.Error);

dt1.Merge(dt2, false, MissingSchemaAction.Error);

空，相当于return

②dt1.Merge(dt2);

dt1.Merge(dt2, false);

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

dt1.Merge(dt2, false, MissingSchemaAction.Ignore);

总结：通过以上示例可知【table1.Merge(table2,preserveChanges,missingSchemaAction)】

1. 要求table1和table2中相同字段名称的值类型应一致，否则报错。
2. preserveChanges默认为false，missingSchemaAction默认为Add
3. preserveChanges为true时，冲突数据以table1为准；为false时以table2为准
4. missingSchemaAction
   1. Add：
   2. AddWithKey：
   3. Error：相当于此行renturn了，不进行下一步代码操作
   4. Ignore：忽略的是table2中的多余字段

至于Add和AddWithKey的区别，暂未测试，等待更新，也欢迎各位大拿指导！QQ:601249408