For loop on Data table on condition

Question

I have a result dataset,

Dataset dsResult = new Dataset();

dsResult.Tables[0].Rows.Count = 17

Now i want to loop through data table for first 5 rows and create a dsResult.Tables[1]

and then next 5 more rows to dsResult.Tables[2]

and next 5 more to dsResult.Tables[3]

then last two rows to dsResult.Tables[4]

from below code i am getting number of tables required

decimal remainder = Decimal.Divide(dsResult.Tables[0].Rows.Count, 5); var numberOfRequests = Math.Ceiling((decimal)remainder);

Or can we do it in SQL

How to genaralize this logic.

Please guide me here.

Answer 1

using Linq this is very simple

var firstFive = dsResult.Tables[0].AsEnumerable().Take(5);
var secondFive = dsResult.Tables[0].AsEnumerable().Skip(5).Take(5);

and so on.
Do not loop, let Linq do it for you.

If later you will need to convert results to a datatables MSDN has very good example on how to Creating a DataTable From a Query (LINQ to DataSet)

Answer 2

Maybe helps, but I don't recommend use this solution in real project.

// size of chunk
int chunkSize = 5;

// dataset and table variables
DataSet ds = GetDataSet();  // get your DataSet from somewhere
DataTable source = ds.Tables[0], dest;

// total rows
int count = ds.Tables[0].Rows.Count;
int tableCount = (count + 1) / chunkSize;

// create tables (copy structure)
for (int i = 0; i < tableCount; i++)
{
    dest = source.Clone();
    dest.TableName = source.TableName + "_" + i.ToString();
    ds.Tables.Add(dest);
}

// fill tables
int index = 0;
foreach (DataRow row in source.Rows)
{
    dest = ds.Tables[1 + index++ / chunkSize];

    // copy row (via copying item arrays)
    DataRow rowCopy = dest.NewRow();
    rowCopy.ItemArray = row.ItemArray;
    dest.Rows.Add(rowCopy);
}

Answer 3

Another solution with ChunkReader<T> (class that slices list to parts with fixed length)

ChunkReader

using System;
using System.Collections.Generic;
using System.Linq;

namespace ServiceCore.Helpers
{
    /// <summary>
    /// чанкер
    /// </summary>
    public class ChunkReader<T>
    {
        #region Declarations

        private List<T> _data;                  // данные
        private int _pos;                       // позиция

        #endregion
        #region Properties

        /// <summary>
        /// число записей
        /// </summary>
        public int Count { get { return _data.Count; } }

        /// <summary>
        /// размер чанка
        /// </summary>
        public int ChunkSize { get; set; }

        /// <summary>
        /// число чанков
        /// </summary>
        public int ChunksCount
        {
            get
            {
                int count = _data.Count;
                return count == 0 ? 0 : (int)Math.Ceiling((double)count / (double)ChunkSize);
            }
        }

        #endregion

        #region Constructors

        /// <summary>
        /// конструктор
        /// </summary>
        public ChunkReader()
        {
            ChunkSize = 20;
        }

        /// <summary>
        /// конструктор
        /// </summary>
        /// <param name="data"> данные </param>
        public ChunkReader(List<T> data)
            : this()
        {
            Init(data);
        }

        /// <summary>
        /// конструктор
        /// </summary>
        /// <param name="data"> данные </param>
        /// <param name="chunkSize"> размер чанка </param>
        public ChunkReader(List<T> data, int chunkSize)
            : this()
        {
            ChunkSize = chunkSize;
            Init(data);
        }

        #endregion

        #region Private methods
        #endregion
        #region Protected methods
        #endregion
        #region Public methods

        /// <summary>
        /// инициализация
        /// </summary>
        /// <param name="data"> данные </param>
        public void Init(List<T> data)
        {
            _data = data;
            _pos = 0;
        }

        /// <summary>
        /// сбросить текущую позицию
        /// </summary>
        public void Reset()
        {
            _pos = 0;
        }

        /// <summary>
        /// прочитать очередной чанк
        /// </summary>
        /// <param name="chunk"> чанк </param>
        /// <returns></returns>
        public bool Read(out List<T> chunk)
        {
            int count = _data.Count;
            if (_pos >= count)
            {
                chunk = null;
                return false;
            }
            else
            {
                chunk = new List<T>();
                int last = _pos + ChunkSize;
                for (int i = _pos; i < count && i < last; i++, _pos++)
                {
                    chunk.Add(_data[_pos]);
                }
                return true;
            }
        }

        #endregion
    }
}

Usage:

// create ChunkReader based on DataTable
var chunker = new ServiceCore.Helpers.ChunkReader<DataRow>(ds.Tables[0].Select().ToList(), 5); // 5 - size of chunk

List<DataRow> chunk;
while (chunker.Read(out chunk))
{
    // do somethind with chunk of data rows
}

Answer 4

With LINQ you can acheive it in the following way,

    int rowCountPerTable = 5; //Number of tables would be based on this
    int currPos = 0;
    DataTable dt = ds.Tables[0];
    DataTable tempDt = dt.Clone();
    dt.Rows.OfType<DataRow>().ToList().ForEach(x =>
    {
        currPos++;
        tempDt.ImportRow(x);
        if (currPos == rowCountPerTable)
        {
            currPos = 0;
            tempDt.TableName = "Table" + (ds.Tables.Count + 1);
            ds.Tables.Add(tempDt);
            tempDt = dt.Clone();
        }
        else if ((dt.Rows.Count - 1) == dt.Rows.IndexOf(x))
        {
            tempDt.TableName = "Table" + (ds.Tables.Count + 1);
            ds.Tables.Add(tempDt);
        }
    });

Hope this helps...