如何对比两个SharePoint列表匹配邮箱后同步Steuernummer字段

DevOps 小满

2026-8-26

SharePoint Online 跨列表字段同步问题修复

需求说明

对比两个SharePoint Online列表实现字段同步：两个列表均包含email字段，当两个列表项的email字段值相等时，将源列表Gesetzliche Vertreter中名为Steuernummer（德语，意为税号）的字段值，同步到目标列表Funktionaere的对应匹配项中。

原有实现代码

using System;
using System.IO;
using System.Configuration;
using System.Collections.Generic;
using System.Reflection;
using System.Diagnostics;
using System.Linq;
using CsvHelper;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Utilities;
using Microsoft.SharePoint.Client.Taxonomy;

namespace ImportListFromCSV {
    class Program {

static void Main() {
            // creates a new SharePoint context 
            // passing the parameters
            Uri siteUri = new Uri(ConfigurationManager.AppSettings["SrcUrl"].ToString());
            using (var clientContext = new ClientContext(siteUri.ToString())) {
                Uri siteUri2 = new Uri(ConfigurationManager.AppSettings["DestUrl"].ToString());
                using (var clientContext2 = new ClientContext(siteUri2.ToString())) {
                    // selects the list by title
                    List list = clientContext.Web.Lists.GetByTitle("Gesetzliche Vertreter");
                    List list2 = clientContext2.Web.Lists.GetByTitle("Funktionaere");
                CamlQuery query = new CamlQuery();
                    query.ViewXml = "<View><Query><Query><OrderBy><FieldRef Name='Steuernummer' /></OrderBy><Query></Query></View>";

                  // ListItemCollection collListItem = list.GetItems(query);
                  //  clientContext.Load(collListItem);
                    ListItemCollection collListItem2 = list2.GetItems(query);
                    clientContext2.Load(collListItem2);

               /* clientContext2.Load(collListItem,
                items => items.Include(
                item => item["Steuernummer"],
                item => item["E-Mail"]));
              */  
                clientContext2.Load(collListItem2,
                items => items.Include(
                item => item["Steuernummer"],
                item => item["Email"]));


                    clientContext2.ExecuteQuery();
                    foreach (ListItem item in collListItem2) {
                        ListItemCreationInformation itemCreateInfo2 = new ListItemCreationInformation();
                        ListItem listItem2 = list.AddItem(itemCreateInfo2);
                       
                        listItem2.SystemUpdate();
                        clientContext.ExecuteQuery();

                    }

                }
            }
        }
    }
}

原有App.config配置

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <appSettings>
    <add key="SrcUrl" value="https://SourceUrlWithSteuernummer/" />
    <add key="DestUrl" value="http://DestinationUrlWithoutSteuernummer/" />
    <add key="AccountName" value="daniel.kienzl@hgv.it" />
    <add key="AccountPwd" value="Hgv12345678" />
    <add key="ListName" value="Funktionaere" />
    <add key="ClientSettingsProvider.ServiceUri" value="" />
  </appSettings>
  <system.web>
    <membership defaultProvider="ClientAuthenticationMembershipProvider">
      <providers>
        <add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
      </providers>
    </membership>
    <roleManager defaultProvider="ClientRoleProvider" enabled="true">
      <providers>
        <add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
      </providers>
    </roleManager>
  </system.web>
</configuration>

原有代码核心问题

未配置认证逻辑：两个ClientContext对象都没有使用配置文件中存储的账号密码做SharePoint Online认证，根本无法正常访问站点资源。
源数据未加载：源列表的条目加载逻辑被整体注释，无法获取源列表中存储的邮箱、税号数据，缺少同步数据源。
CAML语法错误：查询视图XML存在多层嵌套<Query>标签、标签闭合错误问题，会导致查询返回结果不符合预期。
业务逻辑完全错误：遍历目标列表条目时，反向在源列表中新建空白条目，既没有做邮箱字段的相等匹配，也没有更新目标列表的对应字段。
字段加载逻辑错误：源列表字段加载代码写错了关联的ClientContext实例，也没有提前确认两个列表邮箱字段的内部名称，容易触发字段不存在的运行时报错。
大列表兼容问题：没有做分页查询逻辑，列表条目数超过5000时会直接触发SharePoint列表视图阈值拦截报错。
地址协议错误：配置中目标站点地址使用http协议，SharePoint Online默认强制https协议，会出现跳转、认证失败问题。

