如何在c#中加入两个DataTable?

前端之家收集整理的这篇文章主要介绍了如何在c#中加入两个DataTable?前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
如何保留外部连接(我认为是外部加入,但我不是100%确定)两个数据表与以下表格和条件,同时保留两个表中的所有列?

dtblLeft:

  1. id col1 anotherColumn2
  2. 1 1 any2
  3. 2 1 any2
  4. 3 2 any2
  5. 4 3 any2
  6. 5 3 any2
  7. 6 3 any2
  8. 7 any2

dtblRight:

  1. col1 col2 anotherColumn1
  2. 1 Hi any1
  3. 2 Bye any1
  4. 3 Later any1
  5. 4 Never any1

dtblJoined:

  1. id col1 col2 anotherColumn1 anotherColumn2
  2. 1 1 Hi any1 any2
  3. 2 1 Hi any1 any2
  4. 3 2 Bye any1 any2
  5. 4 3 Later any1 any2
  6. 5 3 Later any1 any2
  7. 6 3 Later any1 any2
  8. 7 any2

条件:

>在dtblLeft中,col1不需要具有唯一的值.
>在dtblRight中,col1具有唯一的值.
>如果dtblLeft在col1中缺少一个外键,或者它在dtblRight中不存在一个外键,那么将插入空或空字段.
>加入col1.

我可以使用常规的DataTable操作,LINQ或其他.

我尝试了这个,但它删除重复:

  1. dtblA.PrimaryKey = new DataColumn[] {dtblA.Columns["col1"]}
  2.  
  3. DataTable dtblJoined = new DataTable();
  4. dtblJoined.Merge(dtblA,false,MissingSchemaAction.AddWithKey);
  5. dtblJoined.Merge(dtblB,MissingSchemaAction.AddWithKey);

编辑1:

这是接近我所想要的,但它只有一个表的列(发现在这link):

  1. dtblJoined = (from t1 in dtblA.Rows.Cast<DataRow>()
  2. join t2 in dtblB.Rows.Cast<DataRow>() on t1["col1"] equals t2["col1"]
  3. select t1).CopyToDataTable();

编辑2:

这个link的答案似乎适用于我,但我不得不改变一下如下:

  1. DataTable targetTable = dtblA.Clone();
  2. var dt2Columns = dtblB.Columns.OfType<DataColumn>().Select(dc =>
  3. new DataColumn(dc.ColumnName,dc.DataType,dc.Expression,dc.ColumnMapping));
  4. var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
  5. where targetTable.Columns.Contains(dc.ColumnName) == false
  6. select dc;
  7.  
  8. targetTable.Columns.AddRange(dt2FinalColumns.ToArray());
  9.  
  10. var rowData = from row1 in dtblA.AsEnumerable()
  11. join row2 in dtblB.AsEnumerable()
  12. on row1["col1"] equals row2["col1"]
  13. select row1.ItemArray.Concat(row2.ItemArray.Where(r2 => row1.ItemArray.Contains(r2) == false)).ToArray();
  14.  
  15. foreach (object[] values in rowData)
  16. targetTable.Rows.Add(values);

我也发现这个link,我可能会尝试,因为它似乎更简洁.

编辑3(11/18/2013):

更新表以反映更多的情况.

解决方法

