A console application to synchronise SharePoint Online lists with a SQL Server database, including data quality routines for common list inconsistencies. Built with the SharePoint Client Side Object Model (CSOM) and .NET 8.0.

• Overview
• Features
• Prerequisites
• Installation
• Configuration
• Usage
• Project Structure
• Troubleshooting
• License

This tool automates the transfer of data from SharePoint Online lists to SQL Server tables and performs targeted data quality fixes on specific lists (e.g. Activities, Invoice Request). It supports two sync modes — daily and monthly — and provides detailed logging for monitoring and debugging.

SharePoint → SQL Synchronisation Transfers data from configured SharePoint lists to corresponding SQL Server tables using either incremental (daily) or full (monthly) updates.

Data Quality Fixes

• Activities — Backfills the _OpportunityID field by copying from OpportunityID where null.
• Invoice Request — Updates approver fields (Main_x0020_approver, Optional_x0020_approver, Financial_x0020_approver) based on recent changes in the Unit list.
• Timesheet — Similar data quality logic, extendable as needed.

SQL Connection Testing Verifies connectivity to SQL Server and checks for necessary permissions (SELECT, CREATE TABLE, etc.) before any operation begins.

Observability & Metrics

• Operation Statistics — Tracks execution metrics (total items processed, success/failure counts, duration, success rate, average time per item).
• Operation Context — Correlates operations with unique IDs for end-to-end log tracing and debugging.

Resilience & Error Handling

• Retry Policy — Automatically retries transient SharePoint failures (throttling, timeouts) with exponential backoff and jitter.
• Health Checks — Validates SharePoint and SQL configurations at startup, preventing silent failures.

Developer Experience

• CAML Query Builder — Type-safe helpers for constructing SharePoint queries (null checks, date ranges, text searches, complex conditions) without manual XML construction.

Configurable Logging Verbosity levels (0–3) control console output — run silently in production or in full debug mode during development.

External Configuration All settings (SharePoint credentials, SQL connection string) are stored in an XML file, overridable via a command-line argument.

• Windows operating system (.NET 8.0 target)
• .NET 8.0 SDK or later
• Access to a SharePoint Online tenant with appropriate read/write permissions
• SQL Server (any edition) with a database where target tables will be created/updated
• VPN access if SharePoint or SQL Server is only reachable within a corporate network

1. Clone the repository

git clone https://github.com/your-org/sharepoint-sync-tool.git

2. Restore NuGet packages and build

cd SPOtoSQL-Net8
dotnet restore
dotnet build ConsoleApp1Net8

3. (Optional) Run the application

dotnet run --project ConsoleApp1Net8 -- [arguments]

4. (Optional) Deploy Publish the application for deployment:

dotnet publish ConsoleApp1Net8 -c Release -r win-x64 --self-contained false

The published output will be in ConsoleApp1Net8/bin/Release/net8.0/win-x64/publish.

All runtime settings are defined in an XML file. The default location is XmlConfig\UserConfig.xml (relative to the executable). You can specify an alternative path with the --config argument.

Example UserConfig.xml

<?xml version="1.0" encoding="utf-8"?>
<Configuration>
  <SharePoint>
    <Username>user@company.com</Username>
    <Password>YourPassword</Password>
  </SharePoint>
  <SQL>
    <ConnectionString>Server=myServer;Database=myDB;Integrated Security=true;</ConnectionString>
  </SQL>
</Configuration>

Security Note: Storing passwords in plain text is not recommended for production. Consider using encrypted configuration sections or environment variables.

Note: The tool expects specific SharePoint site relative paths (e.g. seed, wolf, selfservice/invoicerequest). These are hard-coded in the data quality classes. If your site structure differs, adjust the source code accordingly.

Run the executable from the command line:

ConsoleApp1Net8.exe [arguments]

Or via dotnet run:

dotnet run --project ConsoleApp1Net8 -- [arguments]

# Daily sync with normal logging
ConsoleApp1Net8.exe daily

