Skip to content

Instantly share code, notes, and snippets.

@devmnj
Created January 22, 2024 17:48
Show Gist options
  • Save devmnj/b4a08bdd865d9d163115a3023479cf50 to your computer and use it in GitHub Desktop.
Save devmnj/b4a08bdd865d9d163115a3023479cf50 to your computer and use it in GitHub Desktop.
Batchwise data fetching in Dapper-MYSQL, using Background Task in C# - Background worker.
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());
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment