Select 500 rows at a time in SqlCommand in code

Question

I have the following SqlCommand which is returning over 100000 rows (potentially more in future). I would like to break down the creation of the list (listToUse) to 500 entries at a time. I understand that we can do this in SQL quite easily however I am calling the stored procedure from Code so would like to aovid calling the stored procedure multiple times.

I would like to select rows

0 to 500 call method GenereateUnpackConfigList(cmd)
501 to 1000 call method GenereateUnpackConfigList(cmd) and so on

listToUse is a simple collection of a class with properties.

Any help is greatly appreciated!

Code:

 SqlCommand cmd = new SqlCommand();
 cmd.Connection = conn;

 cmd.CommandText = "SP_RPT_V5_LS_EXP_GetOutputsForUnpackingByBatchID";
 cmd.CommandType = CommandType.StoredProcedure;

 SqlParameter param = new SqlParameter("@BatchID", BatchID);
 param.Direction = ParameterDirection.Input;
 param.DbType = DbType.String;
 cmd.Parameters.Add(param);

 listToUse = GenereateUnpackConfigList(cmd);

You know which columns are being returned from the command? Based on that you can create a class. Use cmd.ExecuteReader() method to get the SQLDataReader populated with the data. Loop thru the reader and populate an object of class each time and add it into the list. Share the information about the data being returned from the stored proc. I will be able to provide detailed answer. — Chetan, Feb 17 '17 at 16:12
In SQL Server **2012** and newer, you can use the [OFFSET-FETCH](https://www.mssqltips.com/sqlservertip/2362/overview-of-offset-and-fetch-feature-of-sql-server-2012/) features — marc_s, Feb 17 '17 at 16:24
Stored procedure should have parameter to indicate the start location and optional number of items to return. With SQL server you MUST use and OrderBy in your SQL statement to make sure you get items always in same order. The SQL server is multi-processing when performing a read so the different processes may return data in different order each time you run the query. — jdweng, Feb 17 '17 at 17:02

score 1 · Answer 1 · edited Feb 17 '17 at 16:22

1

Yes you can do it using the stored procedure. Just use

Select Top 500 * 
from <Your-table-name> 
order by Id desc

and store the Id of the last record retrieve so that you can pass it on next function call and add condition

Select Top 500 * 
from <Your-table-name> 
where Id < last-record-id 
order by Id desc

edited Feb 17 '17 at 16:22

marc_s

732,580
175
1,330
1,459

answered Feb 17 '17 at 16:14

Saurabh Srivastava

1,093
14
27

Thanks for looking into this. Yes even better you can use a limit in SQL. However I need to do it code as hence the SQLCommand. SELECT [3,2] * FROM MyTable ORDER BY MyColumn /* hypothetical syntax */ queries 2 rows starting from 3d row, i.e 3d and 4th rows are returned. – Abe Feb 17 '17 at 16:20
Yes you are correct.You can also use `Limit` but that is only supported in `MySql`. For `SQL` `Top 500` is equivalent. – Saurabh Srivastava Feb 17 '17 at 16:20
Thank you. Yes I have gone through a few links for this for e.g., http://stackoverflow.com/questions/758186/how-to-get-n-rows-starting-from-row-m-from-sorted-table-in-t-sql However as i mentioned i need to get it done in the code. – Abe Feb 17 '17 at 16:22
1

**NO**, you **CANNOT** use `LIMIT` since this is **not supported** in SQL Server .... that's a MySQL specific, non-standard extension – marc_s Feb 17 '17 at 16:23

score 0 · Answer 2 · answered Feb 24 '17 at 18:25

To control this via the client code only, an extension method can be implemented to page through your result set. It could look something like this:

public static class Extensions
{        
    public static IList<T> PagedList<T>(this IList<T> source, int pageIndex, int pageSize)
    {
        var list = new List<T>();
        list.AddRange(source.Skip(pageIndex * pageSize).Take(pageSize).ToList());
        return list;
    }
}

An example to call the extension function:

public void test(List<MyObject> listToUse, int pageSize = 500)
{
    var firstBatch = listToUse.PagedList(1, pageSize);
    var secondBatch = listToUse.PagedList(2, pageSize);
}

Select 500 rows at a time in SqlCommand in code

2 Answers2