如何合并两个数据表并合并到新的数据表中 [英] How to combine two data table and merge in to new datatables

问题描述

我有一个代码，它将读取工作簿中的所有excel文件并将其保存到数据集中的数据表中.现在，基于某种条件，我必须将数据表合并到新表中并存储到oracle数据库中.以下代码我曾经将两个数据表dt1和dt2合并到新表dtnew中，但结果很奇怪.如果第一个数据表有3行，第二个数据表有6行，则给出18行结果，而只给出匹配的行值，这意味着3行包含两个表值.在Nutshell中，我创建了第三个表dtnew并设计了结构，然后使用了条件.逻辑上有任何错误或有更好的方法

I have a code that will read all the excel file in a workbook and will save in to a datatables in a dataset. Now based on certain condition i have to merge datatables in to new one and store in to a oracle database. following code i used to merge two datatables dt1 and dt2 in to new table dtnew ,but the result is weird .If first datatable has 3 rows and second one has 6 rows it is giving 18 rows result rather giving only matching rows values that means here 3 rows containing both table values.In Nutshell i created a third table dtnew and designed structure ,then used condition .Anything wrong in the logic or is there any better way

DataSet ds = Exceltotable(@name);
                        for (int i = 0; i < ds.Tables.Count; i++)
                        {
                            if (ds.Tables[i].TableName == "Table2")
                            {
                                dt1 = ds.Tables[i];
                            }
                            if (ds.Tables[i].TableName == "Table3")
                            {
                                dt2 = ds.Tables[i];
                            }
                            if (dt1 != null && dt2 != null)
                            {
                                break;
                            }
                        }
                        System.Data.DataTable dtnew = new System.Data.DataTable();
                        dtnew.Columns.Add("LOCATION", typeof(string));
                        dtnew.Columns.Add("RBSSHARE", typeof(string));
                        dtnew.Columns.Add("VENDOR", typeof(string));
                        dtnew.Columns.Add("PWRTYPE", typeof(string));
                        dtnew.Columns.Add("EQPTINSTYPE", typeof(string));
                        dtnew.Columns.Add("BTSHEIGHT", typeof(string));
                        dtnew.Columns.Add("BTSFAN", typeof(string));
                        dtnew.Columns.Add("HMSAVAIL", typeof(string));
                        dtnew.Columns.Add("MODELNAME", typeof(string));
                        dtnew.Columns.Add("MODELNO", typeof(string));
                        dtnew.Columns.Add("SERIALNO", typeof(string));
                        dtnew.Columns.Add("REVNO", typeof(string));
                        dtnew.Columns.Add("TECHNOLOGY", typeof(string));
                        dtnew.Columns.Add("Asset description", typeof(string));
                        dtnew.Columns.Add("Asset Category", typeof(string));
                        dtnew.Columns.Add("ASSETTAG", typeof(string));
                        dtnew.Columns.Add("ADDREMARK", typeof(string));
                        dtnew.Columns.Add("DELIVERYDATE", typeof(string));

                        var query = from  pp in dt1.AsEnumerable() 
                                    join ii in dt2.AsEnumerable()
                                        on pp.Field<string>("LOCATION") equals
                                            ii.Field<string>("LOCATION")

                                    select dtnew.LoadDataRow(new object[]
{
pp.Field<string>("LOCATION"),
pp.Field<string>("RBSSHARE"),
pp.Field<string>("VENDOR"),
pp.Field<string>("PWRTYPE"),
pp.Field<string>("EQPTINSTYPE"),
pp.Field<string>("BTSHEIGHT"),
pp.Field<string>("BTSFAN"),
pp.Field<string>("HMSAVAIL"),
pp.Field<string>("MODELNAME"),
pp.Field<string>("MODELNO"),
pp.Field<string>("SERIALNO"),
pp.Field<string>("REVNO"),
ii.Field<string>("TECHNOLOGY"),
ii.Field<string>("TECHNOLOGY"),
ii.Field<string>("Asset description"),
ii.Field<string>("ASSETTAG"),
ii.Field<string>("ADDREMARK"),
ii.Field<string>("DELIVERYDATE")
}, false);

                        query.CopyToDataTable();
                        ds.Tables.Add(dtnew);

推荐答案

尝试以下代码:

var query = 
    from row1 in dt1.AsEnumerable()
    from row2 in dt2.AsEnumerable()
                   .Where(x => x["LOCATION"] == row1["LOCATION"])
                   .DefaultIfEmpty()
    select dtNew.LoadDataRow(new object[]
    {
        row1.Field<string>("LOCATION"),
        row1.Field<string>("RBSSHARE"),
        row1.Field<string>("VENDOR"),
        row1.Field<string>("PWRTYPE"),
        row1.Field<string>("EQPTINSTYPE"),
        row1.Field<string>("BTSHEIGHT"),
        row1.Field<string>("BTSFAN"),
        row1.Field<string>("HMSAVAIL"),
        row1.Field<string>("MODELNAME"),
        row1.Field<string>("MODELNO"),
        row1.Field<string>("SERIALNO"),
        row1.Field<string>("REVNO"),
        row2 == null ? null : row2.Field<string>("TECHNOLOGY"),
        row2 == null ? null : row2.Field<string>("TECHNOLOGY"),
        row2 == null ? null : row2.Field<string>("Asset description"),
        row2 == null ? null : row2.Field<string>("ASSETTAG"),
        row2 == null ? null : row2.Field<string>("ADDREMARK"),
        row2 == null ? null : row2.Field<string>("DELIVERYDATE")
    }, false);

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