Task.Run in a for loop

Question

I have a for loop inside of which

First : I want to compute the SQL required to run

Second : Run the SQL asynchronously without waiting for them individually to finish in a loop

My code looks like:

for (
        int i = 0;
        i < gm.ListGroupMembershipUploadDetailsInput.GroupMembershipUploadInputList.Count;
        i++)
{
     // Compute
     SQL.Upload.UploadDetails.insertGroupMembershipRecords(
         gm.ListGroupMembershipUploadDetailsInput.GroupMembershipUploadInputList[i],max_seq_key++,max_unit_key++,
         out strSPQuery,
         out listParam);

     //Run the out SPQuery async
     Task.Run(() => rep.ExecuteStoredProcedureInputTypeAsync(strSPQuery, listParam));
}

The insertGroupMembershipRecords method in a separate DAL class looks like :

public static GroupMembershipUploadInput insertGroupMembershipRecords(GroupMembershipUploadInput gm, List<ChapterUploadFileDetailsHelper> ch, long max_seq_key, long max_unit_key, out string strSPQuery, out List<object> parameters)
{
    GroupMembershipUploadInput gmHelper = new GroupMembershipUploadInput();
    gmHelper = gm;
    int com_unit_key = -1;
    foreach(var item in com_unit_key_lst){
        if (item.nk_ecode == gm.nk_ecode)
            com_unit_key = item.unit_key;
    }

    int intNumberOfInputParameters = 42;
    List<string> listOutputParameters = new List<string> { "o_outputMessage" };
    strSPQuery = SPHelper.createSPQuery("dw_stuart_macs.strx_inst_cnst_grp_mbrshp", intNumberOfInputParameters, listOutputParameters);
    var ParamObjects = new List<object>();

    ParamObjects.Add(SPHelper.createTdParameter("i_seq_key", max_seq_key, "IN", TdType.BigInt, 10));
    ParamObjects.Add(SPHelper.createTdParameter("i_chpt_cd", "S" + gm.appl_src_cd.Substring(1), "IN", TdType.VarChar, 4));
    ParamObjects.Add(SPHelper.createTdParameter("i_nk_ecode", gm.nk_ecode, "IN", TdType.Char, 5)); 

    // rest of the method
   }

But in case of list Count of 2k which I tried,

It did not insert 2k records in DB but only 1.

Why this does not insert all the records the input list has ?

What am I missing ?

Answer 1

Task.Run in a for loop

Even though this is not the question, the title itself is what I'm going to address. For CPU bound operations you could use Parallel.For or Parallel.ForEach, but since we are IO bound (i.e.; database calls) we should rethink this approach.

The obvious answer here is to create a list of tasks that represent the asynchronous operations and then await them using the Task.WhenAll API like this:

public async Task InvokeAllTheSqlAsync()
{
    var list = gm.ListGroupMembershipUploadDetailsInput.GroupMembershipUploadInputList;

    var tasks = Enumerable.Range(0, list.Count).Select(i =>
    {
        var value = list[i];
        string strSPQuery;
        List<SqlParameter> listParam;
        SQL.Upload.UploadDetails.insertGroupMembershipRecords(
            value, 
            max_seq_key++,
            max_unit_key++,
            out strSPQuery,
            out listParam
        );

        return rep.ExecuteStoredProcedureInputTypeAsync(strSPQuery, listParam);
    });

    await Task.WhenAll(tasks);
}