从2个列表中获取数据

问题描述

你好

我想从2个SharePoint列表中获取数据，这些列表位于不同的网站级别，并且通过查找字段没有任何关系.但是，有一个公共字段，即GUI，它是员工编号.

为此，我假设由于SPQuery的限制，数据表只能向前移动.

I would like to get data from 2 SharePoint lists which are residing at different site levels and has no relationship via lookup fields. However, there is a common field i.e. GUI which is a employee number.

To achieve this, i am assuming that the data table is only way forward due to the limitation of SPQuery.

因此，我正在使用以下代码:

Therefore, I'm using the following code:

        public static DataTable LeftOuterJoinTables(DataTable LeftTable, DataTable RightTable)
        {
            DataTable dtJoinedTable = null;
            var resultQuery = from TableA in LeftTable.AsEnumerable()
                              join TableB in RightTable.AsEnumerable()
                              on new
                              {
                                  ColumnA = TableA.Field(LeftTable.Columns[0]),
                                  ColumnB = TableA.Field(LeftTable.Columns[1])
                              }
                              equals new
                              {
                                  ColumnA = TableB.Field(RightTable.Columns[0]),
                                  ColumnB = TableB.Field(RightTable.Columns[1])
                              }
                              into GJ
                              from sub in GJ.DefaultIfEmpty()
                              select new
                              {
                                  Column1 = TableA.Field(LeftTable.Columns[0]),
                                  Column2 = TableA.Field(LeftTable.Columns[1]),
                                  Column3 = sub == null ? TableA.Field(LeftTable.Columns["Variable Column"]) :
                                            sub.Table.TableName.ToUpper().Contains("Operation to perform") ? Convert.ToString
                                            (Convert.ToDecimal(TableA.Field(LeftTable.Columns["Column Conataining Values"])) +
                                            Convert.ToDecimal(sub.Field(RightTable.Columns["Column Conataining Values"]))) : ""
                              };
            dtJoinedTable = resultQuery.ExtCopyToDataTable();

            return dtJoinedTable;
        }

但是，在提到'LeftTable.AsEnumerable()'的第二行给出了错误.错误是:

However, it is giving error on 2nd line where 'LeftTable.AsEnumerable()' is mentioned. The error is:

"找不到源类型"System.Data.EnumerableRowCollection< System.Data.DataRow>"的查询模式的实现.找不到"GroupJoin".  您是否缺少对"System.Core.dll"的引用或对"System.Linq"的using指令?"

"Could not find an implementation of the query pattern for source type 'System.Data.EnumerableRowCollection<System.Data.DataRow>'.  'GroupJoin' not found.  Are you missing a reference to 'System.Core.dll' or a using directive for 'System.Linq'?"

如果我添加System.Linq引用，它将在第6、7、11和12行上开始出现以下错误:

If i add System.Linq reference then it starts giving following error on Line 6, 7, 11 and 12:

"方法"System.Data.DataRowExtensions.Field< T>(System.Data.DataRow，字符串)"的类型参数不能从用法中推断出来.尝试显式指定类型参数."

"The type arguments for method 'System.Data.DataRowExtensions.Field<T>(System.Data.DataRow, string)' cannot be inferred from the usage. Try specifying the type arguments explicitly."

知道我哪里出错了吗?

Sohaib Khan

Sohaib Khan

推荐答案

您可以在下面尝试CZFox共享的解决方案.

DataTable dt1 = new DataTable();
    dt1.Columns.Add("CustID", typeof(int));
    dt1.Columns.Add("ColX", typeof(int));
    dt1.Columns.Add("ColY", typeof(int));

    DataTable dt2 = new DataTable();
    dt2.Columns.Add("CustID", typeof(int));
    dt2.Columns.Add("ColZ", typeof(int));

    for (int i = 1; i <= 5; i++)
    {
        DataRow row = dt1.NewRow();
        row["CustID"] = i;
        row["ColX"] = 10 + i;
        row["ColY"] = 20 + i;
        dt1.Rows.Add(row);

        row = dt2.NewRow();
        row["CustID"] = i;
        row["ColZ"] = 30 + i;
        dt2.Rows.Add(row);
    }

    var results = from table1 in dt1.AsEnumerable()
                 join table2 in dt2.AsEnumerable() on (int)table1["CustID"] equals (int)table2["CustID"]
                 select new
                 {
                     CustID = (int)table1["CustID"],
                     ColX = (int)table1["ColX"],
                     ColY = (int)table1["ColY"],
                     ColZ = (int)table2["ColZ"]
                 };
    foreach (var item in results)
    {
        Console.WriteLine(String.Format("ID = {0}, ColX = {1}, ColY = {2}, ColZ = {3}", item.CustID, item.ColX, item.ColY, item.ColZ));
    }
    Console.ReadLine();

这是供您参考的主题.

https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp

最好的问候，

李

这篇关于从2个列表中获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！