Batchwise data fetching in Dapper-MYSQL, using Background Task in C# - Background worker.

backgrowndwkr.cs

 static class BackgroundDataFetcher
 {
     private static int batchSize = 2;
     public static BindingList<LedgerModel> ledgers = new BindingList<LedgerModel>();
     
     public static async Task FetchAsync(int ledId = 0)
     {
         try
         {
             
             using (var connection = Connection.OpenMySQLConnection())
             {
                 while (true)
                 {
                     var last_fetched_id = ledgers.OrderBy((l) => l.Id).LastOrDefault()?.Id;
                     var parameters = last_fetched_id != null ? " >" + last_fetched_id : " > 0";
                     
                     // Refetching for particular record.
                     if (ledId > 0)
                     {
                         var   idx = ledgers.IndexOf(ledgers.Where(l=>l.Id==ledId).FirstOrDefault());
                         ledgers.RemoveAt(idx);
                         parameters = "=" + ledId;
                     }
                     //The query using parameter to filter the table. Batch size is applied using the 'Limit'
                     var query_prvlg_view = "select prac.acid as id,(select created_at from " + Tables.LEDGERS + " where id=prac.acid limit 1) as Created_At ,(select modified_on from " + Tables.LEDGERS + " where id=prac.acid limit 1) as ModifiedOn, (select name from " + Tables.LEDGERS + " where id=prac.acid limit 1) as `name`,(select (select name from " + Tables.GROUPS + " where id=ll.groupid)  from " + Tables.LEDGERS + " ll where id=prac.acid limit 1) as  `Group`   from " + Tables.USERS + " u  inner join " + Tables.USER_ROLES + " ur on u.id=ur.userid inner join " + Tables.ROLE_PRIVILAGES + " rpset on ur.id = rpset.roleid  inner join " + Tables.PREVILAGES + " pr on rpset.previd=pr.id inner join   " + Tables.PRIVILAGE_ACCOUNTS + " prac on pr.id=prac.previd where pr.name='" + Privilege.PRIV_DEF_VIEW + "' and u.id=" + Globals.CurrentSession.UID + " and prac.acid" + parameters + " order by prac.acid  limit " + batchSize;

                     var result = connection.QueryAsync<LedgerModel>(query_prvlg_view).Result.ToList();
                     if (!result.Any())
                     {
                         Console.WriteLine("Nothing to Fetch  ------------ :(");
                         //Thread.Sleep(10000);   can apply some delay, if needed                         
                         break;
                     }
                     
                     await Task.Run(() => ProcessBatchData(result, ledId)); // Process batch in background
                     if (ledId != 0) { break; }
                 }
             }
         }
         catch (Exception)
         {

             throw;
         }
     }

     private static void ProcessBatchData(List<LedgerModel> data, int ledId)
     {

         try
         {
             foreach (var item in data)
             {                 
                 switch (item.GetType().Name)
                 {
                     case "LedgerModel":
                         Globals.ledgers.Add(item);
                         break;
                 }
                 Console.WriteLine("Data Fetched #:" + item.Id);
                
             }
         }
         catch (Exception)
         {

             throw;
         }

        
     }
 }

 // Usage:
 // Task.Run(new BackgroundDataFetcher().FetchDataAsync());