C# Visio 二次開發：圖面設備資訊儲存（二）——以資料庫欄位為準構建 INSERT 語句批次執行

核心原則：資料庫欄位為準

Shape 屬性可能因模具變更與資料庫不一致，以資料庫 Schema 為基準構建 SQL，確保欄位對應正確。

取得資料表 Schema (欄位清單)

private DataTable GetReaderSchema(string tableName)
{
    DataTable schemaTable = null;
    string sql = string.Format("select * from [{0}]", tableName);
    Database db = DatabaseFactory.CreateDatabase();
    using (DbConnection conn = db.CreateConnection())
    {
        conn.Open();
        DbCommand cmd = db.GetSqlStringCommand(sql);
        cmd.Connection = conn;
        using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
        {
            schemaTable = reader.GetSchemaTable();
        }
    }
    return schemaTable;
}

// 提取欄位名稱清單
DataTable schemaTable = GetReaderSchema(tableName);
List<string> nameList = new List<string>();
foreach (DataRow dr in schemaTable.Rows)
    nameList.Add(dr["ColumnName"].ToString());

構建 INSERT 語句

string sqlValues = "";
foreach (string colName in nameList)
{
    string value = VisioUtility.GetShapeCellValue(shape, colName);
    if (!string.IsNullOrEmpty(value))
        sqlValues += string.Format("'{0}',", value);
    else
        sqlValues += "NULL,";
}
// 移除尾隨逗號，組合完整 INSERT
string insertSql = string.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", 
    tableName, string.Join(",", nameList), sqlValues.TrimEnd(','));

批次執行流程

1. 遍歷圖面所有形狀 (SelectAll 迴圈)
2. 依設備類型查詢對應資料表 (DeviceTableInfo)
3. 對每個形狀構建 INSERT 語句加入清單
4. 事務中批次執行所有 SQL

效果展示