Splitting a list of numbers into multiple lists

I need split the list I get from the select into multiple list so I can use Parallel.For() to generate the next query in different threads.

• I create the main list

• Create the sub list

• Keep filling sub list until reach max capacity.

• Add sublist to main list

• Create new sub list

• When finish the loop save the last sublist

This works but I bet there is an easy / clean way to do this, I can change the db if needed.

List> result = new List>();
using (var conn = new NpgsqlConnection(strConnection))
{
    conn.Open();
    using (var cmd = new NpgsqlCommand())
    {
        cmd.Connection = conn;
        cmd.CommandText = "SELECT route_source_id " +                                      
                          "FROM route_sources " +
                          "WHERE has_route IS NULL " +          
                          "LIMIT 1000; ";

        using (var reader = cmd.ExecuteReader())
        {
            const long list_size = 100;                

            long row_id = 0;
            List route_list = new List();

            while (reader.Read())
            {                                
               if (row_id++ > list_size) // Add the sublist to the result
               {
                   result.Add(route_list); 
                   row_id = 0;
               }

               if (row_id == 0)  // Create new sublist
               {
                  route_list = new List();
               }

               if (!reader.IsDBNull(0)) // fill the sublist
               {
                    route_list.Add(reader.GetInt64(0));                                
               }
            }

            if (route_list.Count > 0 ) // add the last sublist
            {
               result.Add(route_list);
            }
        }
   }
 }
 return result;

EDIT: aditional part of the process.

I have ticker to check the db for pending id to be process

```
// read 1000 rows split into 10 list, using above function
List> route_pendin

Pretty minor but you don't need the if (row_id == 0)

if (row_id++ > list_size) // Add the sublist to the result
{
    result.Add(route_list); 
    row_id = 0;
    route_list = new List();
}

Also it seem like you should also test for

if (route_list.Count > 0 )

Why not just put where route_source_id is not null in the query?

Connection, Command, and Reader behave the same with many DB so this is pretty portable.

I think you can use

conn.CreateCommand()

Long is not required for row_id or list_size

Why bump row_id++ if there is no value?

In summary

using (var reader = cmd.ExecuteReader())
    {
        const int list_size = 100;                
        List route_list = new List(list_size);

        while (reader.Read())
        {                                
           if (route_list.Count >= list_size) // Add the sublist to the result
           {
               result.Add(route_list); 
               route_list = new List(list_size);
           }
           route_list.Add(reader.GetInt64(0));
        }

        if (route_list.Count > 0 ) // add the last sublist
        {
           result.Add(route_list);
        }
    }

StackExchange Code Review Q#155253, answer score: 3