/// <summary>

        /// 数据库中的表复制到另一个数据库中的表

        /// </summary>

        private static void SqlBulkCopyMethod(string connectionStr1, string connectionStr2)

        {

            try

            {

                using (SqlConnection connetionPub = new SqlConnection(connectionStr1))

                {

                    SqlCommand commandPub = connetionPub.CreateCommand();

                    using (commandPub)

                    {

                        commandPub.CommandText = "select top 100 * from Person";

                        commandPub.CommandType = System.Data.CommandType.Text;

                        connetionPub.Open();

                        SqlConnection connectionBulkCopy = new SqlConnection(connectionStr2);

                        using (connectionBulkCopy)

                        {

                            connectionBulkCopy.Open();

                            SqlDataReader dataReader = commandPub.ExecuteReader();

                            SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionBulkCopy);

                            using (bulkCopy)

                            {

                                bulkCopy.BulkCopyTimeout = ;

                                bulkCopy.BatchSize = ;//BatchSize的意思就是同每一批次中的行数，在每一批次结束时，就将该批次中的行发送到数据库

                                bulkCopy.NotifyAfter = ;

                                bulkCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulkCopy_SqlRowsCopied);//bulkCopy对象提供了一个SqlRowCopied事件，在每次处理完NotifyAfter属性指定的行数时发生

                                bulkCopy.DestinationTableName = "Person";

                                bulkCopy.WriteToServer(dataReader);

                            }

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                throw ex;

            }

        }

        public static void bulkCopy_SqlRowsCopied(object obj, SqlRowsCopiedEventArgs e)

        {

            //执行事件处理方法

        }

        /// <summary>

        /// 将表中资料批量插入到数据库

        /// </summary>

        /// <param name="connectionString"></param>

        /// <param name="TableName"></param>

        /// <param name="dt"></param>

        private void SqlBulkCopyByDatatable(string connectionString, DataTable dt)

        {

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))

                {

                    try

                    {

                        sqlbulkcopy.DestinationTableName = "Person";

                        for (int i = ; i < dt.Columns.Count; i++)

                        {

                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);

                        }

                        sqlbulkcopy.WriteToServer(dt);

                    }

                    catch (System.Exception ex)

                    {

                        throw ex;

                    }

                }

            }

        }

        /// <summary>

        /// 不同表之间复制数据

        /// </summary>

        private static void PerformBulkCopyDifferentSchema(string connectionString)

        {

            var stopwatch = new Stopwatch();

            DataTable sourceData = new DataTable();

            // 源

            stopwatch.Start();

            using (SqlConnection sourceConnection = new SqlConnection(connectionString))

            {

                SqlCommand myCommand = new SqlCommand("SELECT * FROM Person", sourceConnection);

                sourceConnection.Open();

                SqlDataReader reader = myCommand.ExecuteReader();

                // 目的

                using (SqlConnection destinationConnection = new SqlConnection(connectionString))

                {

                    // 打开连接

                    destinationConnection.Open();

                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))

                    {

                        bulkCopy.ColumnMappings.Add("PersonId", "NEW_PersonId");

                        bulkCopy.ColumnMappings.Add("Name", "NEW_Name");

                        bulkCopy.ColumnMappings.Add("Age", "NEW_Age");

                        bulkCopy.DestinationTableName = "New_Person";

                        bulkCopy.WriteToServer(reader);

                    }

                }

                Console.WriteLine(stopwatch.ElapsedMilliseconds);

                reader.Close();

            }

        }

        /// <summary>

        /// 使用XML作为数据源

        /// </summary>

        private static void PerformBulkCopyXMLDataSource(string connectionString)

        {

            DataSet ds = new DataSet();

            DataTable sourceData = new DataTable();

            ds.ReadXml(@"E:\Study\SQL\MSSQL\MSSQL_SqlBulkCopy\MSSQL_SqlBulkCopy\PersonFile.xml");

            sourceData = ds.Tables[];

            // 目的

            using (SqlConnection destinationConnection = new SqlConnection(connectionString))

            {

                // 打开连接

                destinationConnection.Open();

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionString))

                {

                    // 列映射

                    //bulkCopy.ColumnMappings.Add("PersonId", "NEW_PersonId");

                    //bulkCopy.ColumnMappings.Add("Name", "NEW_Name");

                    //bulkCopy.ColumnMappings.Add("Age", "NEW_Age");

                    bulkCopy.DestinationTableName = "New_Person";

                    bulkCopy.WriteToServer(sourceData);

                }

            }

        }

        /// <summary>

        /// SqlDataAdapter  Update

        /// </summary>

        private static void PerformSqlDataAdapterUpdate(string connectionString)

        {

            using (SqlConnection conn = new SqlConnection(connectionString))

            {

                DataSet ds = new DataSet();

                DataSet ds_new = new DataSet();

                string SQLstr = "Select top 100 * from Person";

                string SQLstr_new = "Select top 0  * from New_Person";

                conn.Open();

                SqlCommand comm = new SqlCommand(SQLstr_new, conn);

                SqlDataAdapter sad = new SqlDataAdapter(comm);

                SqlCommandBuilder scb = new SqlCommandBuilder(sad);

                sad.Fill(ds_new);

                SqlDataAdapter sda = new SqlDataAdapter(new SqlCommand(SQLstr, conn));

                sda.Fill(ds);

                for (int i = ; i < ds.Tables[].Rows.Count; i++)

                {

                    DataRow NewRow = ds_new.Tables[].NewRow();

                    NewRow[] = ds.Tables[].Rows[i][];

                    NewRow[] = ds.Tables[].Rows[i][];

                    NewRow[] = ds.Tables[].Rows[i][];

                    ds_new.Tables[].Rows.Add(NewRow);

                }

                // ds_new.AcceptChanges();

                sad.Update(ds_new.GetChanges());

            }

        }