修正后可运行代码

注意：使用前请先通过SharePoint列表设置页确认两个列表的邮箱字段、税号字段内部名是否和代码中一致，若不一致请替换为对应内部名；同时请先将App.config中DestUrl的协议改为https。

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Security;
using Microsoft.SharePoint.Client;

namespace ImportListFromCSV
{
    class Program
    {
        static void Main()
        {
            // 读取配置
            string srcUrl = ConfigurationManager.AppSettings["SrcUrl"];
            string destUrl = ConfigurationManager.AppSettings["DestUrl"];
            string account = ConfigurationManager.AppSettings["AccountName"];
            string pwd = ConfigurationManager.AppSettings["AccountPwd"];
            
            // 构造认证凭证
            SecureString securePwd = new SecureString();
            pwd.ToList().ForEach(c => securePwd.AppendChar(c));
            SharePointOnlineCredentials creds = new SharePointOnlineCredentials(account, securePwd);

            // 初始化源站点上下文
            using (var srcCtx = new ClientContext(srcUrl))
            {
                srcCtx.Credentials = creds;
                List srcList = srcCtx.Web.Lists.GetByTitle("Gesetzliche Vertreter");
                
                // 分页加载源列表所有项，避免5000条阈值问题
                ListItemCollectionPosition srcPos = null;
                Dictionary<string, string> emailToTaxIdMap = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
                
                do
                {
                    CamlQuery srcQuery = new CamlQuery
                    {
                        ViewXml = "<View Scope='RecursiveAll'><ViewFields><FieldRef Name='E-Mail'/><FieldRef Name='Steuernummer'/></ViewFields><RowLimit>5000</RowLimit></View>",
                        ListItemCollectionPosition = srcPos
                    };
                    var srcItems = srcList.GetItems(srcQuery);
                    srcCtx.Load(srcItems);
                    srcCtx.ExecuteQuery();
                    srcPos = srcItems.ListItemCollectionPosition;
                    
                    foreach (var item in srcItems)
                    {
                        string email = item["E-Mail"]?.ToString().Trim();
                        string taxId = item["Steuernummer"]?.ToString().Trim();
                        if (!string.IsNullOrEmpty(email) && !emailToTaxIdMap.ContainsKey(email))
                        {
                            emailToTaxIdMap.Add(email, taxId ?? string.Empty);
                        }
                    }
                } while (srcPos != null);

                // 初始化目标站点上下文
                using (var destCtx = new ClientContext(destUrl))
                {
                    destCtx.Credentials = creds;
                    List destList = destCtx.Web.Lists.GetByTitle("Funktionaere");
                    
                    // 分页加载目标列表项，匹配后更新
                    ListItemCollectionPosition destPos = null;
                    do
                    {
                        CamlQuery destQuery = new CamlQuery
                        {
                            ViewXml = "<View Scope='RecursiveAll'><ViewFields><FieldRef Name='Email'/><FieldRef Name='Steuernummer'/></ViewFields><RowLimit>100</RowLimit></View>",
                            ListItemCollectionPosition = destPos
                        };
                        var destItems = destList.GetItems(destQuery);
                        destCtx.Load(destItems);
                        destCtx.ExecuteQuery();
                        destPos = destItems.ListItemCollectionPosition;
                        
                        foreach (var item in destItems)
                        {
                            string destEmail = item["Email"]?.ToString().Trim();
                            if (string.IsNullOrEmpty(destEmail) || !emailToTaxIdMap.ContainsKey(destEmail)) continue;
                            
                            string targetTaxId = emailToTaxIdMap[destEmail];
                            string currentTaxId = item["Steuernummer"]?.ToString().Trim() ?? string.Empty;
                            // 值无变化则跳过更新
                            if (currentTaxId == targetTaxId) continue;
                            
                            item["Steuernummer"] = targetTaxId;
                            item.Update();
                        }
                        // 批量提交当前分页的更新，减少请求次数
                        destCtx.ExecuteQuery();
                    } while (destPos != null);
                }
            }
            Console.WriteLine("字段同步完成");
            Console.ReadKey();
        }
    }
}

内容的提问来源于stack exchange，提问作者Genos

如何对比两个SharePoint列表 匹配邮箱后同步Steuernummer字段

需求说明 #

原有实现代码 #

原有App.config配置 #

原有代码核心问题 #