【記錄】SqlBulkCopy 跨數(shù)據(jù)庫，表自定義導(dǎo)入

使用場(chǎng)景：

一個(gè)數(shù)據(jù)庫中的表數(shù)據(jù)，導(dǎo)入到另一個(gè)數(shù)據(jù)庫中的表中，這兩個(gè)表的表結(jié)構(gòu)不一樣，如果表結(jié)構(gòu)一樣的時(shí)候，導(dǎo)入非常簡單，直接讀取導(dǎo)入就行了，表結(jié)構(gòu)不一樣，就意味著需要加入一些判斷，SqlBulkCopy 提供了 ColumnMappings，但這只是列名的映射，而不像 AutoMapper 那樣可以自定義映射轉(zhuǎn)換，那如何判斷導(dǎo)入呢？答案就是在導(dǎo)入之前對(duì)原數(shù)據(jù)集進(jìn)行驗(yàn)證，得到和目標(biāo)表一致的表數(shù)據(jù)，我們可以使用 case when 進(jìn)行判斷。比如 table1 中有兩列：State1 和 State2，對(duì)應(yīng) table1 中的 State，值對(duì)應(yīng)關(guān)系為：

1. State1 0 && State2 0 對(duì)應(yīng) State 0
2. State1 1 && State2 0 對(duì)應(yīng) State 1
3. State1 0 && State2 1 對(duì)應(yīng) State 2
4. State1 1 && State2 1 對(duì)應(yīng) State 3

代碼示例：

        /// <summary>        /// 數(shù)據(jù)庫中的表復(fù)制到另一個(gè)數(shù)據(jù)庫中的表        /// </summary>        PRivate static void SqlBulkCopyMethod()        {            TimeSpan ts1 = new TimeSpan(DateTime.Now.Ticks);            try            {                SqlConnection connetionPub = new SqlConnection("Server=.; Database=DB1; User ID=sa;PassWord=sa; MultipleActiveResultSets=True;");                using (connetionPub)                {                    SqlCommand commandPub = connetionPub.CreateCommand();                    using (commandPub)                    {                        commandPub.CommandText = @"select                                                     *,                                                    (case when (State1 = 0 and State2=0)                                                    then 0                                                    when (State1 = 0 and State2=1)                                                    then 1                                                    when (State1 = 1 and State2=0)                                                    then 2                                                    when (State1 = 1 and State2=1)                                                    then 3                                                    else 4 end                                                    ) State                                                    from table1";                        commandPub.CommandType = System.Data.CommandType.Text;                        connetionPub.Open();                        SqlConnection connectionBulkCopy = new SqlConnection("Server=.; Database=DB2; User ID=sa;Password=sa; MultipleActiveResultSets=True;");                        using (connectionBulkCopy)                        {                            connectionBulkCopy.Open();                            SqlDataReader dataReader = commandPub.ExecuteReader();                            SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionBulkCopy);                            using (bulkCopy)                            {                                bulkCopy.BatchSize = 1000;                                bulkCopy.ColumnMappings.Add("id", "ID");                                bulkCopy.ColumnMappings.Add("name", "Name");                                bulkCopy.ColumnMappings.Add("State", "State");                                bulkCopy.DestinationTableName = "table2";                                bulkCopy.WriteToServer(dataReader);                            }                        }                    }                }                TimeSpan ts2 = new TimeSpan(DateTime.Now.Ticks);                TimeSpan ts3 = ts1.Subtract(ts2).Duration();                Console.WriteLine(string.Format("執(zhí)行時(shí)間：{0}秒", ts3.TotalSeconds.ToString()));                Console.ReadKey();            }            catch (Exception ex)            {                throw ex;            }        }