緣由
- 目標電腦系統老舊，Windows Server 2003 (可以理解為 Windows XP)，最新版只能運行 .NET Framework 4.0.3。
- 希望製作成可以打開就執行的 headless，搭配一些簡易的檔案輸出提供其他老舊程式語言簡單的存取。
前置作業
Visual Studio 必須安裝以下 Nuget 套件，你也可以視需要斟酌。
至於詳細安裝方法就不贅述，請自行上網了解 Nuget 套件安裝方法。
- CommandLineParser [v2.9.1]
- NLog [v5.4.0]
- Dapper [v1.50.2]
- 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;
}
}
主要步驟
- 讀取設定：從 ini 檔讀取同步範圍 (MaxRecentMonths)
- 讀取 MDB (Access) 資料庫
var items = mdbConn.Query<CardData>("SELECT EmNo, CardID, SiteID FROM CardData")
- 較為個人特殊需求的步驟，讀者可以略過。
運用 Dapper 取得 CardData 資料，建立 Dictionary<CardID, SiteID, EmNo>
運用 Dapper 查詢 AttRecords，並根據 CardData 修正 EmNo
運用 Dapper 讀取 SQL Server 現有資料：
- 從 SQL Server 下載 targetTableName 所有資料
SqlConnection sqlConn = new SqlConnection(sqlConnStr);
sqlConn.Open();
var existedSqlItems = sqlConn.Query<MhPCard>($"SELECT * FROM [{targetTableName}]");
sqlConn.Dispose();
// 你也可以用 using 包起來，我單純是因為不想要太多巢狀括號不利閱讀。
- 建立 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))
);
- 比對找出新資料（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 就應該乾乾淨淨，專門存資料就好！
