ADO.Net使用SqlBulkCopy

ADO.Net从Framework2.0开始就支持批量更新(SqlBulkCopy)，范例如下：

        /// <summary>
        /// 批量复制
        /// </summary>
        /// <param name="connectionString">目标表的数据库连接字符串</param>
        /// <param name="sourceTable">原表</param>
        /// <param name="destinationTable">目标表名</param>
        public static void ExecuteBulkCopy(string connectionString, DataTable sourceTable, string destinationTable)
        {
            using (SqlConnection sqlconn = new SqlConnection(connectionString))
            {
                sqlconn.Open();
                using (SqlBulkCopy sbc = new SqlBulkCopy(sqlconn))
                {
                    sbc.BatchSize = 1000;
                    sbc.BulkCopyTimeout = 180;

                    //将DataTable表名作为待导入库中的目标表名   
                    sbc.DestinationTableName = destinationTable;

                    //将数据集合和目标服务器库表中的字段对应   
                    for (int i = 0; i < sourceTable.Columns.Count; i++)
                    {
                        //列映射定义数据源中的列和目标表中的列之间的关系
                        sbc.ColumnMappings.Add(sourceTable.Columns[i].ColumnName, sourceTable.Columns[i].ColumnName);
                    }

                    sbc.WriteToServer(sourceTable);
                }
                sqlconn.Close();
            }
        }

        /// <summary>
        /// 批量复制
        /// </summary>
        /// <param name="trans">事务</param>
        /// <param name="sourceTable">数据源</param>
        /// <param name="destinationTable">目标表名</param>
        public static void ExecuteBulkCopy(SqlTransaction trans, DataTable sourceTable, string destinationTable)
        {
            using (SqlBulkCopy sbc = new SqlBulkCopy(trans.Connection, SqlBulkCopyOptions.KeepIdentity, trans))
            {
                sbc.BatchSize = 1000;
                sbc.BulkCopyTimeout = 180;

                //将DataTable表名作为待导入库中的目标表名   
                sbc.DestinationTableName = destinationTable;

                //将数据集合和目标服务器库表中的字段对应   
                for (int i = 0; i < sourceTable.Columns.Count; i++)
                {
                    //列映射定义数据源中的列和目标表中的列之间的关系
                    sbc.ColumnMappings.Add(sourceTable.Columns[i].ColumnName, sourceTable.Columns[i].ColumnName);
                }

                sbc.WriteToServer(sourceTable);
            }
        }

然后很多时候我们操作的是IEnumerable集合，这就需要一个IEnumerable到DataTable的接口。实现如下：

public static DataTable LinqToDataTable(IEnumerable list)
{
            DataTable table = new DataTable();
            bool schemaIsBuild = false;
            PropertyInfo[] props = null;
            foreach (object item in list)
            {
                if (!schemaIsBuild)
                {
                    props = item.GetType().GetProperties();
                    foreach (var pi in props)
                        table.Columns.Add(new DataColumn(pi.Name, pi.PropertyType));
                    schemaIsBuild = true;
                }
                var row = table.NewRow();
                foreach (var pi in props)
                    row[pi.Name] = pi.GetValue(item, null);
                table.Rows.Add(row);
            }
            table.AcceptChanges();
            return table;
}