将数据表拆分为多个固定大小的表

我曾经做过一个小的扩展方法：

public static IEnumerable<IEnumerable<T>> ToChunks<T>(this IEnumerable<T> enumerable,
                                                      int chunkSize)
{
    int itemsReturned = 0;
    var list = enumerable.ToList(); // Prevent multiple execution of IEnumerable.
    int count = list.Count;
    while (itemsReturned < count)
    {
        int currentChunkSize = Math.Min(chunkSize, count - itemsReturned);
        yield return list.GetRange(itemsReturned, currentChunkSize);
        itemsReturned += currentChunkSize;
    }
}

其将任何CCD_ 1切割成具有指定块大小的块。

有了这个，你可以简单地做：

var tables = originalTable.AsEnumerable().ToChunks(225)
                          .Select(rows => rows.CopyToDataTable())

这比简单的foreach执行得更好的原因是list.GetRange是从列表中获取一系列行的非常有效的方法。我很想知道你会发现什么。

private static List<DataTable> SplitTable(DataTable originalTable, int batchSize)
    {
        List<DataTable> tables = new List<DataTable>();
        int i = 0;
        int j = 1;
        DataTable newDt = originalTable.Clone();
        newDt.TableName = "Table_" + j;
        newDt.Clear();
        foreach (DataRow row in originalTable.Rows)
        {
            DataRow newRow = newDt.NewRow();
            newRow.ItemArray = row.ItemArray;
            newDt.Rows.Add(newRow);
            i++;
            if (i == batchSize)
            {
                tables.Add(newDt);
                j++;
                newDt = originalTable.Clone();
                newDt.TableName = "Table_" + j;
                newDt.Clear();
                i = 0;
            }

        }
        if (newDt.Rows.Count > 0)
        {
            tables.Add(newDt);
            j++;
            newDt = originalTable.Clone();
            newDt.TableName = "Table_" + j;
            newDt.Clear();
        }
        return tables;
    }

 foreach (var dt1 in SplitTable(table1, 2))
        {
            DataTable dt = dt1;
        }

为懒人做这件事的另一种方法：）

private static DataTable GetDataTable<T>(IEnumerable<T> data, int skip, int take)
        {
            var properties = TypeDescriptor.GetProperties(typeof(T));
            var dataTable = new DataTable();
            foreach (PropertyDescriptor prop in properties)
                dataTable
                    .Columns
                    .Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType)
                                    ?? prop.PropertyType);
            foreach (var item in data.Skip(skip).Take(take))
            {
                var row = dataTable.NewRow();
                foreach (PropertyDescriptor prop in properties)
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
                dataTable.Rows.Add(row);
            }
            return dataTable;
        }

客户会这样称呼它：

 var nthDataTable = GetDataTable(model, skip: n, take: m);

也许我来不及了，但我找到的最好方法是，将我的数据表设置为数据行列表

    public static List<DataTable> Testes(DataTable yourDataTableToSplit, int numberOfDatatablesYouWant) 
    {
        List<DataRow> rows = yourDataTableToSplit.AsEnumerable().ToList();
        List<DataTable> result = new List<DataTable>();
        int rowsToTake = yourDataTableToSplit.Rows.Count / numberOfDatatablesYouWant;
        while (rows.Count > 0)
        {
            result.Add(rows.Take(rowsToTake).CopyToDataTable());
            rows = rows.Skip(rowsToTake).ToList();
        }
        return result;
    }

我用它在sqlserver上大批量插入数据，如下所示：

        public static List<string> DataBulkInsert(DataTable dtDataInsert, SqlConnection conn, string tableName)
        {
            List<string> errors = new List<string>();
            int batchSize = dtDataInsert.Rows.Count;
            bool lastEntry= false;
            List<DataRow> bulkData = dtDataInsert.AsEnumerable().ToList();
            while (bulkData.Count() > 0 && batchSize > 0)
            {
                bulkData = SQLBulkInsert(bulkData, conn, dtDataInsert.TableName, batchSize , ref errors);
                if (batchSize % 2 == 0)
                    batchSize /= 2;
                else if (batchSize > 1)
                    batchSize = (batchSize + 1) / 2;
                else if (batchSize == 1 && !lastEntry)
                    lastEntry= true;
                else
                    batchSize = 0;
            }
            return errors.GroupBy(x => x).Select(x => x.FirstOrDefault()).ToList();
        }
        public static List<DataRow> SQLBulkInsert(List<DataRow> listToInsert, SqlConnection conn, string tableName, int batchSize, ref List<string> erros)
        {
            List<DataRow> dataError = new List<DataRow>();
            List<DataRow> dataToTry = listToInsert.Take(batchSize).ToList();
            while (dataToTry.Count() > 0)
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.BulkCopyTimeout = 120;
                    bulkCopy.DestinationTableName = tableName;
                    try
                    {
                        bulkCopy.WriteToServer(dataToTry.CopyToDataTable());
                    }
                    catch (Exception ex)
                    {
                        errors.Add(ex.Message);
                        dataError.AddRange(dataToTry);
                    }
                    listToInsert = listToInsert.Skip(batchSize).ToList();
                    dataToTry = listToInsert.Take(batchSize).ToList();
                }
            }
            return dataError;
        }

这里给出的解决方案对我不起作用，如果最后一组记录小于块数据表的所需大小，那么它将忽略这些记录并导致它们丢失。。例如，如果有5条记录，块表大小为2，那么它将只创建2个数据表，忽略最后一条记录。

以下是在所有场景中对我都有效的更正代码。

在VB.NET上工作的用户可能无法多次使用LINQ，因此如果您需要相同的VB.NET代码，请查看此处将大型数据表拆分为c#和VB.NET 中的块

 private static List<DataTable> SplitTable(DataTable mainTable, int batchSize)
{
    List<DataTable> tables = new List<DataTable>();
    int i = 0;
    int j = 1;
    int rowCount = 0;
    DataTable tempDt = mainTable.Clone();
    tempDt.TableName = "ChunkDataTable" + j.ToString();
    tempDt.Clear();
    foreach (DataRow row in mainTable.Rows) {
        rowCount += 1;
        DataRow newRow = tempDt.NewRow();
        newRow.ItemArray = row.ItemArray;
        tempDt.Rows.Add(newRow);
        i += 1;
        if (i == batchSize | rowCount == mainTable.Rows.Count) {
            tables.Add(tempDt);
            j += 1;
            tempDt = mainTable.Clone();
            tempDt.TableName = "ChunkDataTable" + j.ToString();
            tempDt.Clear();
            i = 0;
        }
    }
    return tables;
}