Monthly Archives: 3 月 2025

BulkInsert 懶人福音：從 Access MDB 高效同步資料到 SQL Server

Posted on by

緣由

  1. 目標電腦系統老舊，Windows Server 2003 (可以理解為 Windows XP)，最新版只能運行 .NET Framework 4.0.3。
  2. 希望製作成可以打開就執行的 headless，搭配一些簡易的檔案輸出提供其他老舊程式語言簡單的存取。

前置作業

Visual Studio 必須安裝以下 Nuget 套件，你也可以視需要斟酌。
至於詳細安裝方法就不贅述，請自行上網了解 Nuget 套件安裝方法。

  1. CommandLineParser [v2.9.1]
  2. NLog [v5.4.0]
  3. Dapper [v1.50.2]
  4. DataBooster.SqlServer [v1.8.4]
    此外，為了讓 Windows XP 也能運行我們創建專案時選擇 .Net Framework 4 的 Console Application。

Log 紀錄輸出

在專案根目錄建立 nlog.config

<?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">

        <!-- 定義 NLog 使用的 Targets -->
        <targets>
                <!-- Console 輸出 -->
                <target name="console" xsi:type="Console" layout="${longdate} | ${level:uppercase=true} | ${message}" />

                <!-- 檔案輸出 -->
                <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 (只保留最後一個錯誤) -->
                <target name="lastError" xsi:type="File"
                fileName="user/LastError.txt"
                writeMode="Overwrite"
                layout="${longdate} | ${level:uppercase=true} | ${message}" />
        </targets>

        <!-- 定義 Logger 規則 -->
        <rules>
                <!-- Console 和 檔案都要接收 INFO 以上的 Log -->
                <logger name="*" minlevel="Info" writeTo="console, file" />
                <logger name="*" minlevel="Error" writeTo="lastError" />
        </rules>
</nlog>

如此設定將可以～
最後的錯誤訊息輸出到 LastError.txt；
全部的訊息輸出到 Console 和 logs/SyncDemo.log，並自動依照天數管控不超過 7 份。

實際運用起來如下:

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);
        }
        // ...
    }
}

主要部分: 將 Mdb 資料複製 SQL Server

Program.cs

try
{
    using (var mdbconn = new OleDbConnection(mdbConnStr))
    {
        mdbconn.Open();
        Logger.Info("資料庫未被鎖定，準備開始讀取 MDB。");

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

        WriteLastResult(a, result);
    }
}
catch (OleDbException ex)
{
    Logger.Error("無法開啟 MDB，可能被鎖定！ ErrorMsg: " + ex.Message);
    WriteLastResult(a, false);
}
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
        }
        if (clo.Verbose)
        {
            Logger.Info($"MaxRecentMonths: {maxRecentMonths}");
        }
        int currentMonth = DateTime.Now.Month;

        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())";

        // 查詢 AttRecords，並修正 EmNo 為 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) // 只有當 EmNo 為 0 時才修正
            {
                var key = new Tuple<int, int>(record.CardID, record.SiteID);
                if (cardDataLookup.TryGetValue(key, out int correctEmNo))
                {
                    record.EmNo = correctEmNo; // 替換為正確的 EmNo
                }
            }
            return record;
        }).ToList();
        Logger.Info($"Mdb has total {attJoinEmRecords.Count()} items.");

        SqlConnection sqlConn = new SqlConnection(sqlConnStr);
        sqlConn.Open();
        // Download all data from SQL Server to compare later
        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)));
        sqlConn.Dispose();

        // 找出不存在於 SQL Server 的資料
        var newRecords = attJoinEmRecords
            .Where(item => !existedSqlHashItems.Contains(new Tuple<int?, DateTime>(item.EmNo, item.DateT)))
            .ToList();
        Logger.Info($"New records: {newRecords.Count()}");

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

        Logger.Info("BulkInsert succeed!");

        return true;
    }
    catch (Exception ex)
    {
        Logger.Error("SyncMdbToSQLServer Error: " + ex.Message);
        return false;
    }
}

主要步驟

  1. 讀取設定：從 ini 檔讀取同步範圍 (MaxRecentMonths)
  2. 讀取 MDB (Access) 資料庫 
    var items = mdbConn.Query<CardData>("SELECT EmNo, CardID, SiteID FROM CardData")
  3. 較為個人特殊需求的步驟，讀者可以略過。
    運用 Dapper 取得 CardData 資料，建立 Dictionary<CardID, SiteID, EmNo>
    運用 Dapper 查詢 AttRecords，並根據 CardData 修正 EmNo
    運用 Dapper 讀取 SQL Server 現有資料：
  4. 從 SQL Server 下載 targetTableName 所有資料 
    SqlConnection sqlConn = new SqlConnection(sqlConnStr);
