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