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?

Answer 1

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;
}