XML 是網際網路上交換資料的最佳格式之一,資料很容易整合進不同的 Web 應用。但如果要把一份 XML 檔案寫進資料庫呢?XML2OleDb 示範了這件事可以有多輕鬆——不論目標是 SQL Server、Access、Excel、Visual FoxPro 還是 dBase,只要是 OleDb 能連的資料庫,做法都一樣。
整個流程的起點是 .NET 的 DataSet——它天生就能讀 XML。載入之後取第一個資料表,那就是我們要寫進資料庫的 DataTable;至於資料表的名稱,則約定用 XML 檔名去掉副檔名:
// 把 XML 檔讀進 DataSet dataSetXml.ReadXml(HttpContext.Current.Server.MapPath(textBoxXML.Text)); // 取第一個資料表 dataTableXml = dataSetXml.Tables[0]; // 去掉副檔名當作資料表名稱 tableName = textBoxXml.Text.Substring(0, textBoxXml.Text.Length - 4);
XML 格式若不正確,ReadXml() 會直接丟出例外,這是第一道防線。
XML 載入成功後,先確認資料表裡真的有資料列,接著檢查資料庫中是否已存在同名資料表——不存在就建立,然後逐列寫入。
if (dataTableXml.Rows.Count > 0)
{
// 後續處理
}
建立資料庫連線並取得結構描述資訊,用來判斷資料表是否已存在:
OleDbConnection oledbConn = new OleDbConnection(textBoxOleDb.Text);
oledbConn.Open();
DataTable schemaTable = oledbConn.GetOleDbSchemaTable(
OleDbSchemaGuid.Tables,
new object[] { null, null, tableName, "TABLE" });
// 若找不到,schemaTable 會沒有任何列
if (schemaTable.Rows.Count < 1)
{
string sqlCmd = "create table " + tableName + " (";
// 依 XML 的欄位動態產生每一列,型別一律用 char(100)
for (int i = 0; i < dataTableXml.Columns.Count; i++)
{
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + " char(100),";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd, oledbConn);
oledbCmd.ExecuteNonQuery();
}
這裡有個明顯的簡化:所有欄位一律建成 char(100)。這樣做的好處是跨資料庫通用——不必為 SQL Server、Access、dBase 分別處理型別對應;代價是失去了型別資訊,數值與日期都得當字串存。若目標資料庫已存在且型別定義完整,這段建立邏輯自然就不會被觸發。
寫入用雙層迴圈:外層走資料列,內層組出欄位清單與值清單:
foreach (DataRow dr in dataTableXml.Rows)
{
string sqlCmd = "insert into [" + tableName + "] (";
// 組出欄位名稱
for (int i = 0; i < dataTableXml.Columns.Count; i++)
{
sqlCmd = sqlCmd + dataTableXml.Columns[i].ColumnName.ToString() + ",";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ") values (";
// 組出對應的值
for (int x = 0; x < dataTableXml.Columns.Count; x++)
{
sqlCmd = sqlCmd + "'" + dr[x].ToString().Replace("'", "''") + "',";
}
sqlCmd = sqlCmd.Substring(0, sqlCmd.Length - 1) + ");";
OleDbCommand oledbCmd = new OleDbCommand(sqlCmd, oledbConn);
oledbCmd.ExecuteNonQuery();
}
值得稱許的是那個 Replace("'", "''")——把單引號加倍是 SQL 標準的轉義方式,處理掉了最基本的一類 SQL 注入與語法錯誤。但這只是最低限度:字串拼接組 SQL 依然不是好做法,正式環境應改用 OleDbCommand.Parameters 參數化查詢,才能完整避開注入風險,也才能正確處理各資料庫的日期與數值格式差異。
測試前準備:先下載原始碼,建立一個空的資料庫(Database.mdb 或 Database.xls),並確認該檔案具有可寫入權限。若使用 FoxPro 或 dBase,則建立一個空目錄即可。
常見的 OleDb 連線字串:
Access: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\database.mdb; Excel: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\database.xls;Extended Properties=Excel 8.0; FoxPro / dBase: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\data;Extended Properties=dBase IV; SQL Server: Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=database;User ID=sa;Password=;
這個範例的價值在於展示「DataSet 讀 XML → 動態建表 → 逐列寫入」這條路徑有多短,但要放進正式環境,還有幾處該處理:
Update(),或用 OleDbCommand 的參數化批次。把 XML 寫進資料庫這件事,用 DataSet 當中繼站確實優雅——XML 與資料表之間的欄位對應幾乎自動完成,程式碼量遠少於手工解析節點。只要把 SQL 組字串的部分換成參數化查詢,這套做法就能安心用在真實專案裡。