如何通过C#程序实现MySQL新增记录同步至SharePoint Online列表？

Looks like you're already halfway there with the MySQL data fetching! Let's tackle the SharePoint sync part—here's how to check for existing records and insert new ones using the SharePoint REST API in your C# console app.

Core Approach #

Since both your MySQL table and SharePoint list have a shared primary key (let's assume it's EmployeeID for this example), we'll follow this workflow:

1. Fetch all existing primary key values from the SharePoint list
2. Store these in a fast-lookup collection (like a HashSet<string>) for quick existence checks
3. Compare each MySQL record's primary key against this collection
4. For records that don't exist in SharePoint, call the REST API to create a new list item

Step 1: Add SharePoint Authentication & Helper Methods #

First, we need helper methods to interact with SharePoint Online's REST API. We'll include logic to fetch existing keys and insert new items, plus authentication handling.

using System.Net;
using System.Text;
using System.Web.Script.Serialization;

public static class SharePointHelper
{
    // Replace these with your actual SharePoint details
    private const string SiteUrl = "https://yourtenant.sharepoint.com/sites/yoursite";
    private const string ListName = "EmployeeDirectory";
    private const string PrimaryKeyColumnInternalName = "EmployeeID"; // Use internal column name, not display name

    // Fetch all existing primary keys from the SharePoint list
    public static HashSet<string> GetExistingPrimaryKeys(string spUsername, string spPassword)
    {
        var existingKeys = new HashSet<string>();
        var apiEndpoint = $"{SiteUrl}/_api/web/lists/getbytitle('{ListName}')/items?$select={PrimaryKeyColumnInternalName}";

        using (var client = new WebClient())
        {
            client.Credentials = new NetworkCredential(spUsername, spPassword);
            client.Headers.Add("Accept", "application/json;odata=verbose");
            
            try
            {
                var responseJson = client.DownloadString(apiEndpoint);
                var serializer = new JavaScriptSerializer();
                var responseData = serializer.Deserialize<dynamic>(responseJson);
                
                foreach (var listItem in responseData.d.results)
                {
                    var key = listItem[PrimaryKeyColumnInternalName]?.ToString();
                    if (!string.IsNullOrEmpty(key))
                    {
                        existingKeys.Add(key);
                    }
                }
            }
            catch (WebException ex)
            {
                Console.WriteLine($"Error fetching SharePoint keys: {ex.Message}");
            }
        }
        return existingKeys;
    }

    // Insert a new employee record into SharePoint
    public static bool InsertNewEmployee(dynamic employeeData, string spUsername, string spPassword)
    {
        var apiEndpoint = $"{SiteUrl}/_api/web/lists/getbytitle('{ListName}')/items";
        var serializer = new JavaScriptSerializer();

        // Map MySQL columns to SharePoint columns (skip manually maintained columns)
        var listItemData = new
        {
            __metadata = new { type = "SP.Data.EmployeeDirectoryListItem" }, // Replace with your list's entity type
            EmployeeID = employeeData.EmployeeID,
            FullName = employeeData.FullName,
            WorkEmail = employeeData.Email,
            Department = employeeData.Department
        };

        var requestBody = serializer.Serialize(listItemData);

        using (var client = new WebClient())
        {
            client.Credentials = new NetworkCredential(spUsername, spPassword);
            client.Headers.Add("Accept", "application/json;odata=verbose");
            client.Headers.Add("Content-Type", "application/json;odata=verbose");
            client.Headers.Add("X-RequestDigest", GetRequestDigest(SiteUrl, spUsername, spPassword));

            try
            {
                client.UploadString(apiEndpoint, "POST", requestBody);
                Console.WriteLine($"Successfully added employee: {employeeData.FullName}");
                return true;
            }
            catch (WebException ex)
            {
                Console.WriteLine($"Failed to add employee {employeeData.EmployeeID}: {ex.Message}");
                return false;
            }
        }
    }

    // Helper to get the request digest token required for write operations
    private static string GetRequestDigest(string siteUrl, string spUsername, string spPassword)
    {
        var digestEndpoint = $"{siteUrl}/_api/contextinfo";
        
        using (var client = new WebClient())
        {
            client.Credentials = new NetworkCredential(spUsername, spPassword);
            client.Headers.Add("Accept", "application/json;odata=verbose");
            
            var responseJson = client.UploadString(digestEndpoint, "POST", string.Empty);
            var serializer = new JavaScriptSerializer();
            var digestData = serializer.Deserialize<dynamic>(responseJson);
            
            return digestData.d.GetContextWebInformation.FormDigestValue;
        }
    }
}

Step 2: Integrate with Your Existing MySQL Code #

Update your Main method to combine MySQL data fetching with SharePoint sync logic:

static void Main()
{
    // MySQL connection details (replace with your values)
    string dbName = "your_database";
    string serverAddress = "your_mysql_server";
    string dbPwd = "your_db_password";
    string dbUser = "your_db_username";

    // SharePoint credentials (replace with your values)
    string spUsername = "your_sp_user@tenant.onmicrosoft.com";
    string spPassword = "your_sp_password";

    // 1. Connect to MySQL and fetch employee records
    DbConnection mySQLConn = new DbConnection(dbName, serverAddress, dbPwd, dbUser);
    mySQLConn.Connect();

    // Select only the columns you need to sync (match SharePoint columns)
    string sqlQuery = "SELECT EmployeeID, FullName, Email, Department FROM tbl_CC_SP";
    MySqlCommand sqlCom = new MySqlCommand(sqlQuery, mySQLConn.getConnection());
    MySqlDataReader reader = sqlCom.ExecuteReader();

    // 2. Get existing employee IDs from SharePoint
    var existingEmployeeIds = SharePointHelper.GetExistingPrimaryKeys(spUsername, spPassword);

    // 3. Sync new records to SharePoint
    while (reader.Read())
    {
        var employeeId = reader.GetString("EmployeeID");
        if (!existingEmployeeIds.Contains(employeeId))
        {
            // Map MySQL reader data to a dynamic object (use a strongly typed class for better safety)
            var newEmployee = new
            {
                EmployeeID = employeeId,
                FullName = reader.GetString("FullName"),
                Email = reader.GetString("Email"),
                Department = reader.GetString("Department")
            };

            SharePointHelper.InsertNewEmployee(newEmployee, spUsername, spPassword);
        }
        else
        {
            Console.WriteLine($"Employee {employeeId} already exists in SharePoint—skipping");
        }
    }

    // Cleanup resources
    reader.Close();
    mySQLConn.Close();
    Console.WriteLine("Sync process completed! Press Enter to exit.");
    Console.Read();
}

Key Notes to Remember #

• Internal Column Names: SharePoint uses internal names for columns (not display names). Find yours by going to List Settings > Column > Check the URL for the Field parameter.
• Entity Type Name: The __metadata.type value (e.g., SP.Data.EmployeeDirectoryListItem) can be found by making a GET request to /_api/web/lists/getbytitle('YourList')?$select=ListItemEntityTypeFullName.
• Authentication: For production, avoid basic auth—use Azure AD OAuth with libraries like Microsoft.Identity.Client for secure modern authentication.
• Error Handling: Add retry logic, logging, and validation for edge cases (e.g., null values in MySQL) based on your production needs.

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