批量加载数据到SQL数据表

/// <summary>
        /// 使用事务，将 DataTable 中的数据批量加载 SQL Server 表
        /// </summary>
        /// <param name="connStr">数据库连接字符串</param>
        /// <param name="batchSize">分批次拷贝的行数</param>
        /// <param name="destinationTableName">目标表名</param>
        /// <param name="sqlBulkCopyColumnMappings">目标表字段的映射</param>
        /// <param name="dt">包含数据的 DataTable 对象</param>
        /// <returns>布尔值</returns>
        public static bool ExecuteSqlBulkCopy(string connStr, int batchSize, string destinationTableName, SqlBulkCopyColumnMapping[] sqlBulkCopyColumnMappings, DataTable dt)
        {
            bool result = false;

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                //定义SQL事务并嵌入到批量拷贝的工作中
                SqlTransaction _SqlTransaction = conn.BeginTransaction();

                //定义SqlBulkCopy:SqlConn为SqlConnection,SqlBulkCopyOptions枚举类型,objSqlTran为调用的事务
                using (SqlBulkCopy _SqlBulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, _SqlTransaction))
                {
                    //分批次拷贝的行数
                    _SqlBulkCopy.BatchSize = batchSize;
                    //目标表名
                    _SqlBulkCopy.DestinationTableName = destinationTableName;

                    //源表对目标表字段的映射,因为默认是按顺序以及名字匹配,所以这步很重要
                    foreach (SqlBulkCopyColumnMapping _SqlBulkCopyColumnMapping in sqlBulkCopyColumnMappings)
                    {
                        _SqlBulkCopy.ColumnMappings.Add(_SqlBulkCopyColumnMapping.SourceColumn,
                                                        _SqlBulkCopyColumnMapping.DestinationColumn);
                    }

                    try
                    {
                        //将DataTabel类型的objDT作为源拷贝到目标表
                        _SqlBulkCopy.WriteToServer(dt);
                        _SqlTransaction.Commit();
                        result = true;
                    }
                    catch (Exception ex)
                    {
                        _SqlTransaction.Rollback();
                        throw ex;
                    }
                }
            }

            return result;
        }