ADO.NET下的SqlBulkCopy類執行資料庫間批量複製操作

 

很多時侯，我們需要在資料庫間複製大量資料，如SQLSERVER提供的BCP.EXE命令列工具，如ADO.NET下

 

SqlBulkCopy類提供了一個高效能的方法WriteToServer來複製資料。該方法可以接受一個DataRow對像資料、一

 

個實現IDbDataReader介面的對像和一個DataTable，或者接受一個DataTable對像和一個DataRowState枚舉值，

 

以實現從大多數位置中擷取資料。

 

        

 

        /// <summary>
        /// 在目標資料庫與來源資料庫之間批量複製資料
        /// </summary>
        /// <param name="SourceConnectionstr">來源資料庫連接字串</param>
        /// <param name="TargetConnectionstr">目標資料庫連接字串</param>
        /// <param name="SourceSQLstr">查詢資料SQL語句，必須包含主鍵</param>
        /// <param name="TargetTableName">更新目標資料庫中的表名</param>
        /// <returns>Boolean</returns>
       

        public static Boolean dbtodb(string SourceConnectionstr,string TargetConnectionstr,

                                     string SourceSQLstr,string TargetTableName)
        {
            System.Data.SqlClient.SqlConnection
SourceConn= new 

                                                           
System.Data.SqlClient.SqlConnection();
            System.Data.SqlClient.SqlConnection
TargetConn = new                  

                                                           
System.Data.SqlClient.SqlConnection();
            try
            {
                SourceConn.ConnectionString = SourceConnectionstr;
                TargetConn.ConnectionString = TargetConnectionstr;
                System.Data.SqlClient.SqlCommand cmd = SourceConn.CreateCommand();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SourceSQLstr;

                SourceConn.Open();
                TargetConn.Open();

                using (System.Data.SqlClient.SqlDataReader rdr = cmd.ExecuteReader())
                {
                    if (!rdr.HasRows)
                    {
                        return false;
                    }
                    using (System.Data.SqlClient.SqlBulkCopy bc = new

                            System.Data.SqlClient.SqlBulkCopy(TargetConn))
                    {
                        //逾時之前操作所允許完成的秒數
                        bc.BulkCopyTimeout = 360;
                       

                        //目標伺服器上的表名
                        bc.DestinationTableName = TargetTableName;
                        bc.WriteToServer(rdr);                       
                    }
                }
            }
            catch
            {
                throw;
            }
            finally
            {

                //關閉串連
                SourceConn.Close();
                TargetConn.Close();
            }

            return true;

}

 

           如果想以最少資源擷取最佳效能，就考濾使用IDbDateReader參數