yilin 的程式日記: [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>

yilin 的程式日記

2010/08/06

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

沒有留言: