合并两个数据表 [英] Merging two data tables

问题描述

大家好，

我有两个数据表，例如，我想合并以下基于"Id"的表

状况.

DataTable1
-----------

Hi Everybody,

I have two datatables like, I want to merge the below tables Based on "Id"

condition.

DataTable1
-----------

Id          ProductID       Value
---         ---------       -----
10C            1             10
10C            2             20
10C            3             30
20C            1             10
20C            2             20
20C            3             30

DataTable2
----------

DataTable2
----------

Id          ProductID       Value
---         ---------       -----
10C            1             100
10C            2             200
10C            3             300

对于上述表，两个表中都存在ID"10C".基于这一点，我想用DataTable2的值替换"DataTable1的值".


预期结果:

For the above tables Id "10C" present in both tables. based on that i want to replace the "Values of DataTable1" with the values of DataTable2.


Expected Out :

Id          ProductID       Value
---         ---------       -----
10C            1             100
10C            2             200
10C            3             300
20C            1             10
20C            2             20
20C            3             30

在此先感谢.

[edit]已添加格式-OriginalGriff [/edit]

Thanks in Advance.

[edit]Formatting added - OriginalGriff[/edit]

推荐答案

如果将DataTable1 中的值替换为DataTable2中的匹配值，则可以使用以下代码

If the value in DataTable1 is to be replaced with the matching value from DataTable2, then the following code can be used

foreach(DataRow row in DataTable1.Rows){
    DataRow[] rows2 = DataTable2.Select(string.Format("Id='{0}' and ProductId = '{1}'",row["Id"],
                    row["ProductId"]));
    if (rows2.Length > 0) {
        row["Value"]=rows2[0]["Value"];
    }
}

为了进行快速测试，请粘贴以下代码

For a quick test, paste the following code

void Main()
{
    DataTable DataTable1 = new DataTable("DataTable1");
    DataTable1.Columns.Add("Id",typeof(string));
    DataTable1.Columns.Add("ProductId",typeof(string));
    DataTable1.Columns.Add("Value",typeof(string));
    DataTable1.Rows.Add("10C","1","10");
    DataTable1.Rows.Add("10C","2","20");
    DataTable1.Rows.Add("10C","3","30");
    DataTable1.Rows.Add("20C","1","10");
    DataTable1.Rows.Add("20C","2","20");
    DataTable1.Rows.Add("20C","3","30");
    DataTable DataTable2 = new DataTable("DataTable2");
    DataTable2.Columns.Add("Id",typeof(string));
    DataTable2.Columns.Add("ProductId",typeof(string));
    DataTable2.Columns.Add("Value",typeof(string));
    DataTable2.Rows.Add("10C","1","100");
    DataTable2.Rows.Add("10C","2","200");
    DataTable2.Rows.Add("10C","3","300");
    
    foreach(DataRow row in DataTable1.Rows){
    	DataRow[] rows = DataTable2.Select(string.Format("Id='{0}' 
            and ProductId = '{1}'",row["Id"],row["ProductId"]));
    	if (rows.Length > 0) {
    		row["Value"]=rows[0]["Value"];
    	}
    	
    }
    DataTable1.Dump();
}

在LINQPad中，可以从此处 http://www.linqpad.net/ [C# Program并运行程序.

in LINQPad, which can be downloaded from here http://www.linqpad.net/[^], select C# Program in Language combo box and run the program.

HI，

假设考虑
DataTable1是dt1;

而DataTable 2是dt2;

suppose consider
DataTable1 is dt1;

and DataTable 2 is dt2;

if(dt1!=null && dt1.Rows.count>0
{
    if(dt2!=null && dt2.Rows.count>0)
  {
 
       dt1.DefaultView.RowFilter = " id<>'10C'";
            if (dt1.DefaultView.ToTable().Rows.Count > 0)
            {
                dt2.Merge(dt1.DefaultView.ToTable());
            }  
 }
}

这意味着dt2将返回两个表数据.

it Means dt2 Will return both Tables Data.

如果您使用的是SQL SERVER 2008-更好的方法

if you are on SQL SERVER 2008 - better approach

merge DataTable1 as dt1
using DataTable2 as dt2
on dt1.ID=dt2.id and dt1.ProductID=dt2.productID
when matched then update set dt1.value=dt2.value;

任何SQL SERVER版本

Any SQL SERVER version

update DataTable1
set value= isnull((select dt2.value
                from DataTable2 dt2
                where (DataTable1.ID=dt2.ID and DataTable1.productID=dt2.ProductID) and DataTable1.value <> dt2.value),DataTable1.value)

希望对您有帮助

Hope this helps

这篇关于合并两个数据表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！