[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 :

  //將日期轉成 "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 : 

  //將日期轉成 "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 , 例如 (僅供參考) :

<?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>