asp.net-mvcdapperdynamicparameters

Dapper Dynamic Parameter wirth optional return value asp.net mvc


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?


Solution

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