Dapper Dynamic Parameter wirth optional return value asp.net mvc

Question

Hello I have a common function which looks like below,

public async Task<SPResponse> ExecuteAsync(string spName, DynamicParameters p)
{
    SPResponse response = new SPResponse();
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
        conn.Open();
        using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
        {
            try
            {
                p.Add("@SP_MESSAGE", dbType: DbType.String, direction: ParameterDirection.Output, size: 4000);
                p.Add("@RETURNSTATUS", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
                await conn.ExecuteAsync(sql: spName, param: p, commandType: CommandType.StoredProcedure, transaction: transaction);
                response.ReturnMessage = p.Get<string>("@SP_MESSAGE");
                response.ReturnStatus = Convert.ToString(p.Get<int>("@RETURNSTATUS"));
                if (response.ReturnStatus == "0")
                {
                    response.Ref1 = Convert.ToString(p.Get<int>("@SP_ID"));
                    transaction.Commit();
                }
                else
                {
                    transaction.Rollback();
                }
            }
            catch (Exception ex)
            {
                Utils.Logger.Instance.LogException(ex);
                transaction.Rollback();
            }
            conn.Close();
        }
    }
    return response;
}

now on response.Ref1 = Convert.ToString(p.Get<int>("@SP_ID")); line in some of my procedure I am getting SP_ID as output parameter and in some I am not getting SP_ID as output parameter

but the problem is when I am not returning SP_ID as output parameter I am getting error of

The given key was not present in the dictionary.

I want to check the key before execution of p.get<int>()

how can I do this?

hmmm; that's odd - could you try setting `p.RemoveUnused = false;` ? although I didn't think this should apply for stored procs, so: I'm very interested to know if this helps; also - is anything else sharing this instance of `DynamicParameters`? (just wondering if this might be a concurrency problem) — Marc Gravell, Jan 23 '23 at 14:26
well, maybe, but from what I can see: you unconditionally add the `SP_MESSAGE` parameter, so I would expect it to still exist after the call; Dapper doesn't raise that message itself, so I must presume that it is no longer in the inner dictionary... (just for context: I wrote Dapper) — Marc Gravell, Jan 23 '23 at 14:30
`response.Ref1 = Convert.ToString(dp.Get("@SP_ID"));` I want to add a condition to check if I have SP_ID in my dynamic parameter as an output parameter — Ibrahim shaikh, Jan 23 '23 at 14:30
oh, SP_ID - then indeed I did misread it; it is curious that we didn't add a `ContainsKey` or similar - that's a failing: I should fix that. For now, I guess you'll need to track it yourself :( — Marc Gravell, Jan 23 '23 at 14:31
but: where do you ever add `SP_ID` ? I can't see it; is that in the calling code? — Marc Gravell, Jan 23 '23 at 14:31
Yes I actually fixed it, I declared a global variable assign the dynamic parameter in my common function and created function to return dynamic parameter and used that in my source code — Ibrahim shaikh, Jan 23 '23 at 14:33
@MarcGravell just out of context, this is the reason why I love stackoverflow, I can have a conversation with the best in the business. can you please look at my other question which you might give a good solution -> https://stackoverflow.com/questions/75199510/custom-dependency-injection-in-a-class-library-file-c-sharp — Ibrahim shaikh, Jan 23 '23 at 14:36

score 0 · Accepted Answer · answered Feb 01 '23 at 08:52

So I fixed this by myself and thanks to @MarcGravell.

I declared a parameter in my DapperClass where I am using common ExecuteAsync method.

private DynamicParameters _Param;
public DapperClass()
{
    _Param = new DynamicParameters();
}

now before transaction.Commit() line I am assigning the value to my parameter _Param = p;

and I created a public method with return type of DynamicParameters like below

public DynamicParameters GetDynamicParameters()
{
    return _Param;
}

and also added a code like below from where I am executing my common dapper class

SPResponse response = await _Dapper.ExecuteAsync("[dbo].[TemplateAdd]", _DynamicParameter);
if (response.ReturnStatus == "0")
{
    DynamicParameters dp = _Dapper.GetDynamicParameters();
    response.Ref1 = Convert.ToString(dp.Get<int>("@SP_ID"));
    response.Ref2 = request.FileServerId;
}

Dapper Dynamic Parameter wirth optional return value asp.net mvc

1 Answers1