Background

1. The target computer system is outdated, running Windows Server 2003 (which is essentially Windows XP), and the latest framework it can support is .NET Framework 4.0.3.
2. The goal is to create a headless application that executes automatically upon startup and provides simple file-based outputs, allowing easy access for other legacy programming languages.

---

Prerequisites

You need to install the following NuGet packages in Visual Studio. The choice of packages may depend on your specific needs.
(For details on how to install NuGet packages, please refer to online documentation.)

1. CommandLineParser [v2.9.1]
2. NLog [v5.4.0]
3. Dapper [v1.50.2]
4. DataBooster.SqlServer [v1.8.4]

Additionally, since we want Windows XP compatibility, the project is created as a .NET Framework 4 Console Application.

---

Logging Setup

Create an nlog.config file in the project root directory:

<?xml version="1.0" encoding="utf-8" ?>
<nlog xmlns="http://www.nlog-project.org/schemas/NLog.xsd"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

        <!-- Define NLog Targets -->
        <targets>
                <!-- Console Output -->
                <target name="console" xsi:type="Console" layout="${longdate} | ${level:uppercase=true} | ${message}" />

                <!-- File Output -->
                <target name="file" xsi:type="File"
                fileName="logs/SyncDemox.log"
                archiveFileName="logs/SyncDemox.{#}.log"
                archiveNumbering="Rolling"
                archiveEvery="Day"
                maxArchiveFiles="7"
                layout="${longdate} | ${level:uppercase=true} | ${message}" />

                <!-- LastError.txt (Only keeps the latest error) -->
                <target name="lastError" xsi:type="File"
                fileName="user/LastError.txt"
                writeMode="Overwrite"
                layout="${longdate} | ${level:uppercase=true} | ${message}" />
        </targets>

        <!-- Define Logger Rules -->
        <rules>
                <!-- Console and File Logging for INFO and above -->
                <logger name="*" minlevel="Info" writeTo="console, file" />
                <logger name="*" minlevel="Error" writeTo="lastError" />
        </rules>
</nlog>

What this setup does:

• Last error messages are written to LastError.txt.
• All log messages are output to both the console and logs/SyncDemo.log.
• Logs are automatically managed (rolling over daily and retaining only the last 7 files).

Example Usage:

internal class Program
{
    private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

    static void Main(string[] args)
    {
        // ...
        try 
        {
            Logger.Info("Start doing work!");
        }
        catch (Exception ex)
        {
            Logger.Error("SyncMdbToSQLServer Error: " + ex.Message);
        }
        // ...
    }
}

---

Main Functionality: Copying MDB Data to SQL Server

Program.cs

try
{
    using (var mdbconn = new OleDbConnection(mdbConnStr))
    {
        mdbconn.Open();
        Logger.Info("Database is not locked, starting to read MDB.");

        var result = SyncMdbToSQLServer(a, ini, mdbconn, sqlConnStr, targetTableName);

        WriteLastResult(a, result);
    }
}
catch (OleDbException ex)
{
    Logger.Error("Unable to open MDB, it may be locked! ErrorMsg: " + ex.Message);
    WriteLastResult(a, false);
}

Core Function: SyncMdbToSQLServer

private static bool SyncMdbToSQLServer(ParserResult<CLOptions> a, IniReader ini, OleDbConnection mdbConn, string sqlConnStr, string targetTableName)
{
    try
    {
        var clo = a.Value;

        if (!int.TryParse(ini.Read("MaxRecentMonths", "Sync"), out int maxRecentMonths))
        {
            maxRecentMonths = -1; // All records
        }
        if (clo.Verbose)
        {
            Logger.Info($"MaxRecentMonths: {maxRecentMonths}");
        }

        // Step 1: Read CardData and store it in a Dictionary for quick lookup
        var cardDataLookup = mdbConn.Query<CardData>("SELECT EmNo, CardID, SiteID FROM CardData")
            .ToDictionary(x => new Tuple<int, int>(x.CardID, x.SiteID), x => x.EmNo);

        string whereDateClause = maxRecentMonths <= -1 ?
            string.Empty :
            $" AND DateT >= DateAdd('m', {-1 * (maxRecentMonths-1)}, Date())";

        // Step 2: Fetch AttRecords and fix EmNo when it is 0
        string sSQL = "SELECT NoTe,DateT,NodeID,CardID,Events,SiteID,AddrID,AttRecords.EmNo,SetID,Name,Left " +
            "FROM [AttRecords] " +
            "LEFT JOIN EmData ON AttRecords.EmNo = EmData.EmNo " +
            "WHERE 1 = 1 And (Events = 3 OR Events = 11)" + whereDateClause;
        var attJoinEmRecords = mdbConn.Query<SimpleAttRecord>(sSQL)
        .Select(record =>
        {
            if (record.EmNo == 0)
            {
                var key = new Tuple<int, int>(record.CardID, record.SiteID);
                if (cardDataLookup.TryGetValue(key, out int correctEmNo))
                {
                    record.EmNo = correctEmNo;
                }
            }
            return record;
        }).ToList();

        Logger.Info($"MDB contains {attJoinEmRecords.Count()} records.");

        // Step 3: Download SQL Server data for comparison
        using var sqlConn = new SqlConnection(sqlConnStr);
        sqlConn.Open();
        var existedSqlItems = sqlConn.Query<MhPCard>($"SELECT * FROM [{targetTableName}]");
        var existedSqlHashItems = new HashSet<Tuple<int?, DateTime>>(existedSqlItems.Select(item => new Tuple<int?, DateTime>(item.Code, item.Date)));

        // Step 4: Find new records
        var newRecords = attJoinEmRecords
            .Where(item => !existedSqlHashItems.Contains(new Tuple<int?, DateTime>(item.EmNo, item.DateT)))
            .ToList();

        Logger.Info($"New records: {newRecords.Count()}");

        // Step 5: Bulk Insert using SqlLauncher
        using (SqlLauncher sqlLaun = new SqlLauncher(sqlConnStr, "dbo." + targetTableName, map =>
        { 
            map.Add(0,0);
            map.Add(1,2);
            map.Add(2,5); // Test
        }))
        {
            foreach (var item in newRecords)
            {
                sqlLaun.Post(item.EmNo, item.DateT, item.NoTe);
            }
        }

        Logger.Info("BulkInsert completed successfully!");
        return true;
    }
    catch (Exception ex)
    {
        Logger.Error("SyncMdbToSQLServer Error: " + ex.Message);
        return false;
    }
}

---

Conclusion

With this approach, we can keep the old system running while avoiding unnecessary stored procedures in SQL Server.

By leveraging Dapper and SqlLauncher, we:
✅ Efficiently read MDB data
✅ Use HashSet to prevent duplicate inserts
✅ Perform BulkInsert for better performance

Personally, I just prefer keeping SQL Server clean and solely for storing data!