C# 發現之旅第十二講：快速 ORM 刪除命令與參數值生成邏輯

WHERE 子句動態建構

代碼生成器掃描實體類別所有屬性，尋找標記 BindFieldAttribute.Key = true 的關鍵屬性，組合成 WHERE 條件：

• 命名參數模式：FieldName = @KeyPropertyName
• 位置參數模式：FieldName = ?

若無關鍵屬性，生成拋出 NotSupportedException 的代碼，避免框架過早報錯，保留僅供查詢/新增的實體類別彈性。

FillDeleteCommand 代碼生成流程

// 收集關鍵屬性與參數名
ArrayList keyProps = new ArrayList();
ArrayList keyParamNames = new ArrayList();
StringBuilder whereSql = new StringBuilder();

foreach (PropertyInfo p in ps) {
    if (!p.CanRead) continue;
    BindFieldAttribute fa = GetCustomAttribute(p);
    if (!fa.Key) continue;

    string fieldName = GetBindFieldName(p);
    if (whereSql.Length > 0) whereSql.Append(" AND ");
    keyProps.Add(p);

    if (bolNamedParameter) {
        string pName = "Key" + p.Name;
        keyParamNames.Add(pName);
        whereSql.Append(FixFieldName(fieldName) + " = @" + pName);
    } else {
        whereSql.Append(FixFieldName(fieldName) + " = ? ");
    }
}

// 輸出 FillDeleteCommand 方法
writer.WriteLine("public override int FillDeleteCommand(IDbCommand cmd, object objRecord)");
writer.BeginGroup("{");
if (keyProps.Count == 0) {
    writer.WriteLine("throw new NotSupportedException(\"FillDeleteCommand\");");
} else {
    writer.WriteLine("if (cmd == null) throw new ArgumentNullException(\"cmd\");");
    writer.WriteLine("if (objRecord == null) throw new ArgumentNullException(\"objRecord\");");
    writer.WriteLine($"{recordType.FullName} myRecord = objRecord as {recordType.FullName};");
    writer.WriteLine($"if (myRecord == null) throw new ArgumentException(\"must type '{recordType.FullName}'\");");

    StringBuilder deleteSql = new StringBuilder();
    deleteSql.Insert(0, "DELETE FROM " + tableName + " WHERE " + whereSql);
    writer.WriteLine("cmd.Parameters.Clear();");
    writer.WriteLine($"cmd.CommandText = @\"{deleteSql}\";");
    writer.WriteLine("IDbDataParameter parameter = null;");

    for (int i = 0; i < keyProps.Count; i++) {
        PropertyInfo p = (PropertyInfo)keyProps[i];
        writer.WriteLine("parameter = cmd.CreateParameter();");
        WriteSetParameterValue(p, writer);
        if (bolNamedParameter) {
            writer.WriteLine($"parameter.ParameterName = \"{keyParamNames[i]}\";");
        }
        writer.WriteLine("cmd.Parameters.Add(parameter);");
    }
    writer.WriteLine($"return {keyProps.Count};");
}
writer.EndGroup("}");

WriteSetParameterValue 核心邏輯

此輔助方法處理不同資料型別的參數值指派：

• DateTime：依 WriteFormat 屬性格式化，預設 yyyy-MM-dd HH:mm:ss。
• string：檢查 null 或空字串，改指派 DBNull.Value。
• 其他：直接指派屬性值。

private void WriteSetParameterValue(PropertyInfo p, IndentTextWriter writer) {
    if (p.PropertyType == typeof(DateTime)) {
        BindFieldAttribute fa = GetCustomAttribute(p);
        string fmt = string.IsNullOrEmpty(fa.WriteFormat) ? "yyyy-MM-dd HH:mm:ss" : fa.WriteFormat;
        writer.WriteLine($"parameter.Value = myRecord.{p.Name}.ToString(\"{fmt}\");");
    } else if (p.PropertyType == typeof(string)) {
        writer.WriteLine($"if (myRecord.{p.Name} == null || myRecord.{p.Name}.Length == 0)");
        writer.WriteLine("    parameter.Value = DBNull.Value;");
        writer.WriteLine("else");
        writer.WriteLine($"    parameter.Value = myRecord.{p.Name};");
    } else {
        writer.WriteLine($"parameter.Value = myRecord.{p.Name};");
    }
}

實際生成範例（DB_Employees）

public override int FillDeleteCommand(IDbCommand cmd, object objRecord) {
    if (cmd == null) throw new ArgumentNullException("cmd");
    if (objRecord == null) throw new ArgumentNullException("objRecord");
    DB_Employees myRecord = objRecord as DB_Employees;
    if (myRecord == null) throw new ArgumentException("must type 'DB_Employees'");
    cmd.Parameters.Clear();
    cmd.CommandText = @"DELETE FROM Employees WHERE EmployeeID = ?";
    IDbDataParameter parameter = null;
    parameter = cmd.CreateParameter();
    parameter.Value = myRecord.EmployeeID;
    cmd.Parameters.Add(parameter);
    return 1;
}

EmployeeID 標記為 Key，故僅需單一參數即可精準刪除。