如何使用linq更新和添加来自另一个数据表的数据表中的新行 [英] How to update and add new row in a datatable from another datatable using linq
本文介绍了如何使用linq更新和添加来自另一个数据表的数据表中的新行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
II有两个数据表如下
II have two data tables as follows
dtOne
-------------------------
ID | Name
--------------------------
101 | AA
102 | BB
103 | CC
104 | DD
105 | EE
106 | FF
--------------------------
dtTwo
-------------------------
ID | Name
--------------------------
101 | AA
102 | BBAA
103 | CCBB
104 | DD
107 | GG
108 | HH
--------------------------
我只想把结果作为'在dtOne`和'不在dtTwo`中的数据（dtOne-dtTwo）
I just want the result as data which is `in dtOne` and `not in dtTwo` (dtOne-dtTwo)
dtResult
-------------------------
ID | Name
--------------------------
101 | AA
102 | BB
103 | CC
104 | DD
107 | GG
108 | HH
105 | EE
106 | FF
--------------------------
我怎样才能实现这个目标。
我尝试过：
How can i achieve this .
What I have tried:
DataSet ds = new DataSet();
DataSet ds1 = new DataSet();
ds1.ReadXml(HttpContext.Current.Server.MapPath("XML/myFile.xml"));
DataTable dt1 = ds1.Tables[0];
var query = (from p in dt.AsEnumerable()
join t in dt1.AsEnumerable()
on p.Field<string>("Station") equals t.Field<string>("Station")
select new
{
Station = p.Field<string>("Station"),
Max_Temp = p.Field<string>("Max_Temp"),
Min_Temp = p.Field<string>("Min_Temp"),
Weather_Detail = p.Field<string>("Weather_Detail"),
DateTime = p.Field<string>("DateTime")
});
DataTable myDataTable = new DataTable();
myDataTable.Columns.Add("DateTime", typeof(string));
myDataTable.Columns.Add("Station", typeof(string));
myDataTable.Columns.Add("Max_Temp", typeof(string));
myDataTable.Columns.Add("Min_Temp", typeof(string));
myDataTable.Columns.Add("Weather_Detail", typeof(string));
foreach (var element in query)
{
var row = myDataTable.NewRow();
row["DateTime"] = element.DateTime;
row["Station"] = element.Station;
row["Max_Temp"] = element.Max_Temp;
row["Min_Temp"] = element.Min_Temp;
row["Weather_Detail"] = element.Weather_Detail;
myDataTable.Rows.Add(row);
}
DataTable dtExtraRow = new DataTable();
if ((dt1 != null && dt1.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
{
var temp1 = dt1.Rows.OfType<datarow>().Where(a => dt1.Rows.OfType<datarow>().
Select(k => Convert.ToString(k["Station"])).Except(myDataTable.Rows.OfType<datarow>().Select(k => Convert.ToString(k["Station"])).ToList()).Contains(Convert.ToString(a["Station"]))).AsEnumerable();
if (temp1 != null && temp1.Count() > 0)
dtExtraRow = temp1.CopyToDataTable();
}
if (dtExtraRow.Rows.Count > 0)
myDataTable = myDataTable.AsEnumerable().Union(dtExtraRow.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();
DataTable dtExtraRowOfXml = new DataTable();
if ((dt != null && dt.Rows.Count > 0) && (myDataTable != null && myDataTable.Rows.Count > 0))
{
var temp = dt.Rows.OfType<datarow>().Where(a => dt.Rows.OfType<datarow>().
Select(k => Convert.ToString(k["Station"])).Except(myDataTable.Rows.OfType<datarow>().Select(k => Convert.ToString(k["Station"])).ToList()).Contains(Convert.ToString(a["Station"]))).AsEnumerable();
if (temp != null && temp.Count() > 0)
dtExtraRowOfXml = temp.CopyToDataTable();
}
if (dtExtraRowOfXml.Rows.Count > 0)
myDataTable = myDataTable.AsEnumerable().Union(dtExtraRowOfXml.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();
ds.Tables.Add(myDataTable);
string strXml = ds.GetXml();
XmlDocument xDoc = new XmlDocument();
xDoc.LoadXml(strXml);
xDoc.Save(HttpContext.Current.Server.MapPath("XML/myFile.xml")); //XML is the folder name and myFile.xml is ur new file name
推荐答案
private void UpdateXml(DataTable dt1, DataTable dt2)
{
-------matching row should be updated and nomatching row should be added in dt2
DataTable Match = dt1.AsEnumerable().Where(ra => dt2.AsEnumerable().Any(rb => rb.Field<string>("Station") == ra.Field<string>("Station"))).AsDataView().ToTable();
foreach (DataRow dRNew in Match.Rows)
{
DataRow row = dt2.AsEnumerable().Where(r => r.Field<string>("Station").Equals(dRNew["Station"])).First();
row["DateTime"] = dRNew["DateTime"];
row["Station"] = dRNew["Station"];
row["Max_Temp"] = dRNew["Max_Temp"];
row["Min_Temp"] = dRNew["Min_Temp"];
row["Weather_Detail"] = dRNew["Weather_Detail"];
}
DataTable NoMatch = dt1.AsEnumerable().Where(ra => !dt2.AsEnumerable().Any(rb => rb.Field<string>("Station") == ra.Field<string>("Station"))).AsDataView().ToTable();
NoMatch.Merge(dt2);
//dt1.Merge(NoMatch);
NoMatch.DefaultView.Sort = "Station";
NoMatch = NoMatch.DefaultView.ToTable();
}
假设你想加入数据，试试这个：
Assumming that you want to join data, try this:
//get result list by using Union method together with IEqualityComparer
var result = dt1.AsEnumerable()
.Union(dt2.AsEnumerable(), new MyComparer())
.OrderBy(x=>x.Field<int>("ID"));</int>
比较器类的定义：
And the definition of comparer class:
public class MyComparer : IEqualityComparer<datarow>
{
// rows are equal if their ID are equal.
public bool Equals(DataRow x, DataRow y)
{
//Check whether the compared objects reference the same data.
if (Object.ReferenceEquals(x, y)) return true;
//Check whether any of the compared objects is null.
if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null))
return false;
//Check whether the rows are equal.
return x.Field<int>("ID") == y.Field<int>("ID");
}
// If Equals() returns true for a pair of objects
// then GetHashCode() must return the same value for these objects.
public int GetHashCode(DataRow dr)
{
//Check whether the object is null
if (Object.ReferenceEquals(dr, null)) return 0;
//Get hash code for the ID field.
return dr.Field<int>("ID").GetHashCode();
}
}</int></int></int></datarow>
结果：
Result:
ID Name
101 AA
102 BB
103 CC
104 DD
105 EE
106 FF
107 GG
108 HH
更多信息： Enumerable.Union（TSource）方法（IEnumerable（TSource），IEnumerable（TSource），IEqualityComparer（TSource））（System.Linq） [ ^ ]
您可能想要使用：Enumerable.Intersect（TSource）方法（IEnumerable（TSource），IEnumerable（TSource），IEqualityComparer（TSource））（System.Linq） [ ^ ]。
这篇关于如何使用linq更新和添加来自另一个数据表的数据表中的新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
