SqlBulkCopy批量数据插入

 using (SqlConnection con = new SqlConnection(connstring)) 
            { 
                con.Open();
                //启动一个事务。
                SqlTransaction myTran = con.BeginTransaction();
                //为事务创建一个命令，注意我们执行双条命令，第一次执行当然成功。我们再执行一次，失败。
                //第三次我们改其中一个命令，另一个不改，这时候事务会报错，这就是事务机制。
                SqlCommand myCom = new SqlCommand();
                try
                {
                    DataTable dt= NCL.Data.ModelConvertHelper<Teacher>.ToDataTable(hspu);
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con,SqlBulkCopyOptions.Default, myTran))
                    {
                        //一次性插入的数据条数
                        bulkCopy.BatchSize = 20;
                        //插入的表名
                        bulkCopy.DestinationTableName = "[dbo].[HomeSchoolPublishUser]";
                        bulkCopy.WriteToServer(dt);
                        
                    }
                    myTran.Commit();
                    issuccess = 1;
                }
                catch (Exception Ex)
                {
                    myTran.Rollback();
               
                }
            }

SqlBulkCopy还可以从SqlDataReader中获取数据：

using (SqlConnection sourceConnection =
                   new SqlConnection(connectionString))
        {
            sourceConnection.Open();
            // Get data from the source table as a SqlDataReader.
            SqlCommand commandSourceData = new SqlCommand(
                "SELECT ProductID, Name, " +
                "ProductNumber " +
                "FROM Production.Product;", sourceConnection);
            SqlDataReader reader =
                commandSourceData.ExecuteReader();

            using (SqlConnection destinationConnection =
                       new SqlConnection(connectionString))
            {
                destinationConnection.Open();
                using (SqlBulkCopy bulkCopy =
                           new SqlBulkCopy(destinationConnection))
                {
                    bulkCopy.DestinationTableName =
                        "dbo.BulkCopyDemoMatchingColumns";

                    try
                    {
                        bulkCopy.WriteToServer(reader);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
            }
        }