How to read query result by slices with SQLReader using .NET

Question

connection.Open();
var cmd = new SqlCommand("SELECT TOP 1000 [Id] ,[Name]  FROM [SomeBase]", connection); //Here
context.MaxID = 100;
int en = 5; //Maximum number of rows in the slice
int iter = 0; //Iterator for each single row in the slice
try
{
    SqlDataReader reader = cmd.ExecuteReader();
    if(reader.HasRows)
    {
        while (iter < en) //I need 1-5 rows in first iteration, 6-10 in second...
        {
            iter++;
            reader.Read();
            context.TextLog += String.Format("{0}\t{1}\n",
            reader.GetInt64(0), 
            reader.GetString(1));
        }
    }
    reader.Close();
}

I'm trying to get rows from the results by slices. I expect: first five and then exit from script. Next start will be with second five result (6-10) etc. How I can manage it, using MaxID for example or some kind of iterator.

Tell the database server you only want those parts with LIMIT/FETCH and OFFSET. Don't tell it you want 1000 results and just throw them away. — Sami Kuhmonen, Mar 02 '17 at 07:34
Possible duplicate of [What is the best way to paginate results in SQL Server](http://stackoverflow.com/questions/109232/what-is-the-best-way-to-paginate-results-in-sql-server) — bbsimonbb, Mar 02 '17 at 16:40

score 0 · Answer 1 · answered Mar 02 '17 at 07:38

You can't, Since the reader requires an open and available collection to get values from it. If you are going to execute the query again and again for each time will not be a better practice, where you can use DataTables to fulfill this requirements. for that you can use the following method:

// populate the DataTable using adapter
 SqlDataAdapter adapter = new SqlDataAdapter();
 adapter.SelectCommand = new SqlCommand("SELECT TOP 1000 [Id] ,[Name]  FROM [SomeBase]", connection);
 adapter.Fill(dataset);
// Call the method whenever you need Slice
// Which will give you a List<DataRow>
public List<DataRow> GetMySlice(DataTable inputDataTable, int minSlice, int maxSlice)
{
    return inputDataTable.AsEnumerable()
                         .Skip(minSlice)
                         .Take(maxSlice)
                         .ToList();
}

If you need the result as a DataTable means you can make use of .CopyToDataTable() instead for .ToList() in this case, return type of the method would be DataTable instead for List<DataRow>

score 0 · Answer 2 · answered Mar 02 '17 at 08:29

if you still need pagination and use MS SQL-Server you can add to your query SELECT clause , ROW_NUMBER() OVER (ORDER BY <ColumnName>) and wrap it with SELECT *. Than use a WHERE clause with parameters.

SELECT * FROM 
(
    SELECT [Id] ,[Name], ROW_NUMBER() OVER (ORDER BY <ColumnName>) AS RNUM FROM [SomeBase]
) AS T
WHERE (T.RNUM BETWEEN @from AND @to)

How to read query result by slices with SQLReader using .NET

2 Answers2