sqlConn.Open();
var existedSqlItems = sqlConn.Query<MhPCard>($"SELECT * FROM [{targetTableName}]");
sqlConn.Dispose();
// 你也可以用 using 包起來，我單純是因為不想要太多巢狀括號不利閱讀。
  5. 建立 HashSet<Tuple<int?, DateTime>> 快速比對
    因為我每筆資料都要看有沒有存在的，如果不一次做好查表，就得不斷查詢 SQL Select，且查表可以將複雜度降低為 BigO(1)。

    var existedSqlHashItems = new HashSet<Tuple<int?, DateTime>>(
existedSqlItems.Select(item => new Tuple<int?, DateTime>(item.Code, item.Date))
);
  6. 比對找出新資料（MDB 但不在 SQL Server） 
    
var newRecords = attJoinEmRecords
.Where(item => !existedSqlHashItems.Contains(new Tuple<int?, DateTime>(item.EmNo, item.DateT)))
.ToList();

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

7. 使用 SqlLauncher 進行批次插入
```csharp
using (SqlLauncher sqlLaun = new SqlLauncher(sqlConnStr, "dbo." + targetTableName, map=>
{ 
    map.Add(0,0);
    map.Add(1,2);
    map.Add(2,6);
}))
{
    foreach (var item in newRecords)
    {
        sqlLaun.Post(item.EmNo, item.DateT, item.NoTe);
    }
}
Logger.Info("BulkInsert succeed!");

將 Mdb 第 0 個欄位對應到 SqlServer 的第 0 個欄位；1對2；2對6。
※ 若報錯 NonNullable 注意你是不是以為已經設 Identity 但其實還沒設呢？或是你單純直接插入 Null 到該欄。

補充：其他程式個別實作

方便地讀取程式啟動參數

有了 CommandLineParser 便能創建一個物件來方便閱讀：

CLOptions.cs

public class CLOptions
{
    [Option('v', "verbose", Required = false, HelpText = "Set output to verbose messages.")]
    public bool Verbose { get; set; }

    [Option('j', "jobid", Required = false, HelpText = "Job ID.")]
    public string JobId { get; set; }

    [Option('t', "textoutputresultpath", Required = false, HelpText = "Text output result path. (Override Config's one)")]
    public string TextOutputResultPath { get; set; }

    [Option('p', "pause", Required = false, HelpText = "Pause before exit.")]
    public bool Pause { get; set; }
}

Program.cs

在程式中隨時可以讀寫這些屬性，不是唯讀的所以也可以像我另外實作 Ini 設定檔案牘取。

//...
var a = Parser.Default.ParseArguments<CLOptions>(args)
.WithParsed<CLOptions>(o =>
{

    if (o.JobId == null)
    {
        o.JobId = Guid.NewGuid().ToString();
    }
    if (o.Verbose)
    {
        Logger.Info($"{o.JobId} | Verbose output enabled (include sensitive info)");
    }
});

var clo = a.Value;
//...

IniReader.cs - Ini 檔案讀取

Ref: https://stackoverflow.com/a/14906422/3939608

/// <summary>
/// An INI file handling class.
/// </summary>
/// <see cref="https://stackoverflow.com/a/14906422/3939608"/>
public class IniReader
{
    string Path;
    string EXE = Assembly.GetExecutingAssembly().GetName().Name;

    [DllImport("kernel32", CharSet = CharSet.Unicode)]
    static extern long WritePrivateProfileString(string Section, string Key, string Value, string FilePath);

    [DllImport("kernel32", CharSet = CharSet.Unicode)]
    static extern int GetPrivateProfileString(string Section, string Key, string Default, StringBuilder RetVal, int Size, string FilePath);

    public IniReader(string IniPath = null)
    {
        Path = new FileInfo(IniPath ?? EXE + ".ini").FullName;
    }

    public string Read(string Key, string Section = null)
    {
        var RetVal = new StringBuilder(255);
        GetPrivateProfileString(Section ?? EXE, Key, "", RetVal, 255, Path);
        return RetVal.ToString();
    }

    public void Write(string Key, string Value, string Section = null)
    {
        WritePrivateProfileString(Section ?? EXE, Key, Value, Path);
    }

    public void DeleteKey(string Key, string Section = null)
    {
        Write(Key, null, Section ?? EXE);
    }

    public void DeleteSection(string Section = null)
    {
        Write(null, null, Section ?? EXE);
    }

    public bool KeyExists(string Key, string Section = null)
    {
        return Read(Key, Section).Length > 0;
    }
}

結論

我們可以 讓舊系統繼續運作，同時避免在 SQL Server 建立不必要的 Stored Procedure。

運用 Dapper 和 SqlLauncher，我們能夠：
✅ 有效讀取 MDB 資料
✅ 使用 HashSet 避免重複插入資料
✅ 透過 批次插入 提高效能

對我來說，SQL Server 就應該乾乾淨淨，專門存資料就好！