[C#] 從 MySQL 轉資料至 MS SQL Server (SqlBulkCopy)

使用套件: MySQL Connector/Net 6.1 (其他套件可以在此 下載)
因為是大量的資料搬移, 所以採用 .Net 2.0 的 SqlBulkCopy .
連線字串的部分可以到這個網站找: http://www.connectionstrings.com/.
以下是我使用的連線字串:

• MySQL:
  <add name="MySqlConnectionString" connectionString="Server=127.0.0.1;Database=Test;Uid=testuser;Pwd=xxxxxxxx;" providerName="MySql.Data.MySqlClient"/>
• SQL Server:
  <add name="MsSqlConnectionString" connectionString="Data Source=.;Initial Catalog=Test;Persist Security Info=True;User ID=testuser;Password=xxxxxxxx;" providerName="System.Data.SqlClient"/>

MySQL 的資料表 tblSource 如下:

tID (int)	tName (varchar)	tDateTime (datetime)
1	王迪西	2009-08-21
2	陳小美	2009-08-22
5	徐丁丁	2009-08-25
9	黃拉拉	2009-08-13

其中, tID 欄位是自動遞增的PK欄位, 而 tDateTime 是日期欄位.
在轉資料的過程中, 如果有欄位型態是 datetime , 會常看到一個錯誤訊息:

• System.InvalidOperationException: 資料來源的型別 MySqlDateTime 指定值無法轉換成指定目標資料行的型別 datetime.
  ---> MySql.Data.Types.MySqlConversionException: Unable to convert MySQL date/time value to System.DateTime

此錯誤訊息的原因在於, 轉換過程會呼叫到 DateTime 的 Parse() , 進而將資料轉成 .Net datetime型態.
所以只要看一下 DateTime.Parse 方法 (String) , 確認一下要的是以下的幾種格式:

• 具有日期和時間部分的字串.
• 具有日期卻沒有時間部分的字串.
• 具有時間卻沒有日期部分的字串.
• 包含時區資訊並符合 ISO 8601 的字串.
  例如, 下列兩個字串中, 第一個字串是指定 Coordinated Universal Time (UTC), 第二個字串則是指定比 UTC 早七小時之時區中的時間:
  2008-11-01T19:35:00.0000000Z
• 字串, 包含 GMT 指示項並符合 RFC 1123 時間格式. 例如:
  Sat, 01 Nov 2008 19:35:00 GMT
• 字串, 包含日期和時間, 以及時區位移資訊. 例如:
  03/01/2009 05:42:00 -5:00

然後再參考一下 MySQL 的 DATE_FORMAT , 做一下 datetime的格式轉換就可以將日期匯入到 SQL Server .
以下是Sample Code: (注意: 如果自動遞增的值不想保留到目的地的表格, 可在下 SQL 句時不要查出該欄位)

• 透過 MySqlDataAdapter 將資料表放進 DataTable :
  ?

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

  //將日期轉成 "yyyy-MM-dd HH:mm:ssZ" string strSqlSelect = "SELECT tID, tName,DATE_FORMAT(tDateTime, '%Y-%m-%d %H:%i:%sZ') tDateTime FROM tblSource"; DataTable dtData = new DataTable(); using (MySqlDataAdapter adapter = new MySqlDataAdapter(strSqlSelect, ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString)) {   adapter.Fill(dtData); } //若要維持來源表格自動遞增的PK欄位值, 須加上 SqlBulkCopyOptions.KeepIdentity using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MsSqlConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepIdentity)) {   //設定目的地的表格名稱   bulkCopy.DestinationTableName = "[tblTarget]";   //設定來源與目的地的欄位對照   bulkCopy.ColumnMappings.Add("tID", "tID");   bulkCopy.ColumnMappings.Add("tName", "tName");   bulkCopy.ColumnMappings.Add("tDateTime", "tDateTime");   //將資料寫進目的地的表格   bulkCopy.WriteToServer(dtData);   bulkCopy.Close(); }

• 透過 MySqlDataReader : 
  ?

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

  //將日期轉成 "yyyy-MM-dd HH:mm:ssZ" string strSqlSelect = "SELECT tID, tName,DATE_FORMAT(tDateTime, '%Y-%m-%d %H:%i:%sZ') tDateTime FROM tblSource"; using (MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString)) {   using (MySqlCommand cmd = new MySqlCommand("", conn))   {     conn.Open();     //透過 MySqlCommand 產生 MySqlDataReader     MySqlDataReader reader = cmd.ExecuteReader();     //若要維持來源表格自動遞增的PK欄位值, 須加上 SqlBulkCopyOptions.KeepIdentity     using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MsSqlConnectionString"].ConnectionString, SqlBulkCopyOptions.KeepIdentity))     {       //設定目的地的表格名稱       bulkCopy.DestinationTableName = "[tblTarget]";       //設定來源與目的地的欄位對照       bulkCopy.ColumnMappings.Add("tID", "tID");       bulkCopy.ColumnMappings.Add("tName", "tName");       bulkCopy.ColumnMappings.Add("tDateTime", "tDateTime");       //將資料寫進目的地的表格       bulkCopy.WriteToServer(reader);       bulkCopy.Close();     }   }   conn.Close(); }

轉換的過程比較麻煩的是資料格式的轉換, 不過這大多可以透過 SQL 句調整.
整個匯入的過程可以發現, 所需要的資訊有:

• 資料庫的連線字串
• 資料來源的 SQL 句
• 匯入目的地的表格名稱
• 來源與目的地的的資料欄位名稱對照

基於以上幾點, 就可以把這些資料參數化成 XML , 例如 (僅供參考) :

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<?xml version="1.0" encoding="utf-8" ?> <TableList>   <table>     <mysql_select>       SELECT tID, tName,DATE_FORMAT(tDateTime, '%Y-%m-%d %H:%i:%sZ') tDateTime FROM tblSource     </mysql_select>     <ms_table>[tblTarget]</ms_table>     <column_mapping>       <column target="tID" source="tID"/>       <column target="tName" source="tName"/>       <column target="tDateTime" source="tDateTime"/>     </column_mapping>   </table> </TableList>