# Monthly sync with detailed logging and a custom config
ConsoleApp1Net8.exe monthly --verbose=2 --config="C:\Configs\custom.xml"

# Test SQL connection and exit
ConsoleApp1Net8.exe diagnostic

1. Command-line arguments are parsed and logging is configured.
2. The SQL Server connection is tested (including permission checks).
3. SharePoint credentials are loaded from the configuration file.
4. [NEW] Health checks validate SharePoint and SQL configuration.
5. Based on the mode (daily or monthly), RefreshSQLLists.SPOtoSQLUpdate(...) performs the data transfer.
6. Data quality routines (ActivitiesDQ, InvoiceRequestDQ, etc.) run as part of the sync.
7. All actions are logged to the console at the specified verbosity level.
8. [NEW] Operation metrics are tracked and reported via OperationStatistics.

Tracks execution metrics for any batch operation:

var stats = new OperationStatistics();
// ... perform operations ...
stats.TotalItemsProcessed = 100;
stats.SuccessfulUpdates = 95;
stats.FailedUpdates = 5;
Console.WriteLine(stats); // Outputs: "Processed 100 items; 95 succeeded, 5 failed (95% success rate, avg 250ms/item)"

Maintains correlation ID and statistics for entire operations:

var context = new OperationContext { OperationName = "DailySync" };
// ... perform operations, update context.Statistics ...
context.MarkComplete(); // Logs completion with correlation ID

Validates configuration at startup:

var checker = new HealthChecker(Logger.Instance, verbosity: 2);
var result = checker.PerformHealthCheck(spoUser, sqlConnectionString);
if (!result.IsHealthy)
{
    Console.WriteLine(result); // Prints errors and warnings
    return; // Exit if critical issues found
}

Automatically retries transient failures:

var policy = new RetryPolicy(maxRetries: 3, initialDelayMs: 1000);
var result = policy.ExecuteWithRetry(
    () => performSharePointOperation(),
    "PerformSharePointOperation"
);

Construct type-safe CAML queries:

// Query for null fields
var query1 = CamlQueryBuilder.BuildNullFieldQuery("ApprovalStatus");

// Query for date range
var query2 = CamlQueryBuilder.BuildDateRangeQuery("Created",
    DateTime.Now.AddDays(-7), DateTime.Now);

// Complex query with AND conditions
var query3 = CamlQueryBuilder.BuildAndQuery(
    CamlQueryBuilder.BuildNotNullFieldQuery("Owner"),
    CamlQueryBuilder.BuildEqualTextQuery("Status", "Active")
);

SPOtoSQL-Net8/
├── ConsoleApp1Net8/
│   ├── ConsoleApp1Net8.csproj
│   └── ... (source files linked from original location)
SPOtoSQL-Snapshots/
└── ConsoleApp1/
    ├── ConsoleApp1.csproj          (original .NET Framework 4.8 project)
    ├── packages.config
    ├── AssemblyInfo.cs
    ├── ConsoleLogger/
    │   └── Logger.cs
    ├── Sharepoint/
    │   ├── ActivitiesDQ.cs
    │   ├── CamlQueryBuilder.cs      (CAML query builder)
    │   ├── Context.cs
    │   ├── GetallLists.cs
    │   ├── HealthChecker.cs         (health check validation)
    │   ├── InvoiceRequestDQ.cs
    │   ├── OperationContext.cs      (operation tracking with correlation ID)
    │   ├── OperationStatistics.cs   (execution metrics collection)
    │   ├── RetryPolicy.cs           (retry with exponential backoff)
    │   ├── SPOList.cs
    │   ├── SPOUser.cs
    │   └── TimesheetDQ.cs
    ├── SPODataQuality/
    │   └── RefreshSPOLists.cs
    ├── Sqlserver/
    │   ├── RefreshSQLLists.cs
    │   └── SQLInteraction.cs
    └── XmlConfig/
        ├── ConfigHelper.cs
        └── UserConfig.xml

If you encounter unexpected behaviour, run with --verbose=3 for full debug output and open an issue with the log attached.

This project is licensed under the MIT License.