感谢所有的帮助.这是我根据多种资源提出的:
  1. public static class DataTableHelper
  2. {
  3. public enum JoinType
  4. {
  5. /// <summary>
  6. /// Same as regular join. Inner join produces only the set of records that match in both Table A and Table B.
  7. /// </summary>
  8. Inner = 0,/// <summary>
  9. /// Same as Left Outer join. Left outer join produces a complete set of records from Table A,with the matching records (where available) in Table B. If there is no match,the right side will contain null.
  10. /// </summary>
  11. Left = 1
  12. }
  13.  
  14. /// <summary>
  15. /// Joins the passed in DataTables on the colToJoinOn.
  16. /// <para>Returns an appropriate DataTable with zero rows if the colToJoinOn does not exist in both tables.</para>
  17. /// </summary>
  18. /// <param name="dtblLeft"></param>
  19. /// <param name="dtblRight"></param>
  20. /// <param name="colToJoinOn"></param>
  21. /// <param name="joinType"></param>
  22. /// <returns></returns>
  23. /// <remarks>
  24. /// <para>https://stackoverflow.com/questions/2379747/create-combined-datatable-from-two-datatables-joined-with-linq-c-sharp?rq=1</para>
  25. /// <para>http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx</para>
  26. /// <para>http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html</para>
  27. /// <para>https://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server</para>
  28. /// </remarks>
  29. public static DataTable JoinTwoDataTablesOnOneColumn(DataTable dtblLeft,DataTable dtblRight,string colToJoinOn,JoinType joinType)
  30. {
  31. //Change column name to a temp name so the LINQ for getting row data will work properly.
  32. string strTempColName = colToJoinOn + "_2";
  33. if (dtblRight.Columns.Contains(colToJoinOn))
  34. dtblRight.Columns[colToJoinOn].ColumnName = strTempColName;
  35.  
  36. //Get columns from dtblLeft
  37. DataTable dtblResult = dtblLeft.Clone();
  38.  
  39. //Get columns from dtblRight
  40. var dt2Columns = dtblRight.Columns.OfType<DataColumn>().Select(dc => new DataColumn(dc.ColumnName,dc.ColumnMapping));
  41.  
  42. //Get columns from dtblRight that are not in dtblLeft
  43. var dt2FinalColumns = from dc in dt2Columns.AsEnumerable()
  44. where !dtblResult.Columns.Contains(dc.ColumnName)
  45. select dc;
  46.  
  47. //Add the rest of the columns to dtblResult
  48. dtblResult.Columns.AddRange(dt2FinalColumns.ToArray());
  49.  
  50. //No reason to continue if the colToJoinOn does not exist in both DataTables.
  51. if (!dtblLeft.Columns.Contains(colToJoinOn) || (!dtblRight.Columns.Contains(colToJoinOn) && !dtblRight.Columns.Contains(strTempColName)))
  52. {
  53. if (!dtblResult.Columns.Contains(colToJoinOn))
  54. dtblResult.Columns.Add(colToJoinOn);
  55. return dtblResult;
  56. }
  57.  
  58. switch (joinType)
  59. {
  60.  
  61. default:
  62. case JoinType.Inner:
  63. #region Inner
  64. //get row data
  65. //To use the DataTable.AsEnumerable() extension method you need to add a reference to the System.Data.DataSetExtension assembly in your project.
  66. var rowDataLeftInner = from rowLeft in dtblLeft.AsEnumerable()
  67. join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName]
  68. select rowLeft.ItemArray.Concat(rowRight.ItemArray).ToArray();
  69.  
  70.  
  71. //Add row data to dtblResult
  72. foreach (object[] values in rowDataLeftInner)
  73. dtblResult.Rows.Add(values);
  74.  
  75. #endregion
  76. break;
  77. case JoinType.Left:
  78. #region Left
  79. var rowDataLeftOuter = from rowLeft in dtblLeft.AsEnumerable()
  80. join rowRight in dtblRight.AsEnumerable() on rowLeft[colToJoinOn] equals rowRight[strTempColName] into gj
  81. from subRight in gj.DefaultIfEmpty()
  82. select rowLeft.ItemArray.Concat((subRight== null) ? (dtblRight.NewRow().ItemArray) :subRight.ItemArray).ToArray();
  83.  
  84.  
  85. //Add row data to dtblResult
  86. foreach (object[] values in rowDataLeftOuter)
  87. dtblResult.Rows.Add(values);
  88.  
  89. #endregion
  90. break;
  91. }
  92.  
  93. //Change column name back to original
  94. dtblRight.Columns[strTempColName].ColumnName = colToJoinOn;
  95.  
  96. //Remove extra column from result
  97. dtblResult.Columns.Remove(strTempColName);
  98.  
  99. return dtblResult;
  100. }
  101. }

编辑3:

方法现在可以正常工作,并且当表具有2000行时,该方法仍然很快.任何建议/建议/改进将不胜感激.

编辑4:

我有一些情况导致我意识到,以前的版本是真正做一个内在的联合.该功能已被修改解决该问题.我使用这个link的信息来弄清楚.

猜你在找的C#相关文章